SQLite in Ubuntu 9.04


There are a lot of good choices for a free/open-source DBMS in Ubuntu (or anywhere for that matter): MySQL and PostgreSQL would be the two that stand out most prominently. But when you’re developing a small-scale application which requires a small-scale database (whether or not you intend to scale up later), a client-server DBMS like that is the proverbial sledgehammer, with your data structure as the walnut. SQLite offers a file-based transactional SQL database engine, without the installation/configuration/administration time associated with a server-based DB. Think MS Access, cross-platform, minus all the forms.

I’m currently building a proof-of-concept application (for migrating a spreadmart system), using the Django framework to put a web form presentation layer across a db backend. SQLite is ideal for this sort of task, in that the development time is shortened – I can pretty much get down to writing code as soon as the design is complete. An interface to SQLite (sqlite3) is built in to Python since 2.5, so if you wish to create an SQLite database using Python, all you have to do is write the appropriate code to do so. Without using Python you’ll need to enable command-line access by installing the sqlite3 library:

sudo apt-get install sqlite3

or just open Synaptic and search for sqlite3.

So for instance to create a database file ‘expenses.db’, create a category table and populate one row in it, I can do the following from the command line:

$ sqlite3 expenses.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table category(
   ...> id integer primary key,
   ...> cat_code varchar(3),
   ...> cat_name varchar(20)
   ...> );
sqlite> insert into category values(
   ...> null, 'GRO', 'groceries'
   ...> );
sqlite> select * from category;
1|GRO|groceries

To do the same thing in Python:

>>> import sqlite3
>>> conn = sqlite3.connect('expenses.db')
>>> conn.execute('''create table category(
... id integer primary key,
... cat_code varchar(3),
... cat_name varchar(20)
... )''')
<sqlite3.Cursor object at 0xb77ce2c0>
>>> conn.commit()
>>> conn.execute('''insert into category values(
... null, 'GRO', 'groceries')''')
<sqlite3.Cursor object at 0xb77ce290>
>>> conn.commit()
>>> for row in conn.execute('select * from category'):
...   print row
... 
(1, u'GRO', u'groceries')

If you prefer to have a GUI, you can install the SQLite Database Browser:

sudo apt-get install sqlitebrowser

This is a very handy (albeit rather basic) visual tool which gives you access to pretty much all the same functionality as you have from the command line:
sqlite-expenses.db

So that’s SQLite – simple, easy to use and very handy. Incidentally there’s a great blog post here extolling the virtues of SQLite in itself and also in combination with Excel. Intriguing reading and makes a great case for the benefits of using a “fractional horsepower” database. I’ll post more shortly on what this approach has allowed me to do with both Django and Excel.

Advertisements

4 thoughts on “SQLite in Ubuntu 9.04

  1. Tom Gleeson says:

    Hi Geoff,

    Good to know there are others out there who appreciate the power of SQLite (and Excel, and better still, Excel combined with SQLite 🙂 ). And a Python-fan too!

    I’ve added a great deal of functionality to my Excel SQLite tool (xLite) over the summer, including embedding Python. I can now develop SQLite functions and virtual tables in Python and use these from Excel. I can also use Python as a scripting language within Excel (using Excel cells to provide Python code indentation!).

    I don’t use Python’s in-built SQLite libraray, preferring instead APSW http://code.google.com/p/apsw/ which takes full advantage of SQLite’s extendibility while recognising its differences from a ‘normal’ RDBMS.

    Tom

    • geoffness says:

      Thanks for dropping by Tom – yes definitely a Python fan, and keenly interested in embedding Python in Excel! I’ll most certainly be checking out xLite, it sounds ideal for many of the tasks I’ve been slowly stumbling through recently. We’ve had Access removed from the desktop at work, which has hamstrung many an effort to clean up legacy Excel messes – an SQLite backend (which formerly would have been Access) is ideal for this sort of application.

      And thanks for the apsw link, I’ve yet to fully explore the differences between that, pysqlite and the built-in sqlite3 interface but the documentation certainly looks interesting.

      Cheers
      Geoff

  2. Colin McCubbin says:

    ‘sqLite as the mp3 of data’… Would make a great T shirt, but…No, it’s way, way better. The mp3 compression algorithm throws away a massive amount of the subtler ‘sound’ data, and only just fulfills the ‘80% fidelity is good enough’ rule that audio now seems to be ruled by. Bands spend millions and weeks in the studio and then listeners only get to hear 80%… Go figure! ;-0

    sqLite may lack some SQL ‘functionality’, but it looses none of the data! 😉 (Unless I screw up my query, but that’s the subject for another thread….)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s