G
Gerhard Haering
Hello everyone,
After pondering about a redesign of pysqlite for years, and now after half a
year of development, I am happy to finally announce the first stable release of
pysqlite2.
pysqlite 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 issues.
pysqlite homepage: http://pysqlite.org/
On the homepage, there's also a bug tracking system and a wiki.
Sources:
http://initd.org/pub/software/pysqlite/releases/2.0/2.0.0/pysqlite-2.0.0.tar.gz
Windows binaries for Python 2.3:
http://initd.org/pub/software/pysqlite/releases/2.0/2.0.0/pysqlite-2.0.0.win32-py2.3.exe
Windows binaries for Python 2.4:
http://initd.org/pub/software/pysqlite/releases/2.0/2.0.0/pysqlite-2.0.0.win32-py2.4.exe
Advantages of pysqlite 2.0 over pysqlite 1.x
============================================
- Straightforward:
No surprises: pysqlite 2.0 does not convert any types behind your back. With
default settings, it only supports the database types SQLite supports out of
the box: INTEGER, REAL, TEXT, BLOB and NULL.
- Documentation:
pysqlite 2.0 now has usable documentation. The usage manual covers the full
API.
- Advanced type system:
It is, however, possible to turn on type detection like in the old pysqlite.
Types can be detected by their declared type in the "CREATE TABLE" statement.
Or, for values that don't originate directly from tables or views, it's
possible to detect the type from the column name via a neat trick. For
details, look into the pysqlite usage manual. No more "-- types" hack like in
the old pysqlite.
Type conversion from Python to SQLite works via PEP-246-like object
adaptation.
- Fine-grained transaction control:
pysqlite 2.0 allows to control which type of transactions are opened via the
transaction_isolation property - None for auto-commit mode or one of SQLite's
transaction types "DEFERRED", "IMMEDIATE", "EXCLUSIVE".
- executemany() uses precompiled statements for optimal speed.
- Result sets are not prefetched any more, rows are only fetched on demand. So,
pysqlite 2.0 behaves a lot nicer with respect to memory usage.
- pysqlite 2.0 supports both the "qmark" and "named" paramstyle. So you can
supply query parameters as sequences or as mappings.
- Performance:
pysqlite 2.0 is almost entirely written in hand-coded C. Under most
circumstances, it is noticeably faster than the old pysqlite. On the pysqlite
wiki, there's a page with benchmarks:
http://initd.org/tracker/pysqlite/wiki/PysqliteBenchmarks
The benchmark shows that executemany() is 5 times as fast as in pysqlite 1.1.
Open issues:
============
pysqlite 2.0 does currently not compile under MacOS X Tiger (10.2 seems to work
for me on the SF compile farm), because of unresolved symbols. Unfortunately, I
don't have access to that platform. I will need a patch with a real fix from a
MacOS X users to fix the problem.
And, for those who have followed the alpha/beta testing:
Changes since pysqlite 2.0beta1
===============================
- Removed dead code.
- Improved error handling.
- Fixed a leak that occurred when erroneous SQL was sent to execute(many)().
- Recognize REPLACE as DML statement and start transactions appropriately.
- Issue a Warning when users send more than one SQL statement to execute(many)().
- Changed a few mappings SQLite error => DB-API exception.
- Added lots of new unit tests so all testable code paths are tested/executed.
This was done when doing coverage testing using gcov.
- Implemented a nonstandard convenience method cursor.executescript(sql) to
execute several SQL statements in a bunch, for example for creating a
database initially.
- The converters dictionary was moved from the Connection object into the
DB-API module. Register converters with
register_converter(converter_name, callable).
- the prepareProtocol parameter is gone.
- instead, register adapters directly with register_adapter(type, adapter_callable)
in the DB-API module.
- check for closed connections
- make sure it's impossible to destroy connections as long as there are still
cursors using them
- fixed a crash when a converter returned None
- fixes to MANIFEST.in
- default converters and adapters for date/date and datetime/timestamp
- Replaced autocommit flag with isolation_level flag. This way, it's possible
to use no transaction handling at all (autocommit), or control the types of
transactions that are created (DEFERRED, IMMEDIATE, EXCLUSIVE).
- thread_id is now long instead of int - so pysqlite compiles on 64-bit
platforms, too.
--
Gerhard Häring - (e-mail address removed) - Python, web & database development
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFCiOaudIO4ozGCH14RAiMjAKCjQEmt2pTGi7WOq5T/+4qXTzOH1wCfSone
G5AGpyJpDDSj5qu4TpW+DHM=
=74KT
-----END PGP SIGNATURE-----
After pondering about a redesign of pysqlite for years, and now after half a
year of development, I am happy to finally announce the first stable release of
pysqlite2.
pysqlite 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 issues.
pysqlite homepage: http://pysqlite.org/
On the homepage, there's also a bug tracking system and a wiki.
Sources:
http://initd.org/pub/software/pysqlite/releases/2.0/2.0.0/pysqlite-2.0.0.tar.gz
Windows binaries for Python 2.3:
http://initd.org/pub/software/pysqlite/releases/2.0/2.0.0/pysqlite-2.0.0.win32-py2.3.exe
Windows binaries for Python 2.4:
http://initd.org/pub/software/pysqlite/releases/2.0/2.0.0/pysqlite-2.0.0.win32-py2.4.exe
Advantages of pysqlite 2.0 over pysqlite 1.x
============================================
- Straightforward:
No surprises: pysqlite 2.0 does not convert any types behind your back. With
default settings, it only supports the database types SQLite supports out of
the box: INTEGER, REAL, TEXT, BLOB and NULL.
- Documentation:
pysqlite 2.0 now has usable documentation. The usage manual covers the full
API.
- Advanced type system:
It is, however, possible to turn on type detection like in the old pysqlite.
Types can be detected by their declared type in the "CREATE TABLE" statement.
Or, for values that don't originate directly from tables or views, it's
possible to detect the type from the column name via a neat trick. For
details, look into the pysqlite usage manual. No more "-- types" hack like in
the old pysqlite.
Type conversion from Python to SQLite works via PEP-246-like object
adaptation.
- Fine-grained transaction control:
pysqlite 2.0 allows to control which type of transactions are opened via the
transaction_isolation property - None for auto-commit mode or one of SQLite's
transaction types "DEFERRED", "IMMEDIATE", "EXCLUSIVE".
- executemany() uses precompiled statements for optimal speed.
- Result sets are not prefetched any more, rows are only fetched on demand. So,
pysqlite 2.0 behaves a lot nicer with respect to memory usage.
- pysqlite 2.0 supports both the "qmark" and "named" paramstyle. So you can
supply query parameters as sequences or as mappings.
- Performance:
pysqlite 2.0 is almost entirely written in hand-coded C. Under most
circumstances, it is noticeably faster than the old pysqlite. On the pysqlite
wiki, there's a page with benchmarks:
http://initd.org/tracker/pysqlite/wiki/PysqliteBenchmarks
The benchmark shows that executemany() is 5 times as fast as in pysqlite 1.1.
Open issues:
============
pysqlite 2.0 does currently not compile under MacOS X Tiger (10.2 seems to work
for me on the SF compile farm), because of unresolved symbols. Unfortunately, I
don't have access to that platform. I will need a patch with a real fix from a
MacOS X users to fix the problem.
And, for those who have followed the alpha/beta testing:
Changes since pysqlite 2.0beta1
===============================
- Removed dead code.
- Improved error handling.
- Fixed a leak that occurred when erroneous SQL was sent to execute(many)().
- Recognize REPLACE as DML statement and start transactions appropriately.
- Issue a Warning when users send more than one SQL statement to execute(many)().
- Changed a few mappings SQLite error => DB-API exception.
- Added lots of new unit tests so all testable code paths are tested/executed.
This was done when doing coverage testing using gcov.
- Implemented a nonstandard convenience method cursor.executescript(sql) to
execute several SQL statements in a bunch, for example for creating a
database initially.
- The converters dictionary was moved from the Connection object into the
DB-API module. Register converters with
register_converter(converter_name, callable).
- the prepareProtocol parameter is gone.
- instead, register adapters directly with register_adapter(type, adapter_callable)
in the DB-API module.
- check for closed connections
- make sure it's impossible to destroy connections as long as there are still
cursors using them
- fixed a crash when a converter returned None
- fixes to MANIFEST.in
- default converters and adapters for date/date and datetime/timestamp
- Replaced autocommit flag with isolation_level flag. This way, it's possible
to use no transaction handling at all (autocommit), or control the types of
transactions that are created (DEFERRED, IMMEDIATE, EXCLUSIVE).
- thread_id is now long instead of int - so pysqlite compiles on 64-bit
platforms, too.
--
Gerhard Häring - (e-mail address removed) - Python, web & database development
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
iD8DBQFCiOaudIO4ozGCH14RAiMjAKCjQEmt2pTGi7WOq5T/+4qXTzOH1wCfSone
G5AGpyJpDDSj5qu4TpW+DHM=
=74KT
-----END PGP SIGNATURE-----