Learn how to use the SQLite database with Python in this tutorial. You will get to know how to setup a database, insert and request data with Python.

In a database there are the standard CRUD operations: cread, read, update and delete. You’ll learn all of them.

Tutorial

To start this tutorial you need to have some Python coding experience. You’ll do well even as a beginner.

Before starting, install the prequisities first.

You may like:
Learning SQL: Master SQL Fundamentals

Prerequisites

You need these:

  • Python 3 installed.
  • SQLite database (sqlite3)
  • the Python module pysqlite.

Test if it’s installed with these commands:

1
2
3
4
5
6
7
8
$ python3
Python 3.5.3 (default, Jan 19 2017, 14:11:04)
[GCC 6.3.0 20170118] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>>

Create database and table

The program below creates an SQLite database and table. SQLite stores an entire database in a file (products.db)

An SQL database system can have tables, each table can contain data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

try:
con = lite.connect('products.db')
cur = con.cursor()
cur.execute("CREATE TABLE drinks(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL)")
cur.execute("CREATE TABLE fruits(Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Price REAL)")
con.commit()

except e:
if con:
con.rollback()

print("Unexpected error %s:" % e.args[0])
sys.exit(1)
finally:
if con:
con.close()

The program creates two tables: fruits and drinks.
Each table has columns (id, name, price).

Insert

Insert records with the INSERT INTO query. When adding data you need to write the table to insert and its column values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

try:
con = lite.connect('products.db')
cur = con.cursor()

cur.execute("INSERT INTO drinks(Name,Price) VALUES ('Coffee', 3)")
cur.execute("INSERT INTO drinks(Name,Price) VALUES ('Juice', 2.1)")
cur.execute("INSERT INTO drinks(Name,Price) VALUES ('Milk', 4.5)")

cur.execute("INSERT INTO fruits(Name,Price) VALUES ('Strawberry', 3.5)")
cur.execute("INSERT INTO fruits(Name,Price) VALUES ('Banana', 5.5)")

con.commit()
except e:
if con:
con.rollback()

print("Unexpected error %s:" % e.args[0])
sys.exit(1)
finally:
if con:
con.close()

You can see the data has been inserted via the terminal:

Every record has a unique identifier (id). This increases automatically. If you look at the image you’ll see Coffee has id 1, Juice id 2 etcetera.

This is achieved wit the AUTOINCREMENT flag we added when creating the table.

Select

You can read data from an SQLite database with the SELECT query.

The example below reads the entire table drinks from the database and converts that to a Python list.

The Python list structure can be used as a regular list.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#!/usr/bin/env python3                                                               
# -*- coding: utf-8 -*-

import sqlite3 as lite

# connect with SQLite database
con = lite.connect('products.db')

with con:
con.row_factory = lite.Row
cur = con.cursor()

# fetch entire table into Python list
cur.execute("SELECT name, price FROM drinks")
drinks = cur.fetchall()

for drink in drinks:
name = drink["name"]
price = str(drink["price"])
print(name + " costs $" + price)

This will output the table formatted:

Coffee costs $3.0
Juice costs $2.1
Milk costs $4.5

Update

To update data in your SQLite database, the UPDATE QUERY is required.

Remember those unique identifiers (id) ?

You can update records in your database with the unique id.

The steps are as simple as:

  • define record id and column value to update
  • connect to database
  • run update query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#!/usr/bin/env python3                                                          
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

# id of record to update and new value of column
pId = 1
pPrice = 10

# connect with database
con = lite.connect('products.db')

with con:
# update record
cur = con.cursor()
cur.execute("UPDATE drinks SET price=? WHERE id=?", (pPrice, pId))
con.commit()
print("Updated %d rows" % cur.rowcount)

Delete

Delete from a database with the DELETE QUERY.

Use the WHERE clause to set the record id to delete.

The example below deletes one record from the database table drinks (pId=3).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

# id of record to delete
pId = 3

# connect with database
con = lite.connect('products.db')

# delete with DELETE FROM query
with con:
cur = con.cursor()
cur.execute("DELETE FROM drinks WHERE id = ?", (pId,))
con.commit()