Insert python list into SQLite database

Question

I have a query which should insert the contents of a list to a table. It's my understanding that each %s should be replaced by the content of the valueInsert list I've added to the execute command.

However I get the following error

c.executemany(test4, valuesInsert)

sqlite3.OperationalError: near "%": syntax error

Query:

test4 = "INSERT INTO test (city,region_code,os,ip,isp,area_code,\
    dma_code,last_update,country_code3,country_name,postal_code,\
        longitude,country_code,ip_str,latitude,org,asn) VALUES \
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

Command to execute query

c.executemany(test4, valuesInsert)

Contents of List:

['Norwell', 'MA', None, 1572395042, 'EdgeCast Networks', 781, 506, '2019-12-09T00:44:43.812333', 'USA', 'United States', '02061', -70.8217, 'US', '93.184.216.34', 42.15960000000001, 'Verizon Business', 'AS15133']

@alecxe When using "INSERT INTO test (city,region_code,os,ip,isp,area_code,\ dma_code,last_update,country_code3,country_name,postal_code,\ longitude,country_code,ip_str,latitude,org,asn) VALUES \ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" I get the error sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 17, and there are 7 supplied. but when I print the list I'm seeing 17 attributes — Overflow
– Overflow, Commented Dec 9, 2019 at 1:08
in executemany you should use list of lists - [ ['Norwell',... ], ['other place', ...], ] for single element (like yours) you have execute() — furas
– furas, Commented Dec 9, 2019 at 2:07

furas · Accepted Answer · 2019-12-09 02:19:30Z

1

executemany doesn't mean many arguments in one INSERT.

executemany is used when you have list of lists - data for many rows in database - and you want to execute many INSERT

valuesInsert = [ 
    ['Norwell',... ], 
    ['Other Place', ...], 
]


c.executemany(test4, valuesInsert)

but you have only one element - data for one row in database - and you want to execute only one INSERT so you should use execute()

valuesInsert = ['Norwell',... ]

c.execute(test4, valuesInsert)

BTW: when you use ['Norwell',... ] with executemany then it get 'Norwell' as data for first row (for first INSERT) and threat string as list of chars.

valuesInsert = [ 
    ['N', 'o', 'r', 'w', 'e', 'l', 'l'] 
]

Because 'Norwell' has 7 chars so it see 7 elements and you get message The current statement uses 17, and there are 7 supplied.

edited Dec 9, 2019 at 2:19

answered Dec 9, 2019 at 2:12

furas

149k12 gold badges121 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Overflow Over a year ago

Thanks for this, helped a ton. I've changed to just execute. My new problem is that it's still not writing to the database but I get no error code :(

Overflow Over a year ago

Nevermind! running the execute command with conn inserted the data with conn: c.execute(test4, valuesInsert)

GiovaniSalazar · Accepted Answer · 2019-12-09 02:13:29Z

I hope this code can help you (this example just use columns type text)

import sqlite3
con = sqlite3.connect("samples.db")
cur = con.cursor()

cmd = "CREATE TABLE IF NOT EXISTS test2 (city text,region_code text,os text,ip text,isp text,area_code text, dma_code text,last_update text,country_code3 text,country_name text,postal_code text, longitude text,country_code text,ip_str text ,latitude text,org text ,asn text)"
cur.execute(cmd)

list1 = ['Norwell', 'MA', None, 1572395042, 'EdgeCast Networks', 781, 506, '2019-12-09T00:44:43.812333', 'USA', 'United States', '02061', -70.8217, 'US', '93.184.216.34', 42.15960000000001, 'Verizon Business', 'AS15133']
list2 = ['Norwell', 'MA', None, 1572395042, 'EdgeCast Networks', 781, 506, '2019-12-09T00:44:43.812333', 'USA', 'United States', '02061', -70.8217, 'US', '93.184.216.34', 42.15960000000001, 'Verizon Business', 'AS15133']

con.executemany("INSERT INTO test2(city,region_code,os,ip,isp,area_code, dma_code,last_update,country_code3,country_name,postal_code, longitude,country_code,ip_str,latitude,org,asn) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (list1, list2))

for row in cur.execute("SELECT * FROM test2"):

    print (row)

Collectives™ on Stack Overflow

Insert python list into SQLite database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related