gingerandjohn.com

It took me far too long to figure out how to use executemany(). Part of the reason was that the implementor chose to read the Python DB API specification differently, or at least in a more inconsisitent manner than other implementations of the executemany() method. Another part is that I'm just not that good at Python (yet).

Assume you've got a table called TEST, implemented like

create table TEST
(
  NAME VARCHAR2(32),
  ID   NUMBER
)

You need to enter in a few records like so:

NAME	ID
Mike	1
Matt	2

The SQL to insert one record at a time is:

INSERT INTO TEST (NAME, ID) VALUES ('Mike', 1);
INSERT INTO TEST (NAME, ID) VALUES ('Matt', 2);

The trick to executemany() is that it takes a sequence of mappings, as its second argument, not a sequence of sequences. The first argument is the statement itself. More specifically, that means a list of dictionaries. So the statement for this example is:

"INSERT INTO TEST (NAME, ID) VALUES(:1, :2)"

and the parameters argument is:

[{'1': 'Mike', '2':1}, {'1': 'Matt', '2':2}]

Put it all together just for completeness:

cursor.executemany("INSERT INTO TEST (NAME, ID) VALUES(:1, :2)", [{'1': 'Mike', '2':1}, {'1': 'Matt', '2':2}])

My resident Python expert Dick Wall (who has a recipie in the Python Cookbook), supplied this handy little function to convert a sequence into a dictionary for executemany():

def convertSequenceToDict(list):
    """For each element in the sequence, creates a dictionary item equal\n
    to the element and keyed by the position of the item in the list.\n\n
    Thanks to Dick Wall.\n\n
    Example:\n\t
    >>> convertListToDict(("Matt", 1))\t\n
    {'1': 'Matt', '2': 1}\n
    """
    dict = {}
    argList = range(1,len(list)+1)
    for k,v in zip(argList, list):
        dict[str(k)] = v
    return dict

Tags: 1 Comment