title: Using multiple cursors in a nested loop in sqlite3 from python-2.7
body: <p>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:</p>
  
  <ul>
  <li>A clear problem/solution is available</li>
  <li>To see if there’s a better solution</li>
  <li>Perhaps I’ve found a defect in the <code>sqlite3</code> python module</li>
  </ul>
  
  <p>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:</p>
  
  <pre><code>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()  
  </code></pre>
  
  <p>The result is odd. The <code>sharedConnections</code> table gets duplicate entries for the first two records in <code>myConnections</code>. 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:</p>
  
  <pre><code>myConnections
  -------------
  a   
  b  
  c  
  d  
  
  sharedConnections
  -------------
  a-&gt;b  
  a-&gt;c  
  b-&gt;c  
  b-&gt;d  
  a-&gt;b  
  a-&gt;c  
  b-&gt;c  
  b-&gt;d  
  </code></pre>
  
  <p>The solution is imperfect. Instead of using the iterator from the outside loop cursor, I <code>SELECT</code>, then <code>fetchall()</code> and loop through the resulting list. Since my dataset is pretty small, this is OK.</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>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.</p>
  
  <ul>
  <li>Is this truly the best solution?  </li>
  <li>Is there a better solution?  </li>
  <li>Is this a defect that should be addressed?</li>
  </ul>