Simple SQLite3 Tutorial With Python

Share this post

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

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

Import SQLlite3 Packages

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


Subscribe to my Newsletter


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.
You Might Also Like  Google Analytics Reporting API v4 using Python [2021 update]

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',
        '[email protected]'
    )

The result goes like this.

insert_data = """
    INSERT INTO contacts 
    (Firstname, Lastname, Email) 
    VALUES (
        'David',
        'Attenborough',
        '[email protected]'
    )
"""
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':'[email protected]'},
    {'Firstname':'Rachel','Lastname':'Carson','Email':'[email protected]'},
    {'Firstname':'Barry','Lastname':'Bishop','Email':'[email protected]'},
    {'Firstname':'Edward','Lastname':'J.Laurent','Email':'[email protected]'}
    ]

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',
        '[email protected]'
    )
"""
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':'[email protected]'},
    {'Firstname':'Rachel','Lastname':'Carson','Email':'[email protected]'},
    {'Firstname':'Barry','Lastname':'Bishop','Email':'[email protected]'},
    {'Firstname':'Edward','Lastname':'J.Laurent','Email':'[email protected]'}
    ]

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.

You Might Also Like  How To Work With Jupyter Notebook

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.

Enjoyed This Post?