CODEWITHSUNDEEP
pythonsqlite
AB49K
AB49K

Reputation: 3425

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied

def insert(array):
    connection=sqlite3.connect('images.db')
    cursor=connection.cursor()
    cnt=0
    while cnt != len(array):
            img = array[cnt]
            print(array[cnt])
            cursor.execute('INSERT INTO images VALUES(?)', (img))
            cnt+= 1
    connection.commit()
    connection.close()

When I try insert("/gifs/epic-fail-photos-there-i-fixed-it-aww-man-the-tire-pressures-low.gif"), I get an error message like in the title (the string is indeed 74 characters long).

What is wrong with the code, and how do I fix it?

The same problem occurs with MySQLdb and many other popular SQL libraries. See Why do I get "TypeError: not all arguments converted during string formatting" when trying to use a string in a parameterized SQL query? for details.

Upvotes: 304

Views: 339509

Answers (3)

Hosseinzlf
Hosseinzlf

Reputation: 21

For me this problem happened when the number of columns are not same as the inserting values.

For example:

    conn = sqlite3.connect(',ySQL.db') 
    c = conn.cursor()
    
    sql =   "INSERT INTO mytable (X1, X2, X3) VALUES (?,?,?)"
            
    project = (Y1, Y2)

    print(project)
    c.execute(sql, project)
    conn.commit()
    conn.close()

This is going to raise an error because number of X (X1, X2, X3) are not same as number of Y (Y1, Y2). So, your columns should have the same number as inserted values.

Upvotes: 1

cards
cards

Reputation: 4996

You get confused by the fact that you are dealing with a single column data only but the syntax is compatible to deal with several columns at once, hence the reason to cast your string into a, i.e., tuple.

You could fix it with zip: cursor.execute('INSERT INTO images VALUES(?)', zip(img)).

To avoid multiple execute-calls you can store the strings in a list and update your table in a single call with executemany:

def insert(array):
    connection = sqlite3.connect('images.db')
    cursor = connection.cursor()
    cnt = 0
    imgs = []
    while cnt != len(array):
        img = array[cnt]
        print(array[cnt])
        imgs.append(img)
        cnt += 1

    cursor.executemany('INSERT INTO images VALUES(?)', zip(imgs))

executemany is rows-oriented and zip turns the "columns" of imgs into rows

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1124548

You need to pass in a sequence, but you forgot the comma to make your parameters a tuple:

cursor.execute('INSERT INTO images VALUES(?)', (img,))

Without the comma, (img) is just a grouped expression, not a tuple, and thus the img string is treated as the input sequence. If that string is 74 characters long, then Python sees that as 74 separate bind values, each one character long.

>>> len(img)
74
>>> len((img,))
1

If you find it easier to read, you can also use a list literal:

cursor.execute('INSERT INTO images VALUES(?)', [img])

Upvotes: 611

Related Questions