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

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)
• Not trying to answer, but using a lightweight orm like "peewee" might help to not worry about most of the issues you are currently tackling with and stay on a higher level without the mixture of Python+SQL. – alecxe Mar 19 '17 at 22:47
• Python ORMs like Peewee and Pony do not implement a fast upsert. Peewee, for example, recommends using a raw query. – Martin Burch Mar 20 '17 at 10:46
• Answers Invalidated Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. – Mast Mar 20 '17 at 12:21
• Thanks @Mast. There should be a warning about this on the edit page! – Martin Burch Mar 20 '17 at 12:32
• @MartinBurch good point, did not know peewee does not have an upsert. And, I think even SQLAlchemy does not have a straight fast upsert as well. – alecxe Mar 20 '17 at 12:43

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.
• Thanks for the pep8 suggestion! 👍 – Martin Burch Mar 19 '17 at 21:55
• Here's one existing option: github.com/seamusabshere/py-upsert ... a commit message mentions it's 10-25% slower, and since it lacks documentation, it seemed best to stay away. Any solution that's ORM-based and uses transactions is slower because it doesn't use MySQL's multiple-row syntax and it doesn't do upsert in one query. – Martin Burch Mar 19 '17 at 21:59
• That's a terrible commit message - It doesn't say whether the code before or after is 10-25% slower, whether the Python code or SQL is slower, how this was measured, or anything reproducible. So I wouldn't put too much weight on it. And 10-25% might still be preferable to maintaining your own solution forever. It depends completely on your use case. – l0b0 Mar 19 '17 at 22:04

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.

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