Sytnax issue in SQL query written in Python

Question

I have the fallowing code which aim is to extract some sensor data and insert in to a MySQL database.

def sendDataToDB(con, data, table):
    cur = con.cursor()
    cur.execute("INSERT INTO %s (`sensor_id`, `value`, `unit`, `datetime`) VALUES (%s, %s, %s, %s)", (table, data.uuid, data.value, data.unit, data.datetime))
    con.commit()

temperatureSens = TemperatureSensor(0, 'temperature', 'celsius')
con = connector.Connect(user = db_user_name, password = db_password, database = db_name, host = db_host, port = db_port)

temp = temperatureSens.extractData()
print(temp.uuid, temp.value, temp.unit, temp.datetime)
sendDataToDB(con, temp, 'temperature_data')

The data is extracted correctly(this is the purpose of the print statement) but i have an error in the syntax of the INSERT query which i really cant find. There is the error:

mysql.connector.errors.ProgrammingError: 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 ''temperature_data' ('sensor_id', 'value', 'unit',
'datetime') VALUES (0, 23.0, '' at line 1

I'm sure that the problem is in the python syntax(which i'm nor very fimilar with) but i can't find it.
Thank you in advice.

EDIT** the root cause issue is that it is messed up (`) sign with (') in the INSERT query.

`INSERT INTO %s ` table name cannot be parametrized. You need to use string interpolation or concatenation. — Lukasz Szozda, Jun 19 '18 at 11:00

lucumt · Answer 1 · 2018-06-19T11:08:05.480

1

As Lukasz Szozda said table name can not be a parameter,see Table name as variable,also you need add ' to wrap %s,so change

 cur.execute("INSERT INTO %s (`sensor_id`, `value`, `unit`, `datetime`)
 VALUES (%s, %s, %s, %s)",
 (table, data.uuid, data.value, data.unit, data.datetime))

To

cur.execute("INSERT INTO youtable (`sensor_id`, `value`, `unit`, `datetime`) 
VALUES ('%s', '%s', '%s', '%s')",
(data.uuid, data.value, data.unit, data.datetime))

edited Jun 19 '18 at 11:08

answered Jun 19 '18 at 11:01

lucumt

6,642
2
16
32

Why can table name not be a parameter? Can you link to the relevant docs? – FHTMitchell Jun 19 '18 at 11:05
@FHTMitchell see this https://stackoverflow.com/questions/2838490/table-name-as-variable – lucumt Jun 19 '18 at 11:07
I've changed the table name to a hardcoded one, but the issue still persist. Maybe it is sth up to the variable data.unit, because the error message is ending at exactly that variable. Do you have any other suggestions? – Георги Няголов Jun 19 '18 at 16:41
@ГеоргиНяголов have you tried add `'` to the value? – lucumt Jun 20 '18 at 10:38

score 0 · Answer 2 · answered Jan 02 '19 at 08:16

I have encounter same problem, finally found the root cause is:

the datetime value should be string -> wrap by quote, like "datetime_value" or 'datetime_value'

so, should your code:

cur.execute("INSERT INTO %s (`sensor_id`, `value`, `unit`, `datetime`) VALUES (%s, %s, %s, %s)", (table, data.uuid, data.value, data.unit, data.datetime))

which datetime value is like 2019-01-02 15:08:59.490163

should change to:

cur.execute("INSERT INTO %s (`sensor_id`, `value`, `unit`, `datetime`) VALUES (%s, %s, %s, '%s')", (table, data.uuid, data.value, data.unit, data.datetime))

which datetime value is like '2019-01-02 15:08:59.490163'

and better change to, use backtick for tablename:

cur.execute("INSERT INTO `%s` (`sensor_id`, `value`, `unit`, `datetime`) VALUES (%s, %s, %s, '%s')", (table, data.uuid, data.value, data.unit, data.datetime))

Sytnax issue in SQL query written in Python

2 Answers2