Lorsque vous utilisez SQLite à partir de Python, sqlite3 est disponible par défaut dans Python 2.5 et supérieur.
Il fournit une interface SQL conforme à la spécification DB-API 2.0 et a été développée sous le nom de pysqlite.
sqlite3 http://docs.python.jp/2/library/sqlite3.html
Cependant, cette bibliothèque ne possède pas toutes les fonctionnalités de SQLite. Ceci est conforme aux spécifications DB-API.
APSW est une bibliothèque qui vous permet d'utiliser pleinement l'API SQLite en Python. APSW fonctionne avec Python 2.x et Python 3.x.
https://github.com/rogerbinns/apsw http://rogerbinns.github.io/apsw/
L'installation n'est pas possible avec pip ou easy_install.
** Mise à jour du 03.08.2019 ** Actuellement, il peut également être installé ci-dessous.
pip install --user https://github.com/rogerbinns/apsw/releases/download/3.28.0-r1/apsw-3.28.0-r1.zip --global-option=fetch --global-option=--version --global-option=3.28.0 --global-option=--all --global-option=build --global-option=--enable-all-extensions
Voir ci-dessous pour les dernières informations. https://rogerbinns.github.io/apsw/download.html#easy-install-pip-pypi
Téléchargez le binaire approprié à partir de ce qui suit et exécutez le programme d'installation.
http://rogerbinns.github.io/apsw/download.html#source-and-binaries
Si vous disposez de Visual Studio, vous pouvez créer à partir de la source comme UNIX. (Avec mingw32, je pourrais le construire, mais cela n'a pas fonctionné en raison du manque de DLL au moment de l'exécution.)
Téléchargez le code source et exécutez la commande suivante.
python setup.py fetch --all build --enable-all-extensions install
Des détails sur la construction peuvent être trouvés sur la page suivante. http://rogerbinns.github.io/apsw/build.html
APSW et pysqlite permettent d'accéder à SQLite depuis des directions radicalement différentes.
APSW encapsule uniquement la version 3 de SQLite et fournit un moyen d'accéder à toutes les API.
pysqlite se comporte comme n'importe quelle autre base de données afin de fournir un wrapper compatible DBAPI. Par conséquent, il cache certaines fonctionnalités de SQLite.
Vous trouverez ci-dessous certains des avantages et des fonctionnalités améliorées d'APSW.
APSW met à disposition la dernière version de SQLite. Si des fonctionnalités sont ajoutées ou modifiées dans SQLite, APSW suivra également ces fonctionnalités.
Table virtuelle est une fonctionnalité introduite dans SQLite 3.3.7.
Les tables virtuelles ne sont pas différentes des autres tables et vues en termes d'instructions SQL. Mais dans les coulisses, les requêtes et les écritures dans Virtual Table déclenchent des méthodes de rappel au lieu de lire et d'écrire des fichiers de base de données.
Voici un exemple de manipulation de données dans un tableau à deux dimensions avec SQL.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
connection=apsw.Connection(":memory:")
cursor=connection.cursor()
###
### Virtual tables
###
#
data = [
[1, 'test1', 'categoryA'],
[2, 'test2', 'categoryA'],
[3, 'test3', 'categoryA'],
[4, 'test4', 'categoryB'],
[5, 'test5', 'categoryB'],
[6, 'test6', 'categoryB'],
[7, 'test7', 'categoryB'],
[8, 'test8', 'categoryC'],
[9, 'test9', 'categoryC'],
[10, 'test10', 'categoryC']
]
counter = len(data)
# This gets registered with the Connection
class Source:
def Create(self, db, modulename, dbname, tablename, *args):
columns = ['rowid', 'name', 'category']
schema="create table foo("+','.join(["'%s'" % (x,) for x in columns[1:]])+")"
return schema,Table(columns,data)
Connect=Create
# Represents a table
class Table:
def __init__(self, columns, data):
self.columns=columns
self.data=data
def BestIndex(self, *args):
return None
def Open(self):
return Cursor(self)
def Disconnect(self):
pass
def UpdateChangeRow(self, row, newrowid, fields):
for d in data:
if(d[0] == row):
d[0] = newrowid
d[1] = fields[0]
d[2] = fields[1]
def UpdateDeleteRow(self, row):
for i in range(len(data)):
if(data[i][0] == row):
del data[i]
return
def UpdateInsertRow(self, rowid, fields):
global counter
counter = counter + 1
data.append([counter, fields[0], fields[1]])
return counter
Destroy=Disconnect
# Represents a cursor
class Cursor:
def __init__(self, table):
self.table=table
def Filter(self, *args):
self.pos=0
def Eof(self):
return self.pos>=len(self.table.data)
def Rowid(self):
return self.table.data[self.pos][0]
def Column(self, col):
return self.table.data[self.pos][1+col]
def Next(self):
self.pos+=1
def Close(self):
pass
connection.createmodule("source", Source())
cursor.execute("create virtual table test using source()")
ret = cursor.execute("select * from test where category = 'categoryB'")
for row in ret:
print row[0], row[1]
print ('update -----------------')
cursor.execute("update test set category='categoryB' where name='test1'")
ret = cursor.execute("select * from test where category = 'categoryB'")
for row in ret:
print row[0], row[1]
print ('delete -----------------')
cursor.execute("delete from test where name='test4'")
ret = cursor.execute("select * from test")
for row in ret:
print row[0], row[1]
print ('insert ----------------')
cursor.execute("insert into test values('xxxx','yyyy')")
ret = cursor.execute("select * from test")
for row in ret:
print row[0], row[1]
De cette manière, vous pouvez manipuler des données arbitraires avec SQL à l'aide de VirutalTable. Dans l'exemple officiel, il existe un exemple qui sélectionne les fichiers dans le répertoire avec SQL. http://apidoc.apsw.googlecode.com/hg/example.html
Pour plus de détails sur VirtualTable, reportez-vous à ce qui suit. http://rogerbinns.github.io/apsw/vtable.html
Vous pouvez utiliser VFS, qui définit l'interface entre les systèmes d'exploitation principaux et sous-jacents de SQLite.
Avec APSW, vous pouvez tirer parti de la fonctionnalité VFS, et vous pouvez hériter et étendre le VFS par défaut. Par exemple, l'exemple suivant utilise VFS pour masquer un fichier SQLite.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
###Cet exemple est extrait de ce qui suit
### http://apidoc.apsw.googlecode.com/hg/example.html
###Obfusquer la base de données avec VFS
###XOR tous les octets du schéma avec 0xa5.
###Cette méthode est utilisée par MAPI et SQL SERVER
###
def encryptme(data):
if not data: return data
return "".join([chr(ord(x)^0xa5) for x in data])
# ""L'héritage de la base de est le VFS par défaut
class ObfuscatedVFS(apsw.VFS):
def __init__(self, vfsname="obfu", basevfs=""):
self.vfsname=vfsname
self.basevfs=basevfs
apsw.VFS.__init__(self, self.vfsname, self.basevfs)
#Je veux implémenter mon propre fichier, mais je veux aussi en hériter
def xOpen(self, name, flags):
# We can look at uri parameters
if isinstance(name, apsw.URIFilename):
print "fast is", name.uri_parameter("fast")
print "level is", name.uri_int("level", 3)
print "warp is", name.uri_boolean("warp", False)
print "notpresent is", name.uri_parameter("notpresent")
return ObfuscatedVFSFile(self.basevfs, name, flags)
#Remplacez xRead et xWrite pour implémenter des routines cryptographiques
class ObfuscatedVFSFile(apsw.VFSFile):
def __init__(self, inheritfromvfsname, filename, flags):
apsw.VFSFile.__init__(self, inheritfromvfsname, filename, flags)
def xRead(self, amount, offset):
return encryptme(super(ObfuscatedVFSFile, self).xRead(amount, offset))
def xWrite(self, data, offset):
super(ObfuscatedVFSFile, self).xWrite(encryptme(data), offset)
# To register the VFS we just instantiate it
obfuvfs=ObfuscatedVFS()
# Lets see what vfs are now available?
print apsw.vfsnames()
# Make an obfuscated db, passing in some URI parameters
obfudb=apsw.Connection("file:myobfudb?fast=speed&level=7&warp=on",
flags=apsw.SQLITE_OPEN_READWRITE | apsw.SQLITE_OPEN_CREATE | apsw.SQLITE_OPEN_URI,
vfs=obfuvfs.vfsname)
# Check it works
obfudb.cursor().execute("create table foo(x,y); insert into foo values(1,2)")
#Vérifiez le contenu du disque réel
print `open("myobfudb", "rb").read()[:20]`
# '\xf6\xf4\xe9\xcc\xd1\xc0\x85\xc3\xca\xd7\xc8\xc4\xd1\x85\x96\xa5\xa1\xa5\xa4\xa4'
print `encryptme(open("myobfudb", "rb").read()[:20])`
# 'SQLite format 3\x00\x04\x00\x01\x01'
# Tidy up
obfudb.close()
os.remove("myobfudb")
Voir ci-dessous pour plus de détails. http://rogerbinns.github.io/apsw/vfs.html
Blob est un type de données SQLite qui représente une séquence d'octets. Il s'agit d'un octet de taille zéro ou supérieure.
Vous pouvez lire et écrire dans cet objet blob en utilisant APSW. L'exemple d'utilisation est présenté ci-dessous.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
import os
connection=apsw.Connection("blob.sqlite")
cursor=connection.cursor()
###
### Blob I/O
### http://apidoc.apsw.googlecode.com/hg/example.html
cursor.execute("create table blobby(x,y)")
# Add a blob we will fill in later
cursor.execute("insert into blobby values(1,zeroblob(10000))")
# Or as a binding
cursor.execute("insert into blobby values(2,?)", (apsw.zeroblob(20000),))
# Open a blob for writing. We need to know the rowid
rowid=cursor.execute("select ROWID from blobby where x=1").next()[0]
blob=connection.blobopen("main", "blobby", "y", rowid, 1) # 1 is for read/write
blob.write("hello world")
blob.seek(100)
blob.write("hello world, again")
blob.close()
Veuillez vous référer à ce qui suit pour plus de détails. http://rogerbinns.github.io/apsw/blob.html
APSW peut utiliser la sauvegarde pour sauvegarder un DB connecté sur un autre DB connecté.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
import os
connection=apsw.Connection("src.sqlite")
cursor=connection.cursor()
#Créer une source de sauvegarde
cursor.execute("create table test(x,y)")
cursor.execute("insert into test values(1,'TEST1')")
cursor.execute("insert into test values(2,'TEST2')")
cursor.execute("insert into test values(3,'TEST3')")
cursor.execute("insert into test values(4,'TEST4')")
cursor.execute("insert into test values(5,'TEST5')")
#sauvegarde
memcon=apsw.Connection("backup.sqlite")
with memcon.backup("main", connection, "main") as backup:
backup.step() # copy whole database in one go
for row in memcon.cursor().execute("select * from test"):
print row[0], row[1]
pass
Veuillez vous référer à ce qui suit pour plus de détails. http://rogerbinns.github.io/apsw/backup.html
Vous pouvez partager des connexions et des curseurs entre les threads. Pour pysqlite, la connexion et les curseurs doivent être utilisés dans le même thread.
exemple pysqlite
# -*- coding: utf-8 -*-
import threading
import sqlite3
def func(t):
return 1 + t
class TestThread(threading.Thread):
def __init__(self, conn):
threading.Thread.__init__(self)
self.conn = conn
def run(self):
self.conn.create_function("func", 1, func)
cur = self.conn.cursor()
ret = cur.execute("select func(3)")
for row in ret:
print(row[0])
conn = sqlite3.connect(":memory:")
th = TestThread(conn)
th.start()
th.join()
Puisque pysqlite n'autorise pas les opérations cross-thread, les exceptions suivantes se produisent.
Exception in thread Thread-1:
Traceback (most recent call last):
File "C:\Python27\lib\threading.py", line 810, in __bootstrap_inner
self.run()
File "test_thread.py", line 14, in run
self.conn.create_function("func", 1, func)
ProgrammingError: SQLite objects created in a thread can only be used in that sa
me thread.The object was created in thread id 19540 and this is thread id 4652
Dans le cas de APSW, aucune exception n'est levée sur des threads similaires.
Thrraddle threads avec APSW
# -*- coding: utf-8 -*-
import threading
import apsw
def func(t):
return 1 + t
class TestThread(threading.Thread):
def __init__(self, conn):
threading.Thread.__init__(self)
self.conn = conn
def run(self):
self.conn.createscalarfunction("func", func, 1)
cur = self.conn.cursor()
ret = cur.execute("select func(3)")
for row in ret:
print(row[0])
conn = apsw.Connection(":memory:")
th = TestThread(conn)
th.start()
th.join()
Cependant, si vous ne gérez pas le traitement exclusif avec soin, cela provoquera un blocage ou un blocage.
APSW vous permet d'utiliser des transactions imbriquées à l'aide du gestionnaire de contexte de Connection. Une seule transaction peut être utilisée à la fois dans pysqlite et l'imbrication n'est pas possible.
Le SavePoint utilisé dans cette transaction imbriquée a été ajouté dans SQLite 3.6.8. C'est l'un des avantages d'APSW, qui vous permet d'utiliser SQLite à jour.
Voici un exemple de transactions imbriquées.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
import os
connection=apsw.Connection(":memory:")
connection.cursor().execute("create table test(x primary key,y)")
with connection: #Démarrez une transaction avec avec. Rollback avec exceptions, valider autrement
connection.cursor().execute("insert into test values(1,'TEST1')")
try: #Démarrez SAUVEGARDER POINT avec avec. Rollback avec exceptions, valider autrement
with connection:
connection.cursor().execute("insert into test values(2,'TEST2')")
connection.cursor().execute("insert into test values(3,'TEST3')")
except Exception, ex:
print (ex)
print ('rollback 1')
try:
with connection: #Le SQL suivant n'est pas enregistré avec une erreur
connection.cursor().execute("insert into test values(4,'TEST4')")
connection.cursor().execute("insert into test values(4,'Error')")
except Exception, ex:
print (ex)
print ('rollback 2')
try:
with connection:
connection.cursor().execute("insert into test values(5,'TEST5')")
connection.cursor().execute("insert into test values(6,'TEST6')")
except Exception, ex:
print (ex)
print ('rollback 3')
for row in connection.cursor().execute("select * from test"):
print row[0], row[1]
** À propos du contexte de connexion ** http://rogerbinns.github.io/apsw/connection.html#apsw.Connection.enter
** À propos des transactions imbriquées SQLite ** https://sqlite.org/lang_savepoint.html
Dans APSW, plusieurs commandes peuvent être exécutées en les séparant par un point-virgule.
# -*- coding: utf-8 -*-
import apsw
con=apsw.Connection(":memory:")
cur=con.cursor()
for row in cur.execute("create table foo(x,y,z);insert into foo values (?,?,?);"
"insert into foo values(?,?,?);select * from foo;drop table foo;"
"create table bar(x,y);insert into bar values(?,?);"
"insert into bar values(?,?);select * from bar;",
(1,2,3,4,5,6,7,8,9,10)):
print row
Dans APSW, SQL qui renvoie des données comme SELECT est disponible dans Cursor.executemany ().
# -*- coding: utf-8 -*-
import apsw
con=apsw.Connection(":memory:")
cur=con.cursor()
cur.execute("create table foo(x);")
cur.executemany("insert into foo (x) values(?)", ( [1], [2], [3] ) )
# You can also use it for statements that return data
for row in cur.executemany("select * from foo where x=?", ( [1], [2], [3] ) ):
print row
Dans pysqlite, executemany () ne peut pas être utilisé dans SQL, y compris SELECT. http://stackoverflow.com/questions/14142554/sqlite3-python-executemany-select
APSW génère une exception facile à suivre, par exemple lorsqu'une erreur se produit dans une fonction définie par l'utilisateur.
Ci-dessous, vérifions la différence lorsqu'une exception est déclenchée dans une fonction définie par l'utilisateur.
exception pysqlite
import sqlite3
def badfunc(t):
return 1/0
#sqlite3.enable_callback_tracebacks(True)
con = sqlite3.connect(":memory:")
con.create_function("badfunc", 1, badfunc)
cur = con.cursor()
cur.execute("select badfunc(3)")
Si enable_callback_tracebacks vaut False (par défaut), vous obtiendrez l'erreur suivante:
Traceback (most recent call last):
File "test_fnc1.py", line 9, in <module>
cur.execute("select badfunc(3)")
sqlite3.OperationalError: user-defined function raised exception
Si enable_callback_tracebacks a la valeur True, l'erreur suivante se produit.
Traceback (most recent call last):
File "test_fnc1.py", line 3, in badfunc
return 1/0
ZeroDivisionError: integer division or modulo by zero
Traceback (most recent call last):
File "test_fnc1.py", line 9, in <module>
cur.execute("select badfunc(3)")
sqlite3.OperationalError: user-defined function raised exception
Si enable_callback_tracebacks a la valeur False, les exceptions de la fonction définie par l'utilisateur sont écrasées, et même si elle est définie sur True, la façon dont Traceback est affiché ne sera pas intuitive.
D'un autre côté, regardons l'exception de APSW.
Exception dans APSW
def badfunc(t):
return 1/0
import apsw
con = apsw.Connection(":memory:")
con.createscalarfunction("badfunc", badfunc, 1)
cur = con.cursor()
cur.execute("select badfunc(3)")
APSW produit Traceback intuitivement facile à comprendre comme suit.
Traceback (most recent call last):
File "test_fnc2.py", line 9, in <module>
cur.execute("select badfunc(3)")
File "c:\apsw\src\connection.c", line 2021, in user-defined-scalar-badfunc
File "test_fnc2.py", line 2, in badfunc
return 1/0
ZeroDivisionError: integer division or modulo by zero
APSW Trace trace facilement l'exécution SQL sans changer votre code et fournit un rapport de synthèse.
APSW Trace se trouve dans le dossier tools du code source ci-dessous. http://rogerbinns.github.io/apsw/download.html#source-and-binaries
** Méthode d'exécution **
$ python /path/to/apswtrace.py [apswtrace options] yourscript.py [your options]
** Exemple d'exécution ** Voici un exemple de demande de rapport du script utilisé dans "Utilisation de transactions imbriquées".
C:\dev\python\apsw>python apswtrace.py --sql --rows --timestamps --thread test_n
est.py
290e5c0 0.002 1734 OPEN: "" win32 READWRITE|CREATE
292aad8 0.009 1734 CURSORFROM: 290e5c0 DB: ""
292aad8 0.010 1734 SQL: create table test(x primary key,y)
290e5c0 0.012 1734 SQL: SAVEPOINT "_apsw-0"
292aad8 0.013 1734 CURSORFROM: 290e5c0 DB: ""
292aad8 0.015 1734 SQL: insert into test values(1,'TEST1')
290e5c0 0.016 1734 SQL: SAVEPOINT "_apsw-1"
292aad8 0.018 1734 CURSORFROM: 290e5c0 DB: ""
292aad8 0.019 1734 SQL: insert into test values(2,'TEST2')
292aad8 0.021 1734 CURSORFROM: 290e5c0 DB: ""
292aad8 0.022 1734 SQL: insert into test values(3,'TEST3')
290e5c0 0.023 1734 SQL: RELEASE SAVEPOINT "_apsw-1"
290e5c0 0.025 1734 SQL: SAVEPOINT "_apsw-1"
292ab10 0.026 1734 CURSORFROM: 290e5c0 DB: ""
292ab10 0.028 1734 SQL: insert into test values(4,'TEST4')
292ab10 0.029 1734 CURSORFROM: 290e5c0 DB: ""
292ab10 0.031 1734 SQL: insert into test values(4,'Error')
290e5c0 0.032 1734 SQL: ROLLBACK TO SAVEPOINT "_apsw-1"
290e5c0 0.034 1734 SQL: RELEASE SAVEPOINT "_apsw-1"
ConstraintError: UNIQUE constraint failed: test.x
rollback 2
290e5c0 0.038 1734 SQL: SAVEPOINT "_apsw-1"
292ab48 0.040 1734 CURSORFROM: 290e5c0 DB: ""
292ab48 0.041 1734 SQL: insert into test values(5,'TEST5')
292ab48 0.043 1734 CURSORFROM: 290e5c0 DB: ""
292ab48 0.044 1734 SQL: insert into test values(6,'TEST6')
290e5c0 0.046 1734 SQL: RELEASE SAVEPOINT "_apsw-1"
290e5c0 0.047 1734 SQL: RELEASE SAVEPOINT "_apsw-0"
292acd0 0.049 1734 CURSORFROM: 290e5c0 DB: ""
292acd0 0.050 1734 SQL: select * from test
292acd0 0.052 1734 ROW: (1, "TEST1")
1 TEST1
292acd0 0.056 1734 ROW: (2, "TEST2")
2 TEST2
292acd0 0.059 1734 ROW: (3, "TEST3")
3 TEST3
292acd0 0.062 1734 ROW: (5, "TEST5")
5 TEST5
292acd0 0.066 1734 ROW: (6, "TEST6")
6 TEST6
APSW TRACE SUMMARY REPORT
Program run time 0.072 seconds
Total connections 1
Total cursors 9
Number of threads used for queries 1
Total queries 18
Number of distinct queries 14
Number of rows returned 5
Time spent processing queries 0.017 seconds
MOST POPULAR QUERIES
3 SAVEPOINT "_apsw-1"
3 RELEASE SAVEPOINT "_apsw-1"
1 select * from test
1 insert into test values(6,'TEST6')
1 insert into test values(5,'TEST5')
1 insert into test values(4,'TEST4')
1 insert into test values(4,'Error')
1 insert into test values(3,'TEST3')
1 insert into test values(2,'TEST2')
1 insert into test values(1,'TEST1')
1 create table test(x primary key,y)
1 SAVEPOINT "_apsw-0"
1 ROLLBACK TO SAVEPOINT "_apsw-1"
1 RELEASE SAVEPOINT "_apsw-0"
LONGEST RUNNING - AGGREGATE
1 0.017 select * from test
3 0.000 SAVEPOINT "_apsw-1"
3 0.000 RELEASE SAVEPOINT "_apsw-1"
1 0.000 insert into test values(6,'TEST6')
1 0.000 insert into test values(5,'TEST5')
1 0.000 insert into test values(4,'TEST4')
1 0.000 insert into test values(4,'Error')
1 0.000 insert into test values(3,'TEST3')
1 0.000 insert into test values(2,'TEST2')
1 0.000 insert into test values(1,'TEST1')
1 0.000 create table test(x primary key,y)
1 0.000 SAVEPOINT "_apsw-0"
1 0.000 ROLLBACK TO SAVEPOINT "_apsw-1"
1 0.000 RELEASE SAVEPOINT "_apsw-0"
LONGEST RUNNING - INDIVIDUAL
0.017 select * from test
0.000 insert into test values(6,'TEST6')
0.000 insert into test values(5,'TEST5')
0.000 insert into test values(4,'TEST4')
0.000 insert into test values(4,'Error')
0.000 insert into test values(3,'TEST3')
0.000 insert into test values(2,'TEST2')
0.000 insert into test values(1,'TEST1')
0.000 create table test(x primary key,y)
0.000 SAVEPOINT "_apsw-1"
0.000 SAVEPOINT "_apsw-1"
0.000 SAVEPOINT "_apsw-1"
0.000 SAVEPOINT "_apsw-0"
0.000 ROLLBACK TO SAVEPOINT "_apsw-1"
0.000 RELEASE SAVEPOINT "_apsw-1"
C:\dev\python\apsw>
Pour plus de détails, veuillez consulter ce qui suit. http://rogerbinns.github.io/apsw/execution.html#apswtrace
Les tests suivants montrent qu'APSW est plus rapide que pysqlite. http://rogerbinns.github.io/apsw/benchmarking.html
APSW 3.8.7.3-r1 documentation » pysqlite differences http://rogerbinns.github.io/apsw/pysqlite.html#pysqlitediffs
Recommended Posts