python - 如何在Python中将字符串值调用到sql查询中

Question

我试图从容器tn(用户给出的输入)调用字符串值来完成sql查询

# lists is a list in which all table names in the database is stored
ct=0
while ct!=2:
    print("1.Enter the table name you want access")
    print("2.Exit")
    ct=int(input("Enter your choice (1 or 2) :"))
    if ct==1:
        tn=input("Enter table name :")
        break
    elif ct==2:
        sys.exit(0)
    else:
        print("INVALID INPUT")
if tn in lists:
    print()
    print("Table found!")
    mycursor.execute("SELECT * FROM (%s)",(tn,))
for i in mycursor:
    print(i) 

我希望 SQL 查询完成，它会执行并向我显示结果(即应该显示用户输入的表)

我在运行代码时收到此错误消息:-

Traceback (most recent call last):
  File "C:\Users\11c1.System2-PC\Desktop\MYSQL\Mysql (Software).py", line 59, in <module>
    mycursor.execute("SELECT * FROM (%s)",(tn,))
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''11c')' at line 1

假设“11c”是用户给出的输入，并且数据库中确实存在名为 11c 的表

当我尝试使用 ? 相同的代码时，例如:-

ct=0
while ct!=2:
    print("1.Enter the table name you want access")
    print("2.Exit")
    ct=int(input("Enter your choice (1 or 2) :"))
    if ct==1:
        tn=input("Enter table name :")
        break
    elif ct==2:
        sys.exit(0)
    else:
        print("INVALID INPUT")
if tn in lists:
    print()
    print("Table found!")
    mycursor.execute("SELECT * FROM (?)",(tn,))
for i in mycursor:
    print(i)

这给出的错误为:-

Traceback (most recent call last):
  File "C:\Users\11c1.System2-PC\Desktop\MYSQL\Mysql (Software).py", line 59, in <module>
    mycursor.execute("SELECT * FROM (?)",(tn,))
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 543, in execute
    "Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Answer 1

尝试下面的查询语法:

query= "SELECT * FROM %s"
mycursor.execute(query%(tn,))

希望这有帮助!