Problem with SQLite executemany

Question

I can not find my error in the following code. When it starts, an error of the type is indicated for the line: cur.executemany (sql% itr.next ()) => 'the function takes exactly 2 arguments (1 set) ,

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 be grateful for your thoughts.

Answer 1

As they say, executeemany takes two arguments. Instead of interpolating the row values ​​yourself with%, you should pass both sql and values, and let the db adapter quote them.

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

Answer 2

sqlite3. , Cursor.executemany . , Connection.executemany, ?

Answer 3

Thank you all for your answers. After pushing and popping for several days and using your manual, the following works. It’s my fault that I rethought my problem. No iter () conversion required. The objData variable is a list and is already 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 4

Did you mean:

cur.executemany (sql, itr)

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