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)
解决方案
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)