Dynamic table for Insert Syntax SQL - Python [duplicate]

Question

I would like to have the SQL SYNTAX INSERT code to be dynamic for which table to insert data into. When I try the code below I get SYNTAX error, probably due to that it adds '-marks around the table. How can I solve this?

system=str(event['system'])
value1=str(event['value1'])
value2=str(event['value2'])
timestamp=str(event['timestamp'])
table=str('table1')    

insert_stmt = ("INSERT INTO %s (system, value1, value2, timestamp) ""VALUES (%s, %s, %s, %s)")
data = (table, system, value1, value2, timestamp)
cur.execute(insert_stmt, data)
conn.commit()

My Error:

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 ''table1' (system, value1, value2, timestamp) VALUES ('qwerty', '1', '1',' at line 1\")", "errorType": "ProgrammingError",

Bernd Buffen · Accepted Answer · 2019-03-27 21:55:48Z

0

You use reserved keywords as fieldname timestamp. You can only use it if you put it in backticks like:

 insert_stmt = ("INSERT INTO %s (system, value1, value2, `timestamp`) ""VALUES (%s, %s, %s, %s)")
data = (table, system, value1, value2, timestamp)

But make your live easier! Dont use KEYWORDS as fieldname !!!

answered Mar 27, 2019 at 21:55

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

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

2 Comments

aurelius Over a year ago

Thank you! That is a good valid point. I will change that. But I dont' think that's the cause for my error.

Bernd Buffen Over a year ago

if it not or not only your error. please output the generated query and post it

Collectives™ on Stack Overflow

Dynamic table for Insert Syntax SQL - Python [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related