Использование нескольких курсоров во вложенном цикле в sqlite3 из python-2.7

У меня были проблемы с использованием нескольких курсоров в одной базе данных sqlite во вложенном цикле. Я нашел решение, которое работает для меня, но оно ограничено, и я не видел этой конкретной проблемы, задокументированной в Интернете. Я публикую это так:

Доступна ясная проблема/решение
Чтобы увидеть, есть ли лучшее решение
Возможно, я обнаружил дефект в модуле Python sqlite3

Мое приложение Python хранит данные социальных отношений в sqlite. Набор данных включает отношение "один ко многим" между двумя таблицами: myConnections и sharedConnections. Первый имеет один ряд для каждого соединения. Таблица sharedConnections имеет строки 0: N, в зависимости от того, сколько подключений является общим. Для построения структуры я использую вложенный цикл. Во внешнем цикле я посещаю каждую строку в myConnections. Во внутреннем цикле я заполняю таблицу sharedConnections. Код выглядит так:

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. Они немного сопоставлены. Как соединения, соединения Bs, затем A и B снова. После первоначального заикания обработка идет правильно! Пример:

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

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

Решение несовершенно. Вместо использования итератора из курсора внешнего цикла, я SELECT, затем fetchall() и перебираю результирующий список. Так как мой набор данных довольно маленький, это нормально.

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 во вложенном цикле, похоже, не работает. Более того, он не дает сбоя, он просто дает странные результаты.

Это действительно лучшее решение?
Есть ли лучшее решение?
Это дефект, который следует устранить?

Ответ 1

Вы можете создать список строк для вставки во внутренний цикл, а затем cursor.executemany() вне цикла. Это не отвечает на вопрос с несколькими курсорами, но может быть обходным путем для вас.

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:

curOuter.execute('SELECT id FROM myConnections')

Ответ 2

Похоже, вы решаете проблему 10513, исправленную в Python 2.7.13, 3.5.3 и 3.6.0b1.

Произошла ошибка в способе обработки транзакций, когда все состояния курсора были сброшены при определенных обстоятельствах. Это привело к тому, что curOuter начался с самого начала.

Обходной путь - обновить или до тех пор, пока вы не сможете обновить, чтобы не использовать курсоры при фиксации транзакции. Используя curOuter.fetchall() вы достигли последнего.

Ответ 3

При создании списка в памяти, кажется, лучшее решение, я обнаружил, что использование явных транзакций уменьшает количество дубликатов, возвращаемых во внешнем запросе. Это сделало бы что-то вроде:

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

Ответ 4

Это немного старше, я вижу. Но, споткнувшись об этом вопросе, я задавался вопросом, имеет ли в sqlite3 такие проблемы в python-2.7. Давайте посмотрим:

#!/usr/bin/python
import sqlite3
import argparse
from datetime import datetime

DBFILE = 'nested.sqlite'
MAX_A = 1000
MAX_B = 10000

parser = argparse.ArgumentParser(description='Nested SQLite cursors in Python')
parser.add_argument('step', type=int)
args = parser.parse_args()

connection = sqlite3.connect(DBFILE)
connection.row_factory = sqlite3.Row
t0 = datetime.now()

if args.step == 0:
    # set up test database
    cursor = connection.cursor()
    cursor.execute("""DROP TABLE IF EXISTS A""")
    cursor.execute("""DROP TABLE IF EXISTS B""")
    # intentionally omitting primary keys
    cursor.execute("""CREATE TABLE A ( K INTEGER )""")
    cursor.execute("""CREATE TABLE B ( K INTEGER, L INTEGER )""")
    cursor.executemany("""INSERT INTO A ( K ) VALUES ( ? )""", 
        [ (i,) for i in range(0, MAX_A) ])
    connection.commit()
    for row in cursor.execute("""SELECT COUNT(*) CNT FROM A"""):
        print row['CNT']

if args.step == 1:
    # do the nested SELECT and INSERT
    read = connection.cursor()
    write = connection.cursor()
    for row in read.execute("""SELECT * FROM A"""):
        bs = [ ( row['K'], i ) for i in range(0, MAX_B) ]
        for b in bs: # with .executemany() it would be twice as fast ;)
            write.execute("""INSERT INTO B ( K, L ) VALUES ( ?, ? )""", b)
    connection.commit()
    for row in connection.cursor().execute("""SELECT COUNT(*) CNT FROM B"""):
        print row['CNT']

elif args.step == 2:
    connection = sqlite3.connect(DBFILE)
    connection.row_factory = sqlite3.Row
    control = connection.cursor()
    ca = cb = 0 # will count along our expectation
    for row in control.execute("""SELECT * FROM B ORDER BY K ASC, L ASC"""):
        assert row['K'] == ca and row['L'] == cb
        cb += 1
        if cb == MAX_B:
            cb = 0
            ca += 1
    assert ca == MAX_A and cb == 0
    for row in connection.cursor().execute("""SELECT COUNT(*) CNT FROM B"""):
        print row['CNT']

print datetime.now() - t0

Выход

$ ./nested.py 0
1000
0:00:04.465695
$ ./nested.py 1
10000000
0:00:27.726074
$ ./nested.py 2
10000000
0:00:19.137563

Этот тест был выполнен с использованием

$ python
Python 2.7.6 (default, Jun 22 2015, 17:58:13) [GCC 4.8.2] on linux2
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.2'

Ситуация меняется, когда мы commit в пакетах, например. отступом connection.commit() на шаге 1 вышеуказанного теста script. Поведение довольно странно, потому что только второй commit для курсора write сбрасывает курсор read точно так, как показано в OP. После того, как я вернул код выше, я предполагаю, что OP не сделал один commit, как показано в примере кода, но сделал commit в пакетах.

Примечание: рисование курсоров read и write из отдельных подключений для поддержки упакованного commit, как предложено в ответе на другой вопрос, не потому что commit будет работать против внешней блокировки.