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

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 = 1
update_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 = 6
delete_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))

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