À l'aide de plusieurs curseurs dans une boucle imbriquée dans sqlite3 à partir de python 2.7

J'ai eu des problèmes à l'aide de plusieurs curseurs sur une base de données sqlite dans une boucle imbriquée. J'ai trouvé une solution qui fonctionne pour moi, mais il est limité et je n'ai pas vu ce problème spécifique documenté en ligne. Je suis l'affichage de cette sorte:
-- Claire du problème/solution est disponible
-- Pour voir si il y a une meilleure solution
- Peut-être que j'ai trouvé un défaut dans le sqlite3 module python

Voici la situation:
Mon Python application est de stocker des relations sociales données sqlite. L'ensemble de données comprend un un-à-plusieurs relation entre deux tables: myConnections et sharedConnections. Le premier a une ligne pour chaque connexion. Le sharedConnections tableau a 0:N lignes, en fonction du nombre de connexions sont partagés. Pour construire la structure, j'utilise une boucle imbriquée. À l'extérieur de la boucle que je visite chaque ligne dans myConnections. Dans l'intérieur de la boucle, j'remplir le sharedConnections table. Le code ressemble à ceci:

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

Le résultat est impair. Le sharedConnections obtient le tableau des entrées en double pour les deux premiers enregistrements dans myConnections. Ils sont un peu assemblés. Une connexions de l', B connexions, suivi par Un, puis de B à nouveau. Après la période initiale de bégayer, le traitement est correct! Exemple:

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

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

La solution est imparfaite. Au lieu d'utiliser l'itérateur de l'extérieur de la boucle du curseur, je SELECT, puis fetchall() et boucle à travers la liste. Depuis mon dataset est assez petit, c'est 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()

Là vous l'avez. À l'aide de deux curseurs à l'encontre de différentes tables dans la même base de données sqlite dans une boucle imbriquée ne semble pas fonctionner. Qui plus est, il n'a pas d'échec, il donne des résultats bizarres.

Donc:
-- Est-ce vraiment la meilleure solution?
-- Est-il une meilleure solution?
-- Est-ce un défaut qui doit être adressée?

