Simple SQLite3 Tutorial With Python

Sqlite3 is the Python library that provide access to databases of the SQL query language.

In this tutorial, we will learn how to use SQLite3 with Python.

Prerequisites

If you don’t know SQL, make sure that you read my guide to help you learn SQL. If you want to learn python, read my guide that covers SEO with Python.

Steps to Interact with a Relational Database

Import required packages
Connect to the database
Create a cursor to connect to the database engine
Query the database (create, add or read tables and data from the db)
Save the result to a Pandas DataFrame (optional)
Close the connection

Import SQLlite3 Packages

Sqlite3 is included in the Python standard library so you don’t need to install it.

Start by importing it into your python code.

import sqlite3

Connect to the Database

Now, create the SQLite database using the sqlite3.connect()command.

The command creates the database if it does not exist. If it exists, it connects to it.

try:
    conn = sqlite3.connect('database.db')
except Error as e:
    print(e)

conn.close()

The try: and except: statements handle possible errors.

Create a Cursor

To allow Python to execute SQL commands, create a cursor object.

# Create a cursor to allow to execute SQL commands
cursor = conn.cursor()

Create a SQL Table

Create a table using the CREATE TABLE IF NOT EXIST SQL command. To understand the data type defined below, I suggest that you read the guide that I wrote to help you learn SQL.

# Create a SQL Table
sql_command = '''
    CREATE TABLE IF NOT EXISTS contacts (
        Id INTEGER PRIMARY KEY AUTOINCREMENT, 
        Firstname TEXT, 
        Lastname TEXT, 
        Email TEXT
    )'''

cursor.execute(sql_command)

# Commit the changes to the database
conn.commit()

Basically, Id, FirstName, Lastname and Email will be the column names of your table. The Id will automatically increase by 1 each time you add a row.

The cursor.execute() command will run the command into the database.
The conn.commit() will commit the changes to the database.

Add (or Insert) Data to the Table

To insert data into the table, use the INSERT INTO command.

First, define which columns that you want to add data into.

INSERT INTO contacts 
    (Firstname, Lastname, Email)

Then, specify the values using the VALUES keyword.

VALUES (
        'David',
        'Attenborough',
        'dattenborough@example.com'
    )

The result goes like this.

insert_data = """
    INSERT INTO contacts 
    (Firstname, Lastname, Email) 
    VALUES (
        'David',
        'Attenborough',
        'dattenborough@example.com'
    )
"""
cursor.execute(insert_data)

# Commit the changes to the database
conn.commit()

Read the SQL Table with Python

There are two ways to read the SQLite3 Table with Python:

Using cursor.fetchone(), fetchmany(n) and fetchall();
Using Pandas.

Read SQL Table Using Fetchone, Fetchmany and Fetchall

Select a single line with cursor.fetchone().

select_data = 'SELECT * FROM contacts'
cursor.execute(select_data)

row = cursor.fetchone()

print(row)

Select multiple lines using cursor.fetchmany(n).

select_data = 'SELECT * FROM contacts'
cursor.execute(select_data)

rows = cursor.fetchmany(5) # select 5 lines

for row in rows:
    print(row)

Select all lines using cursor.fetchall().

select_data = 'SELECT * FROM contacts'
cursor.execute(select_data)

rows = cursor.fetchall()

for row in rows:
    print(row)

Read SQL Table into a DataFrame with Pandas

import pandas as pd

select_data = 'SELECT * FROM contacts'

df = pd.read_sql_query(select_data,conn)
df

Add Multiple Elements to the Database

users = [
    {'Firstname':'Jane','Lastname':'Goodall','Email':'jgoodall@example.com'},
    {'Firstname':'Rachel','Lastname':'Carson','Email':'rcarson@example.com'},
    {'Firstname':'Barry','Lastname':'Bishop','Email':'bbishop@example.com'},
    {'Firstname':'Edward','Lastname':'J.Laurent','Email':'ejlaurent@example.com'}
    ]

for user in users:
    insert_data = f"""
    INSERT INTO contacts 
    (Firstname, Lastname, Email) 
    VALUES (
        '{user['Firstname']}',
        '{user['Lastname']}',
        '{user['Email']}'
    )
    """
    cursor.execute(insert_data)
    conn.commit()

select_data = 'SELECT * FROM contacts'

df = pd.read_sql_query(select_data,conn)
df

Close the Database

End your script by closing the Database.

# Close the Database
conn.close()

Full code

import sqlite3

# Create the sqlite database if it does not exist. If it exist, connect to it.
try:
    conn = sqlite3.connect('database.db')
except Error as e:
    print(e)

# Create a cursor to allow to execute SQL commands
cursor = conn.cursor()

# Create a SQL Table
sql_command = '''
    CREATE TABLE IF NOT EXISTS contacts (
        Id INTEGER PRIMARY KEY AUTOINCREMENT, 
        Firstname TEXT, 
        Lastname TEXT, 
        Email TEXT
    )'''

cursor.execute(sql_command)

insert_data = f"""
    INSERT INTO contacts 
    (Firstname, Lastname, Email) 
    VALUES (
        'David',
        'Attenborough',
        'dattenborough@example.com'
    )
"""
cursor.execute(insert_data)
# Commit the changes to the database
conn.commit()

select_data = 'SELECT * FROM contacts'
cursor.execute(select_data)

rows = cursor.fetchall()

for row in rows:
    print(row)


users = [
    {'Firstname':'Jane','Lastname':'Goodall','Email':'jgoodall@example.com'},
    {'Firstname':'Rachel','Lastname':'Carson','Email':'rcarson@example.com'},
    {'Firstname':'Barry','Lastname':'Bishop','Email':'bbishop@example.com'},
    {'Firstname':'Edward','Lastname':'J.Laurent','Email':'ejlaurent@example.com'}
    ]

for user in users:
    insert_data = f"""
    INSERT INTO contacts 
    (Firstname, Lastname, Email) 
    VALUES (
        '{user['Firstname']}',
        '{user['Lastname']}',
        '{user['Email']}'
    )
    """
    cursor.execute(insert_data)
    conn.commit()
    
import pandas as pd
df = pd.read_sql_query(select_data,conn)
df
# Close the Database
conn.close()

SQLAlchemy

Alternatively, you can use SQLAlchemy to interact with SQLite databases. It is good to learn SQLAlchemy as it also interacts with other relational databases management systems such as PostgreSQL and MySQL.

Notice in the example below how I use the with keyword to launch a context manager to launch the connection.

This best practice lets you run the query without worrying about closing the connection to the database.

import pandas as pd 
from sqlalchemy 

with engine.connect() as con:
    result = con.execute('SELECT * FROM table')
    df = pd.DataFrame(result.fetchall())
    df.columns = result.keys()

Conclusion

This is the end of the tutorial on how to use SQLite3 databases with Python.

5/5 - (1 vote)

Jean-Christophe Chouinard

SEO Strategist at Tripadvisor, ex- Seek (Melbourne, Australia). Specialized in technical SEO. Writer in Python, Information Retrieval, SEO and machine learning. Guest author at SearchEngineJournal, SearchEngineLand and OnCrawl.