sqlite3, requête update et sortie de boucle

**goran kajfes** · 04/07/2009, 12h49

Bonjour à tous. J'essaie de traiter et de remplacer les données d'un tableau d'une base de données; plus précisément celles correspondant à la colonne tags d'un tableau facts (en utilisant au passage la colonne id des identifiants).

Le code est le suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# -*- coding: utf-8 -*-
 
import sqlite3, re
 
def parseTags(tags):
	"Modification des tags"
	# J'ai remplacé la vraie fonction par celle-là (qui n'a pas de sens) pour simplifier
	return tags + "ZZZ"
 
connexion = sqlite3.connect('/home/identifiant/chemin/fichier.db')
cursor = connexion.cursor()
 
selectSql = cursor.execute("select id, tags from facts ORDER BY id;")
i = 0
for idt, tag in selectSql:
	tag = parseTags(tag)
	sql = "update facts set tags='%s' where id='%i'" %(tag, idt)
	cursor.execute(sql) ###
	i += 1
 
print "nombre d'itération(s):", i

La boucle for n'est itéré qu'une seule fois au lieu des 5000 et quelques prévues. Ceci est dû à l'utilisation de cursor.execute(sql) (marqué ### dans le code) car sans cette ligne il y a bien 5000 et quelques itérations.

Quel est le problème?

**pacificator** · 04/07/2009, 13h38

J'ai essayé ça pour voir, peut être cela te servira:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
import sqlite3
import os
 
try:
    os.remove('test.db3')
except:
    pass
 
 
conn = sqlite3.connect('test.db3')
 
# creons une table items
conn.execute('create table items(id, texte)')
# creons 500 items
l = [(i, "texte%i" % i) for i in range(500)]
# inserons les dans la base de donnees
conn.executemany('insert into items(id, texte) values (?, ?)', l)
# sauvegardons les modifications
conn.commit()
 
# creons une nouvelle connection
con = sqlite3.connect('test.db3')
 
# la fonction de modification
def modifyItem(item):
    id, txt = item
    if not id % 2:
        txt = txt.replace('e', 'a')
    if id > 200:
        txt = txt.upper()
    return (txt, id)
 
# iterons sur les items present en base
for item in con.execute('select * from items'):
 
    print item,
    item = modifyItem(item)
    print item
    con.execute('update items set texte="%s" where id=%i' % item)
 
# essayer de l'enlever, pour voir ;)
con.commit()
 
# recuperons le resultat a partir d'une autre connection
print conn.execute('select * from items where id=444').fetchone()

**goran kajfes** · 04/07/2009, 17h33

J'ai remplacé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

selectSql = ...

par:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

