SQLite Python Tutorial with Examples

If you are working or learning Python, SQLite is the most important thing you should know. For example if you are learning Django, you may observe that they have SQLite at their backend database by default. This is the reason I thought of making this tutorial, so that you can understand most basics of SQLite database with Python by reading this one article. It will take you around half an houre to complete.

About SQLite Database

SQLite is a lightweight, file-based relational database management system. You can easily integrate this database with Python coding. This is the reason this database got so much popularity nowadays. While writing text to SQL query converter project tutorial, I thought of making a separate article on SQLite. This article is result of that.

Whether you want to create fresh SQLite Database, create, update, delete tables, you can do it using Python code itself. In this short tutorial I will explore below points:

• Import sqlite
• Create or Connect to a SQLite Database
• Create a table in SQLite
• Insert Single row to Table
• Insert Multiple rows to a Table
• Show list of Tables in a Database
• Remove a table from SQLite Database
• Query to a Table Data
• Convert SQLite table into Pandas dataframe
• Update existing table data based on condition
• Delete table data based on condition
• Commiting Changes and Closing Connection
• Read and work with external SQLite Database

Import SQLite

Let’s now import sqlite3 Python libray to work with SQLite database for this tutorial.

# Install and import sqlite library
import sqlite3

Create or Connect to a SQLite database

To work with SQLite data, you need to have a database right? You can create a new database or read external SQLite db in Python easily. For now I am creating a dummy SQLite database using below Python code. I mentioned my sample database name as test.db, you can name it anything (.db). If any database is not exist with that name it will automatically create a new one.

# Connecting to sqlite
conn = sqlite3.connect('test.db')

Create a table in SQLite DB

So we created a sample Database. Let’s now create a table inside that SQLite Database using below Python code. In this example I am naming my sample SQLite table as employee, you can name it anything.

# Define SQLite table scema
# cursor object
cursor = conn.cursor()
 
# Creating table
table = """ CREATE TABLE employee (
            employee_id INTEGER PRIMARY KEY,
            first_name TEXT NOT NULL,
            Last_Name TEXT NOT NULL,
            age INTEGER,
            position TEXT
        ); """

cursor.execute(table)

Output:
<sqlite3.Cursor at 0x7b6753bf54c0>

In the above code, we are mentioning table schema SQL query as a string. This is the way to work with SQLite/ SQLite3 database in Python. After defining table schema .execute() function is used to implement that into the SQLite database.

Insert Single row to Table

In above step we created a blank SQLite table. Now we need to know the way to insert data to that table. Below Python code is to insert a single row to any SQLite3 table. Format of each value must be same with table schema or table metadata (mentioned in previous step).

# Insert Single row
cursor.execute("INSERT INTO employee (first_name, last_name, age, position) VALUES (?, ?, ?, ?)",
               ('John', 'Doe', 28, 'Software Engineer'))

Output:
<sqlite3.Cursor at 0x7b6753bf54c0>

Insert Multiple rows to a Table

So we now know how to insert single row to a SQLite table using a Python code. But in practice, this is not enough. In real life project, there can be n-number or rows in a SQLite table. So we should know how to insert or add multiple rows to a SQLite table using a Python code.

# Insert Multiple rows
emp_list = [
    ('Ritu', 'Sinha', 28, 'Software Engineer'),
    ('Ram', 'Pal', 28, 'Sr. Software Engineer'),
    ('Neha', 'Sing', 28, 'Tech Lead'),
    ('Siva', 'Dey', 28, 'QA Engineer'),
    ('Ravi', 'Kumar', 28, 'QA Engineer')
]

cursor.executemany("INSERT INTO employee (first_name, last_name, age, position) VALUES (?, ?, ?, ?)",emp_list)

Output:
<sqlite3.Cursor at 0x7b6753bf54c0>

In the above code, I made a list for each row values then using a similar Python command (like previous) passing those values to our table.

Show list of Tables in a Database

As off now we have one table (employee) in our sample SQLite database. But in a real project, there can be multiple tables. So we must know a way to see names of all tables in a certian SQLite databse. As per my exploration there are mainly two ways to print SQLite table names.

Method 1: Using pandas read_sql Method

In this way we can fetch entire SQLite databse using pandas read_sql method, then print all table names as a dataframe.

# Method 1
import pandas as pd
def sq(str,con = conn):
    return pd.read_sql(f'{str}', con)

tables = sq('''select * from sqlite_master where type = 'table';''')
tables

Method 2: Using fetchall

In this technique we can use fetchall() method of SQLite3 library to read entire database. Then using a for loop we can print list of tables names.

# Method 2
x = cursor.execute("SELECT * FROM sqlite_master where type='table'")

for y in x.fetchall():
    print(y[1])

Output:
employee

Create Another table

For now we have onle one table in our SQLite database. Let’s now quickly create another table to verify above mothods are are working properly or not. I am naming this table as student.

# Create Another table
table = """ CREATE TABLE student (
            student_id INTEGER PRIMARY KEY,
            first_name TEXT NOT NULL,
            Last_Name TEXT NOT NULL,
            age INTEGER
        ); """
        
cursor.execute(table)

Let’s see whether that newly created table is also getting displayed while running show table command or not.

# Method 1
import pandas as pd
def sq(str,con = conn):
    return pd.read_sql(f'{str}', con)

tables = sq('''select * from sqlite_master where type = 'table';''')
tables

# Method 2
x = cursor.execute("SELECT * FROM sqlite_master where type='table'")

for y in x.fetchall():
    print(y[1])

Output:
employee
student

Remove a table from SQLite Database

