Mysql表名得到不需要的引号导致表不存在错误

Question

import mysql.connector
def add_features_to_db(stockname, timeframe, date, feature):
try:
conn = mysql.connector.connect(
user='root', password='', host='localhost', database='fx003')
cursor = conn.cursor()
dbtable = stockname + timeframe
mySql_insert_query = """INSERT INTO `%s` (date, trend) VALUES ( `%s`, `%s` )""" 
record = (dbtable, date, feature) 
cursor.execute(mySql_insert_query, record)
conn.commit()

print("Record inserted successfully")
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if conn.is_connected():
cursor.close()
conn.close()
print("MySQL connection is closed")
add_features_to_db("aud-cad", "_30mins", "2021-09-24 21:00:00", "Short")

我有上面的代码，给了我下面的错误:

Failed to insert into MySQL table 1146 (42S02): Table 'fx003.'aud-cad_30mins'' doesn't exist

aud-cad_30mins表确实存在，并且像下面这样的插入查询正在工作:

mySql_insert_query = """INSERT INTO aud-cad_30mins (date, trend) VALUES ( "2021-09-24 21:00:00","Short"  )""" 

所以当我尝试在查询中使用变量时，它给出了错误。为什么表名得到不必要的引号?检查了几个教程，但找不到解决方案，有什么想法吗?

Answer 1

表名应该硬编码在查询字符串中，而不是作为占位符%s，这意味着要插入的值。因此，如果变量中有表名，可以在调用cursor.execute()

之前通过format()替换它

dbtable = stockname + timeframe
mySql_insert_query = """INSERT INTO {} (date, trend) VALUES ( %s, %s )""".format(dbtable)

参见文档

中的示例编辑:正如Bill在评论中提到的，不要在%s占位符周围添加反引号。