python2.7 - python 如何执行mysql单个参数过滤

Question

使用python执行mysql，报错了：

name = "AAA'A"
cursor.execute('select * from tb where name=%s',name)
cursor.execute('select * from tb where name=%s',(name))

都会报错

query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

但是以下不会报错：

name = "AAA'A"
cursor.execute('select * from tb where name=%s and %s',(name,1))

python27 如何过滤mysql 单个参数

Answer 1

Since the questioner did not mention which library is used to connect to the database, it is assumed that you are using the source code of mysqldb。
可以看一下mysqldb:

...
def execute(self, query, args=None):
    """
    ...
    args -- optional sequence or mapping, parameters to use with query.
    ...
    """
    if args is not None:
        # 首先判断args是否为字典类型
        if isinstance(args, dict):
            # 以k-v形式填入查询语句中。
            query = query % dict((key, db.literal(item))
                                 for key, item in args.iteritems())
        # 当args为非字典类型时
        else:
            # 遍历args, 最后生成一个元组填入查询语句中。
            query = query % tuple([db.literal(item) for item in args])
    ...

You can see that the args parameter is an optional sequence or mapping, that is, the expected type of the args parameter is list或者tuple.
Then look back at the input parameters you gave:

>>> name = 'test'
>>> type(name)
<type 'str'>
>>> type((name))
<type 'str'>
>>> type(('name', 1))
<type 'tuple'>

So, the solution is simple:

>>> type((name, ))
<type 'tuple'>
>>> cursor.execute('select * from tb where name=%s',(name, ))
1L

This involves a small detail.
When creating a tuple with only one element, you need to add a comma, otherwise the interpreter will create it as a string.

Answer 2

cursor.execute('select * from tb where name="%s"',name)