0

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

GMB
  • 216,147
  • 25
  • 84
  • 135
aurelius
  • 448
  • 7
  • 23

1 Answers1

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 !!!

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39