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

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

Comments (0)


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