Python MySQLdb cursor.execute()插入不同数量的值 [英] Python MySQLdb cursor.execute() insert with varying number of values

问题描述

提出了类似的问题，但所有问题都得到解决，例如

Similar questions have been asked, but all of them - for example This One deals only with specified number of values.

例如，我尝试通过以下方式执行此操作:

for example, I tried to do this the following way:

def insert_values(table, columns, values, database):
    """
    columns - a string representing tuple with corresponding columns
    values - a tuple containing values
    """
    query=database.cursor()
    query.execute("INSERT INTO `{}` {} VALUES{}".format(table,columns,str(values))) 

但是那不好-尽管插入最初看起来不错(可以接受并提交到数据库)，但是很快就出现了数据类型错误，这是由于将所有数据类型都转换为字符串引起的.例如:

But that's no good - although the insert initially seemed fine (accepted and commited to database), soon the data types errors began, which came from converting all data types to string. For example:

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

我最初的方式还涉及到预先转换日期时间对象，这可能不是MySQLdb的创建者所想到的.

My initial way also involved pre-converting datetime objects, which is probably not what the creators of MySQLdb had in mind.

我在其他MySQLdb示例中看到的方式涉及字符串插值，但这意味着将定义数量的变量定义为'％s'，并且它没有给代码带来灵活性.

The way I saw in other examples for MySQLdb involves string interpolation, but that means defined number of variables as '%s', and it gives no flexibility to the code.

我如何定义一个方法，该方法可以接受与MySQLdb兼容的任意数量的，以前未定义的值?是否有可能?还是我缺少有关字符串插值的东西?

How could I define a method, which could take an arbitrary, previously undefined number of values that would be compatibile with MySQLdb? Is it possible? Or am I missing something about string interpolation?

[edit]我想到的方法的示例用法:

[edit] example usage of a method I had in mind:

两个数据库:source_db，destination_db

two databases: source_db, destination_db

row = source_db.cursor.execute('SELECT x from Y where z='1')

(返回元组)

insert_values('table_name','(column_name_1,column_name_2)',row, destination_db)

推荐答案

您应该使用字符串插值为内容生成占位符，然后允许DB-API填充它们.例如:

You should use string interpolation to produce placeholders for the content, then allow the DB-API to populate them. For example:

placeholders = ','.join('%s' for col in columns)
query_string = "INSERT INTO `{}` {} VALUES ({})".format(table, columns, placeholders)
query.execute(query_string, values)

这篇关于Python MySQLdb cursor.execute()插入不同数量的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！