Mysql table name getting unwanted quotes resulting table does not exist error-CodePudding

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")

I have the code above and giving me the below error:

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

aud-cad_30mins table does exist and an insert query like below doing its job:

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

So when I try to use variables in the query, it gives the error. Why the table name getting unwanted quotes? Checked several tutorials but couldn't find a solution, any ideas?

CodePudding user response：

The table name should be hardcoded in the query string instead of having it there as a placeholder %s, which is meant for the values to be inserted. So if you have the table name in the variable, you can replace it via format() before calling cursor.execute()

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

see the examples in the docs

edit: as Bill mentioned in the comment, dont add the backticks around the %s placeholders.