mysql - Python MySQLdb cursor.execute() insert with varying number of values

Question

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))

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

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.

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] example usage of a method I had in mind:

two databases: source_db, destination_db

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

(returns tuple)

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

Answer 1

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)