July 10, 2012 15:36 / 0 comments / berkeleydb python sqlite

Building the python SQLite driver for use with BerkeleyDB

As of Oracle's 11gR2 release of BerkeleyDB, the library has included a SQL API which is fully compatible with SQLite3. BerkeleyDB can even be compiled with support for SQLite's full-text search and r-tree extensions. There's a good whitepaper that Oracle published detailing the performance of their implementation versus SQLite. To summarize, since Berkeley supports page-level locking as opposed to database-level locking, it can push quite a few more transactions per second, making it a better fit for write-heavy applications (the one area sqlite suffers, IMO). Additionally, Berkeley makes fewer syscalls due to the way it blocks as opposed to SQLite's use of busy-locks.

This post is partly for me so I remember how I did this, and partly for anyone else interested in trying out Berkeley's SQL support. plug: I enjoy using SQLite for small projects, if you're interested you might check out peewee, a lightweight ORM with some fun SQLite extensions.

Getting started

I'm testing this on a fresh 64-bit Ubuntu 12.04 EC2 instance. To compile, we'll need a C compiler, mercurial and python libs. If you already have this stuff, feel free to skip to the next section.

sudo apt-get update --fix-missing
sudo apt-get install build-essential python-dev git-core

Compiling BerkeleyDB and the python library

Grab a copy of the source code (all versions). Its rather infuriating, but you may have to register for an account to get the source. Compile it and install it to /usr/local/:

wget http://download.oracle.com/berkeley-db/db-6.0.30.tar.gz
tar xzf db-6.0.30.tar.gz
cd db-6.0.30/build_unix
export CFLAGS='-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_RTREE=1 -fPIC'
../dist/configure --enable-static --disable-shared --enable-sql --enable-sql-compat
make
sudo make prefix=/usr/local/ install

Next grab a copy of the pysqlite2 driver.

cd ~/bin/
git clone https://github.com/ghaering/pysqlite
cd pysqlite

Inside setup.cfg the include_dirs and library_dirs need to be updated to point to /usr/local. By default these values are supplied but are commented out. Edit setup.cfg by hand or execute the following to uncomment the default values:

sed -i "s|#||g" setup.cfg

Now we're ready to build the extension:

python setup.py build

The extension will be stored in build/lib.linux-x86_64-2.7/pysqlite2/ -- you can either symlink this directory into your python-path or install the library globally:

sudo python setup.py install

Testing the installation

To make sure we're using the right libraries, use the dbsql utility to fire up a SQLite-compatible shell:

dbsql test.db

Execute a couple queries to create some test data:

create table testing (id integer not null primary key, data text);
insert into testing (data) values ('test1');
insert into testing (data) values ('test2');
.quit

Now let's fire up python and see if we can talk to the database we just created:

python

Here is the python code from an interactive shell session:

>>> from pysqlite2 import dbapi2 as sqlite
>>> conn = sqlite.connect('test.db')
>>> curs = conn.cursor()
>>> res = curs.execute('select * from testing;')
>>> res.fetchall()
[(1, u'test1'), (2, u'test2')]

You should see the two rows we inserted earlier, indicating everything is working correctly.

Links

More like this

Comments (0)


Commenting has been closed, but please feel free to contact me