When using SQLite from Python, sqlite3 is available by default in Python 2.5 and above.
It provides a SQL interface that conforms to the DB-API 2.0 specification and is developed under the name pysqlite.
sqlite3 http://docs.python.jp/2/library/sqlite3.html
However, this library does not have all the features of SQLite. This is to comply with the DB-API specifications.
APSW is a library that allows you to fully use the SQLite API in Python. APSW works with Python 2.x and Python 3.x.
https://github.com/rogerbinns/apsw http://rogerbinns.github.io/apsw/
It cannot be installed with pip or easy_install.
** 2019.08.03 update ** Currently, you can also install the following.
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
See below for the latest information. https://rogerbinns.github.io/apsw/download.html#easy-install-pip-pypi
Download the appropriate binary from the following and run the installer.
http://rogerbinns.github.io/apsw/download.html#source-and-binaries
If you have Visual Studio, you can build from source just like UNIX. (With mingw32, I could build it, but it didn't work due to lack of DLL at runtime.)
Download the source code and execute the following command.
python setup.py fetch --all build --enable-all-extensions install
Details about the build can be found on the following page. http://rogerbinns.github.io/apsw/build.html
APSW and pysqlite provide access to SQLite from radically different directions.
APSW wraps only version 3 of SQLite and provides a way to access all APIs.
pysqlite behaves like any other database in order to provide a DBAPI compliant wrapper. Therefore, it hides some SQLite features.
Below are some of the benefits and enhanced features of APSW.
APSW makes the latest version of SQLite available. If features are added or changed in SQLite, APSW will follow those features as well.
Virtual Table is a feature introduced in SQLite 3.3.7.
Virtual Tables are no different from other tables and views in terms of SQL statements. But behind the scenes, queries and writes to the Virtual Table trigger callback methods instead of reading and writing database files.
The following is an example of manipulating 2D array data with 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]
In this way, you can manipulate arbitrary data with SQL by using VirutalTable. In the official sample, there is a sample that selects the files in the directory with SQL. http://apidoc.apsw.googlecode.com/hg/example.html
For other details of VirtualTable, refer to the following. http://rogerbinns.github.io/apsw/vtable.html
A VFS is available that defines the interface between SQLite's core and underlying operating systems.
With APSW, you can use the functions of VFS, and you can inherit the default VFS and add extended functions. For example, the following example uses VFS to obfuscate a SQLite file.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
###This sample is excerpted from the following
### http://apidoc.apsw.googlecode.com/hg/example.html
###Obfuscate the database with VFS
###XOR all bytes of the schema with 0xa5.
###This method is used by MAPI and SQL SERVER
###
def encryptme(data):
if not data: return data
return "".join([chr(ord(x)^0xa5) for x in data])
# ""Inheritance from the base of is the default VFS
class ObfuscatedVFS(apsw.VFS):
def __init__(self, vfsname="obfu", basevfs=""):
self.vfsname=vfsname
self.basevfs=basevfs
apsw.VFS.__init__(self, self.vfsname, self.basevfs)
#I want to implement my own file, but I also want it to be inherited
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)
#Override xRead and xWrite to implement crypto routines
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)")
#Check the contents of the actual disc
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")
See below for details. http://rogerbinns.github.io/apsw/vfs.html
Blob is a SQLite data type that represents a sequence of bytes. It is a byte of size zero or greater.
You can read and write to this Blob using APSW. The usage example is shown below.
# -*- 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()
Please refer to the following for details. http://rogerbinns.github.io/apsw/blob.html
APSW can use backup to back up a connected DB to another connected DB.
# -*- coding: utf-8 -*-
import os, sys, time
import apsw
import os
connection=apsw.Connection("src.sqlite")
cursor=connection.cursor()
#Create backup source
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')")
#backup
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
Please refer to the following for details. http://rogerbinns.github.io/apsw/backup.html
You can share connections and cursors across threads. For pysqlite, Connection and cursors must be used in the same thread.
pysqlite example
# -*- 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()
Since pysqlite does not allow operations across threads, the following exception will occur.
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
In the case of APSW, no exception is raised across similar threads.
Straddle threads with 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()
However, if you do not handle exclusive processing carefully, it will cause a crash or deadlock.
APSW allows you to use nested transactions by using Connection's Context Manager. Only one transaction is available at a time in pysqlite, not nesting.
The SavePoint used in this nested transaction was added in SQLite 3.6.8. This is one of the advantages of APSW, which allows you to use SQLite up to date.
The following is an example of a nested transaction.
# -*- 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: #Start a transaction with with. Rollback with exceptions, Commit otherwise
connection.cursor().execute("insert into test values(1,'TEST1')")
try: #Start SAVEPOINT with with. Rollback with exceptions, Commit otherwise
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: #The following SQL is not recorded with an error
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]
** About Connection Context ** http://rogerbinns.github.io/apsw/connection.html#apsw.Connection.enter
** About SQLite nested transactions ** https://sqlite.org/lang_savepoint.html
APSW allows multiple commands to be executed by separating them with a semicolon.
# -*- 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
In APSW, SQL that returns data like SELECT is available in 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
In pysqlite, executemany () cannot be used in SQL including SELECT. http://stackoverflow.com/questions/14142554/sqlite3-python-executemany-select
APSW outputs an easy-to-track exception, such as when an error occurs in a user-defined function.
Below, let's check the difference when an exception is raised in a user-defined function.
pysqlite exception
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)")
If enable_callback_tracebacks is False (default), the following error will occur.
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
If enable_callback_tracebacks is True, the following error will occur.
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
If enable_callback_tracebacks is False, the exceptions in the user-defined function are squeezed, and even if this is set to True, the way Traceback is displayed will not be intuitive.
On the other hand, let's look at the exception of APSW.
Exception in 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 outputs an intuitively easy-to-understand Traceback as follows.
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 easily traces SQL execution without changing your code and provides a summary report.
APSW Trace is located in the tools folder in the source code below. http://rogerbinns.github.io/apsw/download.html#source-and-binaries
Execution method
$ python /path/to/apswtrace.py [apswtrace options] yourscript.py [your options]
** Execution example ** The following is an example of requesting a report of the script used in "Using Nested Transactions".
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>
For other details, please refer to the following. http://rogerbinns.github.io/apsw/execution.html#apswtrace
The following tests show that APSW is faster than 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