selectSql = [(idt, tag) for (idt, tag) in cursor.execute("select id, tags from facts ORDER BY id;"]

Il valait mieux parcourir une vraie liste qu'un objet Cursor. Comme ça on est sûr qu'elle ne sera pas altérée à chaque itération.

Il faut terminer par:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
connexion.commit()
cursor.close()

@pacificator: merci quand même

**eyquem** · 07/07/2009, 20h07

J'ai voulu comprendre pourquoi le code initial qui paraissait bon ne marche pas, et le faire marcher. En m’acharnant à creuser le sujet, j’en suis arrivé à comprendre (certainement partiellement) les choses comme suit.

Apparemment, selectSql est une méthode du curseur cursor qui construit une sorte de planning de déplacement et d’extraction dans un ensemble de rows(rangées) défini par l’instruction SQLIte
"select id, tags from facts ORDER BY id;"
pour débiter des données de ces rangées à la demande. Pour débiter effectivement, il faut quelque chose de plus qui le sollicite: soit une instruction for...in selectSql, soit une une méthode fetchone() ou autre.
Finalement selectSql n’est rien d’autre qu’un itérateur d’une BDD passant par le curseur cursor.

Apparemment aussi, cursor n’apprécie pas d’être sollicité en plus pour construire une autre méthode consistant en un autre planning de déplacement et une mise à jour définie dans l’instruction SQLIte
"update facts set tags='%s' where id='%i'" %(tag, idt)
qui va s’exécuter tout de suite.
Cette fois, ça ressemble plus à une fonction.

cursor n’apprécie pas, c’est à dire que la fois suivante où l’itérateur selectSql doit débiter un autre couple de données, il ne le fait pas.

-------------------------------------

cursor étant perturbé par le fait qu’on lui demande deux choses, j’ai pensé distribuer sur deux curseurs les deux demandes.

Un curseur défini par des instructions

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
connexion = sqlite3.connect("zmabase.db3")
curs = connexion.cursor()

doit être envisagé comme un mix de pointeur (analogue à un pointeur de fichier) permettant de se repérer dans une base de données et d’un canal au travers duquel se font les échanges avec la BDD et les interventions sur elle.
Sur un curseur pèsent donc les contraintes aussi bien des pointeurs en général que des transactions dans une BDD SQLite.

Il faut bien qu’il y ait une partie ’pointeur“ dans un curseur, puisqu’on constate le comportement suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/usr/bin/python
# -*- coding: iso-8859-1 -*-
import sqlite3, os
 
# CREATE --------------------------------------------------------
if os.access('E:\Python\Essais Python\zmabase.db3',1 ):
    conn = sqlite3.connect("zmabase.db3")
    conn.cursor().execute('DELETE FROM stocks')
    conn.commit()
else:
    sqlite3.connect("zmabase.db3").execute('''create table stocks
    (id text,date text, trans text, symbol text,
    qty real, price real)''')
    conn = sqlite3.connect("zmabase.db3")
 
 
 
# INSERT --------------------------------------------------------
for t in (('11','2006-01-05','BUY','RHAT',100,42.63),
          ('21','2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('31','2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('41','2006-04-06', 'SELL', 'IBM', 500, 53.00),
          ('51','2005-11-23', 'COUIC','TAT', 124, 21.77),
          ('61','2034-01-02','PROJECT','FUTUR',2000,87.7),
          ('71','1998-51-52','Faillite','ANTIK',13,3009)
         ):
    conn.cursor().execute('insert into stocks values (?,?,?,?,?,?)', t)
conn.commit()
 
 
ce = conn.cursor().execute("select * from stocks")
print '111111111111111111111111111111111111111111111111111111111111'
for y in ce:
    print y
print
print '222222222222222222222222222222222222222222222222222222222222'
for y in ce:
    print y
print
print 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
111111111111111111111111111111111111111111111111111111111111
(u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
(u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
(u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
(u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
(u'61', u'2034-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
(u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
 
222222222222222222222222222222222222222222222222222222222222
 
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

Si on ne remet pas le compteur à zéro, sa réutilisation ne produit aucune sortie.

Par contre, si on écrit:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
print '111111111111111111111111111111111111111111111111111111111111'
for y in conn.cursor().execute("select * from stocks"):
    print y
print
print '222222222222222222222222222222222222222222222222222222222222'
for y in conn.cursor().execute("select * from stocks"):
    print y
print
print 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

on obtient deux fois la lecture de la BDD

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
111111111111111111111111111111111111111111111111111111111111
(u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
(u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
(u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
(u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
(u'61', u'2034-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
(u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
 
222222222222222222222222222222222222222222222222222222222222
(u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
(u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
(u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
(u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
(u'61', u'2034-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
(u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
 
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

l’instruction conn.cursor().execute("select * from stocks") crée un curseur en initialisant le pointeur au début de la BDD. Si cette instruction est rencontrée une deuxième fois, le curseur est recréé avec le pointeur au début. Sinon, en voulant réutiliser ce (1er code), le pointeur est à la fin de la BDD et l’exécution ne renvoie rien.

On peut aussi créer un deuxième curseur, soit avec le même canal et donc un pointeur différent,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
ce = conn.cursor().execute("select * from stocks")
cw = conn.cursor().execute("select * from stocks")
 
print '111111111111111111111111111111111111111111111111111111111111'
for y in ce:
    print y
print
print '222222222222222222222222222222222222222222222222222222222222'
for y in cw:
    print y
print
print 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

soit à partir d’une deuxième connexion avec la BDD et donc deux curseurs utilisant deux canaux différents,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
ce = conn.cursor().execute("select * from stocks")
connw = sqlite3.connect("zmabase.db3")
cw = connw.cursor().execute("select * from stocks")
 
print '111111111111111111111111111111111111111111111111111111111111'
for y in ce:
    print y
print
print '222222222222222222222222222222222222222222222222222222222222'
for y in cw:
    print y
print
print 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

ça marche encore:
on obtient deux lectures de la BDD.

On peut vérifier qu’un deuxième curseur construit sur un même canal a un pointeur différent, c’est à dire que les 2 pointeurs se déplacent de façons indépendantes. par le code suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/usr/bin/python
# -*- coding: iso-8859-1 -*-
import sqlite3, os
 
# CREATE --------------------------------------------------------
if os.access('E:\Python\Essais Python\zmabase.db3',1 ):
    conn = sqlite3.connect("zmabase.db3")
    conn.cursor().execute('DELETE FROM stocks')
    conn.commit()
else:
    sqlite3.connect("zmabase.db3").execute('''create table stocks
    (id text,date text, trans text, symbol text,
    qty real, price real)''')
    conn = sqlite3.connect("zmabase.db3")
 
 
 
# INSERT --------------------------------------------------------
for t in (('11','2006-01-05','BUY','RHAT',100,42.63),
          ('21','2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('31','2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('41','2006-04-06', 'SELL', 'IBM', 500, 53.00),
          ('51','2005-11-23', 'COUIC','TAT', 124, 21.77),
          ('61','2034-01-02','PROJECT','FUTUR',2000,87.7),
          ('71','1998-51-52','Faillite','ANTIK',13,3009)
         ):
    conn.cursor().execute('insert into stocks values (?,?,?,?,?,?)', t)
conn.commit()
 
print
for u in conn.cursor().execute("select * from stocks"):
    print u
 
 
# DEUX CURSEURS -------------------------------------------------
 
c = conn.cursor()
ce = c.execute("select * from stocks")
w = conn.cursor()
cw = w.execute("select id,symbol,qty,price from stocks")
 
p = 0
for u in ce:
    p+=1
    print '\np =',p
    print '  c',u
    if p in (2,3,5,7):
        x = cw.fetchone()
        print '  w',x
 
del conn
del c,w,ce,cw

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
(u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
(u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
(u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
(u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
(u'61', u'2034-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
(u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
 
p = 1
  c (u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
 
p = 2
  c (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
  w (u'11', u'RHAT', 100.0, 42.630000000000003)
 
p = 3
  c (u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
  w (u'21', u'IBM', 1000.0, 45.0)
 
p = 4
  c (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
 
p = 5
  c (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
  w (u'31', u'MSOFT', 1000.0, 72.0)
 
p = 6
  c (u'61', u'2034-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
 
p = 7
  c (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
  w (u'41', u'IBM', 500.0, 53.0)

---------------------------------------------

Maintenant si on fait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
# DEUX CURSEURS -------------------------------------------------
 
c = conn.cursor()
w = conn.cursor()
 
p = 0
for u in c.execute('select * from stocks'):
    p+=1
    print '\np =',p
    print '  c',u
    if p==4:
        c.execute("update stocks set trans='%s' where symbol='%s'" % ('$$$!BUY!$$$','MSOFT'))
        conn.commit()
        print 'fait'
 
del conn
del c
del w

on obtient le comportement décrit dans la question de goran, mais au 4ième tour, ce qui confirme que c’est bien l’update qui crée une interférence avec le curseur c.

L’interférence semble concerner plus spécialement la partie ’canal’ du curseur puisque l’interférence d’un update avec deux curseurs donne ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# DEUX CURSEURS -------------------------------------------------
 
c = conn.cursor()
w = conn.cursor()
 
p = 0
for u in c.execute('select * from stocks'):
    p+=1
    print '\np =',p
    print '  c',u
    if p==4:
        c.execute("update stocks set trans='%s' where symbol='%s'" % ('$$$!BUY!$$$','MSOFT'))
        conn.commit()
        print 'fait'
    if p in (2,3,5,7):
        x = w.execute("select id,symbol,qty,price from stocks").fetchone()
        print '  w',x
 
del conn
del c
del w

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
p = 1
  c (u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
 
p = 2
  c (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
  w (u'11', u'RHAT', 100.0, 42.630000000000003)
 
p = 3
  c (u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
  w (u'11', u'RHAT', 100.0, 42.630000000000003)
 
p = 4
  c (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
Traceback (most recent call last):
  File "E:\Python\Essais Python\zzzzzzz15.py", line 44, in <module>
    c.execute("update stocks set trans='%s' where symbol='%s'" % ('$$$!BUY!$$$','MSOFT'))
OperationalError: database table is locked

Il semble qu’on puisse interpréter ainsi: le programme commence d’abord par couper le curseur c impliqué dans le parcours de la BDD, puis le programme cherche à définir la transaction d’update, mais à ce moment-là bute sur le fait que la BDD est engagé dans un autre type de relation avec un autre curseur (w): la BDD n’est pas accessible.

Nota: toute modification dans une BDD SQLIte se réalise en effet par l’intermédiaire d’une transaction: toute commande qui déclenche une modification de BDD (delete, update, insert, replace) démarre automatiquement un processus basé sur une transaction, sauf si une transaction est déjà en cours. Il y a d’abord définition de la transaction, puis elle est effectuée (to commit: commettre, perpétrer) soit par une instruction COMMIT soit par la survenue d’une instruction SELECT*. Je n'arrive pas à retrouver où j’ai bien pu lire ce dernier point dans la foutue documentation du site SQLite3, tellement elle est bien faite.

Je pense que c'est cette dernière propriété (*) qui fait que, dans le code de goran, ça bloque: en réalité, au deuxième tour d’itération, idt, tag in selectSql déclenche d’abord la procédure de COMMIT sur la transaction de update qui était en cours (première), puis , comme cet achèvement de la transaction update a eu lieu, ce même idt, tag in selectSql n’arrive pas à avoir lieu parce que l’achèvement de la transaction a perturbé selectSql comme on l’a vu plus haut.

---------------------------------------------

J’ai fumé des neurones pendant des heures, d’abord en voulant comprendre le problème, puis en cherchant un code dans lequel il y aurait à la fois for u in c.execute('select * from stocks') et une instruction d’update qui ne perturberait pas le curseur c. En vain.

Je connaissais peu les BDD en commençant: eh bien j’ai trouvé la documentation du site SQLite3 tout à fait indigeste et elle a m’a bien bien énervé!
Elle est compacte, toutes les informations sont mises les unes à coté des autres sans livrer de plan d’ensemble qui donnerait a un novice une compréhension rapide. Il faut lire des quantité de pages pour acquérir les notions dispersées un peu partout. Il n'y a pas d’exemples. J’ai dû trouver la plupart des réponses et instructions que je cherchais sur Google et en lisant des forums. Il y a même des pages qui ne sont accessible que par un lien noyé dans des explications. Le pompon: il n'y a pas de fonction de recherche dans le site ! Pas vu en tous cas.

Enfin bref, pas de possibilité d'itération avec un curseur, et je ne trouvais pas terrible la solution de créer une liste séparée avant de commencer le parcours avec updating de la BDD:
cela nécessite d’une part un parcours préalable de la BDD, avec extraction de données et leur enregistrement progressif dans une liste; et d’autre part si la BDD est très grande, la liste préalable le sera aussi.

La seule solution est donc de trouver un moyen d’itérer sur les lignes de la BDD sans passer par un curseur. Pour cela, je ne vois pas d'autre moyen que de se baser sur un indice qu’on incrémente autant de fois qu’il y a de lignes dans la BDD, et qui permette d’extraire les lignes de la BDD. En fait cette définition fait que cet indice est tout simplement le numéro de rangée (rowid).

Pour pouvoir l'incrémenter simplement,
il faut faire exécuter un ordre VACUUM sur la BDD avant de commencer

tous les rowids s'étagent sagement de rowid=1 au rowid max par pas de 1.

Malgré la docu, j’ai quand même réussi à trouver comment compter le nombre de lignes (rangées), l'instruction VACUUM et la fonction fetchone()
Soit dit en passant , faîtes l’essai: tapez ’count sqlite3’ ou ’count rows sqlite3’ dans google -> les 2 seules références au site sqlite.org dans les trois premières pages de results conduisent à une page du site qui explique comment compter le nombre de lignes QUI ONT CONNU DES CHANGEMENTS. Toutes les autres sont des références de forums ou d’autres sites. Lamentable.

Bon, je m’étais d’abord perdu dans une solution avec DELETE de l’ancienne ligne et INSERT de la nouvelle après modification. Mais ça a le désavantage d’introduire des blancs à l’endroit des lignes éliminées. Or , comme expliqué ici:
http://www.sqlabs.net/blog/2007/01/s...thing-you.html
il survient parfois des défragmentations de la base de données au cours desquelles les rowids sont redéfinis. Même en admettant qu’on parte d’une BDD sans trous en faisant un VACUUM avant de commencer l’itération, c’est à dire avec des rowid bien ordonnés à partir de 1, rien ne prémunit contre la survenue d’une défragmentation après un certain nombre de délétions de lignes créant des trous -> bouleversement de la succession des rowid et donc de l'algorithme.

Si on fait un VACUUM avant le programme et seulement un usage de update (pas de DELETE de ligne) , il n’y a pas ce problème.

D'où enfin la solution sans liste extérieure désirée au départ (je laisse le code tel que je l'ai utilisé pour faire des essais, les lignes cruciales sont en bleu)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
#!/usr/bin/python
# -*- coding: iso-8859-1 -*-
import sqlite3, re, os


# CREATE --------------------------------------------------------
if os.access('E:\Python\Essais Python\zmabase.db3',1 ):
    conn = sqlite3.connect("zmabase.db3")
    conn.cursor().execute('DELETE FROM stocks')
    conn.commit()
else:
    sqlite3.connect("zmabase.db3").execute('''create table stocks
    (rowid PRIMARY KEY AUTOINCREMENT, id text, date text, trans text, symbol text,
    qty real, price real)''')
    conn = sqlite3.connect("zmabase.db3")
# ---------------------------------------------------------------



# INSERT
for t in (('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('11','2006-01-05','BUY','RHAT',100,42.63),
          ('21','2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('31','2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('41','2006-04-06', 'SELL', 'IBM', 500, 53.00),
          ('51','2005-11-23', 'COUIC','TAT', 124, 21.77),
          ('61','2035-01-02','PROJECT','FUTUR',2000,87.7),
          ('**','2222-05-11', '%%%', 'FDS', 123, 23),
          ('71','1998-51-52','Faillite','ANTIK',13,3009)
         ):
    conn.cursor().execute('insert into stocks values (?,?,?,?,?,?)', t)
conn.commit()



# SELECT etat
print "~~~~~ Etat initial de 'stocks' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
for u in conn.cursor().execute('select rowid,* from stocks'):
    print 'row '+str(u[0]).rjust(2)+'  ',u[1:]
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n'



# UPDATE
print "----- Updating et délétion --------------------------------------"
print "Un updating de la ligne where symbol='MSOFT'"
print "et une deletion de toutes les lignes where id=='**'"
print "sont réalisés."
conn.cursor().execute("update stocks set trans='%s' where symbol='%s'" % ('$$$!BUY!$$$','MSOFT'))
conn.cursor().execute("delete from stocks where id='**'")
print "-----------------------------------------------------------------\n\n"
conn.commit()






# TRANSFORMATION de la BDD
print '\n\n\n\n"""""""""""""  TRANSFORMATION DE LA BDD \'stocks\'  """""""""""""""""""""""""""""'

def parseTags(x):
    "Modification des tags"
    return x[3]+'ooVX'


print "\n~~~~~ Etat initial de 'stocks' au depart de la transformation ~~~"
for u in conn.cursor().execute('select rowid,* from stocks'):
    print 'row '+str(u[0])+'  ',u[1:]
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n'


c = conn.cursor()


c.execute("vacuum")
print "~~~~~ Etat de 'stocks' apres 'vacuum' ~~~~~~~~~~~~~~~~~~~~~~~~~~~"
for u in conn.cursor().execute('select rowid,* from stocks'):
    print 'row '+str(u[0])+'  ',u[1:]
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n'


print "----- Execution de la transformation ----------------------------"
nrows = c.execute("select count(*) from stocks").fetchone()[0]
c = conn.cursor()
for rangee in xrange(1, nrows + 1):
    s = c.execute("select * from stocks where rowid='%d'" % rangee).fetchone()
    print '\nrangee =',rangee,'  ',s
    if '5' in s[1]:
        print "                '5' in ",s[1],' ---> la rangee est mise a jour'
        c.execute("update stocks set symbol='%s' where rowid='%d'" % (parseTags(s),rangee))
        
    print "Etat de 'stocks' apres traitement de s:"
    for u in c.execute('select rowid,* from stocks'):
        print 'row '+str(u[0])+'  ',u[1:]

            
conn.commit()
del conn



# SELECT etat apres transformation
print '\n\n\n~~~~~ Etat de stocks apres transformation ~~~~~~~~~~~~~~~~~~~~~~~:'
for u in c.execute('select rowid,* from stocks'):
    print 'row '+str(u[0])+'  ',u[1:]
print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n'
del c

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
~~~~~ Etat initial de 'stocks' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
row  1   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row  2   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row  3   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row  4   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row  5   (u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
row  6   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row  7   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row  8   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row  9   (u'31', u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0)
row 10   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 11   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 12   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 13   (u'**', u'2222-05-11', u'%%%', u'FDS', 123.0, 23.0)
row 14   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


----- Updating et délétion --------------------------------------
Un updating de la ligne where symbol='MSOFT'
et une deletion de toutes les lignes where id=='**'sont réalisés.
-----------------------------------------------------------------






"""""""""""""  TRANSFORMATION DE LA BDD 'stocks'  """""""""""""""""""""""""""""

~~~~~ Etat initial de 'stocks' au depart de la transformation ~~~
row 5   (u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
row 6   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 9   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFT', 1000.0, 72.0)
row 10   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 11   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 12   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 14   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~ Etat de 'stocks' apres 'vacuum' ~~~~~~~~~~~~~~~~~~~~~~~~~~~
row 1   (u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFT', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


----- Execution de la transformation ----------------------------

rangee = 1    (u'11', u'2006-01-05', u'BUY', u'RHAT', 100.0, 42.630000000000003)
                '5' in  2006-01-05  ---> la rangee est mise a jour
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFT', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)

rangee = 2    (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFT', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)

rangee = 3    (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFT', 1000.0, 72.0)
                '5' in  2006-04-05  ---> la rangee est mise a jour
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFTooVX', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)

rangee = 4    (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFTooVX', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)

rangee = 5    (u'51', u'2005-11-23', u'COUIC', u'TAT', 124.0, 21.77)
                '5' in  2005-11-23  ---> la rangee est mise a jour
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFTooVX', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TATooVX', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)

rangee = 6    (u'61', u'2035-01-02', u'PROJECT', u'FUTUR', 2000.0, 87.700000000000003)
                '5' in  2035-01-02  ---> la rangee est mise a jour
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFTooVX', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TATooVX', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTURooVX', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)

rangee = 7    (u'71', u'1998-51-52', u'Faillite', u'ANTIK', 13.0, 3009.0)
                '5' in  1998-51-52  ---> la rangee est mise a jour
Etat de 'stocks' apres traitement de s:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFTooVX', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TATooVX', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTURooVX', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIKooVX', 13.0, 3009.0)



~~~~~ Etat de stocks apres transformation ~~~~~~~~~~~~~~~~~~~~~~~:
row 1   (u'11', u'2006-01-05', u'BUY', u'RHATooVX', 100.0, 42.630000000000003)
row 2   (u'21', u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
row 3   (u'31', u'2006-04-05', u'$$$!BUY!$$$', u'MSOFTooVX', 1000.0, 72.0)
row 4   (u'41', u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
row 5   (u'51', u'2005-11-23', u'COUIC', u'TATooVX', 124.0, 21.77)
row 6   (u'61', u'2035-01-02', u'PROJECT', u'FUTURooVX', 2000.0, 87.700000000000003)
row 7   (u'71', u'1998-51-52', u'Faillite', u'ANTIKooVX', 13.0, 3009.0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

En mettant la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

c.execute("vacuum")

en commentaire, on constate que le programme ne peut pas tourner.

Je me suis pas mal étendu parce que j’ai trouvé assez ardu d’arriver à comprendre un peu le fonctionnement du SGBD SQLite et à atteindre une solution, ça intéressera peut être quelqu’un.

Le fait est aussi que le problème n’a pas reçu de réponse jusqu’à présent, hormis celle de pacificator, mais désolé, en la faisant tourner ça bloque.
L’itérateur con.execute('select * from items') y est défini dans la déclaration de boucle for item in con.execute('select * from items'):
tandis que dans le code de goran l’itérateur selectSql est défini préalablement à la boucle.
Manifestement, ça fait une différence: dans le code de goran c’est selectSql qui bloque, dans le code de pacificator c’est l’instruction d’updating.
J’avoue que je ne comprends pas les arcanes de cette différence.

---------------------------------------------

Au passage , j’ai donc appris plein de chose sur le fonctionnementdu SGBD SQLite.
Mais aussi à mon petit avis, que c’est un piètre SGBD. Je sais, il est très employé, jusque dans les iPhones. J'ai aussi compris que le fonctionnement du SGBD SQLite a des complexités liées à l’objectif voulu par les développeurs que les transactions effectuées le soient avec fiabilité. Mais quand même, quand même... les intentions sont peut être bonnes, mais si la réalisation est déficiente, l’intention ne suffit pas. À mon humble avis,

SQLite ne doit pas être un des meilleurs SGBD.

1/ il a une documentation sur le site indigeste (ça pourrait se corriger mais les développeurs n’ont pas l’air pressés de faire certaines modifs).

2/ il a des insuffisances techniques: pas d’accès concurrents à la BDD, et d’autres trucs encore que j’ai lus de spécialistes sans bien comprendre

3/ il y a des bugs non corrigés depuis longtemps:
« This asymmetrical behavior is unfortunate and is really due to a bug in the parser in early versions of SQLite. But fixing the bug would result in very serious backwards incompatibilities. The SQLite developers feel that goofy behavior in an corner case is far better than a compatibility break, so the original behavior is retained. »
tout en bas de la page
http://www.sqlite.org/lang_createtable.html

Et dans la même page,
«Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly.»
Ça fait longtemps que ça ne va pas, et un jour, certainement un jour, attention, le problème sera réglé. C'est risible non ?

4/ en cas d’instruction impossible à réaliser, il arrive qu’il n’y ait pas de message d’erreur ou d’avertissement qui soit fait. Ça explique certains comportements que je ne comprenais pas pendant que je me remuais les neurones sur ce sujet. C’est vachement pratique !

Par contre, les développeurs de SQLite laissent trainer dans le code des warnings qui ne devraient plus y être :
« Some people say that we should eliminate all warnings because benign warnings mask real warnings that might arise in future changes. »
http://www.sqlite.org/faq.html#q17
C’est vachement entertenu leur truc !

5/ je me suis enfin aperçu que les nouvelles versions sortent à rythme soutenu, et que ça a l’air d’être rendu nécessaire par des bugs à répétition. Ça me fait penser à Windows.
http://www.developpez.net/forums/d61...lles-versions/

Je ne devrais peut être pas être aussi critique , armé de ma seule ignorance généralisée en informatique, mais enfin bon, vu le nombre d’heures que j’ai passé sur ce problême, je m’y autorise.

J’espère ne pas avoir été trop indigeste.

**goran kajfes** · 26/07/2009, 18h30

Salut eyquem. Je n'ai pris connaissance du message privée qu'il y a 2 jours. J'avais bien vu ton message public sur cette page, mais sa longueur m'avait fait partir en courant.

Désolé. En fait, n'utilisant que très rarement sqlite3, et de surcroit sur de pas trop grandes bases de données, je me suis contenté de ma liste, quitte à réquisionner de la mémoire pour rien. En tout cas, j'ai bien noté que fetchone[] permettait de produire un code plus rigoureux, que j'utiliserai le jour où je ne me contenterai pas de simples bidouilles sur des bases de données sqlite3. Et comme tu le disais, cela pourrait en intéresser d'autres.

Tes recherches auraient peut-être leur place sur la FAQ Python du site.

PS : je ne suis pas non plus un fan de la documentation python en général. S'il n'y a pas d'outil de recherche en ligne, il n'y en a pas non plus en local. Pas sous Linux en tout cas. On est loin de la qualité de la documentation Qt4 (QtAssistant). Même quand on ne connait que Python et pas le C++, on s'y retrouve bien mieux.

sqlite3, requête update et sortie de boucle

Python

Discussions similaires

Partager

Partager