[DB-SIG] [ANN] pysqlite 2.1.0 released
gh at ghaering.de
Sun Jan 15 18:17:15 CET 2006
-----BEGIN PGP SIGNED MESSAGE-----
pysqlite 2.1.0 released
I'm pleased to announce the availability of pysqlite 2.1.0. This is a
major release with many new features and some internal changes. While
the code was tested by a few people who tracked Subversion, users are
still adviced to test their applications intensively with the new
release before upgrading them to pysqlite 2.1.0.
Go to http://pysqlite.org/ for downloads, online documentation and
What is pysqlite?
pysqlite is a DB-API 2.0-compliant database interface for SQLite.
SQLite is a relational database management system contained in a
relatively small C library. It is a public domain project created
by D. Richard Hipp. Unlike the usual client-server paradigm, the
SQLite engine is not a standalone process with which the program
communicates, but is linked in and thus becomes an integral part
of the program. The library implements most of SQL-92 standard,
including transactions, triggers and most of complex queries.
pysqlite makes this powerful embedded SQL engine available to
Python programmers. It stays compatible with the Python database
API specification 2.0 as much as possible, but also exposes most
of SQLite's native API, so that it is for example possible to
create user-defined SQL functions and aggregates in Python.
If you need a relational database for your applications, or even
small tools or helper scripts, pysqlite is often a good fit. It's
easy to use, easy to deploy, and does not depend on any other
Python libraries or platform libraries, except SQLite. SQLite
itself is ported to most platforms you'd ever care about.
It's often a good alternative to MySQL, the Microsoft JET engine
or the MSDE, without having any of their license and deployment
Planned since the start of the pysqlite2 development, the most
difficult to implement feature has now been implemented: transparent
What is statement caching?
Every SQL engine has a COMPILE and EXECUTE phase. In older pysqlite
releases, every SQL statement (except for executemany) was always
COMPILED and EXECUTED.
With statement caching, pysqlite can transparently optimize away the
The statement cache is implemented using a LRU cache with a default
capacity of 100. The cache is per connection - it's capacity can be
set when opening a connection:
con = sqlite.connect(..., cached_statements=30)
In a nutshell, this means the 100 most used SQL statements in your
application will only have to be COMPILED once by the SQLite engine.
This will of course only work well if you use the parameter-form of
the execute() method, i. e. for:
cur.execute("insert into foo(bar) values (?)", ("xy",))
cur.execute("insert into foo(bar) values (?)", ("ab",))
the SQL statement "insert into foo(bar) values (?)" will then only be
Users have seen significant performance improvements with the
statement caching in pysqlite 2.1.0:
More flexibility for TEXT data
Until now, pysqlite would always return Unicode strings for text data,
unless you did some crazy trickery.
If you prefer to retrieve text data as Python bytestrings or a
different type, you can now set a text_factory callable per
con.text_factory = str # ... to always return bytestrings
An optimized shortcut has been enabled to retrieve Unicode strings for
non-ASCII data, but bytestrings for non-ASCII text:
con.text_factory = sqlite.OptimizedUnicode
or something custom:
con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
Highly optimized row_factory for column access by name
A new function has been implemented that allows for case-insensitive
column access by name with minimal performance and memory impact,
unlike a dictionary or db_row-based approach.
To enable it, set the row_factory attribute of your connection to
from pysqlite2 import dbapi2 as sqlite
con = sqlite.connect(...)
con.row_factory = sqlite.Row
cur = con.cursor()
cur.execute("select name_last, age from people")
for row in cur:
print row["Name_Last"], row
The execute(), executemany() and executescript() methods are now also
available in the Connection class.
This allows you to write very concise code like this:
con = sqlite.connect(...)
con.execute("insert into foo(bar) values (5)")
for row in con.execute("select bar from foo"):
The row_factory attribute has been moved from the Cursor class to the
Connection class. For backwards compatibility, using it on the Cursor
class still works.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
-----END PGP SIGNATURE-----
More information about the DB-SIG