Python, insert XML data to sqlite3

Question

Im trying to pull some XML from a URL, parse it and store the entries in an sqlite3 database, Im trying numerous things and all are failing. Codde so far:

#!/usr/bin/env python

from urllib2 import urlopen
import gc
import xml.etree.ElementTree as ET
import sqlite3

rosetta_url = ("https://boinc.bakerlab.org/rosetta/team_email_list.php?teamid=12575&account_key=Y&xml=1")

root = ET.parse(urlopen(rosetta_url)).getroot()
cpids = [el.text for el in root.findall('.//user/cpid')]
print cpids

conn = sqlite3.connect("GridcoinTeam.db")
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS GRIDCOINTEAM (cpid TEXT)''') 
c.executemany("INSERT INTO GRIDCOINTEAM VALUES (?);", cpids)
conn.commit()       
conn.close()

conn = sqlite3.connect("GridcoinTeam.db")
c = conn.cursor()
cpids = c.execute('select cpid from GRIDCOINTEAM').fetchall()
conn.close()

print cpids

gc.collect()

Im getting the error:

Incorrect number of bindings supplied. The current statement uses 1, and there are 32 supplied.

I tried making the insertion tuples by changing to

c.executemany("INSERT INTO GRIDCOINTEAM VALUES (?);", (cpids, ))

but that just gives:

Incorrect number of bindings supplied. The current statement uses 1, and there are 3289 supplied.

The XML extract is in the form ['5da243d1f47b7852d372c51d6ee660d7', '5a6d18b942518aca60833401e70b75b1', '527ab53f75164864b74a89f3db6986b8'], but there are several thousand entries.

Thanks.

XML and downloading of data has nothing to do with the problem. As you have proven with the print line, the cpids list is correctly defined. Next time please try to shrink the problem to the relevant part. — Hugo G
– Hugo G, Commented Jan 13, 2017 at 13:56

Shijo · Accepted Answer · 2017-01-13 13:59:50Z

1

You need to insert this as multiple rows instead of multiple columns into one row

cpids = [el.text for el in root.findall('.//user/cpid')]
cpids = zip(*[iter(cpids)]*1)
print cpids

answered Jan 13, 2017 at 13:59

Shijo

9,7813 gold badges23 silver badges31 bronze badges

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

1 Comment

Scalextrix Over a year ago

Ok thanks, I didnt know how to fix that list format, it works thanks.

Hugo G · Accepted Answer · 2017-01-13 14:06:51Z

The problem lies in

c.executemany("INSERT INTO GRIDCOINTEAM VALUES (?);", cpids)

This executemany expects a list of tuples, but you pass a list of strings. What the code does effectively is:

for entry in cpids:
    c.execute("INSERT INTO GRIDCOINTEAM VALUES (?);", *entry)

Note the star before entry, which unloads the string, and which gives you 32+ params whereas you only want one.

In order to fix that we'd need to know your GRIDCOINTEAM table schema. If you have a table with only one column and you want to insert that, you could probably do this:

for entry in cpids:
    c.execute("INSERT INTO GRIDCOINTEAM VALUES (?)", entry)

In contrast to executemany, execute takes each parameter as one param - no tuples and lists unloading here.

Alternatively you can resort to using executemany, but you'd then need to wrap every one of your strings in a tuple or generator:

c.executemany("INSERT INTO GRIDCOINTEAM VALUES (?);", [(i,) for i in cpids])

Collectives™ on Stack Overflow

Python, insert XML data to sqlite3

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related