python - 格式说明符未被从 python 传递到 mysql 的选择查询中的值替换

Question

我尝试从 python 查询 sql 数据库。当我直接在字符串中对过滤器的值进行编码并将 i 作为查询传递时，查询是正确的。但是，当我使用“%s”等格式说明符时，我遇到了问题。

代码如下

[import tkinter
import tkinter.messagebox
from tkinter import *
import mysql.connector

top = tkinter.Tk()

#database connecion
from mysql.connector import errorcode
try:
    cnx =mysql.connector.connect(host='localhost',user='root',password='1234')
    cur = cnx.cursor()
    query="CREATE DATABASE IF NOT EXISTS prj_db"
    cur.execute(query)
    cnx =mysql.connector.connect(host='localhost',database='prj_db',user='root',password='1234')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("no db")
  else:
    print(err)
else:
    cur = cnx.cursor()  
    query = ("CREATE TABLE IF NOT EXISTS product_det_2"
            "(P_id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,"
            "P_name CHAR(20) DEFAULT '' NOT NULL,"
            "Drawing_num CHAR(20) DEFAULT '' NOT NULL,"
            "Price INT(4) UNSIGNED ZEROFILL NOT NULL,"
            "PRIMARY KEY(P_id))")
    cur.execute(query)
query = ("SELECT * FROM product_det_2")
cur.execute(query)
print(query)
for (P_id, P_name, Drawing_num,Price) in cur:
      print("{}, {}, {},{}".format(P_id, P_name, Drawing_num,Price))
print("*********************************")
query = ("SELECT * FROM product_det_2 WHERE P_id=2")
print(query)
cur.execute(query)
for (P_id, P_name, Drawing_num,Price) in cur:
      print("{}, {}, {},{}".format(P_id, P_name, Drawing_num,Price))
print("*********************************")
a="2"
query = ("SELECT * FROM product_det_2 WHERE P_id=%s")
print(query)
cur.execute(query,a)
for (P_id, P_name, Drawing_num,Price) in cur:
      print("{}, {}, {},{}".format(P_id, P_name, Drawing_num,Price))
cnx.commit()
top.mainloop()][1]

这里如果我给出 %d 作为格式说明符并且 a=2 即使这样我也会遇到错误。请让我知道我哪里出了问题enter image description here

Answer 1

cursor.execute() 的第二个参数应该是一个元组或字典。即使只有一个参数要插入到查询中，这也适用。试试这个:

cur.execute(query, (a,))

此处包含单个元素的元组被传递给 execute()。