python 连接mysql

本文为参考其他大佬的博客及视频后所做的笔记，如有侵权，请告知删除。原文链接：

参考博客：https://blog.youkuaiyun.com/hzw6991/article/details/87893761

参考视频：https://www.bilibili.com/video/av44630247?from=search

目录

数据库连接基础：

在数据库中创建表格

在表格中插入数据

查询表格数据

更新

删除数据

删除表格（DROP Table）

---

数据库连接基础：

（确保连接成功）

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '******' #你自己的密码
DBNAME = 'mydb'  #所要连接的数据库

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
except pymysql.Error as e:
    print("Error connect:"+str(e))

在数据库中创建表格

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '111111111'
DBNAME = 'mydb'

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
    cur = db.cursor() #创建游标
    cur.execute("drop table if exists Student")
    sql = 'create table student(Name char(20) not null, email char(20) , age int)'
    cur.execute(sql)
    print('表格创建成功')
except pymysql.Error as e:
    print("Error connect:"+str(e))

在表格中插入数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '111111111'
DBNAME = 'mydb'

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
    cur = db.cursor() #创建游标
    sql = 'insert into Student(Name,email, age) value (%s,%s,%s)'
    value = ('Mike','123@123.com',20)
    cur.execute(sql,value)
    db.commit()
    print('数据插入成功')
except pymysql.Error as e:
    print("Error Insert:"+str(e))
    db.rollback()

db.close()

查询表格数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '111111111'
DBNAME = 'mydb'

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
    cur = db.cursor() #创建游标
    sql = 'select * from Student'
    cur.execute(sql)
    results = cur.fetchall() #获取查询列表
    for row in results:
        name = row[0]
        email = row[1]
        age = row[2]
        print('Name:%s, Email:%s, Age:%s'%(name,email,age))
except pymysql.Error as e:
    print("Error query:"+str(e))
    

db.close()

更新

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '11111111'
DBNAME = 'mydb'

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
    cur = db.cursor() #创建游标
    sql = 'update Student set Name = %s where Name = %s'
    value = ('John','Mike')
    cur.execute(sql,value)
    db.commit()
    print('数据更新成功！')
except pymysql.Error as e:
    print("Error update:"+str(e))
    db.rollback()

db.close()

删除数据

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '1111111'
DBNAME = 'mydb'

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
    cur = db.cursor() #创建游标
    sql = 'delete from Student where Name = %s'
    value = ('John')
    cur.execute(sql,value)
    db.commit()
    print('数据删除成功！')
except pymysql.Error as e:
    print("Error delete:"+str(e))
    db.rollback()

db.close()

删除表格（DROP Table）

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '11111111'
DBNAME = 'mydb'

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('connect success!')
    cur = db.cursor() #创建游标
    sql = 'drop table if EXISTS Student'
    cur.execute(sql)
    print('表格删除成功！')
except pymysql.Error as e:
    print("Error drop:"+str(e))

db.close()