CODEWITHSUNDEEP
pythonsqlite
Walker
Walker

Reputation: 29

Sqlite execute vs executemany difference in a for loop

I am confused on the difference between .execute and .executemany. Especially when used to INSERT into a table using a for loop. It would appear there is some extra indexing going on but I'm not seeing where

import sqlite3

con = sqlite3.connect("customer.db")
c = con.cursor()

c.execute("""CREATE TABLE IF NOT EXISTS quotes(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            quote TEXT
            )""")

quo = ('aaa', 'bbb', 'ccc')

for x in quo:
    c.execute("""INSERT INTO quotes (quote) VALUES (?)""", x)
    print(x)
    

c.execute("""
        SELECT * 
        FROM quotes
        """)
b = c.fetchall()
for _ in b:
    print(_)

con.commit()
con.close()

VS

import sqlite3

con = sqlite3.connect("customer.db")
c = con.cursor()

c.execute("""CREATE TABLE IF NOT EXISTS quotes(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            quote TEXT
            )""")

quo = ('aaa', 'bbb', 'ccc')

for x in quo:
    c.executemany("INSERT INTO quotes (quote) VALUES (?)", x)
    print(x)

c.execute("""
        SELECT * 
        FROM quotes
        """)
b = c.fetchall()
for _ in b:
    print(_)

con.commit()
con.close()

When I use .execute I receive the error "Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.", but when I use .executemany then it results in the following

(1, 'a')
(2, 'a')
(3, 'a')
(4, 'b')
(5, 'b')
(6, 'b')
(7, 'c')
(8, 'c')
(9, 'c')

Why is does this not result in a table like

(1, 'aaa')
(2, 'bbb')
(3, 'ccc')

Upvotes: 1

Views: 1309

Answers (1)

Barmar
Barmar

Reputation: 781350

When you use execute(), you're inserting just one row. x needs to be a sequence containing as many elements as placeholders in the query. The query has 1 ?, so x needs to be a sequence of one value. "xxx" is a sequence of 3 characters, which is the wrong number. You need to put it in a tuple, then the string will be one value.

for x in quo:
    c.execute("""INSERT INTO quotes (quote) VALUES (?)""", (x,))
    print(x)

When you use executemany(), you don't need the loop at all. The parameter argument should be a 2-dimensional sequence of rows. This means that each element of quo should be a tuple, not a single string.

quo = (('aaa',), ('bbb',), ('ccc',))

c.executemany("""INSERT INTO quotes (quote) VALUES (?)""", quo)

Upvotes: 1

Related Questions