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