python中如何在重复更新时动态生成mysql

Question

我尝试在给定csv文件的情况下动态生成MySQL插入/更新查询。
我有一个csv文件爱好.csv：

id,name,hobby
"1","rick","coding"
"2","mike","programming"
"3","tim","debugging"

然后我有2个函数：1生成查询，1更新数据库：

生成_sql.py版本

from connect_to_database import read_db_config
from config_parser import read_csv_files
from update_db import insert_records
import csv

def generate_mysql_queries():
    csv_file_list, table_list, temp_val, temp_key, temp_table, reader, header, data, data_list = ([] for i in range(9))
    val_param = '%s'
    query = ''
    total_queries = 0
    db = read_db_config(filename='config.ini', section='mysql')
    csv_file_dict = read_csv_files(filename='config.ini', section='data')
    for key, value in csv_file_dict.items():
        temp_val = [value]
        temp_key = [key]
        csv_file_list.append(temp_val)
        table_list.append(temp_key)
    for index, files in enumerate(csv_file_list):
        with open("".join(files), 'r') as f:
            reader = csv.reader(f)
            header.append(next(reader))
            data.append([row for row in reader])
            for d in range(len(data[index])):
                val_param_subs = ','.join((val_param,) * len(data[index][d]))
                total_queries += 1
                query = """INSERT INTO """ + str(db['database']) + """.""" + """""".join('{0}'.format(t) for t in table_list[index]) + \
                        """(""" + """, """.join('{0}'.format(h) for h in header[index]) + """) VALUES (%s)""" % val_param_subs + \
                        """ ON DUPLICATE KEY UPDATE """ + """=%s, """.join(header[index]) + """=%s"""
                data_list.append(data[index][d])
            insert_records(query, data_list)

然后，我将查询和数据传递到update中的insert_records（）_双倍：

from mysql.connector import MySQLConnection, Error
from connect_to_database import read_db_config


def insert_records(query, data):
    query_string = query
    data_tuple = tuple(data)
    try:
        db_config = read_db_config(filename='config.ini', section='mysql')
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
        cursor.executemany(query, data_tuple)
        print("\tExecuted!")
        conn.commit()
    except Error as e:
        print('\n\tError:', e)
        print("\n\tNot Executed!")
    finally:
        cursor.close()
        conn.close()

数据传入游标.executemany（query，data\u string）如下所示（query是字符串，data\u tuple是元组）：

query: INSERT INTO test.hobbies(id, name, hobby) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE id=%s, name=%s, hobby=%s
data_tuple: (['1', 'rick', 'coding'], ['2', 'mike', 'programming'], ['3', 'tim', 'debugging'])

给定这两个参数，我得到以下错误：

错误：1064（42000）：您的SQL语法有错误；请检查与您的MariaDB服务器版本相对应的手册，以便在第1行的“%s，name=%s，hobby=%s”附近使用正确的语法

我尝试过通过只发送不带“%s”参数的完整字符串来非动态地传递相同的字符串，效果很好。我错过了什么？非常感谢您的帮助。你知道吗

Answer 1

可能是python中使用了三个双引号。当你用这个的时候

query = """INSERT INTO """ + str(db['database']) + """.""" + """""".join('{0}'.format(t) for t in table_list[index]) + \
                    """(""" + """, """.join('{0}'.format(h) for h in header[index]) + """) VALUES (%s)""" % val_param_subs + \
                    """ ON DUPLICATE KEY UPDATE """ + """=%s, """.join(header[index]) + """=%s"""

你对python说一切都是一个字符串，包括%s