問題描述

我很難讓 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)

這篇關于帶有 SQL 通配符和 LIKE 的 Python 字符串格式的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！