trying to place a variable for mysql table name in python

Question

day = datetime.datetime.today().strftime('%Y%m%d')

def connect():

    conn = None
    try:
        conn = mysql.connector.connect(user='elijah', password='12345678',
                                      host='127.0.0.1', database='userdb',
                                      auth_plugin='mysql_native_password')
        if conn.is_connected():
            print('Connected to MySQL database')
        cursor = conn.cursor()
        sql = '''CREATE TABLE %s(
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        rating VARCHAR(255) NOT NULL, 
        reservation_rate VARCHAR(255) NOT NULL
        )'''%day
        cursor.execute(sql)

I am trying to insert a variable into the table name but it keeps giving me this error :

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 '20200320(
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) ' at line 1

I have tried format, string concatenation, and %s replacing nothing has worked so far.

Mureinik · Accepted Answer · 2020-03-20 10:17:44Z

1

Object names (such as tables) in MySQL can't start with a digit. You could create a valid name by prepending a letter, e.g.:

sql = '''CREATE TABLE t%s(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
rating VARCHAR(255) NOT NULL, 
reservation_rate VARCHAR(255) NOT NULL
)'''%day

If you absolutely must use this name, though, you could escape it with backticks:

sql = '''CREATE TABLE `%s`(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
rating VARCHAR(255) NOT NULL, 
reservation_rate VARCHAR(255) NOT NULL
)'''%day

answered Mar 20, 2020 at 10:17

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

trying to place a variable for mysql table name in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related