在来自 python-2.7 的 sqlite3 的嵌套循环中使用多个游标 [英] Using multiple cursors in a nested loop in sqlite3 from python-2.7

问题描述

我在嵌套循环中的单个 sqlite 数据库上使用多个游标时遇到问题.我找到了一个对我有用的解决方案，但它是有限的，而且我还没有在网上看到这个特定问题的记录.我是这样发帖的:

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:

• 有明确的问题/解决方案可用
• 看看是否有更好的解决方案
• 也许我在 sqlite3 python 模块中发现了一个缺陷

• 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

我的 Python 应用程序将社交关系数据存储在 sqlite 中.该数据集包括两个表之间的一对多关系:myConnections 和 sharedConnections.前者为每个连接排一排.sharedConnections 表有 0:N 行，具体取决于共享连接的数量.为了构建结构，我使用了嵌套循环.在外部循环中，我访问 myConnections 中的每一行.在内部循环中，我填充了 sharedConnections 表.代码如下所示:

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()  

结果很奇怪.sharedConnections 表获取 myConnections 中前两条记录的重复条目.他们有点整理.A 的连接，B 的连接，然后是 A，然后是 B.初始卡顿后，处理正确！示例:

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  

解决方案并不完美.我没有使用来自外部循环游标的迭代器，而是 SELECT，然后 fetchall() 并循环遍历结果列表.由于我的数据集很小，所以没关系.

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()

给你.在嵌套循环中针对同一个 sqlite 数据库中的不同表使用两个游标似乎不起作用.更重要的是，它不会失败，只会给出奇怪的结果.

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.

• 这真的是最好的解决方案吗?
• 有更好的解决方案吗?
• 这是一个应该解决的缺陷吗?

推荐答案

您可以建立一个行列表以插入到内部循环中，然后将 cursor.executemany() 插入到循环外部.这不能回答多光标问题，但可能是您的解决方法.

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()

最好只从 myConnections 中选择 ID:

Better yet only select the ID from myConnections:

curOuter.execute('SELECT id FROM myConnections')

这篇关于在来自 python-2.7 的 sqlite3 的嵌套循环中使用多个游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！