[ANN] pysqlite 2.1.0 released

[Gerhard Häring]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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
reporting bugs.

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
    issues.

=======
CHANGES
=======

Statement caching
=================

Planned since the start of the pysqlite2 development, the most
difficult to implement feature has now been implemented: transparent
statement caching.

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
COMPILE step.

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
compiled once.

Users have seen significant performance improvements with the
statement caching in pysqlite 2.1.0:

    http://lists.initd.org/pipermail/pysqlite/2005-November/000234.html

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
connection:

    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
sqlite.Row:

    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[1]

Convenience methods
===================

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"):
        print row


API changes
===========

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

iD8DBQFDyoObdIO4ozGCH14RAt9uAJ9EO8jHuLjhPleYjWWUe+lt++GsngCeInQY
CxfGAxmV5I8UMuM620wWTac=
=jsNu
-----END PGP SIGNATURE-----