sqlite3 in Python

SQLite is a lightweight, self-contained, high-performance relational database management system (RDBMS). 

Data in an SQLite database is stored in a single file, it does not require a separate server to operate. This makes it a great choice for use in embedded systems for both development and production environment.

The sqlite3 module in the standard library provides a Python interface for accessing, using and managing SQLite databases.

To use the module, you will have to import it first.

import sqlite3

print(sqlite3)

Creating an sqlite database

The following example shows a basic example on how to use sqlite3 module to create an sqlite database.

import os
import sqlite3

db_name = 'cities.db'

conn = sqlite3.connect(db_name)
conn.close()

In the above example, a new database file 'cities.db' will be created in the working directory.

The sqlite3.connect() function starts a connection with the database whose name is given. If a database with the name does not exist, a new database with that name is created.

Calling the conn.close() methods ensures that the database is closed, and the involved file is released. We can automate calling this method by using the with statement. The with statement ensures that the database is closed even if you forget to call the conn.close() method or an error is encountered.

Create database tables

After creating a database, the next step would be to create its schema i.e the database tables.

In our previous example, we created a database called cities. For such a database, we can have the following tables:

The "country" table

In the above tables, we described the structure of our cities database. The sql commands needed to create the tables are as shown below:

CREATE TABLE city(
   city_id integer PRIMARY KEY AUTOINCREMENT,
   name text NOT NULL,
   country text,
   FOREIGN KEY (country)
   REFERENCES country(name)
   );

CREATE TABLE country(
   name text NOT NULL,
   continent text,
   PRIMARY KEY name,
   );

We now need to create the tables in the database.

The connection object returned by sqlite3.connect() contains various methods for executing arbitrary sql commands. In the following example we use the executescript() method to create the tables.

import sqlite3

commands = """
  CREATE TABLE city (
    city_id integer PRIMARY KEY AUTOINCREMENT,
    name text NOT NULL,
    country text,
    FOREIGN KEY (country) REFERENCES country(name));

  CREATE TABLE country (
    name text PRIMARY KEY NOT NULL,
    continent text);"""

with sqlite3.connect('cities.db') as conn:
  try:
     conn.executescript(commands)
     print('Tables were created Successfully.')
  except Exception as e:
     print('An error occurred.')
     print(e)

After running the above snippet, the tables will have been successfully committed to the database.

In the above example, we used the conn.executescript() method to execute the sql statements, there are other similar methods for executing sql depending on the nature of the sql command(s). The  functions are summarized in the following table:

Populating the tables

We can now run an sql command to populate the tables with data.

The following shows the commands:

INSERT INTO country (name, continent) VALUES ('Japan', 'Asia');
INSERT INTO country (name, continent) VALUES ('China', 'Asia');
INSERT INTO country (name, continent) VALUES ('Canada', 'N.America');
INSERT INTO country (name, continent) VALUES ('Kenya', 'Africa');
INSERT INTO city (name, country) VALUES ('Tokyo', 'Japan');
INSERT INTO city (name, country) VALUES ('Osaka', 'Japan');
INSERT INTO city (name, country) VALUES ('Yokohama', 'Japan');
INSERT INTO city (name, country) VALUES ('Beijing', 'China');
INSERT INTO city (name, country) VALUES ('Shanghai', 'China');
INSERT INTO city (name, country) VALUES ('Guangzhou', 'China');
INSERT INTO city (name, country) VALUES ('Ottawa', 'Canada');
INSERT INTO city (name, country) VALUES ('Edmonton', 'Canada');
INSERT INTO city (name, country) VALUES ('Montreal', 'Canada');
INSERT INTO city (name, country) VALUES ('Calgary', 'Canada');
INSERT INTO city (name, country) VALUES ('Nairobi', 'Kenya');
INSERT INTO city (name, country) VALUES ('Mombasa', 'Kenya');
INSERT INTO city (name, country) VALUES ('Kisumu', 'Kenya');

We can now execute the commands using the conn.executescript() method as shown below:

import sqlite3

commands = '''
INSERT INTO country (name, continent) VALUES ('Japan', 'Asia');
INSERT INTO country (name, continent) VALUES ('China', 'Asia');
INSERT INTO country (name, continent) VALUES ('Canada', 'N.America');
INSERT INTO country (name, continent) VALUES ('Kenya', 'Africa');
INSERT INTO city (name, country) VALUES ('Tokyo', 'Japan');
INSERT INTO city (name, country) VALUES ('Osaka', 'Japan');
INSERT INTO city (name, country) VALUES ('Yokohama', 'Japan');
INSERT INTO city (name, country) VALUES ('Beijing', 'China');
INSERT INTO city (name, country) VALUES ('Shanghai', 'China');
INSERT INTO city (name, country) VALUES ('Guangzhou', 'China');
INSERT INTO city (name, country) VALUES ('Ottawa', 'Canada');
INSERT INTO city (name, country) VALUES ('Edmonton', 'Canada');
INSERT INTO city (name, country) VALUES ('Montreal', 'Canada');
INSERT INTO city (name, country) VALUES ('Calgary', 'Canada');
INSERT INTO city (name, country) VALUES ('Nairobi', 'Kenya');
INSERT INTO city (name, country) VALUES ('Mombasa', 'Kenya');
INSERT INTO city (name, country) VALUES ('Kisumu', 'Kenya');
'''

