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)andfetchall(); - 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.
SEO Strategist at Tripadvisor, ex- Seek (Melbourne, Australia). Specialized in technical SEO. In a quest to programmatic SEO for large organizations through the use of Python, R and machine learning.
