Python3简单操作MySQL（增删改查）

来源：互联网发布：java web和前端开发编辑：程序博客网时间：2024/06/11 11:27

Python3使用pymysql操作MySQL数据库
操作时遇到的“难点”
①查询时的中文编码问题
②数据库的增删改查，提交到数据库执行

import pymysql#导入pymysql库con = pymysql.connect(host='localhost',user='root',password='572939586',port=3306,db='db_dictionary',charset='utf8')#注意一定添加charset='utf8',且不是utf-8，解决查询时的中文编码问题cursor = con.cursor()#创建表cursor.execute('DROP TABLE IF EXISTS tb_user')sql = """CREATE TABLE tb_user(id INT(10) PRIMARY KEY AUTO_INCREMENT UNIQUE NOT NULL,user_name VARCHAR(10),user_desc VARCHAR(50) NOT NULL)"""#插入数据cursor.execute(sql)insert_sql = """INSERT INTO tb_user values                (null,'詹姆斯','NBA'),                (null,'科比','NBA'),                (null,'奥尼尔','NBA'),                (null,'乔丹','NBA'),                (null,'林书豪','NBA'),                (null,'杜兰特','NBA')"""try:    cursor.execute(insert_sql)#执行sql语句    con.commit()#提交到数据库执行except:    con.rollback()#错误回滚#更新数据update_user_id = 1update_sql = """UPDATE tb_user SET user_desc='CBA' WHERE id='%s'"""%(update_user_id)try:    cursor.execute(update_sql)    con.commit()except:    con.rollback()#删除数据delete_user_id = 6delete_sql = """DELETE FROM tb_user WHERE id='%s'"""%(delete_user_id)try:    cursor.execute(delete_sql)    con.commit()except:    con.rollback()#查询数据cursor.execute('SELECT * FROM tb_user')rows = cursor.fetchall()for row in rows:    name = row[1]    desc = row[2]    print('name=%s,desc=%s'%(name,desc))

注：在网上参考了很多大神的博客，记录下来以后自己看着方便，希望与大家一起学习交流。

1 0