Python MySQL 使用预编译语句 执行参数化查询
一、事故缘起
今天构造了一个超过 50 多个参数的 SQL 插入语句，在执行的时候提示 Not all parameters were used in the SQL statement，提示「SQL 语句中未使用所有参数」的异常，但是前前后后检查了 SQL 语句，发现每个参数都是与相应的字段一一对应的，类似于下面这样的代码块：
mydb = mysql.connect(...)
cursor = mydb.cursor()
queries = [(...), (...), (...)] # 当然这几部分都是动态构造的
sql = "INSERT INTO `db`.`table`(`col01`, `col02`, `col03`) VALUES (%s, %s, %s)"
# 注意上条语句中 %s 和 Python 本身的字符串占位符重合，但其实不能混用
# 它可以用 ? 号代替，但是不能用 Python 的 %d, %f 等格式化符号代替
cursor.executemany(sql, queries)
cursor.close()
mydb.close()
在确定参数一个都没有少的情况下，开始猜测是系统截断了什么东西，然后在 StackOverflow 回答 的评论区找到了上述异常的解决方案，在声明数据库游标 cursor 的时候，直接加上参数 prepared=True 就搞定了，类似于下面的语句：
cursor = mydb.cursor(prepared=True)
接下来的文章就试着描述一下这个 prepared 预编译的作用吧，文章要点参考自 PyNative，加入了一些个人见解。
二、什么是参数化查询
参数化查询是在原有的查询语句中，用占位符填充各个参数，然后在执行的过程中，再将参数值传入的一种方法。这意味着参数化查询的被调用语句只被编译一次，但是可以重复传参。比如下面这条语句：
sql = "UPDATE `transaction` SET `quantity` = %s WHERE `customer_id` = %s"
在上述语句中，我们使用了 MySQL 中的 %s 占位符来传递参数值，当然这里用 ? 号或者关键词参数 %(customer_id)s 也是可以的，通过 execute(sql, data_tuple) 能够有效地防止 SQL 注入攻击，当然它有更多其他好处。
三、参数化查询的益处
参数化查询的特性主要有以下 4 点：
- 只编译一次：在默认的标准查询中，MySQL 在每次执行的时候都会编译一次语句。而在参数化查询中，被执行的查询语句只被编译一次，然后在内存中恭候传入参数进行调用，MySQL 能够直接执行预编译好的语句从而减少每次编译的时间；
- 加快执行速度：尤其是需要多次调用相同的 SQL 语句时，次数越多速度提升越明显，有点像正则表达式的 compile 预编译；
- 能够使用不同的数据执行相同的操作：假设你拥有几百行的数据要插入到数据库表中，使用参数化查询能够存入不同的字段值；
- 防止 SQL 注入攻击，有效提升数据安全。
四、如何使用参数化查询
我们使用 mysql.connection.cursor(prepared=True) 声明游标，就可以使用参数化查询执行预编译语句了，它本身是一个 MySQLCursorPrepared 类，继承自 MySQLCursor 类对象。
from mysql import connector
connection = connector.connect(
host="localhost",
port="3306",
user="MoonYear530",
password="StanleyBlog",
database="query_test"
)
cursor = connection.cursor(prepared=True)
# 上一条语句也可以拟写如下：
# cursor = connection.cursor(cursor_class=MySQLCursorPrepared)
这样，在接下来的每一次调用中，传入的参数虽然不同，但是在准备执行时发现 SQL 语句是一样的时候，就会跳过编译了。
五、使用参数化查询更新数据的示例
from mysql import connector
from mysql.connector import Error
try:
connection = connector.connect(
host="localhost",
port="3306",
user="MoonYear530",
password="StanleyBlog",
database="query_test"
) # 创建数据库连接
cursor = connection.cursor(prepared=True) # 声明数据游标
sql = """UPDATE `transaction`
SET `update_date` = %s, `quantity` = %s
WHERE `customer_id` = %s""" # 被调用 SQL 语句
data_tuple = ("2020-09-20", 16530, "SZ0198") # 传入的参数元组
cursor.execute(sql, data_tuple) # 执行 SQL 语句
connection.commit() # 提交事务
print("恭喜，数据更新成功！")
except Error as error:
connection.rollback() # 执行失败，数据回滚
print(f"参数化查询执行异常：{error}")
finally:
if connection.is_connected():
cursor.close() # 关闭数据游标
connection.close() # 关闭数据库连接
print("老铁，数据库连接已关闭。")
以上代码只是一个简短的示例，需要按照工程内容适当修改，祝一切顺利，代码无 Bug，身体倍棒，吃嘛嘛香~
