Python Database Programming: SQLite (tutorial)

In this tutorial you will learn how to use the SQLite database management system with Python. You will learn how to use SQLite, SQL queries, RDBMS and more of this cool stuff!

Data is everywhere and software applications use that. Data is either in memory, files or databases. One of these database management systems (DBMS) is called SQLite. SQLite was created in the year 2000 for a missile control system (boom! did we say it was cool?) and is one of the many management systems in the database zoo. MySQL, Postregsql, Oracle, Microsoft SQL Server and Maria DB are other database animals in this zoo. So what is this SQL?

SQL is a special-purpose programming language designed for managing data held in a databases. The language has been around since 1986 and is worth learning. The video below is an old funny video about SQL.

Why use SQLite? SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. It is a self-contained, serverless, zero-configuration, transactional SQL database engine. The SQLite project is sponsored by Bloomberg and Mozilla.

Installation:

$ sudo apt-get install sqlite

1	$ sudo apt-get install sqlite

Verify if it is correctly installed. Copy this program and save it as test1.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = None

try:
    con = lite.connect('test.db')
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()')
    data = cur.fetchone()
    print "SQLite version: %s" % data                
except lite.Error, e:   
    print "Error %s:" % e.args[0]
    sys.exit(1)
finally:    
    if con:
        con.close()

#!/usr/bin/python

# -*- coding: utf-8 -*-

import sqlite3 as lite

import sys

con = None

try:

con = lite.connect('test.db')

cur = con.cursor()

cur.execute('SELECT SQLITE_VERSION()')

data = cur.fetchone()

print "SQLite version: %s" % data

except lite.Error, e:

print "Error %s:" % e.args[0]

sys.exit(1)

finally:

if con:

con.close()

Execute with:

$ python test1.py

1	$ python test1.py

It should output:

SQLite version: 3.8.2

1	SQLite version: 3.8.2

What did the script above do?
The script connected to a new database called test.db with this line:

con = lite.connect('test.db')

1	con = lite.connect('test.db')

It then queries the database management system with the command

SELECT SQLITE_VERSION()

1	SELECT SQLITE_VERSION()

which in turn returned its version number. That line is known as an SQL query.

SQL joke. Source: Reddit

Storing data
The script below will store data into a new database called user.db

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = lite.connect('user.db')

with con:
    
    cur = con.cursor()    
    cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")
    cur.execute("INSERT INTO Users VALUES(1,'Michelle')")
    cur.execute("INSERT INTO Users VALUES(2,'Sonya')")
    cur.execute("INSERT INTO Users VALUES(3,'Greg')")

#!/usr/bin/python

# -*- coding: utf-8 -*-

import sqlite3 as lite

import sys

con = lite.connect('user.db')

with con:

cur = con.cursor()

cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")

cur.execute("INSERT INTO Users VALUES(1,'Michelle')")

cur.execute("INSERT INTO Users VALUES(2,'Sonya')")

cur.execute("INSERT INTO Users VALUES(3,'Greg')")

SQLite is a database management system that uses tables. These tables can have relations with other tables: it’s called relational database management system or RDBMS. The table defines the structure of the data and can hold the data. A database can hold many different tables. The table gets created using the command:

    cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")

1	cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")

We add records into the table with these commands:

    cur.execute("INSERT INTO Users VALUES(2,'Sonya')")
    cur.execute("INSERT INTO Users VALUES(3,'Greg')")

1 2	cur.execute("INSERT INTO Users VALUES(2,'Sonya')") cur.execute("INSERT INTO Users VALUES(3,'Greg')")

The first value is the ID. The second value is the name. Once we run the script the data gets inserted into the database table Users:

Exploring the data
We can explore the database using two methods: the command line and a graphical interface.

From console: To explore using the command line type these commands:

sqlite3 user.db
.tables
SELECT * FROM Users;

sqlite3 user.db

.tables

SELECT * FROM Users;

This will output the data in the table Users.

sqlite> SELECT * FROM Users;
1|Michelle
2|Sonya
3|Greg

sqlite> SELECT * FROM Users;

1|Michelle

2|Sonya

3|Greg

From GUI: If you want to use a GUI instead, there is a lot of choice. Personally I picked sqllite-man but there are many others. We install using:

sudo apt-get install sqliteman

1	sudo apt-get install sqliteman

We start the application sqliteman. A gui pops up.