In this tutorial, till now we learned how to create tables in SQLite database with Python code. Let’s now try to drop/remove or delete a table from our SQLite database. In the below Python code, I am deleting student table from my test.db SQLite database.

# Remove Table
# Drop the student table if already exists.
cursor.execute("DROP TABLE IF EXISTS student")

Again let’s print list of SQlite table names from our test database.

# Method 2
x = cursor.execute("SELECT * FROM sqlite_master where type='table'")

for y in x.fetchall():
    print(y[1])

Output:
employee

Query to a Table Data

If you are working with SQL, query data or data manipulation is most common tasks you need to perform. In the below Python code, I just gave you one example query to fetch and display specic data from a SQLite table.

# Query Data
cursor.execute("SELECT * FROM employee")
rows = cursor.fetchall()

for row in rows:
  print(row)

Output:
(1, 'John', 'Doe', 28, 'Software Engineer')
(2, 'Ritu', 'Sinha', 28, 'Software Engineer')
(3, 'Ram', 'Pal', 28, 'Sr. Software Engineer')
(4, 'Neha', 'Sing', 28, 'Tech Lead')
(5, 'Siva', 'Dey', 28, 'QA Engineer')
(6, 'Ravi', 'Kumar', 28, 'QA Engineer')

Convert SQLite table into Pandas dataframe

As a data analyst, above displayed result is not very user friendly to me. Luckily we can convert that SQLite data to a pandas dataframe, below Python code is to do that.

# Convert SQLite table into Pandas dataframe
df = pd.read_sql_query("SELECT * FROM employee", conn)
df

Update existing table data based on condition

Now let’s say we have a SQLite table and we want to update that table values based on some condition. In the below Python code I am updaing age of an employee to 30 if his name is “Ram”.

# Update Data
cursor.execute("UPDATE employee SET age = ? WHERE first_name = ?", (30, 'Ram'))

# Show table as Data Frame
df = pd.read_sql_query("SELECT * FROM employee", conn)
df

Delete table data based on condition

Similarly we can delete any specific row from our existing table based on some condition.

# Delete SQLite Data based on condition
cursor.execute("DELETE FROM employee WHERE first_name = ?", ('Neha',))

# Show table as Data Frame
df = pd.read_sql_query("SELECT * FROM employee", conn)
df

As you can see in the above output SQLite table, row of Neha is missing (removed).

Commiting Changes and Closing Connection

Once we are done with everything with SQLite, we can commit those changes and close our database connection using below Python command.

# Commiting Changes and Closing Connection
conn.commit()
conn.close()

Read and work with external SQLite Database

So far in this tutorial, we created a dummy SQLite database and tables to learn the concept. Let’s now come to the real world approaches where we need to work with an existing database.

For this tutorial, I am going to use Chinook Music Store Data from Kaggle. This SQLite DB has various tables. As a starting point this database can be a good choice to learn and practice SQLite database with Python. You just need to download that database (.db file) from kaggle and place it inside your working directory. Below is the metadata of this database.

Let’s now import that downloaded SQLite data in Python. The process is same. We need to create a connection for that database. Then setup cursor object of SQLite database. Next print list of tables.

# Read external SQLite Database
# Connecting to sqlite
conn_chinook = sqlite3.connect('/content/chinook.db')
cursor_chinook = conn_chinook.cursor()

# Print list of tables
x = cursor_chinook.execute("SELECT * FROM sqlite_master where type='table'")

for y in x.fetchall():
    print(y[1])

Output:
album
artist
customer
employee
genre
invoice
invoice_line
media_type
playlist
playlist_track
track

Let’s now display a table (in this case invoice_line table) as a pandas dataframe.

# Print invoice table
# Show table as Data Frame
df = pd.read_sql_query("SELECT * FROM invoice_line", conn_chinook)
df

FAQs

What is difference between SQL and SQLite?

SQLite is like a little database that can be put right into an application. It doesn’t need a separate server. You can use this database right there, embedded in the app itself.

Now, SQL Server is like a big teamwork database. It has a main server that holds all the information, and different parts of the project can talk to it. So, if the application needs more teamwork and organization, SQL Server is the go-to helper.

What is SQLite used for?

SQLite is like a smart helper for making things work inside gadgets like TVs, phones, and cameras. It’s great at handling a not-too-crowded flow of messages and can even make files smaller so they take up less space.

Think of SQLite as a temporary notebook for an application. It helps organize and process information before it’s ready to be used. So, it’s like a handy assistant inside our devices.

Which is better MySQL or SQLite?

Imagine SQLite like a personal diary kept in a file – it’s simple and perfect for small projects with just one user. Now, MySQL is like a super helpful librarian that uses Structured Query Language (SQL) to manage a big library of information. It’s open-source and works for all sorts of things, like big online stores, data keeping, and more.

So, if your project is a small story with one main character (user), SQLite is cool. But if it’s an epic adventure with lots of characters (users), MySQL is the best database to use.

Which database is fastest?

Alright, let’s break it down! NoSQL databases are like speedsters compared to SQL databases. Why? Well, NoSQL databases have a simpler way of doing things. They don’t have to organize information in a super structured way like SQL databases do, so they can grab data faster.

Think of it like this: SQL databases are like meticulously organized bookshelves, while NoSQL databases are like grabbing exactly what you need from a well-arranged stack of books. NoSQL takes the fast lane for quick data access.

This is it for this article. If you have any questions or suggestions regarding this tutorial, please let me know in the comment section below 🙂.

Anindya

Hi there, I’m Anindya Naskar, Data Science Engineer. I created this website to show you what I believe is the best possible way to get your start in the field of Data Science.