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
– Martijn Pieters, Commented Sep 26, 2015 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
– BigZ, Commented Sep 26, 2015 at 19:17

Community · Accepted Answer · 2020-06-20 09:12:55Z

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)

ahmed · Accepted Answer · 2015-09-26 19:36:05Z

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
})

Collectives™ on Stack Overflow

OperationalError: not enough arguments for format string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related