带有 SQL 通配符和 LIKE 的 Python 字符串格式
问题描述
我很难让 python 中的一些 sql 正确地通过 MySQLdb.是 pythons 字符串格式让我很生气.
I'm having a hard time getting some sql in python to correctly go through MySQLdb. It's pythons string formatting that is killing me.
我的 sql 语句使用带通配符的 LIKE 关键字.我在 Python 中尝试了许多不同的东西.问题是，一旦我让其中一个工作起来，MySQLdb 中有一行代码会在字符串格式上打嗝.
My sql statement is using the LIKE keyword with wildcards. I've tried a number of different things in Python. The problem is once I get one of them working, there's a line of code in MySQLdb that burps on string format.
尝试 1:
"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOINtag ON user.id = tag.userId WHERE user.username LIKE '%%s%'" % (query)
"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '%%s%'" % (query)
这是不行的.我得到值错误:
This is a no go. I get value error:
ValueError: 索引 128 处不支持的格式字符 ''' (0x27)
ValueError: unsupported format character ''' (0x27) at index 128
尝试 2:
"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOINtag ON user.id = tag.userId WHERE user.username LIKE '\%%s\%'" %(查询)
"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '\%%s\%'" % (query)
我从尝试 1 中得到相同的结果.
I get the same result from attempt 1.
尝试 3:
like = "LIKE '%" + str(query) + "%'" totalq = "SELECT tag.userId,count(user.id) as totalRows FROM user INNER JOIN tag ON user.id =tag.userId WHERE user.username " + like
like = "LIKE '%" + str(query) + "%'" totalq = "SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username " + like
这会正确创建 totalq 变量，但现在当我运行查询时，我从 MySQLdb 收到错误:
This correctly creates the totalq variable, but now when I go to run the query I get errors from MySQLdb:
文件build/bdist.macosx-10.6-universal/egg/MySQLdb/cursors.py"，行158、在执行query = query % db.literal(args) TypeError: not enough格式字符串的参数
File "build/bdist.macosx-10.6-universal/egg/MySQLdb/cursors.py", line 158, in execute query = query % db.literal(args) TypeError: not enough arguments for format string
尝试 4:
like = "LIKE '\%" + str(query) + "\%'" totalq = "SELECT tag.userId,count(user.id) as totalRows FROM user INNER JOIN tag ON user.id =tag.userId WHERE user.username " + like
like = "LIKE '\%" + str(query) + "\%'" totalq = "SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username " + like
这与尝试 3 的输出相同.
This is the same output as attempt 3.
这一切看起来真的很奇怪.python 如何在sql语句中使用通配符?
This all seems really strange. How can I use wildcards in sql statements with python?
解决方案
问题不是字符串格式化，而是如何根据 Python 中的 db 操作要求执行查询(PEP 249)
It's not about string formatting but the problem is how queries should be executed according to db operations requirements in Python (PEP 249)
试试这样的:
sql = "SELECT column FROM table WHERE col1=%s AND col2=%s"
params = (col1_value, col2_value)
cursor.execute(sql, params)
这里有一些 psycog2 的例子你有一些对mysql也应该有效的解释(mysqldb也遵循PEP249 dba api guide 2.0:这里是mysqldb的例子)
here are some examples for psycog2 where you have some explanations that should also be valid for mysql (mysqldb also follows PEP249 dba api guidance 2.0: here are examples for mysqldb)
相关文章