apsw-3.3.5-r1 25th April 2005
APSW provides an SQLite 3 wrapper that provides the thinnest layer over SQLite 3 possible. Everything you can do from the C API to SQLite 3, you can do from Python. Although APSW looks vaguely similar to the DBAPI, it is not compliant with that API and instead works the way SQLite 3 does. (pysqlite is DBAPI compliant - differences between apsw and pysqlite 2).
You can download APSW from SourceForge at http://sourceforge.net/project/showfiles.php?group_id=75211&package_id=113804. There is a zip of the source as well as binary packages for Windows.
Some Linux distributions also have packages. Debian users can grab the package python-apsw. Gentoo users can grab the package dev-python/apsw.
The source is controlled by Subversion accessible as https://svn.sourceforge.net/svnroot/bitpim/subprojects/apsw/trunk and can be web browsed at http://svn.sourceforge.net/viewcvs.cgi/bitpim/subprojects/hb2web/trunk/
APSW binds to the C interface of SQLite. That interface is stable for each major version of SQLite (ie the C interface for any SQLite 3.x is stable, but SQLite 4.x would be an incompatible change). Consequently you can use APSW against any revision of SQLite with the same major version number. There are small enhancements to the C api over time, and APSW adds support for them as appropriate. The version number of APSW covers the version these enhancements were added. The vast majority of changes to SQLite are in the SQL syntax and engine. Those will be picked up with any version of APSW.
Before you do any benchmarking with APSW or other ways of accessing SQLite, you must understand how and when SQLite does transactions. See section 7.0, Transaction Control At The SQL Level of sqlite.org/lockingv3.html. APSW does not alter SQLite's behaviour with transactions. Some access layers try to interpret your SQL and manage transactions behind your back, which may or may not work well with SQLite also do its own transactions. You should always manage your transactions yourself. For example to insert 1,000 rows wrap it in a single transaction else you will have 1,000 transactions. The best clue that you have one transaction per statement is having a maximum of 60 statements per second. You need two drive rotations to do a transaction - the data has to be committed to the main file and the journal - and 7200 RPM drives do 120 rotations a second. On the other hand if you don't put in the transaction boundaries yourself and get more than 60 statements a second, then your access mechanism is silently starting transactions for you. This topic also comes up fairly frequently in the SQLite mailing list archives.
This is an example of how to use apsw, and also demonstrates all the features.
import apsw
###
### Opening/creating database
###
connection=apsw.Connection("dbfile")
cursor=connection.cursor()
###
### simple statement
###
cursor.execute("create table foo(x,y,z)")
###
### multiple statements
###
cursor.execute("insert into foo values(1,2,3); create table bar(a,b,c) ; insert into foo values(4, 'five', 6.0)")
###
### iterator
###
for x,y,z in cursor.execute("select x,y,z from foo"):
print cursor.getdescription() # shows column names and declared types
print x,y,z
###
### iterator - multiple statements
###
for m,n,o in cursor.execute("select x,y,z from foo ; select a,b,c from bar"):
print m,n,o
###
### bindings - sequence
###
cursor.execute("insert into foo values(?,?,?)", (7, 'eight', False))
cursor.execute("insert into foo values(?,?,?1)", ('one', 'two')) # nb sqlite does the numbers from 1
###
### bindings - dictionary
###
cursor.execute("insert into foo values(:alpha, :beta, :gamma)", {'alpha': 1, 'beta': 2, 'gamma': 'three'})
###
### tracing execution
###
def mytrace(statement, bindings):
"Called just before executing each statement"
print "SQL:",statement
if bindings:
print "Bindings:",bindings
return True # if you return False then execution is aborted
cursor.setexectrace(mytrace)
cursor.execute("create table bar(x,y,z); select * from foo where x=?", (3,))
SQL: create table bar(x,y,z);
SQL: select * from foo where x=?
Bindings: (3,)
###
### tracing results
###
def rowtrace(*results):
"""Called with each row of results before they are handed off. You can return None to
cause the row to be skipped or a different set of values to return"""
print "Row:",results
return results
cursor.setrowtrace(rowtrace)
for row in cursor.execute("select x,y from foo where x>3"):
pass
Row: (4, 'five')
Row: (7, 'eight')
###
### executemany
###
# (This will work correctly with multiple statements, as well as statements that
# return data. The second argument can be anything that is iterable.)
cursor.executemany("insert into foo (x) values(?)", ( [1], [2], [3] ) )
# You can also use it for statements that return data
for row in cursor.executemany("select * from foo where x=?", ( [1], [2], [3] ) ):
print row
###
### defining your own functions
###
def ilove7(*args):
"a scalar function"
print "ilove7 got",args,"but I love 7"
return 7
connection.createscalarfunction("seven", ilove7)
for row in cursor.execute("select seven(x,y) from foo"):
print row
###
### aggregate functions are more complex
###
# here we return the longest item when represented as a string
def longeststep(context, *args):
"are any of the arguments longer than our current candidate"
for arg in args:
if len( str(arg) ) > len( context['longest'] ):
context['longest']=str(arg)
def longestfinal(context):
"return the winner"
return context['longest']
def longestfactory():
"""called for a new query. The first item returned can be
anything and is passed as the context to the step
and final methods. We use a dict."""
return ( { 'longest': '' }, longeststep, longestfinal)
connection.createaggregatefunction("longest", longestfactory)
for row in cursor.execute("select longest(x) from foo"):
print row
###
### Defining collations.
###
# The default sorting mechanisms don't understand numbers at the end of strings
# so here we define a collation that does
cursor.execute("create table s(str)")
cursor.executemany("insert into s values(?)",
( ["file1"], ["file7"], ["file17"], ["file20"], ["file3"] ) )
for row in cursor.execute("select * from s order by str"):
print row
('file1',)
('file17',)
('file20',)
('file3',)
('file7',)
def strnumcollate(s1, s2):
# return -1 if s1<s2, +1 if s1>s2 else 0
# split values into two parts - the head and the numeric tail
values=[s1, s2]
for vn,v in enumerate(values):
for i in range(len(v), 0, -1):
if v[i-1] not in "01234567890":
break
try:
v=( v[:i], int(v[i:]) )
except ValueError:
v=( v[:i], None )
values[vn]=v
# compare
if values[0]<values[1]:
return -1
if values[0]>values[1]:
return 1
return 0
connection.createcollation("strnum", strnumcollate)
for row in cursor.execute("select * from s order by str collate strnum"):
print row
('file1',)
('file3',)
('file7',)
('file17',)
('file20',)
###
### Authorizer (eg if you want to control what user supplied SQL can do)
###
def authorizer(operation, paramone, paramtwo, databasename, triggerorview):
"""Called when each operation is prepared. We can return SQLITE_OK, SQLITE_DENY or
SQLITE_IGNORE"""
# find the operation name
ign=["SQLITE_OK", "SQLITE_DENY", "SQLITE_IGNORE"] # not operation names but have same values
print "AUTHORIZER:",
for i in dir(apsw):
if getattr(apsw,i)==operation:
print i,
break
print paramone, paramtwo, databasename, triggerorview
if operation==apsw.SQLITE_CREATE_TABLE and paramone.startswith("private"):
return apsw.SQLITE_DENY # not allowed to create tables whose names start with private
return apsw.SQLITE_OK # always allow
connection.setauthorizer(authorizer)
###
### progress handler (SQLite 3 experimental feature)
###
# something to give us large numbers of random numbers
import random
def randomintegers(howmany):
for i in xrange(howmany):
yield (random.randint(0,9999999999),)
# create a table with 10,000 random numbers
cursor.execute("begin ; create table bigone(x)")
cursor.executemany("insert into bigone values(?)", randomintegers(10000))
cursor.execute("commit")
# display an ascii spinner
_phcount=0
_phspinner="|/-\\"
def progresshandler():
global _phcount
sys.stdout.write(_phspinner[_phcount%len(_phspinner)]+chr(8)) # chr(8) is backspace
sys.stdout.flush()
_phcount+=1
time.sleep(0.1) # deliberate delay so we can see the spinner (SQLite is too fast otherwise!)
return 0 # returning non-zero aborts
# register progresshandler every 20 instructions
connection.setprogresshandler(progresshandler, 20)
# see it in action
print "spinny thing -> ",
for i in cursor.execute("select max(x) from bigone"):
print # newline
print i # and the maximum number
###
### commit hook (SQLite3 experimental feature)
###
def mycommithook():
print "in commit hook"
hour=time.localtime()[3]
if hour<8 or hour>17:
print "no commits our of hours"
return 1 # abort commits outside of 8am through 6pm
print "commits okay at this time"
return 0 # let commit go ahead
connection.setcommithook(mycommithook)
cursor.execute("begin; create table example(x,y,z); insert into example values (3,4,5) ; commit")
The simple way is:
python setup.py install
On Windows the above command uses Visual C++. You can use MinGW
with the command below. (If MinGW complains about missing Python
functions starting with _imp__Py_ then run
mingwsetup.bat which will ensure your Python
distribution is initialized for MinGW compilation).
python setup.py build --compile=mingw32 install
By default whatever SQLite 3 you already have on your system is
used. If you place a copy of the headers and library in a
sqlite3 subdirectory then that will be used instead.
Here is a quick and easy way of doing everything on Linux/Mac or
Windows with MinGW, including the SQLite library statically into the
extension (ie no external DLLs/shared libraries will be needed at
runtime).
Download the SQLite 3 code.
- Windows
Get the processed .zip> mkdir sqlite3 > cd sqlite3 > unzip sqlite-source-3.3.5.zip > del tclsqlite.c > gcc -DTHREADSAFE -O3 -c *.c # Adding -DNDEBUG will turn off assertion checking and # improve performance (about 25%) at the expense of safety > ar r libsqlite3.a *.o > ranlib libsqlite3.a > cd .. > python setup.py build --compile=mingw32 install > python -c "import apsw ; print apsw.sqlitelibversion(), apsw.apswversion()"- Mac/Linux/etc
Get the normal source.
The extension just turns into a single file apsw.so
(Linux/Mac) or apsw.pyd (Windows). You don't need to install it
and can drop it into any directory that is more convenient for
you and that your code can reach. To just do the build and not
install, leave out install from the lines above and
add build if it isn't already there.
If you want to check that your build is correct then you can run the unit tests. Run tests.py. It will print the APSW file used, APSW and SQLite versions and then run lots of tests all of which should pass.
Everything you can do from the SQLite 3 C API you can do from Python. The documentation below notes which C API functions are called where you can get further details on what happens. The only C function not implemented is sqlite3_collation_needed. (You can still add collations, you just can't use this function to find out about them on-demand.) Additionally sqlite3_trace is not wrapped but instead tracers are provided that have more functionality.
Some functions are marked experimental in the SQLite API. These
have also been made available, but as the SQLite documentation notes
these functions may change form or disappear in future versions of
SQLite. You can exclude these functions by commenting out the relevant
line in the setup.py when building aspw.
Various methods create functions, collations and set various hooks and handlers. To remove the relevant function/collation/hook/handler, pass in None as the callable method.
Returns the version of the SQLite library as a string. This function calls sqlite3_libversion.
Returns the version of the APSW module.
Calls sqlite3_enable_shared_cache. This sets the shared cache mode which was introduced in SQLite 3.3. Note that it only affects the current thread and should be called before any databases are opened. If called after that then you'll get MisuseError. APSW already enforces the other conditions required to use this functionality, namely that all operations on a Connection must happen in the same thread. Consequently you can safely use this functionality. You do not need to worry about sqlite3_thread_cleanup as that is only needed when abnormally terminating a thread (something you can't do in Python). Python's normal reference counting ensures all objects are cleaned up, and SQLite automatically cleans up the shared cache when the last Connection in a thread is closed.
The connection class wraps a sqlite3 pointer.
Opens an SQLite database named filename.
(This calls sqlite3_open
behind the scenes and sqlite3_close
when no longer in use.)
Creates a new cursor object on this database.
This function returns the number of database rows that were changed (or inserted or deleted) by the most recently completed INSERT, UPDATE, or DELETE statement. (This calls sqlite3_changes. Read that link for some additional notes.)
This function returns the total number of database rows that have be modified, inserted, or deleted since the database connection was opened. (This calls sqlite3_total_changes. Read that link for some additional notes.)
Returns the integer key of the most recent insert in the database. (This calls sqlite3_last_insert_rowid.)
Calls sqlite3_complete which tells you if the input string comprises one or more complete SQL statements.
Sets the busy timeout. (This calls sqlite3_busy_timeout).
Sets the busy handler to callable. callable will be called with one integer argument which is the number of prior calls to the busy callback for the same lock. If the busy callback returns something that evaluates to False, then SQLite returns SQLITE_BUSY to the calling code.. If the callback returns something that evaluates to True, then SQLite tries to open the table again and the cycle repeats. (This calls sqlite3_busy_handler).
Causes any pending operations on the database to abort at the earliest opportunity. (This calls sqlite3_interrupt).
Registers a scalar function. The callable will be called. You can specify how many arguments your function takes as the numargs parameter or supply -1 to take any amount. (This calls sqlite3_create_function).
Registers an aggregate function. (This calls sqlite3_create_function.) You can specify how many arguments your function takes as the numargs parameter or supply -1 to take any amount. When the function is called by an SQL query, the factorycallback is called without any arguments. The factorycallback needs to return a tuple consisting of three 3 items.
a context object (of any type)
a step function which is called for each row. The context object will be the first parameter, and the remaining parameters will be from the SQL statement. The return value is ignored - you supply it in final.
a final function which is called at the end. The only parameter will be the context object. The value returned is set as the return for the function. It must be a valid SQLite type. Note that the final function is always called even if an exception was raised by the step function. This allows you to ensure any resources are cleaned up.
Creates a collation with the specified name and callable. The callable will be passed two string arguments. It should return -1 if the first is less than the second, 0 if they are equal and 1 and if the first is greater than the second. Note that this controls sorting (ORDER BY in SQL) so your comparisons don't affect other SQL operations. Read more about SQLite's handling of collations. (This calls sqlite3_create_collation.) If there is an error in your Python code then 0 (ie items are equal) is returned.
The callable is invoked while SQL statements are being prepared. The intent is to allow applications to safely execute user entered SQL. The callable is called with 5 parameters:
an integer representing the operation (the constants are
available on the apsw module - eg
apsw.SQLITE_CREATE_TABLE.
A string (or None) dependent on the operation
Another string (or None) dependent on the operation
The string name of the database (or None)
Name of the innermost trigger or view doing the access (or None)
You should return apsw.SQLITE_OK to allow the
operation or apsw.SQLITE_DENY or
apsw.SQLITE_IGNORE as applicable. (SQLITE_DENY is
returned if there is an error in your Python code).
This calls sqlite3_set_authorizer which contains more detailed documentation.
Sets a callable which is invoked during data changing. The callable takes four parameters:
(This calls sqlite3_update_hook.)
Sets a callable which is invoked during a rollback. The callable takes no parameters and the return value is ignored. (This calls sqlite3_rollback_hook.)
Sets a callable which is invoked just before a commit. It should return zero for the commit to go ahead and non-zero for it to be turned into a rollback. In the case of an exception in your callable, a non-zero (ie rollback) value is returned. (This calls sqlite3_commit_hook.)
Sets a callable which is invoked at the end of execution of each statement and passed the statement string and how long it took to execute. (The execution time appears to be in nanoseconds.) Note that it is called only on completion. If for example you do a SELECT and only read the first result, then you won't reach the end of the statement.
(This calls sqlite3_profile which is not documented on the SQLite web site. See the sqlite3.h header file for documentation.).
Sets a callable which is invoked every nsteps SQLite inststructions. The callable should return a non-zero value to abort or zero to continue. (If there is an error in your Python callable then non-zero will be returned). (This calls sqlite3_progress_handler which has more detailed documentation).
The Cursor class creates and executes SQLite prepared statements.
You cannot create cursors directly. The are created by calling Connection.cursor().
Returns the Connection object to which this cursor belongs.
Executes the statements using the supplied bindings. The bindings can be supplied as a tuple or as a dict. Execution returns when the first row is available or all statements have completed. The cursor object is returned which you can use as an iterator. (See execution model for more details. This function wraps sqlite3_prepare and sqlite3_step.)
Repeatedly executes statements using each element of sequenceofbindings for the bindings each time. Execution returns when the first row is available or all statements have completed. The cursor object is returned which you can use as an iterator. (See execution model for more details. This function runs in a loop on each member of sequenceofbindings and wraps sqlite3_prepare and sqlite3_step .)
The Cursor object is an iterator, and so you can use it in
a for loop or similar situations. You can also explicitly
call the next() method. This method returns a tuple of the
contents of a returned row or raises StopIteration after all
returned rows have been seen.
Returns a list describing each column in the current result
set. Each item is a tuple of (column name, declared column type).
You should only call this function while data is being returned
such as when next() returns a row. This function
calls sqlite3_column_name
and sqlite3_column_decltype.
Sets or gets the tracers.
All exception types have apsw.Error as a parent.
The following exceptions can happen:
You have used an object allocated in one thread in a different thread. All objects (Connection, Cursor) must be used in the same thread they were allocated in.
You have tried to start a new SQL execute call before executing all the previous ones. See the execution model for more details.
There is an incorrect number of bindings when using
tuples. Or you supplied a dictionary of bindings and not all
bindings were named in the SQL statement. For example
select * from foo where x=:name and y=?.
You should either use colon name style or question mark style
in a query but not both.
Note It is not considered an error to have missing
keys in a dictionary. For example cursor.execute("insert into foo values($a,:b,$c)", {'a': 1})
is perfectly valid (note b and c are not in the dict). For missing keys,
None/NULL will be used. This is so you don't have to add lots of spurious values to the
supplied dict. If your schema requires every column have a value, then SQLite will generate
an error due to some values being None/NULL so that case will be caught.
A statement is complete but you try to run it more anyway!
The execution tracer returned False so execution was aborted.
The following Exception classes correspond to SQLite error codes.
General Errors SQLITE_ERRORSQLError SQLITE_MISMATCHMismatchError Internal Errors SQLITE_INTERNALInternalError SQLITE_PROTOCOLProtocolError SQLITE_MISUSEMisuseError SQLITE_RANGERangeError Permissions etc SQLITE_PERMPermissionsError SQLITE_READONLYReadOnlyError SQLITE_CANTOPENCantOpenError SQLITE_AUTHAuthError
Abort/Busy/etc SQLITE_ABORTAbortError SQLITE_BUSYBusyError SQLITE_LOCKEDLockedError SQLITE_INTERRUPTInterruptError SQLITE_SCHEMASchemaChangeError SQLITE_CONSTRAINTConstraintError Memory/Disk/etc SQLITE_NOMEMNoMemError SQLITE_IOERRIOError SQLITE_CORRUPTCorruptError SQLITE_FULLFullError SQLITE_TOOBIGTooBigError SQLITE_NOLFSNoLFSError SQLITE_EMPTYEmptyError SQLITE_FORMATFormatError SQLITE_NOTADBNotADBError Codes in italics are no longer used by SQLite (according to headers in 3.2.7).
Read about SQLite 3 types. ASPW always maintains the correct type for values, and never converts them to something else. Note however that SQLite may convert types based on column affinity as described in that link. ASPW requires that all values supplied are one of the corresponding Python/SQLite types (or a subclass).
None in Python is NULL in SQLite
Python int or long is INTEGER in SQLite. The value represented must fit within a 64 bit signed quantity (long long at the C level) or an exception is generated. (Don't worry - aspw only uses the long type when values are too large to fit in ints).
Python's float type is used for REAL in SQLite. (At the C level they are both 8 byte quantities and there is no loss of precision).
Python's string or unicode is used for TEXT in SQLite. (ASPW automatically uses Python's string type for ASCII text and Unicode for other text.)
Python's buffer class is used for BLOB in SQLite.
All SQLite strings are Unicode. The actual binary representations can be UTF8, or UTF16 in either byte order. ASPW uses the UTF8 interface to SQLite which results in the binary string representation in your database defaulting to UTF8 as well. All this is totally transparent to your Python code.
Everywhere strings are used (eg as database values, SQL statements, bindings names, user defined functions) you can use Unicode strings. You can also use the bare Python string class, and ASPW will automatically call the unicode converter if any non-ascii characters are present.
When reading values from SQLite, ASPW uses the Python string class for any pure ASCII text, else it uses the Python unicode class.
ASPW lets you use SQLite in multi-threaded programs and will let other threads execute while SQLite is working. (Technically the GIL is released when sqlite3_step or sqlite3_open are running. The GIL is re-acquired while user defined functions, collations and the various hooks/handlers run.)
Note that you cannot use the same Connection object in mulitple threads. You must allocate a new Connection object per thread. (This is a requirement of SQLite). A cursor object can only be used in the same thread as it was allocated. (Also an SQLite requirement). Fortunately ASPW will check this for you and throw a ThreadingViolationError if you try to use objects in the wrong thread. Note that your destructors also need to run in the creation thread.
If you have multiple threads and/or multiple programs accessing the same database then there may be contention for the file. SQLite will return SQLITE_BUSY which will be raised as BusyError. You can call the Cursor.next() method to resume execution. Alternately you can call Connection.setbusytimeout to set how long SQLite will retry for or Connection.setbusyhandler to install your own busy handler. SQLite's locking and concurrency is described here
A cursor object can only be executing one query at a time. You cannot issue a new query from inside a trace function or from a user defined function or collation since these are called while executing a query. You can however make new cursors and use those without issue. You may want to remember the Connection object when you set your trace or user defined functions.
You can install tracers on a cursor as an easy way of seeing exactly what gets executed and what is returned. The tracers can also abort execution and cause different values to be returned. This is very useful for diagnostics and testing without having to modify your main code.
Note: You cannot issue new execute statements against the cursor your tracer was called from. If you would like to make more queries in the tracer then do them from a new cursor object.
The execution tracer is called after an SQL statement has been prepared. (ie syntax errors will have caused an exception during preparation so you won't see them with a tracer). It is called with two arguments. The first is a string which is the SQL statement about to be executed, and the second is the bindings used for that statement (and can be None). If the return value evaluates to False/None then execution is aborted with an ExecTraceAbort exception. See the example above.
The row tracer is called before each row is returned. The arguments are the items about to be returned. Whatever you return from the tracer is what is actually returned. If you return None then the whole row is skipped. See the example above.
Prior to Python 2.5, you were still limited to 32 bit quantities for items in Python such as the length of strings, number of items in a sequence etc. APSW will work correctly with those items in Python 2.5 that use 64 bits. Unfortunately SQLite is limited to 32 bit quantities for strings, blobs, number of columns etc. Consequently you will get a TooBig exception from APSW or SQLite if you try to use strings/buffers longer than 1GB or 2GB (depends on internal storage). You will not get silent truncation or data loss.
This section only matters if you give multiple SQL statements
in one go to cursor.execute(). (Statements are
seperated by semi-colons.)
SQLite does execution in two steps. First a statement is prepared, which verifies the syntax, tables and fields and converts the statement into an internal representation. The prepared statement is then run. Execution stops when a row is available, there is an error or the statement is complete.
The cursor.execute() method automatically does the
preparing and starts execution. If none of the statements return
rows then execution will go to the end. If a row is returned then
you need to call cursor.next() to get the row values. Execution will
resume as necessary to satisfy next() calls.
However this means that if you don't read the rows returned then the rest of your statements won't be executed. APSW will detect unexecuted previous statements and generate an exception. For example:
>>> cursor.execute("select * from foo ; create table bar(x,y,z)")
>>> cursor.execute("create table bam(x,y,z)")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
apsw.IncompleteExecutionError: Error: there are still remaining sql statements to execute
Because I didn't read the results of select * from
foo then the following create table command didn't have a
chance to get executed. On the next execute that condition is
detected and an exception raised.
DBAPI is defined in PEP 249. This section desribes how APSW complies or differs from it.
There is no connect method. Use the Connection constructor instead, which only takes one parameter - the name of the database.
The Connection object and any cursors must all be used in the same thread they were allocated from. You cannot use them in different threads even if you protect them with mutexes.
Three different paramstyles are supported. You can use qmark ('... WHERE name=?'), numeric ('... WHERE name=?4') and named ('... WHERE name=:name' or '... WHERE name=$name'). Note that SQLite numbers parameters from one not zero.
The DBAPI exceptions are not used.
There is no close method
There are no commit or rollback methods. You should use cursor.execute("COMMIT") etc. The SQLite documentation has more details.
Several methods that are defined in DBAPI to be on the cursor are instead on the Connection object, since this is where SQLite actually stores the information. Doing operations in any other cursor attached to the same Connection object does update their values, and this makes you aware of that.
Use getdescription() instead of description. This information is only obtained on request.
There is no rowcount.
callproc is not implemented as SQLite doesn't support stored procedures.
There is no close method.
execute returns the Cursor object and you can use it as an iterator to get the results (if any).
executemany returns the Cursor object and you can use it as an iterator to get the results (if any).
fetchone is not available. Use the cursor as an iterator, or call next() which raises StopIteration when there are no more results.
fetchmany is not available. Call next() for however many results you want.
fetchall is not available. Call next() or use a list
comprehension such as [row for row in
cursor.execute("....")].
nextset is not applicable or implemented.
arraysize is not available as fetchmany isn't.
Neither setinputsizes or setoutputsize are applicable or implemented.
None of the date or time methods are available since SQLite 3 does not have a native date or time type.
Use the standard Python buffer class for BLOBs.
rownumber is not available.
Exception classes are not available as attributes of Connection.
Use Cursor.getconnection() to get the associated Connection object.
scroll and messages are not available.
The Cursor object supports the iterator protocol and this is the only way of getting information back.
To get the last inserted row id, call
Connection.last_insert_rowid(). That stores the id
from the last insert on any Cursor associated with the the
Connection. You can also add select
last_insert_rowid() to the end of your execute statements.
There is no errorhandler attribute.
pysqlite already provides a DBAPI compliant wrapper over SQLite 2 and 3. APSW only wraps SQLite 3 and has the following enhancements/differences over pysqlite 3:
*Nothing* happens behind your back. By default pysqlite tries to manage transactions by parsing your SQL for you, but you can turn it off.
SQLite's Manifest typing is used, which limits values to being supplied as integer (32/64 bit), string (utf8/unicode), double, null/None and BLOB. (An exception happens if you don't supply values in one of the supported types.) APSW doesn't do any type conversion or co-ercion. Note that SQLite will under some circumstances. pysqlite does have a mechanism for marshalling your own types.
You can use semi-colons at the end of commands and you can have multiple commands in the execute string.
The cursor object is an iterator and returns itself from execute. (The latest SQLite has no copied this feature).
cursor.executemany() also works with
statements that return data such as selects, and you can have
multiple statements.
You can define your own collations in Python.
You can implement tracers, authorizers and various hooks in Python.
Copyright (C) 2004-2006 Roger Binns rogerb@rogerbinns.com
This software is provided 'as-is', without any express or implied warranty. In no event will the authors be held liable for any damages arising from the use of this software.
Permission is granted to anyone to use this software for any purpose, including commercial applications, and to alter it and redistribute it freely, subject to the following restrictions:
The origin of this software must not be misrepresented; you must not claim that you wrote the original software. If you use this software in a product, an acknowledgment in the product documentation would be appreciated but is not required.
Altered source versions must be plainly marked as such, and must not be misrepresented as being the original software.
This notice may not be removed or altered from any source distribution.
You can use this release against any release of SQLite 3 from 3.3.5 onwards. A bug was also fixed when reporting an error during the cleanup of an aggregate function if there had also been an error in the step function. (PyErr_WriteUnraisable(NULL) crashed on some versions of Python but not others.)
SQLite added several functions for returning metadata about result column sets. You have to compile SQLite with SQLITE_ENABLE_COLUMN_METADATA to get them. This is not the default for SQLite. I don't believe these are generally useful except in some corner cases and so they aren't wrapped. However please shout if you do need them. Note that cursor.getdescription will already give you generally useful information. (Also see the pragmas).
The test code has been converted into using the unittest module. Run tests.py to get the tests run. There should be no errors.
Updated code to work correctly with new Py_ssize_t introduced in Python 2.5. See 64 bit hosts, Python 2.5 for more details on how Python and SQLite handle 64 bit sized items.
The following functions were added to SQLite and are wrapped. They are all functions defined on the Connection object.
The following functions were added/changed in SQLite C API but are not useful to be wrapped.
You can use this release against any release of SQLite 3.
SQLite 3.2.7 has several bug fixes. The undocumented experimental
function sqlite3_profile was added, but it not present
in apsw yet.
You can use this release against any release of SQLite 3.
SQLite 3.2.2 API removed sqlite3_global_recover.
That function was not wrapped in APSW. Note that SQLite 3.2.2
contains a bug fix that applies when you use 64 bit integer primary
keys (32 bit ints are fine).
You can use this release against any release of SQLite 3.
SQLite 3.2.1 had one addition in the stable C API, which was
a new function named sqlite3_global_recover. That
function is not applicable for wrapping in APSW.
You can use this release against any release of SQLite 3.
SQLITE_ALTER_TABLE and
SQLITE_REINDEX constants for the authorizer function.
(These constants were introduced in SQLite 3.1.3).
apswversion which
returns the version of APSW.
SQLite 3.1.3 had no changes in the stable C API other than what is mentioned above. There were some new experimental functions added which are not currently documented on the SQLite website, which are not wrapped by APSW. Please contact me if you believe they will remain in SQLite and you would like them wrapped:
sqlite3_sleep An alternative function which sleeps for
a specified number of milliseconds can be provided. By default SQLite
just uses the standard operating system call.
sqlite3_expired This function is internal to statement
execution. It would apply to the implementation of Cursor.executemany
and could in theory provide a marginal improvement in performance.
sqlite3_temp_directory can be used
before any databases are opened to set where temporary files are
created. By default SQLite just uses the standard operating system
mechanisms.
There are no functional changes. The only changes were to correct some variable names in the example code (they were cut and pasted from the test code which used different names) and to make the source zip file extract its contents into a sub-directory which is the more typical way of packaging that sort of thing.
All remaining functionality in the C API for SQLite 3.0.8 is now available.
Finished this documentation.
Initial release