Python and SQLite: A beautiful combination

When building small systems which do nice and interesting things, I prefer keeping the implementation as simple as possible. I don't like the philosophy of many people who think too much ahead of time and try to make things work in future requirements too. I like look at problems in a computer scientists perspective than a software engineers perspective. Today I had to build a simple prototype of a system which needs a small back-end database and some simple interactions with it from a front-end. So, without thinking twice, I went for Python as the programming language and SQLite as the database. Simple and easy.

This is a brief note about using Python to interact with SQLite database.

Let's install the sqlite client program first. I'm working on a Ubuntu 16.04 LTS machine.

sudo apt-get install sqlite3 libsqlite3-dev

We can start sqlite3 tool with a name for our database file as follows. The file name is test.db in this case.

sqlite3 test.db

Let's create a simple table and insert some data to it as follows in the prompt.

CREATE TABLE Cars(Id INT, Name TEXT, Price INT);

INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Volvo',12345);

We can query the data we inserted as follows.

SELECT * FROM Cars;

Additionally, we can check the currently created tables and their scheme using following commands.

.tables

.schema

Once we are ready to exit, give the following command on the prompt to exit from the sqlite3 tool.

.exit

Now open a text editor and insert the following python program into it. Save it as sqlite-program.py for the program name.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

#!/usr/bin/python import sqlite3 conn = sqlite3.connect('test.db') print "Opened database successfully"; cursor = conn.execute("SELECT * FROM Cars") for row in cursor: print "id = ", row[0] print "name = ", row[1] print "price = ", row[2], "\n" print "Operation done successfully"; conn.close()

Now we can run this python program to see it taking data from the sqlite database.

python sqlite-program.py

That's all forlks!

References:
[1] http://zetcode.com/db/sqlitepythontutorial/