Python - 使用 pymysql 操作 MySQL 详解

简单使用：

import pymysqldb = pymysql.connect(host='',user='',password='',database='')
cursor = db.cursor()
try:cursor.execute("SELECT * FROM employees LIMIT 200")results = cursor.fetchall()print(results)
except Exception as e:print("查询失败" + str(e))

创建连接 pymsql.connect() 方法的可传参数

pymsql.connect()的可传参数：

参数	说明
user	数据库用户名
password	数据库用户密码
host	数据库连接地址
database	要连接的数据库名称
port	端口号，默认3306
charset	要连接的数据库的字符编码（可以在终端登陆mysql后使用 \s 查看），默认""
connect_timeout	连接数据库的超时时间，(默认值：10，最小值：1，最大值：31536000)
unix_socket	可以选择使用unix套接字而不是TCP/IP，默认None
sql_mode	要使用的默认sql_mode，默认None
read_default_file	指定my.cnf文件以从[client]部分下读取这些参数，默认None
conv	要使用的转换词典，而不是默认词典，默认None
use_unicode	是否默认为unicode字符串，默认True
client_flag	要发送到MySQL的自定义标志。在常量.CLIENT中查找潜在值，默认0
cursorclass	要使用的自定义游标类，默认Cursor
init_command	建立连接时要运行的初始SQL语句，默认None
read_default_group	配置文件中要读取的组，默认None
autocommit	自动提交模式。无表示使用服务器默认值。（默认值：False）
local_infile	布尔值以启用LOAD DATA local命令。（默认值：False）
max_allowed_packet	发送到服务器的数据包的最大大小（字节）。（默认值：16MB）。仅用于限制“LOAD LOCAL INFILE”数据包的大小小于默认值（16KB）
defer_connect	在构造时不显式连接-等待连接调用。（默认值：False）
auth_plugin_map	处理插件的类的插件名称字典，默认None
read_timeout	读取超时时间，默认None
write_timeout	写入超时时间，默认None
bind_address	当客户端具有多个网络接口时，请指定连接到主机的接口。参数可以是主机名或IP地址，默认None
binary_prefix	在字节和字节数组上添加二进制前缀，默认False
program_name	程序名称，默认None
server_public_key	SHA256身份验证插件公钥值。，默认None
ssl	类似于mysql_ssl_set()参数的参数字典，默认None
ssl_ca	包含PEM格式ca证书的文件路径，默认None
ssl_cert	包含PEM格式客户端证书的文件的路径，默认None
ssl_disabled	禁用TLS的布尔值，默认None
ssl_key	包含客户端证书的PEM格式私钥的文件路径，默认None
ssl_verify_cert	设置为true以检查服务器证书的有效性，默认None
ssl_verify_identity	设置为true以检查服务器的标识，默认None
compress	不支持
named_pipe	不支持
passwd	已弃用，数据库的别名
db	已弃用，密码别名

连接对象 conn = pymsql.connect() 方法

方法	说明
conn.cursor()	创建并返回游标
conn.commit()	提交当前事务
conn.rollback()	回滚当前事务
conn.close()	关闭connection

游标对象 cursor() 方法

方法	说明
execute()	用于执行一个数据库的查询命令
executemany(query, args)	针对一个查询运行多个数据
fetchone()	获取结果集的下一行
fetchmany(size)	获取结果集中的size行数据
fetchall()	获取结果集中的所有行
rowcount	最近一次execute返回数据/影响的行数
close()	关闭游标

使用示例

创建数据库表

import pymysql# 打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='',database='laomao')# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")# 使用预处理语句创建表
sql = """CREATE TABLE `employees` (`employee_id`       int primary key auto_increment,`employee_name`     varchar(20) DEFAULT NULL,`email`             varchar(25) NOT NULL,`birthday`          datetime    null) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""cursor.execute(sql)# 关闭数据库连接
db.close()

插入数据操作

import pymysql# 打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='',database='laomao')# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()# SQL 插入语句
sql = """INSERT INTO ssm.employees (employee_name, email, birthday)VALUES ('乐昌','[e-mail]','2008-08-18 00:00:00'),('老猫','[e-mail]','2020-02-22 06:06:06')"""try:# 执行sql语句cursor.execute(sql)# 提交到数据库执行db.commit()
except:# 如果发生错误则回滚db.rollback()
# 关闭数据库连接
db.close()

数据查询操作

import pymysql# 打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='',database='laomao')# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()# SQL 查询语句
sql = """SELECT * FROM employees LIMIT 200"""try:# 执行SQL语句cursor.execute(sql)# 获取所有记录列表results = cursor.fetchall()for row in results:employee_id = row[0]name = row[1]email = row[2]birthday = row[3]# 打印结果print("employee_id=%s, name=%s, email=%s, birthday=%s" % (employee_id, name, email, birthday))
except:print("Error: unable to fetch data")# 关闭数据库连接
db.close()# 输出
employee_id=1, name=乐昌, email=[e-mail], birthday=2008-08-18 00:00:00
employee_id=2, name=老猫, email=[e-mail], birthday=2020-02-22 06:06:06

数据更新操作

import pymysql# 打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='',database='laomao')# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()# SQL 更新语句
sql = """UPDATE ssm.employees SET employee_name = '彦祖' WHERE employee_id = 1"""try:# 执行SQL语句cursor.execute(sql)# 提交到数据库执行db.commit()
except:# 发生错误时回滚db.rollback()# 关闭数据库连接
db.close()

数据删除操作

import pymysql# 打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='',database='laomao')# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()# SQL 删除语句
sql = """DELETE FROM employees WHERE employee_id = 2"""try:# 执行SQL语句cursor.execute(sql)# 提交到数据库执行db.commit()
except:# 发生错误时回滚db.rollback()# 关闭数据库连接
db.close()

SQL中使用变量

birthday = "2008-08-18 00:00:00"sql = "SELECT * FROM employees WHERE birthday = " + "'" + birthday + "' LIMIT 10"
print(sql)# 打印
SELECT * FROM employees WHERE birthday =  '2008-08-18 00:00:00' LIMIT 10

封装使用

#!/usr/bin/python3import pymysqldef conn():'''连接MySQL数据库'''try:conn = pymysql.connect(host='',user='',password='',database='')conn.set_charset('utf8')return connexcept Exception:raise Exception("数据库连接失败")def query(sql):print('sql:'+sql)db = conn()cursor = db.cursor()try:cursor.execute(sql)result = cursor.fetchall()if result: return resultreturn ["none"]except Exception as e:db.rollback()print("查询失败," + str(e))finally:cursor.close()db.close()
if __name__ == '__main__':select_data = query("SELECT * FROM tb_name LIMIT 20")print(select_data)