CRUD (Create, Retrieve, Update, and Delete) operations are essential in any application. In this article, we will use python programming to do CRUD operations with SQLite database.

Python has built-in support for SQLite that can be accessed using the sqlite3 module. This module is used to connect the SQLite database and perform all the operations including CRUD operations.

We will see how to connect the database, create database tables and perform CRUD operations using python.

• Create a new table in SQLite database using Python
• Insert a record into an SQLite table using Python
• Insert a record into an SQLite table using parameters in Python
• Insert multiple records into an SQLite table using parameters in Python
• Retrieve records from an SQLite table using Python
• Update records into an SQLite table using Python
• Delete a record from an SQLite table using Python 

Install and access SQLite database

We can download SQLite files from the official website https://www.sqlite.org/download.html where you can see the precompiled binaries for all operating systems. A bundle of command-line tools contains a command-line shell and other utilities to manage SQLite database files.

We will download the latest version of SQLite (version 3.35.2) along with command-line tools and extract the zip file.

If you want to create a new SQLite database, you should navigate to the folder where you have unzipped the archive via the command prompt as shown below.

C:\Users>cd..

C:\>cd sqlite-tools

C:\sqlite-tools>sqlite3 E:\Projects\Samples\Python\CRUD_Operation_Python\CarsDb.db
SQLite version 3.35.2 2021-03-17 19:07:21
Enter ".help" for usage hints.

You can easily create a table, insert records into the SQLite database using the below queries to verify if the SQLite is working fine in your machine,

sqlite> Create table cars (Name text, model text, year int);
sqlite> Insert into Cars Values ('Ford', 'Figo', 2020);
sqlite> Select * from Cars;
Ford|Figo|2020
sqlite>

DB-API in Python

In order to connect the database with python, you have to use a Python database API module that has in-built support for SQLite database connectivity. It has a sqlite3 module which adheres to DB-API 2.0. You can also connect various databases in python using the below corresponding DB-API modules,

• MySQL: PyMySql module
• Oracle: Cx-Oracle module
• SQL Server: PyMsSql module
• PostgreSQL: psycopg2 module
• ODBC: pyodbc module

How to connect SQLite database to Python?

As we discussed, python has an in-built module sqlite3 for SQLite connection. First, you have to import sqlite3 module in your project as shown below,

import sqlite3

Next, you have to connect SQLite database using connect() method from sqlite3 module,

# Connect to sqlite database
con = sqlite3.connect('employeeDb.db')

Once you connected the database, you have to return a cursor object which uses this Connection using cursor().

# Cursor object
cur = con.cursor()

After establishing the database connection, we can use execute() method to execute any SQL queries with or without parameters.

# Execute SQL query
cur.execute("Select * from employees")

You have to use close() method to close the database connection once you performed the SQL execution.

# Close connection
con.close()

Create a new table in SQLite database using Python

The below code creates a new database table using CREATE TABLE SQL query. In order to execute the query, you have to pass SQL query into execute() method as shown below, 

# Creating a table into SQLite database

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeeDb.db')
try:
    # Cursor object
    cur = con.cursor()
    # Execute query
    cur.execute('''CREATE TABLE Employees (
    EmployeeId INTEGER PRIMARY KEY AUTOINCREMENT,
    EmployeeName TEXT (50) NOT NULL,
    Designation TEXT (25) NOT NULL,
    Age INTEGER,
    Country TEXT (25) NOT NULL);''')
    # Commit changes
    con.commit()
    # Print successful message
    print('Employee table is created successfully')
except:
    print('Error occurred...')
    # Roll back if in case of issue
    con.rollback()
# Close connection
con.close()

 You can verify whether the table is created or not using the command .tables as shown below,

C:\sqlite-tools>sqlite3 E:\Projects\Samples\Python\CRUD_Operation_Python\employeesDb.db
SQLite version 3.35.2 2021-03-17 19:07:21
Enter ".help" for usage hints.
sqlite> .tables
Employees
sqlite>

Insert a record into an SQLite table using Python

In the below code snippet, we insert the employee records into the table using the INSERT INTO query, We used commit() method to explicitly commits any pending transactions to the database.

# Inserting a record into SQLite table

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeesDb.db')
query = "Insert into Employees (EmployeeName, Designation, Age, Country) values" \
        "('Sabari', 'Software Engg', 35, 'India');"
try:
    # Cursor object
    cur = con.cursor()
    # Execute query
    cur.execute(query)
    # Commit changes
    con.commit()
    # Print successful message
    print("Employee record inserted successfully")
except:
    print("Error occurred...")
    # Roll back if in case of issue
    con.rollback()
# Close connection
con.close()

