在Python中执行本机SQL到Mysql

Question

我想在mysql数据库中执行此语句。但是我不能得到正确的格式。这个正在替换表名而不是值。我也尝试将字符串连接在一起，但结果相同

尝试执行与我在Mysql workbench中执行的相同的插入，没有出现问题。但还是不行

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');"""

Answer 1

您只能像那样绑定值，而不能绑定对象名称。在这种情况下，您必须求助于某种字符串操作，如格式化：

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)