mysql查询

mmvthczy 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(362)

我正在学习python编程，并尝试从简单的select查询开始实现最安全的mysql查询。问题是，每当我在查询中使用coma时，都会出现以下错误：

cursor.execute(query)
  File "C:\Users\username\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 536, in execute
    stmt = operation.encode(self._connection.python_charset)
AttributeError: 'tuple' object has no attribute 'encode'

我知道coma本身并不是问题的根源，但我尝试了许多不同的mysql语法，每次使用come，我都会遇到“attributeerror:'tuple'object has no attribute'encode'”错误。
我还尝试更改mysql数据库编码-没有任何更改。代码如下。

import mysql.connector
conn = mysql.connector.connect(
    charset='utf8',
    # init_command='SET NAMES UTF8',
    host="10.0.0.234",
    user="x",
    passwd="x>",
    database="x",
)
print(conn.is_connected())
param = "test"
cursor = conn.cursor()
# =========== query below does work ========
# query = ("SELECT * from list WHERE username LIKE '%test%'")
# ============ query below does work =======
# query = ("SELECT * from list HAVING username = '%s'" % param)
# ============ query below doesn't work =====
# query = ("SELECT * from list HAVING username = %s", (param,))
# ============= query below doesn't work =====
query = "SELECT * from list WHERE username = :name", {'name': param}
cursor.execute(query)
result = cursor.fetchall()
for x in result:
    print(x)
conn.close()

你知道我做错什么了吗？

mysql python

来源：https://stackoverflow.com/questions/53960082/python-mysql-queries-with-parametres-and-comas

1条答案

按热度按时间

xqk2d5yq1#

答案有点棘手，但本质上是因为“query”变量的实际值是什么。。。
例如：


# 1.
query = ("SELECT * from list WHERE username LIKE '%test%'")
# when you do this, query is a string variable,
# NB: the parentheses are not necessary here
# so when you call
cursor.execute(query)
# the value passed into the execute call is the string "SELECT * from list WHERE username LIKE '%test%'"
# 2.
query = ("SELECT * from list HAVING username = '%s'" % param)
# when you do this, query is the result of a string formatting operation
# This is a Python 2 form of string formatting
# The discussion here probably makes it more clear:
# https://stackoverflow.com/questions/13945749/string-formatting-in-python-3
# it is almost the same as doing this:
query = "SELECT * from list HAVING username = 'test'"
# so when you call
cursor.execute(query)
# the value passed into the execute call is the string "SELECT * from list HAVING username = 'test'"
# 3.
query = ("SELECT * from list HAVING username = %s", (param,))
# This operation is assigning a 2-value tuple into the query variable
# The first value in the tuple is the string "SELECT * from list HAVING username = %s"
# The second value in the tuple is a 1-value, with 'test' as its first value
# 4.
query = "SELECT * from list WHERE username = :name", {'name': param}
# This is similar to #3, but the values in the tuple are instead
# query[0] == "SELECT * from list WHERE username = :name"
# query[1] is a dictionary: {'name': param}

上面的3和4都没有调用mysql execute 使用您期望的参数（请参阅此处的api）。您可能需要执行以下操作之一：
将查询元组解压到单独的变量中，并用它们调用函数

operation, params = query # unpack the first elem into operation, and second into params
cursor.execute(operation, params)

只需索引到查询元组中

cursor.execute(query[0], query[1])
# NB: you could also use the 'named parameters' feature in Python
cursor.execute(query[0], params=query[1])

使用“解包参数列表”（splat运算符）

cursor.execute(*query)

展开查看全部

赞(0）回复(0）举报 2021-06-17

我来回答

mysql查询

1条答案

相关问题

热门标签

最新问答