python通过pymysql连接mysql

首先安装pymysql 模块，可以通过 pip 安装

在进行mysql操作前，先设置MySQL服务器地址，MySQL服务器端口号，用户名，密码，数据库名称。

import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'datest'

建立连接

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

创建一张表

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('数据库连接OK')
    cur = db.cursor()
    cur.execute('DROP TABLE IF EXISTS Student')
    sqlQuery = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age int )"
    cur.execute(sqlQuery)
    print("表格创建OK")
except pymysql.Error as e:
    print("NO "+str(e))

向表插入一条数据

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('数据库连接OK')
    cur = db.cursor()
    sqlQuery = 'INSERT INTO Student (Name, Email, Age) VALUE (%s,%s,%s)'
    value = ('MIN','12@123',19)
    cur.execute(sqlQuery,value)
    db.commit()
    print("数据插入OK")
except pymysql.Error as e:
    print("NO "+str(e))
    db.rollback()

db.close()

查询表中的数据

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('数据库连接OK')
    cur = db.cursor()
    sqls = 'SELECT * FROM Student'
    cur.execute(sqls)
    result = cur.fetchall()
    for row in result:
        name = row[0]
        email = row[1]
        age = row[2]
        print("%s %s %s"%(name,email,age))
    print("数据查询OK")
except pymysql.Error as e:
    print("NO "+str(e))

db.close()

更新表中的数据

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('数据库连接OK')
    cur = db.cursor()
    sqls = 'UPDATE Student SET Name= %s WHERE Name=%s'
    values = ('LIN', 'MIN')
    cur.execute(sqls,values)
    db.commit()

    print("数据更新OK")
except pymysql.Error as e:
    print("NO "+str(e))
    db.rollback()

db.close()

删除表中的数据

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('数据库连接OK')
    cur = db.cursor()
    sqls = 'DELETE FROM Student where Name=%s'
    values = ('LIN')
    cur.execute(sqls,values)
    db.commit()

    print("数据删除OK")
except pymysql.Error as e:
    print("NO "+str(e))
    db.rollback()

db.close()

删除一张表

try:
    db = pymysql.connect(DBHOST,DBUSER,DBPASS,DBNAME)
    print('数据库连接OK')
    cur = db.cursor()
    sqls = 'DROP TABLE IF EXISTS Student'

    cur.execute(sqls)

    print("表格删除OK")
except pymysql.Error as e:
    print("NO "+str(e))
    db.rollback()

db.close()

结束的时候最好加上一句db.close()关闭连接