Getting started with SQLite in Python

/ Python / By

Getting started with sqlite in Python

SQLite is a lightweight disk-based relational database engine that does not require a separate server process. It allows you to access databases using the SQL language.

A relational database is a collection of data organised and formally described in tables that consist of rows and columns. A simple analogy to explain this is to think of a database as a digital filling cabinet. Related information inside the “cabinet” is stored in records. Cabinets contain folders that store similar information, in databases these are tables. Each table or “folder” contains rows of data.

It is possible to retrieve, update and add or remove data from the database. Most databases are manipulated this way using a language called SQL which stands for Structured Query Language.

SQL Commands

The most basic commands in the SQL language are the following:

SELECT – Retrieves data from a database
INSERT – Inserts data into the database
UPDATE – Updates data from the database
DELETE – deletes data from the database

SQLite is one of many relational database management systems such as MySQL, Microsoft Access, PostgreSQL and SQL Server. It has an advantage over other database management systems in that it is lightweight, fast and does not require a separate server process. SQLite makes efficient use of memory, disk space and requires little to no maintanace which makes it ideal for use in cellphones, MP3 Players and also for testing purposes and in small or simple applications.

Using SQLite in Python

Python has built in support for SQLite, which can be enabled by importing the sqlite3 module. The module provides an interface to the database. In this article, I will show you how to connect to a SQLite database, insert data into it and also retrieve the data.

A cars database

Let’s build a database of cars.

# support for sqlite is baked into Python
import sqlite3


# This will create a new cars.db database and a connection object
with sqlite3.connect('cars.db') as connection:
    c = connection.cursor()

    # Here we use SQL to create a table called inventory and add 3 columns to it
    c.execute("CREATE TABLE inventory(make TEXT, model TEXT, quantity INT)")

    cars = [('Honda', 'Fit', 5), ('Ford', 'Ranger', 1), ('Honda', 'Civic', 2), ('Ford', 'Focus', 10), ('Ford', 'Fiesta', 6)]

    # Adds cars data to database
    c.executemany("INSERT INTO inventory(make, model, quantity) VALUES(?, ?, ?)", cars)

Databases are read in a similar fashion to how files are read, we opened the database and created a database connection object.The connection object represents the database. Here, we are reading and writing to the cars.db database. We use the connection object’s cursor() method to make changes to the database or to retrieve information from it. When adding data to a database through scripts, it is important to do this safely. In the example above, the variable cars is a list of tuples. Tuples are immutable so their values cannot be changed maliciously before they are saved to the database. Passing Python variables to databases is something you will do a lot. SQL queries should not be assembled using Python strings as this would make your program vulnerable to SQL injection. The recommended way to pass values to SQL is to use parameter substitution. This is done by placing a ? in place of a value and then providing a tuple of values as the second argument to the cursor’s execute() method.

Updating a database

import sqlite3

with sqlite3.connect("cars.db") as connection:
    c = connection.cursor()

    c.execute("UPDATE inventory SET quantity = 3 WHERE model='Ranger'")
    c.execute("UPDATE inventory SET quantity = 2 WHERE model = 'Fit'")

The SQL keyword UPDATE is used to make changes to databases. To make an update, you specify the table and column you want to make changes to and then pass in the new values. an UPDATE query looks like this: UPDATE TABLE_NAME ACTION COLUMN.Retrieving data from a database

The SELECT statement is used to get data from a database. After executing a SELECT statement, you can retrieve data from the cursor by iterating through it or calling the cursor’s fetchone()method to retrieve a single matching row or calling fetchall() to get a list of the matching rows.

This example uses the fetchall() method:

import sqlite3

>>> with sqlite3.connect("cars.db") as connection:
...    c = connection.cursor()
...    c.execute("SELECT * FROM inventory")
...    rows = c.fetchall()
...    for item in rows:
...        print(item)

(u'Honda', u'Fit', 2)
(u'Honda', u'Civic', 2)
(u'Ford', u'Ranger', 3)
(u'Ford', u'Focus', 10)
(u'Ford', u'Fiesta', 6)

Thank you for reading, more information about SQLite is available on the SQLite website at http://www.sqlite.org/