How to use python mysqldb to insert many rows at once

Question

I have a list of lists, e.g [['a','b'],['c','d']].

I have a table called T and two fields F1, F2. The first item in the field list maps to F1, second to F2.

How can I insert rows for each inner list in a single command or call, rather than using a for loop like this?

for i in [['a','b'],['c','d']]:
    c.execute("insert into T (F1,F2) values (%s, %s)", (i[0], i[1]))

score 75 · Accepted Answer · answered Dec 23 '12 at 13:07

75

From MySQLdb User's Guide:

c.executemany(
      """INSERT INTO breakfast (name, spam, eggs, sausage, price)
      VALUES (%s, %s, %s, %s, %s)""",
      [
      ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
      ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
      ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
      ] )

so in your case:

c.executemany("insert into T (F1,F2) values (%s, %s)",
    [('a','b'),('c','d')])

answered Dec 23 '12 at 13:07

zenpoy

19,490
9
60
87

4

Is this more efficient than using the mySQL build in with a some string manipulation? So that your string is "insert into T (F1,F2) values (a,b), (c,d)" and pass it to execute? – Jochen Sep 23 '15 at 15:42
4

@Jochen You really don't want to do that unless you are _absolutely sure_ your data is coming from a trusted source (and I still believe "never trust the client" is a good rule to keep in all circumstances). It's generally a lot better, at least when you're working with a mature library like MySQLdb, to leave the necessary quoting/encoding/escaping to the lib rather than attempting it yourself; even if it incurs a bit of overhead, the peace of mind is worth it. – kungphu Dec 03 '15 at 01:36
Unfortunately, this doesn't work with utf8mb4 character set. It throws a strange error "unknown encoding: utf8mb4" – Jalal El-Shaer Jan 07 '16 at 09:28
1

@Jochen **Don't** use string manipulation to include data that could potentially come from end users into a query. That is the main (if not the only) source of SQL injection vulnerabilities. Train yourself to think the opposite way: there must be a very, *very*, **very** good reason why you would build up the query with data included directly, and even then, you need to be *really, really* sure that the end user can't include SQL at all or that it will be escaped. A single subtle mistake could be the difference between your app returning an error vs. spitting back e-mails and passwords hashes. – jpmc26 Jun 13 '16 at 23:27
3

to see the results in DB you also will have to add `c.execute("COMMIT")`. – nishant Jan 22 '18 at 11:21
6

Calling `execute()` for each row: **12.2 minutes**. Calling `executemany()` with 32 rows per query: **9.9 minutes (19% faster)**. Custom SQL code: **2.6 minutes (79% faster)**. It's not true that custom SQL code is insecure: `con.escape_string()` was made for this purpose. Heck, it's not even against untrusted input, it's also about correctly handling any apostrophes or quotes in regular input. My tests were done with 4 million rows, which was 927MB of SQL code in the custom sql code test. – Luc Mar 13 '19 at 15:20
@Luc: what do you mean custom SQL code? I am trying to insert code with executemany but it is so slow – Tuan Do Oct 25 '20 at 21:04
1

@TuanDo Sorry, that wasn't very clear. I must have meant building a big query string manually, so a bunch of inserts in one query (`INSERT INTO x (col1, col2) VALUES("val1.1","val1.2"),("val2.1","val2.2"),("val3.1","val3.2");`), making it as large as a single query allows (there is a configurable limit of, by default, a few megabytes if I remember correctly). Other answers show how to do this, but they don't use `escape_string` and the code is **insecure** if you don't escape the values (or parse them as number or something) before including them in a query! – Luc Oct 25 '20 at 21:24

score 22 · Answer 2 · answered Oct 14 '15 at 15:34

It's possible to insert all rows in one single statement like @adamhajari, and avoid sql injections like @zenpoy, at the same time. You just need to create a big insert statement and let mysqldb's execute do the formatting.

values_to_insert = [('a','b'),('c','d')]
query = "INSERT INTO T (F1, F2) VALUES " + ",".join("(%s, %s)" for _ in values_to_insert)
flattened_values = [item for sublist in values_to_insert for item in sublist]
c.execute(query, flattened_values)

Not super readable, but can be slightly faster than executemany (I tried inserting batches of 50000 rows in a local DB, executemany was 20% slower).

I think some db driver cannot handle super long parameter list. eg. "pyodbc.Error: ('HY000', 'The driver did not supply an error!')" — Gqqnbig, Nov 14 '19 at 03:15

score 2 · Answer 3 · answered Nov 25 '19 at 15:41

def multiple_insert(cursor, table, cols, rows):
    sql_insert = 'INSERT INTO %s(%s) values %s' % (
        table,
        ','.join(cols),
        ','.join('(%s , %s)' for _ in rows)
    )
    values = [_ for r in rows for _ in r]
    cursor.execute(sql_insert, values)


# eg:
rows = [(a1 , b1),(a2 , b2),(a3 , b3)]
multiple_insert(cursor, 'your_table',('col1', 'col2'), rows)
conn.commit()

How to use python mysqldb to insert many rows at once

3 Answers3

Linked