Database CRUD Operations in Python with SQLite

Learn how to perform CRUD (Create, Read, Update, Delete) operations in Python using the SQLite database. This tutorial covers the use of the sqlite3 module for managing persistent data. Discover SQLite's features, including its self-contained, serverless nature, and how to set up a new SQLite database.

Database CRUD Operations in Python

In this tutorial, you will learn how to perform CRUD operations in Python with the SQLite database. Python has built-in support for SQLite through the sqlite3 module, which provides functions for performing persistent CRUD operations on an SQLite database.

SQLite Database

SQLite is a self-contained, transactional relational database engine that doesn't require server configuration, unlike Oracle, MySQL, etc. It is open-source and developed by D. Richard Hipp in August 2000. The entire SQLite database is contained in a single file, which can be placed anywhere in the computer's file system.

SQLite is widely used as an embedded database in mobile devices, web browsers, and other standalone applications. Despite its small size, it is a fully ACID-compliant database conforming to ANSI SQL standards. SQLite can be downloaded from its official website at https://www.sqlite.org/download.html.

To create a new SQLite database, navigate from the command prompt to the folder where you have unzipped the archive and enter the following command:

sqlite3 test.db

It is now possible to execute any SQL query. The following statement creates a new table:

create table student(name text, age int, marks real);

Add a record to the above table:

insert into student values('Ramesh', 21, 55.50);

To retrieve the record, use the SELECT query as below:

select * from student;

Python DB-API

Python Database API is a set of standards recommended for database module standardization. Python modules that provide database interfacing functionality with all major database products are required to adhere to this standard. The DB-API standards were further modified to DB-API 2.0 by another Python Enhancement Proposal (PEP-249).

The standard Python distribution includes built-in support for SQLite database connectivity via the sqlite3 module. Other RDBMS products also have DB-API compliant modules:

• MySQL: PyMySQL module
• Oracle: Cx-Oracle module
• SQL Server: PyMsSQL module
• PostgreSQL: psycopg2 module
• ODBC: pyodbc module

To establish a connection with an SQLite database, the sqlite3 module needs to be imported and the connect() function needs to be executed:

import sqlite3
db = sqlite3.connect('test.db')

The connect() function returns a connection object referring to the existing database or a new database if it doesn't exist. The following methods are defined in the connection class:

A cursor is a Python object that enables you to work with the database. It acts as a handle for a given SQL query, allowing the retrieval of one or more rows of the result. Hence, a cursor object is obtained from the connection to execute SQL queries:

cur = db.cursor()

The following methods of the cursor object are useful:

The commit() and rollback() methods of the connection class ensure transaction control. The execute() method of the cursor receives a string containing the SQL query. A string with an incorrect SQL query raises an exception, which should be properly handled. That's why the execute() method is placed within a try block and the effect of the SQL query is persistently saved using the commit() method. If the SQL query fails, the resulting exception is processed by the except block and the pending transaction is undone using the rollback() method.

Typical use of the execute() method:

try:
    cur = db.cursor()
    cur.execute("Query")
    db.commit()
    print("Success message")
except:
    print("Error")
    db.rollback()
db.close()

Create a New Table

A string enclosing the CREATE TABLE query is passed as a parameter to the execute() method of the cursor object. The following code creates the student table in the test.db database:

import sqlite3
db = sqlite3.connect('test.db')
try:
    cur = db.cursor()
    cur.execute('''CREATE TABLE student (
        StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT (20) NOT NULL,
        age INTEGER,
        marks REAL);''')
    print('Table created successfully')
except:
    print('Error in operation')
    db.rollback()
db.close()

This can be verified using the .tables command in the SQLite shell:

sqlite3 test.db
sqlite> .tables
student

Insert a Record

To insert a record into the table, the execute() method of the cursor object should be called with a string argument representing the INSERT query syntax. The following code inserts a record into the student table:

import sqlite3
db = sqlite3.connect('test.db')
qry = "INSERT INTO student (name, age, marks) VALUES ('Rajeev', 20, 50);"
try:
    cur = db.cursor()
    cur.execute(qry)
    db.commit()
    print("One record added successfully")
except:
    print("Error in operation")
    db.rollback()
db.close()

You can check the result by using the SELECT query in the SQLite shell:

sqlite> select * from student;
1|Rajeev|20|50.0

Using Parameters in a Query

Often, the values of Python variables need to be used in SQL operations. One way is to use Python's string format() function to put Python data in a string. However, this may lead to SQL injection attacks. Instead, use parameter substitution as recommended in Python DB-API. The ? character is used as a placeholder in the query string and the values are provided in the form of a tuple in the execute() method. The following example inserts a record using the parameter substitution method:

import sqlite3
db = sqlite3.connect('test.db')
qry = "INSERT INTO student (name, age, marks) VALUES (?, ?, ?);"
try:
    cur = db.cursor()
    cur.execute(qry, ('Vijaya', 16, 75))
    db.commit()
    print("One record added successfully")
except:
    print("Error in operation")
    db.rollback()
db.close()

The executemany() method is used to add multiple records at once. Data to be added should be given in a list of tuples, with each tuple containing one record. The list object (containing tuples) is the parameter of the executemany() method, along with the query string:

import sqlite3
db = sqlite3.connect('test.db')
qry = "INSERT INTO student (name, age, marks) VALUES (?, ?, ?);"
students = [('Amar', 18, 70), ('Deepak', 25, 87)]
try:
    cur = db.cursor()
    cur.executemany(qry, students)
    db.commit()
    print("Records added successfully")
except:
    print("Error in operation")
    db.rollback()
db.close()

Retrieve Records

When the query string holds a SELECT query, the execute() method forms a result set object containing the records returned. Python DB-API defines two methods to fetch the records:

• fetchone(): Fetches the next available record from the result set. It is a tuple consisting of values of each column of the fetched record.
• fetchall(): Fetches all remaining records in the form of a list of tuples. Each tuple corresponds to one record and contains values of each column in the table.

When using the fetchone() method, use a loop to iterate through the result set:

import sqlite3
db = sqlite3.connect('test.db')
sql = "SELECT * from student;"
cur = db.cursor()
cur.execute(sql)
while True:
    record = cur.fetchone()
    if record is None:
        break
    print(record)
db.close()

The fetchall() method returns a list of tuples, each being one record:

students = cur.fetchall()
for rec in students:
    print(rec)

Update a Record

The query string in the execute() method should contain an UPDATE query syntax. To update the value of 'age' to 17 for 'Amar', define the string as below:

qry = "UPDATE student SET age = 17 WHERE name = 'Amar';"

You can also use the substitution technique to pass the parameter to the UPDATE query:

import sqlite3
db = sqlite3.connect('test.db')
qry = "UPDATE student SET age = ? WHERE name = ?;"
try:
    cur = db.cursor()
    cur.execute(qry, (19, 'Deepak'))
    db.commit()
    print("Record updated successfully")
except:
    print("Error in operation")
    db.rollback()
db.close()

Delete a Record

The query string should contain the DELETE query syntax. For example, the below code is used to delete 'Bill' from the student table:

qry = "DELETE from student WHERE name = 'Bill';"

You can use the ? character for parameter substitution:

import sqlite3
db = sqlite3.connect('test.db')
qry = "DELETE from student WHERE name = ?;"
try:
    cur = db.cursor()
    cur.execute(qry, ('Bill',))
    db.commit()
    print("Record deleted successfully")
except:
    print("Error in operation")
    db.rollback()
db.close()