Using the below query, you can verify the inserted records in the table.

sqlite> select * from Employees;
1|Sabari|Software Engg|35|India
sqlite>

Insert a record into an SQLite table using parameters in Python

The safest way of inserting records into the database is using parameters instead of passing the values with the SQL query. Using parameters in python DB-API is recommended. The below code snippet shows how to pass the values using parameters when inserting the records.

# Inserting a record into SQLite table using parameters query

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeesDb.db')
query = "Insert into Employees (EmployeeName, Designation, Age, Country) values(?,?,?,?);"
try:
    # Cursor object
    cur = con.cursor()
    # Execute query
    cur.execute(query, ('Peter', 'Tech Lead', 36, 'USA'))
    # Commit changes
    con.commit()
    # Print successful message
    print("Employee record is inserted successfully")
except:
    print("Error occurred...")
    # Roll back if in case of issue
    con.rollback()
# Close connection
con.close()

You can verify the records using SELECT statement,

sqlite> select * from Employees;
1|Sabari|Software Engg|35|India
2|Peter|Tech Lead|36|USA
sqlite>

Insert multiple records into an SQLite table using parameters in Python

You can also INSERT multiple records at the same time in python using executemany() method as shown below. You need to add the records in the list of tuples to the executemany() method.

# Inserting multiple records into SQLite table using parameters query

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeesDb.db')
query = "Insert into Employees (EmployeeName, Designation, Age, Country) values(?,?,?,?);"
employees = [('Raj', 'Software Engg', 30, 'India'), ('John', 'Business Analyst', 28, 'UK'),
             ('Kohli', 'Tester', 25, 'India')]
try:
    # Cursor object
    cur = con.cursor()
    # Execute query
    cur.executemany(query, employees)
    # Commit changes
    con.commit()
    # Print successful message
    print("Employee records are inserted successfully")
except:
    print("Error occurred..")
    # Roll back if in case of issue
    con.rollback()
# Close connection
con.close()

You can verify the records using SELECT statement,

sqlite> select * from Employees;
1|Sabari|Software Engg|35|India
2|Peter|Tech Lead|36|USA
3|Raj|Software Engg|30|India
4|John|Business Analyst|28|UK
5|Kohli|Tester|25|India

Retrieve records from an SQLite table using Python

In order to retrieve all the records from the table, we need to use SELECT statement as shown below,

# Retrieving records from SQLite table

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeesDb.db')
sql = "SELECT * from Employees;"
# Cursor object
cur = con.cursor()
# Execute query
cur.execute(sql)
while True:
    record = cur.fetchone()
    if record is None:
        break
    # Print records
    print(record)
# Close connection
con.close()

If you execute the above code snippet, you will see the below results.

(1, 'Sabari', 'Software Engg', 35, 'India')
(2, 'Peter', 'Tech Lead', 36, 'USA')
(3, 'Raj', 'Software Engg', 30, 'India')
(4, 'John', 'Business Analyst', 28, 'UK')
(5, 'Kohli', 'Tester', 25, 'India')

Update records into an SQLite table using Python

In this code snippet, We will pass the UPDATE query into the execute method. To update the value of 'designation' to 'Senior Software Engg' and 'country' to 'Canada' for the EmployeeName of 'raj'.

# Updating record into SQLite table

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeesDb.db')
qry = "Update Employees set Designation=?, Country=? where EmployeeName=?;"
try:
    # Cursor object
    cur = con.cursor()
    # Execute query
    cur.execute(qry, ('Senior Software Engg', 'Canada', 'Raj'))
    # Commit changes
    con.commit()
    # Print successful message
    print("Employee record is updated successfully")
except:
    print("Error occurred...")
    # Roll back if in case of issue
    con.rollback()
# Close connection
con.close()

You can see the updated value for the Employee Raj below,

sqlite> select * from Employees;
1|Sabari|Software Engg|35|India
2|Peter|Tech Lead|36|USA
3|Raj|Senior Software Engg|30|Canada

Delete a record from an SQL table using Python 

To delete the particular records from the table, you have to use DELETE statement as shown below and pass the statement to the execute() method.

# Deleting  a record from SQLite table

import sqlite3

# Connect to sqlite database
con = sqlite3.connect('employeesDb.db')
qry = "Delete from Employees where EmployeeName=?;"
try:
    # Cursor object
    cur = con.cursor()
    # Execute query
    cur.execute(qry, ('Kohli',))
    # Commit changes
    con.commit()
    # Print successful message
    print("Employee record is deleted successfully")
except:
    print("Error occurred...")
    # Roll back if in case of issue
    con.rollback()
# Close connection
con.close()