共计 3058 个字符,预计需要花费 8 分钟才能阅读完成。
计划一:应用 %s
占位符
这也是官网举荐的计划,长处是不须要本人关注需不需要加引号的问题(主动对字符串类型加引号,不会对数字类型加引号)
“ 主动对字符串类型加引号,不会对数字类型加引号 ”,加引号这个操作是 python 语言的个性,而是 pymysql 帮咱们解决的,文后有解释
示例代码:
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
database='db',
cursorclass=pymysql.cursors.DictCursor)
with connection:
with connection.cursor() as cursor:
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
对于占位符格调的更多内容请参考:pep249:paramstyle
计划二:手动调用 escape 办法
让咱们先通过源码来看看 cursor.execute
背地都对参数参数做了什么加工!
👇当调用 execute
办法的时候,会应用 mogrify
对参数 args
进行加工。
mogrify 通过谷歌翻译的后果是:降级
pymysql/cursors.py
def execute(self, query, args=None):
"""Execute a query
:param str query: Query to execute.
:param args: parameters used with query. (optional)
:type args: tuple, list or dict
:return: Number of affected rows
:rtype: int
If args is a list or tuple, %s can be used as a placeholder in the query.
If args is a dict, %(name)s can be used as a placeholder in the query.
"""
while self.nextset():
pass
query = self.mogrify(query, args)
result = self._query(query)
self._executed = query
return result
👇当调用 mogrify
办法的时候,会应用 _escape_args
对参数 args
进行加工。
咱们只探讨 if isinstance(args, (tuple, list)):
这种条件,这也是最罕用的形式,进入该条件之后,会调用 literal
办法来对每个参数进行加工
pymysql/cursors.py
def _escape_args(self, args, conn):
if isinstance(args, (tuple, list)):
return tuple(conn.literal(arg) for arg in args)
elif isinstance(args, dict):
return {key: conn.literal(val) for (key, val) in args.items()}
else:
# If it's not a dictionary let's try escaping it anyways.
# Worst case it will throw a Value error
return conn.escape(args)
def mogrify(self, query, args=None):
"""
Returns the exact string that is sent to the database by calling the
execute() method.
This method follows the extension to the DB API 2.0 followed by Psycopg.
"""
conn = self._get_db()
if args is not None:
query = query % self._escape_args(args, conn)
return query
👇当调用 literal
办法的时候,会应用 escape
对参数 arg
(此处换了一个称说:obj)进行加工。pymysql/connections.py
def escape(self, obj, mapping=None):
"""Escape whatever value you pass to it.
Non-standard, for internal use; do not use this in your applications.
"""
if isinstance(obj, str):
return "'"+ self.escape_string(obj) +"'"
if isinstance(obj, (bytes, bytearray)):
ret = self._quote_bytes(obj)
if self._binary_prefix:
ret = "_binary" + ret
return ret
return converters.escape_item(obj, self.charset, mapping=mapping)
def literal(self, obj):
"""Alias for escape()
Non-standard, for internal use; do not use this in your applications.
"""
return self.escape(obj, self.encoders)
所以,当咱们须要手动本义的时候,就能够间接调用 escape
办法。
从 escape 办法代码中能够看到,当参数是字符串的时候,就会在前后加上
'
单引号,这也就是答复了文章结尾那个 “ 主动对字符串类型加引号,不会对数字类型加引号 ” 问题if isinstance(obj, str): return "'"+ self.escape_string(obj) +"'"
👇 通过上面的代码,咱们就通过应用十分 pythonic 的 f-string
来解决 sql
了,然而须要留神的是 {}
须要本人增加里面的引号了
示例代码:
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
database='db',
cursorclass=pymysql.cursors.DictCursor)
user = 'webmaster@python.org'
password = 'very-secret'
with connection:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES ('{user}','{password}')"
cursor.execute(sql)
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
扩大:应用占位符的同时获取残缺的 sql 语句