OperationalError: not enough arguments for format string

Question

I'm running the following code:

#converts strings that are ints to int.
for i,x in enumerate(values):
    try:
        values[i] = int(x)
    except:
        pass

# Fills values with NULLs if needed
if len(values) < no_of_columns:
    values = values + ["NULL"]*(no_of_columns-len(values))
print(values)

# Creates dict with params and values
params = {}
for i, x in enumerate(values):
    params[i] = x

query = "INSERT INTO {} VALUES ({});".format(table_name,",".join(['%s']*no_of_columns))

self.cur.execute(query, params)
self.print_answer()

And what happens is I get the following error:

Traceback (most recent call last):
  File "interface.py", line 228, in <module>
    db.run()
  File "interface.py", line 219, in run
    actions[self.print_menu()-1]()
  File "interface.py", line 194, in insert
    self.cur.execute(query, params)
  File "build/bdist.macosx-10.6-intel/egg/pgdb.py", line 323, in execute
  File "build/bdist.macosx-10.6-intel/egg/pgdb.py", line 359, in executemany
pg.OperationalError: internal error in 'BEGIN': not enough arguments for format string

This confuses me because when I print params and quote I can see that there are exactly as many elements as there are %s tags:

params = {0: 22, 1: 'ehj', 2: 'NULL', 3: 'NULL'}
query = 'INSERT INTO books VALUES (%s,%s,%s,%s);'

What am I doing wrong? The parameters should be the same amount as the %s's, right?

I suspect you need to pass in the `values` list, not a dictionary. Does `self.cur.execute(query, values)` work? — Martijn Pieters, Sep 26 '15 at 18:59
I would suggest that the first value 0 : 22 isn't a string and you therefore need to use %d to format it. Edit: Sorry, my mistake. '%s'%22 works fine — BigZ, Sep 26 '15 at 19:17

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You have two problems:

You are using positional parameters, each %s would match a successive value in the second argument to cursor.execute(), which should be a list or tuple here. You want to use values and not build the params dictionary at all.
You should not use the string NULL for nulls, use None; strings will be inserted literally (so not a SQL NULL, but the *string value 'NULL'), the Python value None represents actual nulls.

Alternatively, you could replace parameters with NULL values in the generated INSERT statement (so the generated SQL has NULL literals rather than parameters.

I'd also not use blanket except: statements; you are silencing any and all errors. Just catch ValueError:

#converts strings that are ints to int.
for i,x in enumerate(values):
    try:
        values[i] = int(x)
    except ValueError:
        pass

# Fills values with NULLs if needed
values += [None] * (no_of_columns - len(values))
    
query = "INSERT INTO {} VALUES ({});".format(
    table_name, ",".join(['%s'] * no_of_columns))

self.cur.execute(query, values)

score 0 · Answer 2 · answered Sep 26 '15 at 19:36

Make sure you don't escape string, if you just pass the exception, you will alter the passed values order. Also the database will do the conversation, so no need to int() anyway.

#converts strings that are ints to int.
for i,x in enumerate(values):
    try:
        values[i] = int(x)
    except:
        values[i] = x # see note above

also, here is my solution to the same problem:

def db_insert(conn, cur, table, data):
    sql = ('INSERT INTO %s (' % table) + ', '.join(data.keys()) + ') VALUES(' + ', '.join(['?' for j in data.values()]) +')'

    cur.execute(sql, tuple(data.values()))
    lastid = cur.lastrowid
    conn.commit()

    return lastid

you can use it like this:

conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()

db_insert(conn, cur, 'ig_media', {
   'user_id': uid,
   'media_id': mid,
   'like_date': arrow.now().timestamp
})

OperationalError: not enough arguments for format string

2 Answers2