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)

Daniel Roseman · Accepted Answer · 2015-07-20 17:30:51Z

2

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)

edited Jul 20, 2015 at 17:30

answered Jul 20, 2015 at 15:50

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Taku Over a year ago

This looks mighty fine, and thank you for this, but this returns error in my case. query.execute(query_string, values) returns TypeError: must be string or read-only buffer, not tuple, while query.execute(query_string% values) when a field is returned by MySQLdb as dict, gives a MySQL syntax error. For example, select returned a tuple: ('default', 'Default', 1, 26L, '{"description": "content available"}') and this gives an error _mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; (...) use near \'description":

Taku Over a year ago

I'm sorry if that's unclear, but replies are very limited when it comes to formatting ;/

Daniel Roseman Over a year ago

I think you just need parens around the values - edited.

Taku Over a year ago

values_tmp=[str(value).replace('"',"'") if type(value)==str else value for value in values] -> values=tuple(values_tmp) - this worked for replacing the double quotes with single quotes. Now the whole query works... Is this kind of struggling normal with MySQLdb lib?

Daniel Roseman Over a year ago

No, and there is no reason to do that. The code I showed above works fine - the library escapes the quotes for you.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related