I’ve never had cause to use executemany before, so perhaps it is unfortunate that my first encounter be in the context of oursql’s somewhat anaemic documentation.

I’ve just spent well over an hour trying to get a simple executemany statement to work – in retrospect, the solution was obvious. But the presence of an example (just one!) would have made things immediately clear.

To that end:

reportDB = oursql.connect(host = "localhost",
                          user = "LapsedPacifist",
                          passwd = "ContentsMayDiffer",
                          db = "AnythingLegalConsidered")

with reportDB as query:
  query.execute("BEGIN")
  query.execute("""
                INSERT INTO assets(clientid, assettype, model, deployed, name)
                VALUES(?, ?, ?, ?, ?)
                """, (clientID, ASSET_TYPE_UNKNOWN, model, deployed, name))

  assetID = query.lastrowid
  addresses = []

  for ip in ips:
    addresses.append([assetID, ip])

  query.executemany("""
                    INSERT INTO asset_ips(assetid, ipv4address)
                    VALUES (?, ?)
                    """, (addresses))

That’s it – just as oursql’s parameterisation is looking for a list of parameters, executemany is looking for a list of parameter lists – not just a list of strings.

Hence the for loop creating a list with the two parameters and then appending it to the executemany parameter list.

I’m sure there’s a better, more pythonic way of doing that – please let me know if I’m missing something obvious.

And behold, the grand total of what oursql’s documentation has to say about executemany:

Additionally, executemany() is lazy; if passed a generator or any other iterator which does produces values lazily, values will only be taken from the iterator immediately before they are sent to the database.

executemany(query, parambatch)
Execute the same query with different sets of parameters. This is more efficient than calling execute many times with the same query.

I don’t want it to sound like I’m down on oursql – I feel it’s the superior library for MySQL in Python, and it’s still quite young – but this particular gap in the documentation had me pulling out my hair.

I’ve read some complaints regarding scalability of executemany in oursql – fortunately my code won’t ever have to handle huge numbers of queries at one time.