Build a MySQL upsert query to insert/update multiple records in Python

Question

I'm generating a multi-row insert/update with the MySQLdb/MySQL-python module from lists of data rows and field names. For reference, see the executemany() example in the docs.

You can see some revised code on GitHub.

def upsert(db,table,fields,object_list):
    cursor = db.cursor();
    placeholders = ["%s" for x in fields]
    assignments = ["{x} = VALUES({x})".format(x=x) for x in fields]

    query_string = """INSERT INTO
    {table}
    ({fields})
    VALUES
    ({placeholders})
    ON DUPLICATE KEY UPDATE {assignments}"""

    cursor.executemany(query_string.format(
        table = table,
        fields = ", ".join(fields),
        placeholders = ", ".join(placeholders),
        assignments = ", ".join(assignments)
    ),object_list)
    db.commit()

Should I be quoting or escaping some strings/fields? The data is made safe by parameterized queries, but the table and field names could still cause trouble. I'm less concerned about security than failed queries due to odd characters.

Is there already a library out there for this? The existing solutions for upsert and batch-insert seem more generalized, and therefore slower, than implementing a MySQL-specific solution.

Also, I'm clearly in love with str.format()—is this the best way of handling string composition?

Here's some code to run the function, assuming a database named demo exists and mysql is listening on the localhost socket.

import MySQLdb
db = MySQLdb.connect(host="localhost", user="root", passwd="", db="demo", charset="utf8")
c = db.cursor()

c.execute("""DROP TABLE IF EXISTS upsert_demo""")

c.execute("""CREATE TABLE upsert_demo (
  `id` int(11) unsigned NOT NULL,
  `foo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8""")

c.execute("""INSERT INTO upsert_demo
  (`id`,`foo`)
  VALUES (1, 'baz')""")

demo_fields = ("id","foo")
demo_objects = [(1,"upserted!"),(2,"new record")]

upsert(db,"upsert_demo",demo_fields,demo_objects)

Answer 1

Should I be quoting or escaping some strings/fields?

Yes, you should definitely escape table and field names. Currently this is trivial to exploit. It should not be possible to produce invalid SQL with any input parameters. If you get to that point there might not be any exploits left.

The existing solutions for upsert and batch-insert seem more generalized, and therefore slower, than implementing a MySQL-specific solution.

Which options are these? And have you tested them? I would be very surprised if there are no options available which would save you much more time overall than rolling your own.

Also, I'm clearly in love with str.format()—is this the best way of handling string composition?

str.format() is very nice for relatively simple format strings, but in this code it's hard to see what the actual result will be for some given input. I would pull out variables for the various .join()ed strings, but even then it's a big string to compose.

Some general things:

• Use more descriptive variable names. database = MySQLdb.connect(… and cursor = database.cursor() are much more descriptive.
• Use transactions by initialising your cursors using with database.cursor() as cursor:
• You'll want to run your code through pep8 to improve readability.

Answer 2

Use db.escape_string() around the table and field names, because those inputs are used in the query without parameterization.

table = "`"+db.escape_string(table)+"`"
fields = ["`"+db.escape_string(field)+"`" for field in fields]
placeholders = ["%s" for field in fields]
assignments = ["`{x}` = VALUES(`{x}`)".format(
    x=db.escape_string(x)
) for x in fields]

It is important to use the connection's escape_string() because that will properly handle multi-byte characters. See the mapping of mysql_real_escape_string() in the docs.

Answer 3

In my testing with mysql-connector 2.2.9, bulk insert queries using executemany() were automatically batched as described in the mysql-connector documentation UNLESS they used 'INSERT IGNORE'. (I have yet to test 'ON DUPLICATE KEY …' statements.)

I found that the difference was up to 100x faster for plain 'INSERT' statements vs. 'INSERT IGNORE'; batching is clearly not being applied for the latter method.

In order to achieve a similar level of performance, I used a raw insert into a temp table, then an upsert to the target data using data within that temp table. This was quite close in performance to the raw insert statement.

As a side note, the PugSQL library makes it very easy to run properly parameterized queries - no more string formatting - and since it's raw SQL, there's no problem with upserts. It's a lot easier to learn than an ORM library, too. https://pugsql.org/tutorial