Python字符串格式与SQL通配符和LIKE [英] Python String Formats with SQL Wildcards and LIKE

问题描述

我很难在python中获得一些sql来正确地通过MySQLdb。这是pythons字符串格式化正在杀死我。

我的sql语句使用LIKE关键字和通配符。我已经在Python中尝试了许多不同的东西。问题是一旦我得到其中一个工作，在MySQLdb中有一行代码字符串格式。

尝试1：

SELECT tag.userId，count（user.id）as totalRows FROM user INNER JOIN
tag ON user.id = tag.userId WHERE user.username LIKE '%% s％'％（query）

这是不行的。
$ b

ValueError：索引128处不支持的格式字符'''（0x27）

尝试2：

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相同。

尝试3：

lockquote
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中得到错误：
$ b

文件build / bdist。 macosx-10.6-universal / egg / MySQLdb / cursors.py，行
158，执行query = qu red％db.literal（args）TypeError：没有足够的格式字符串
参数

尝试4：

$ b

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相同。

这一切似乎都很奇怪。如何在Python语句中使用通配符在sql语句中？

解决方案

这不是关于字符串格式，而是问题是如何执行查询根据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指导2.0：这里是mysqldb的例子）

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.

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.

Attempt 1:

"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: unsupported format character ''' (0x27) at index 128

Attempt 2:

"SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '\%%s\%'" % (query)

I get the same result from attempt 1.

Attempt 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

This correctly creates the totalq variable, but now when I go to run the query I get errors from MySQLdb:

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

Attempt 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

This is the same output as attempt 3.

This all seems really strange. How can I use wildcards in sql statements with python?

解决方案

It's not about string formatting but the problem is how queries should be executed according to db operations requirements in Python (PEP 249)

try something like this:

sql = "SELECT column FROM table WHERE col1=%s AND col2=%s" 
params = (col1_value, col2_value)
cursor.execute(sql, params)

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)

这篇关于Python字符串格式与SQL通配符和LIKE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！