pymysql handles escape characters correctly

Option 1: Use %s placeholder

This is also the officially recommended solution, with advantages:

• Do not need to pay attention to the question of whether to add quotation marks (automatically add quotation marks to string types, not to add quotation marks to numeric types)
• Different types of parameters can be automatically escaped (numbers, strings, bytes, etc.)

"Automatically add quotation marks to string types, not numeric types". The operation of adding quotation marks is a feature of the python language, but pymysql handles it for us, as explained later in the article

Sample code:

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, ('[e-mail]',))
        result = cursor.fetchone()
        print(result)

For more information about placeholder style, please refer to: pep249: paramstyle

Option 2: Manually call the escape method

Although placeholders are good, when you use f-string , format to splice strings, you have to deal with the problem of escaped characters manually!

First, let's see what processing has been done to the parameters behind cursor.execute

👇When calling the execute method, it will use mogrify to process the parameter args .

The result of mogrify's Google translation is: upgrade

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

👇When calling the mogrify method, the parameter args be processed _escape_args
We only discuss if isinstance(args, (tuple, list)): , which is also the most common way. After entering this condition, the literal method will be called to process each parameter

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

👇 when calling literal time method will be used escape method parameters arg (here a change of address: obj) for processing. Different types of parameters have different processing schemes. For the string type, the escape_string method will be used, the byte type will use the escape_bytes method ( _quote_bytes calls escape_bytes ), and the other types are the escape_item method.

So we can choose which method to call according to the parameter type to handle the escape character problem. Generally speaking, we only need to pay attention to the string.

The best way is that we directly call the escape method, so as not to deal with the data type problem escape , but the 061d53d20a1bef method is a class method, not directly exposed to me to call.
The escape_string , escape_bytes and escape_item methods are pymysql/converters.py and can be called directly.

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)

Therefore, when we need to manually escape, we can directly call the escape method.

As you can see from the escape method code, when the parameter is a string, the ' single quotation marks will be added before and after. This is the answer to the beginning of the article "Automatically quote string types, not numeric types Quotation mark" problem

if isinstance(obj, str):
   return "'" + self.escape_string(obj) + "'"

👇 Through the following code, we use the very pythonic f-string to process sql , but it should be noted that {} needs to add the outside quotes by ourselves

Sample code:

import pymysql.cursors
from pymysql.converters import escape_string
# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)


user = escape_string('[e-mail]')
password = escape_string('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()

Extension: Use placeholders to obtain complete sql statements at the same time

Suppose there are the following requirements: you need to obtain a complete sql statement and record it in the log, and you want to use placeholders to deal with the escape character problem, you can also use the above method to deal with it!