RÉPONSE:
À ce stade, les questions ont été posées, il y a eu quelques discussions et je pense que nous sommes assez bien complète. Voici comment il me semble:

  1. Vous ne pouvez pas utiliser fiable de plusieurs curseurs de boucles imbriquées avec le module sqlite3 à partir de python. Nous n'avons pas eu confirmation explicite malheureusement.
  2. Il y a une meilleure solution que celle que j'ai posté.
    2a: Réduire le sélectionner pour les champs dont vous avez besoin (j'ai été en utilisant *).
    2b: Structure de la boucle donc la plus petite empreinte de passe en mémoire. Pour ce cas, sharedConnections <= connexions (c'est un sous-ensemble). Il faut donc utiliser le curseur de la souris sur les connexions et d'accumuler des sharedConnections dans la mémoire.
    2c: à l'Aide .executemany sur le cumul de la liste de sharedConnections devrait être plus efficace que l' .exécuter à l'intérieur de la boucle.
  3. Est-ce un défaut? Nous n'avons pas obtenu de réponse. Je suppose que c'est la vie!

Merci à tous pour votre intérêt et de vos suggestions.

Meilleures salutations,
-Jim

Ce n' retrieve_shared_connections() faire? Elle affecte la DB?
retrieve_shared_connections(id) n'implique pas la base de données. C'est une fonction qui utilise un webservice pour retourner une liste des connexions partagées, donné un id. La boucle immédiate en dessous de cet appel Insère chaque connexion partagée dans la base de données.
Je n'ai pas regardé de trop près à votre code, mais serait un INSERT INTO ... SELECT FROM déclaration de travaux? Les instructions INSERT dans SQLite ne permettre les valeurs au sein d'une instruction SELECT.
Ah je vois ce que vous dites maintenant. Il a été un moment depuis que j'ai utilisé SQLite alors peut-être que quelqu'un avec plus d'expérience récente peut commentaire. J'aurais pensé que, parce que vous êtes à la sélection et à la mise à jour de différentes tables il ne devrait pas. Mais il semble que la loi de faire des insertions est de confondre le générateur de méthode pour votre curseur extérieur. Donc, utiliser fetchall() est probablement un bon pari pour l'instant à obtenir autour de cela. Cependant, il semble que vous êtes seulement en utilisant le id colonne de myConnections de sorte que vous pouvez économiser beaucoup en utilisant SELECT id from myConnections au lieu de toutes les colonnes.
Wow. Vraiment? C'est vraiment boiteux. Personne ne sait si c'est une limitation de l'sqlite ou le python interface? Dans mon cas, le outter boucle a ~6 millions de lignes. Je ne peux pas tirer de tout cela dans la mémoire. Je peux venir avec une sorte de contourner. Peut-être une entreprise DB est le chemin à parcourir (SQL Server, Postgres, MySQL, etc).

OriginalL'auteur tjim | 2012-11-05

  1. 2

    Vous pouvez construire une liste de lignes à insérer dans la boucle intérieure et alors le curseur.executemany() en dehors de la boucle. Cela ne veut pas répondre aux multiples curseur de la question, mais peut-être une solution pour vous. 

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

    Mieux encore que de sélectionner l'ID de myConnections: 

    curOuter.execute('SELECT id FROM myConnections')
    Merci @user1451298. Un précédent commentaire, il est proposé de remplacer * avec l'Id. D'accord. Ma première pensée sur votre restructuration a été "6 de celle-ci; 1/2 douzaine de l'autre." Mais, après réflexion par le biais de votre solution, il me semble plus extensible car les lignes peuvent être mis en file d'attente sur le disque si la mémoire devient un problème - merci!
    oh, n'est-ce pas la voir. Oui, vous ne savez pas si la construction d'un cache de lignes à insérer est mieux que de tirer tous les id avec fetchall()...

    OriginalL'auteur Anov

  2. 2

    Tout en construisant une liste en mémoire semble être la meilleure solution, j'ai trouvé que l'utilisation de transactions explicites réduit le nombre de doublons retourné dans la requête externe. Que ferait-il quelque chose comme:

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

    OriginalL'auteur Arthur de Jong

  3. 2

    Cela ressemble à vous frapper question 10513, fixe dans l' (à venir) Python 2.7.13 et 3.5.3 les rejets, ainsi que dans Python 3.6.0b1.

    Il y avait un bug dans la façon dont les transactions ont été traitées, où tous curseur états ont été remis à zéro dans certaines circonstances. Cela a conduit à curOuter de départ depuis le début.

    Travail est de mettre à niveau, ou jusqu'à ce que vous pouvez mettre à niveau, à ne pas utiliser les curseurs à travers validation de transaction. En utilisant curOuter.fetchall() vous avez atteint le dernier.

    OriginalL'auteur Martijn Pieters

  4. 1

    C'est un peu plus âgé, je vois. Mais lorsqu'ils tombent sur cette question, je me demandais, si sqlite3 encore a ces questions dans python 2.7. Voyons voir:

    #!/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

    De sortie est

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

    Ce test a été fait à l'aide de

    $ 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'

    La situation change lorsque nous commit en paquets, par exemple, par l'indentation de la connection.commit() à l'étape 1 ci-dessus script de test. Le comportement est assez étrange, parce que seule la deuxième commit à la write curseur réinitialise le read curseur, exactement comme indiqué dans l'OP. Après de jongler avec le code ci-dessus, je suppose que l'OP n'a pas faire un commit comme le montre l'exemple de code, mais n'a commit dans les packages.

    Remarque: le Dessin les curseurs read et write de séparer les connexions à la prise en charge emballés commit, comme suggéré dans une réponse à une autre question, ne fonctionne pas car la commits sera exécutée à l'encontre d'un étranger de verrouillage.

    OriginalL'auteur flaschbier