How to Conduct CRUD Operations on SQLite Database Using Python

How to Conduct CRUD Operations on SQLite Database Using Python


Published at - Nov 15, 2021

This post will teach you how to conduct CRUD operations on the SQLite database using Python. The sqlite3 module in Python provides built-in SQLite functionality. This module includes routines for conducting SQLite database persistent CRUD operations.

SQLite Database

About SQLite Database

SQLite is a self-contained transaction relational database engine that does not require a server configuration, unlike Oracle, MySQL, and others. D. Richard Hipp created it in August 2000 as an open-source and in-process library. The SQLite database is stored in a single file that may be placed anywhere on the computer’s file system.

Download and Install SQLite

SQLite may be downloaded for free from the official website at https://www.sqlite.org/download.html. Pre-compiled binaries for all major operating systems are available on this page. A command-line tool package includes a command-line shell and other programs for managing SQLite database files.

Create New Database

To create the database you will have to navigate to the dir where you want to store your database file and then run create DB command.

cd ~/path_to_db_file/
sqlite3 database_name.db

Now, you can perform queries for creating tables and other operations.

Create a Table in SQLite Database

In this tutorial, we will use the student table to perform all crud operations.

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

Inserting Records into the Table

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

Selecting Records from the Table

select * from student;

Python

Creating Connection

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

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 containing the CREATE TABLE query is supplied as a parameter to the cursor object’s execute() function.

The code below inserts the student table into 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()

Insert a Record

The cursor object’s execute() function should be invoked once more, this time with a string parameter encoding the INSERT query syntax. We developed a student table with three fields: name, age, and grades. The string containing the INSERT query is defined as follows:

qry="INSERT INTO student (name, age, marks) VALUES ('Rajeev',20,50);"

It must be sent as a parameter to the execute() function. To accommodate for any exceptions, the execute() statement is placed in the try block, as previously described. The following is the entire code for the inset operation:

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()

Retrieve Records

When a SELECT query is contained in the query string, the execute() function creates a result set object containing the records returned. The Python DB-API includes two ways for retrieving records:

  1. fetchone(): Retrieves the next entry from the result collection. It is a tuple that contains the values from each column of the fetched record.

  2. fetchall(): Returns a collection of tuples containing all remaining records. Each tuple represents a single record and contains the values from each column in the table.

When utilizing the fetchone() function, cycle through the result set using a loop, as seen below:

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

The fetchall() function produces a list of tuples, each of which represents one record.

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

Update a Record

The query string in the execute() function must include UPDATE query syntax. To change the value of ‘age’ for ‘Amar’ to 17, define the string as follows:

qry="update student set age=17 where name='Amar';"

The replacement approach may also be used to pass the argument 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 must include the DELETE query syntax. For instance, the code below is used to remove ‘Bill’ from the student database.

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

For parameter replacement, use the? character.

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()

Summary

In this tutorial of python, we have learned about SQLite database and performed CRUD operations using python on it. These operations are very common when we start working with any database. We are often required to perform these operations while developing ant database-related project.

I hope you will find this tutorial helpful. Thank you for reading and don’t forget to follow me to read more articles like this. 👏

More content at plainenglish.io





About author

Harendra
Harendra Kanojiya

Hello, I am Harendra Kumar Kanojiya - Owner of this website and a Fullstack web developer. I have expertise in full-stack web development using Angular, PHP, Node JS, Python, Laravel, Codeigniter and, Other web technologies. I also love to write blogs on the latest web technology to keep me and others updated. Thank you for reading the articles.



Follow Us

Follow us on facebook Click Here

Facebook QR
Scan from mobile
Join our telegram channel Click Here
Telegram QR
Scan from mobile