Press File > Open > user.db. It appears like not much has changed, do not worry, this is just the user interface. On the left is a small tree view, press Tables > users. The full table including all records will be showing now.

This GUI can be used to modify the records (data) in the table and to add new tables.

The SQL language
SQL has many commands to interact with the database. You can try the commands below from the command line or from the GUI:

sqlite3 user.db 
SELECT * FROM Users;
SELECT count(*) FROM Users;
SELECT name FROM Users;
SELECT * FROM Users WHERE id = 2;
DELETE FROM Users WHERE id = 6;

sqlite3 user.db

SELECT * FROM Users;

SELECT count(*) FROM Users;

SELECT name FROM Users;

SELECT * FROM Users WHERE id = 2;

DELETE FROM Users WHERE id = 6;

We can use those queries in a Python program:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys


con = lite.connect('user.db')

with con:    
    
    cur = con.cursor()    
    cur.execute("SELECT * FROM Users")

    rows = cur.fetchall()

    for row in rows:
        print row

#!/usr/bin/python

# -*- coding: utf-8 -*-

import sqlite3 as lite

import sys

con = lite.connect('user.db')

with con:

cur = con.cursor()

cur.execute("SELECT * FROM Users")

rows = cur.fetchall()

for row in rows:

print row

This will output all data in the Users table from the database:

$ python get.py 
(1, u'Michelle')
(2, u'Sonya')
(3, u'Greg')

$ python get.py

(1, u'Michelle')

(2, u'Sonya')

(3, u'Greg')

SQL joke. Source: flickr

Creating a user information database
We can structure our data across multiple tables. This keeps our data structured, fast and organized. If we would have a single table to store everything, we would quickly have a big chaotic mess. What we will do is create multiple tables and use them in a combination. We create two tables:

Users:

Jobs:

To create these tables, you can do that by hand in the GUI or use the script below:

# -*- coding: utf-8 -*-
 
import sqlite3 as lite
import sys
 
con = lite.connect('system.db')
 
with con:
    
    cur = con.cursor()    
    cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")
    cur.execute("INSERT INTO Users VALUES(1,'Michelle')")
    cur.execute("INSERT INTO Users VALUES(2,'Howard')")
    cur.execute("INSERT INTO Users VALUES(3,'Greg')")

    cur.execute("CREATE TABLE Jobs(Id INT, Uid INT, Profession TEXT)")
    cur.execute("INSERT INTO Jobs VALUES(1,1,'Scientist')")
    cur.execute("INSERT INTO Jobs VALUES(2,2,'Marketeer')")
    cur.execute("INSERT INTO Jobs VALUES(3,3,'Developer')")

# -*- coding: utf-8 -*-

import sqlite3 as lite

import sys

con = lite.connect('system.db')

with con:

cur = con.cursor()

cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")

cur.execute("INSERT INTO Users VALUES(1,'Michelle')")

cur.execute("INSERT INTO Users VALUES(2,'Howard')")

cur.execute("INSERT INTO Users VALUES(3,'Greg')")

cur.execute("CREATE TABLE Jobs(Id INT, Uid INT, Profession TEXT)")

cur.execute("INSERT INTO Jobs VALUES(1,1,'Scientist')")

cur.execute("INSERT INTO Jobs VALUES(2,2,'Marketeer')")

cur.execute("INSERT INTO Jobs VALUES(3,3,'Developer')")

The jobs table has an extra parameter, Uid. We use that to connect the two tables in an SQL query:

SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid

1	SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid

You can incorporate that SQL query in a Python script:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys


con = lite.connect('system.db')

with con:    
    
    cur = con.cursor()    
    cur.execute("SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid")

    rows = cur.fetchall()

    for row in rows:
        print row

#!/usr/bin/python

# -*- coding: utf-8 -*-

import sqlite3 as lite

import sys

con = lite.connect('system.db')

with con:

cur = con.cursor()

cur.execute("SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid")

rows = cur.fetchall()

for row in rows:

print row

It should output:

$ python get2.py
(u'Michelle', u'Scientist')
(u'Howard', u'Marketeer')
(u'Greg', u'Developer')

$ python get2.py

(u'Michelle', u'Scientist')

(u'Howard', u'Marketeer')

(u'Greg', u'Developer')

If you liked this tutorial please share it using one of the buttons below

Leave a Reply Click here to cancel reply.