with sqlite3.connect('cities.db') as conn:
   try:
      conn.executescript(commands)
      print('Tables were populated Successfully.')
   except Exception as e:
         print('An error occurred.')
         print(e)

Retrieving data

We can now perform queries to retrieve data from the database by creating a conn.cursor() object . The following example shows how to create a cursor object:

import sqlite3

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   print(cursor)

The cursor object also contains the execute(), executescript() and executemany() methods. After performing a query with the cursor object, we can get the fetched objects by calling any of the following methods:

For example to retrieve cities whose country is 'Canada', we can execute the following sql query:

SELECT name, country FROM city where country='Canada';

import sqlite3

command = "SELECT name, country FROM city where country='Canada';"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(command)
   print(cursor.fetchone())
   
   cursor.close() #close the cursor

As shown in the above example, the cursor.fetchone() method returns a tuple containing the values in the fields of a single row in the fetched rows.

The cursor.fetchone() method remembers where it left off, this means that subsequent calls will retrieve the next row in the results. Consider the following example:

import sqlite3

command = "SELECT name, country FROM city where country='Canada';"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(command)
   print(cursor.fetchone())
   print(cursor.fetchone())
   print(cursor.fetchone())
   print(cursor.fetchone())

   cursor.close()

Retrieve multiple rows

To retrieve multiple rows at once, use the cursor.fetchmany() method. This method allows you to specify the number of rows to be fetched using an integer value. 

cursor.fetchmany(n = 1)

The cursor.fetchmany() method returns a list of n tuples, where each tuples contains values of the fields in the fetched rows. n defaults to 1.  

import sqlite3

command = "SELECT name, country FROM city where country='Canada';"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(command)
   print(cursor.fetchmany(2)) #fetch 2 rows
   print(cursor.fetchmany(2)) #fetch 2 rows

   cursor.close()

Get all returned rows in one call

The cursor.fetchall() method will return a list of tuples for all yielded rows. Consider the following example:

import sqlite3

command = "SELECT name, country FROM city where country='Canada';"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(command)
   print(cursor.fetchall()) #get all rows

   cursor.close()

Queries with parameters

The cursor.execute() and the other similar methods allow parameters which gets passed to the query. We can pass the query parameters either as positional arguments or as keyword arguments. 

The ? character in a query  acts as a placeholder where positional arguments will be placed by the conn.execute() method. Consider the following query:

SELECT name, country FROM city WHERE country = ?

To pass an actual value at the position of ? , we pass that value(s) to the execute() function in a tuple, as shown below:

import sqlite3

query = "SELECT name, country FROM city WHERE country = ?"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(query, ('Japan', ))
   print(cursor.fetchall())
  
   cursor.close()

The following example demonstrates the above example with multiple positional arguments.

import sqlite3

query = "SELECT name, country FROM city WHERE country = ? OR country = ?"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(query, ('China','Japan' ))

   print(*cursor.fetchall(), sep = '\n')
   cursor.close()

With keyword arguments 

To pass keyword arguments to the query string, use the :keyword_name syntax. The :keyword_name parameter in the query will be replaced with value that matches the specified keyword_name in the dictionary passed to the execute() function.

Consider the following query:

SELECT name, country FROM city WHERE country = :country_name

import sqlite3

query = "SELECT name, country FROM city WHERE country = :country_name"

with sqlite3.connect('cities.db') as conn:
   cursor = conn.cursor()
   cursor.execute(query, {'country_name': 'Kenya'})
   print(*cursor.fetchall(), sep = '\n')
   
   cursor.close()

In the above example we called the cursor.execute() method with the second argument as a dictionary. The dictionary values gets passed as keyword argument to the querystring such that the keywords that match are replaced with the matching values from the dictionary.

Summary on Cursor methods and attributes

In the previous examples, we looked at and used some methods of the cursor objects, including those for executing sql statements such as execute(), executemany(), and  executescript() as well as those for fetching data such as fetchone(), fetchmany() and fetchall().

The following table summarizes the conn.cursor() methods: