Using multiple cursors in a nested loop in sqlite3 from python-2.7

Question

I’ve been having problems using multiple cursors on a single sqlite database within a nested loop. I found a solution that works for me, but it’s limited and I haven’t seen this specific problem documented online. I’m posting this so:

• A clear problem/solution is available
• To see if there’s a better solution
• Perhaps I’ve found a defect in the sqlite3 python module

My Python app is storing social relationship data in sqlite. The dataset includes a one-to-many relationship between two tables: myConnections and sharedConnections. The former has one row for each connection. The sharedConnections table has 0:N rows, depending on how many connections are shared. To build the structure, I use a nested loop. In the outside loop I visit each row in myConnections. In the inside loop, I populate the sharedConnections table. The code looks like this:

curOuter = db.cursor()  
for row in curOuter.execute('SELECT * FROM myConnections'):    
    id  = row[0]  
    curInner = db.cursor()  
    scList = retrieve_shared_connections(id)  
    for sc in scList:  
        curInner.execute('''INSERT INTO sharedConnections(IdConnectedToMe, IdShared) VALUES (?,?)''', (id,sc))  
db.commit()  

The result is odd. The sharedConnections table gets duplicate entries for the first two records in myConnections. They’re a bit collated. A’s connections, B’s connections, followed by A and then B again. After the initial stutter, the processing is correct! Example:

myConnections
-------------
a   
b  
c  
d  

sharedConnections
-------------
a->b  
a->c  
b->c  
b->d  
a->b  
a->c  
b->c  
b->d  

The solution is imperfect. Instead of using the iterator from the outside loop cursor, I SELECT, then fetchall() and loop through the resulting list. Since my dataset is pretty small, this is OK.

curOuter = db.cursor()
curOuter.execute('SELECT * FROM myConnections'):
rows = curOuter.fetchall()
for row in rows:    
    id  = row[0]
    curInner = db.cursor()
    scList = retrieve_shared_connections(id)
    for sc in scList:
        curInner.execute('''INSERT INTO sharedConnections(IdConnectedToMe, IdShared) VALUES (?,?)''', (id,sc))
db.commit()

There you have it. Using two cursors against different tables in the same sqlite database within a nested loop doesn’t seem to work. What’s more, it doesn’t fail, it just gives odd results.

• Is this truly the best solution?
• Is there a better solution?
• Is this a defect that should be addressed?

Answer 1

This looks like you are hitting issue 10513, fixed in Python 2.7.13, 3.5.3 and 3.6.0b1.

There was a bug in the way transactions were handled, where all cursor states were reset in certain circumstances. This led to curOuter starting from the beginning again.

The work-around is to upgrade, or until you can upgrade, to not use cursors across transaction commits. By using curOuter.fetchall() you achieved the latter.

Answer 2

You could build up a list of rows to insert in the inner loop and then cursor.executemany() outside the loop. This doesn't answer the multiple cursor question but may be a workaround for you.

curOuter = db.cursor()
rows=[]
for row in curOuter.execute('SELECT * FROM myConnections'):    
    id  = row[0]    
    scList = retrieve_shared_connections(id)  
    for sc in scList:

        rows.append((id,sc))
curOuter.executemany('''INSERT INTO sharedConnections(IdConnectedToMe, IdShared) VALUES (?,?)''', rows)  
db.commit()

Better yet only select the ID from myConnections:

curOuter.execute('SELECT id FROM myConnections')