executing native SQL to Mysql in Python

Question

I want to execute this statement in the mysql database. But I can't get the format right. The ? is replacing a table name not a value. I have tried to concatenate the string together also but the same result.

Tried executing the same insert that I do from Mysql workbench without problems. But still won't work

mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1

import mysql.connector    
for table in tables:
    sql = """insert into close_price
    select * from nn_indexes_20180105
    where identifier = 'OMXS30'
    and seconds_offs = (select max(seconds_offs) as seconds_offs 
    from nn_indexes_20180105 
    where seconds_offs <= 55800
    and identifier = 'OMXS30');"""

score 1 · Answer 1 · answered Apr 29 '19 at 18:59

You can only bind values like that, not object names. In this case, you'll have to resort to some sort of string manipulation, like formatting:

for table in tables:
    sql = """insert into close_price select * from {} where identifier = 'OMXS30' and seconds_offs = (select max(seconds_offs)
    from {} where seconds_offs <= 55800 and identifier = 'OMXS30'""".format(table[0], table[0])
    cursor = mySQLconnection.cursor()
    cursor.execute(sql)

executing native SQL to Mysql in Python

1 Answers1