计划一：应用 %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 语句