Python 3 进阶 —— 使用 PyMySQL 操作 MySQL
PyMySQL 是一个纯 Python 实现的 MySQL 客户端操作库，支持事务、存储过程、批量执行等。
PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
安装 pip install PyMySQL 创建数据库连接 import pymysql connection = pymysql.connect(host='localhost', port=3306, user='root', password='root', db='demo', charset='utf8')参数列表：
参数 描述host 数据库服务器地址，默认 localhost
user 用户名，默认为当前程序运行用户
password 登录密码，默认为空字符串
database 默认操作的数据库
port 数据库端口，默认为 3306
bind_address 当客户端有多个网络接口时，指定连接到主机的接口。参数可以是主机名或IP地址。
unix_socket unix 套接字地址，区别于 host 连接
read_timeout 读取数据超时时间，单位秒，默认无限制
write_timeout 写入数据超时时间，单位秒，默认无限制
charset 数据库编码
sql_mode 指定默认的 SQL_MODE
read_default_file Specifies my.cnf file to read these parameters from under the [client] section.
conv Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshaling of types.
use_unicode Whether or not to default to unicode strings. This option defaults to true for Py3k.
client_flag Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass 设置默认的游标类型
init_command 当连接建立完成之后执行的初始化 SQL 语句
connect_timeout 连接超时时间，默认 10，最小 1，最大 31536000
ssl A dict of arguments similar to mysql_ssl_set()'s parameters. For now the capath and cipher arguments are not supported.
read_default_group Group to read from in the configuration file.
compress Not supported
named_pipe Not supported
autocommit 是否自动提交，默认不自动提交，参数值为 None 表示以服务器为准
local_infile Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet 发送给服务器的最大数据量，默认为 16MB
defer_connect 是否惰性连接，默认为立即连接
auth_plugin_map A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental)
server_public_key SHA256 authenticaiton plugin public key value. (default: None)
db 参数 database 的别名
passwd 参数 password 的别名
binary_prefix Add _binary prefix on bytes and bytearray. (default: False)
执行 SQL
cursor.execute(sql, args) 执行单条 SQL
# 获取游标 cursor = connection.cursor() # 创建数据表 effect_row = cursor.execute(''' CREATE TABLE `users` ( `name` varchar(32) NOT NULL, `age` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ''') # 插入数据(元组或列表) effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18)) # 插入数据(字典) info = {'name': 'fake', 'age': 15} effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info) connection.commit()
executemany(sql, args) 批量执行 SQL
# 获取游标 cursor = connection.cursor() # 批量插入 effect_row = cursor.executemany( 'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [ ('hello', 13), ('fake', 28), ]) connection.commit()