Problem with SQLite executemany

Question

I can't find my error in the following code. When it is run a type error is given for line: cur.executemany(sql % itr.next()) => 'function takes exactly 2 arguments (1 given),

import sqlite3
con = sqlite3.connect('test.sqlite')
cur = con.cursor()
cur.execute("create table IF NOT EXISTS fred (dat)")

def newSave(className, fields, objData):
    sets = []
    itr = iter(objData)
    if len(fields) == 1:
        sets.append( ':' + fields[0])
    else:
        for name in fields:
            sets.append( ':' +  name)
    if len(sets)== 1:
        colNames = sets[0]
    else:
        colNames = ', '.join(sets)
    sql = " '''insert into %s (%s) values(%%s)'''," % (className, colNames)
    print itr.next()
    cur.executemany(sql  % itr.next())
    con.commit()

if __name__=='__main__':
    newSave('fred', ['dat'], [{'dat':1}, {'dat':2}, { 'dat':3}, {'dat':4}])

I would appreciate your thoughts.

Answer 1

See the sqlite3 documentation. As you'll see, the Cursor.executemany method expects two parameters. Perhaps you mistook it for the Connection.executemany method which only takes one?

Answer 2

Perhaps you meant:

cur.executemany(sql, itr)

also note that the print statement consumes one item from the iterator.

Answer 3

Like it says, executemany takes two arguments. Instead of interpolating the string values yourself with the %, you should pass both the sql and the values and let the db adapter quote them.

sql = " '''insert into %s (%s) values(%%s)'''," % (className, colNames)
cur.executemany(sql, itr.next())

Answer 4

Thank you all for your answers. After pushing and poking for several days and using your guidance the following works. I'm guilty of overthinking my problem. Didn't need an iter() conversion. The objData variable is a list and already an iterable! This was one of the reasons the code didn't work.

import sqlite3
con = sqlite3.connect('test.sqlite')
cur = con.cursor()
cur.execute("create table IF NOT EXISTS fred (dat, tad)")

def newSave(className, fields, objData):
    colSets = []
    valSets = []
    If len(fields) == 1:
        colSets.append( fields[0])
        valSets.append(':' + fields[0])
    else:
        for name in fields:
            colSets.append( name)
            valSets.append(':' + name)
    if len(colSets)== 1:
        colNames = colSets[0]
        vals = valSets[0]
    else:
        colNames = ', '.join(colSets)
        vals = ', '.join(valSets)
    sql = "insert into %s (%s) values(%s)" % (className, colNames, vals)
    cur.executemany(sql , objDat)
    con.commit()

if __name__=='__main__':
    newSave('fred', ['dat',  'tad'], [{'dat':  100, 'tad' :  42}, {'dat': 200 , 'tad' : 43}, {'dat': 3 , 'tad' :  44}, {'dat': 4 , 'tad' :  45} ])

Answer 5

Take a look at the source of the y_serial module which is open at http://yserial.sourceforge.net It covers the batch execution mode and writes out secure SQL statements via Python.

It will do what you appear to be striving for: persistance of Python objects. yserial will serialize and compress them before taking a NoSQL approach with SQLite.

Hope this helps your project.