Introduction to SQLite in Python

For those who are not familiar with SQLite, it is a self-contained Relational DBMS (Database Management System). Unlike other databases such as MySQL or MS SQL, SQLite has a server-less architecture. These features of SQLite make it an ideal choice for data storage in embedded systems and devices with low computing power.

SQLite can also create an in-memory database if you need faster read-writes. It is used by several popular OSs and software like Windows, Android, and Google Chrome, and Firefox to store its data.

SQLite in Python

We can use Python’s in-built library named sqlite3 to work with the SQLite database. All you need to do is to import the module.

import sqlite3

Creating a database

To create a database, you have to create an SQLite3 database connection using the connect() function of the sqlite3 module.

import sqlite3
conn = sqlite3.connect('mydb.db')

This will create a database file named mydb.db in the same directory (or the current working directory). You can also specify a different path like this:

conn = sqlite3.connect('data\db\mydb.db')

Creating table

To execute SQL commands, first, we need to create a cursor. We can call cursor as a pointer that can iterate over the rows in a result set. In python, we can create a cursor using the cursor() method.

conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()

A cursor is tied to the connection object and is disposed of if the connection is closed.

Now, let’s create a table to store the details of employees.

import sqlite3
conn = sqlite3.connect('mydb.db')
cursor = conn.cursor()
sql = '''
CREATE TABLE "employees" (
	"empid"	TEXT,
	"name"	TEXT,
	"email"	TEXT UNIQUE,
	"age"	INTEGER,
	PRIMARY KEY("empid")
)
'''
cursor.execute(sql)
conn.close()

The execute() method of cursor object executes the specified query against the database to which it is tied.

Note: We should always close the database connection after executing the queries.

Insert data

Let’s see how we can insert data into our employees table.

import sqlite3

conn = sqlite3.connect('mydb.db')

cursor = conn.cursor()
sql = '''
INSERT INTO "employees" 
VALUES("01DEfG", "Employee 1", "emp1@mail.com", 25)
'''

cursor.execute(sql)
conn.commit()
conn.close()

You might have noticed that I used the commit() method of connection object this time. If you do not call the commit method, the changes that we made will not be saved in the database.

Note: It’s necessary to call the commit() method to write changes to the database.

We can also save multiple records in a single statement like this.

sql = '''
INSERT INTO "employees" VALUES
("02DEfG", "Employee 2", "emp2@mail.com", 24),
("03DEfG", "Employee 3", "emp3@mail.com", 22)
'''

Select data

Now let’s see how we can retrieve these data from the table.

import sqlite3

conn = sqlite3.connect('mydb.db')

cursor = conn.cursor()
sql = '''
SELECT * FROM "employees"
'''

cursor.execute(sql)

print('*' * 100)
for empid, name, email, age in cursor:
    print(f'ID {empid} | Name {name} | Email {email} | age {age}')
    print('-' * 100)

print('*' * 100)

conn.close()

Output

************************************************************
ID 01DEfG | Name Employee 1 | Email emp1@mail.com | age 25
------------------------------------------------------------
ID 02DEfG | Name Employee 2 | Email emp2@mail.com | age 24
------------------------------------------------------------
ID 03DEfG | Name Employee 3 | Email emp3@mail.com | age 22
------------------------------------------------------------
************************************************************

CRUD Operations using SQLite

import sqlite3
 
# CRUD operations - Create, Read, Update, Delete
 
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
 
def create_table(cursor):
    sql = "create table if not exists employees(eid integer primary key, name varchar(30) not null)"
    cursor.execute(sql)
 
def insert_data(cursor, conn, id, name):
    sql = f"insert into employees(eid, name) values({id},'{name}')"
    cursor.execute(sql)
    conn.commit()
 
def view_data(cursor):
    sql = "select * from employees"
    cursor.execute(sql)
    print("EID | NAME")
    print("----------")
    for eid, name in cursor:
        print(eid, name, sep=" | ")
 
def update_data(cursor, conn, id, name):
    sql = f"update employees set name='{name}' where eid={id}"
    cursor.execute(sql)
    conn.commit()
 
def delete_data(cursor, conn, id):
    sql = f"delete from employees where eid={id}"
    cursor.execute(sql)
    conn.commit()
 
 
print("What DB Operation do you want to do?")
print("1.CREATE TABLE")
print("2.INSERT DATA")
print("3.READ DATA")
print("4.UPDATE DATA")
print("5.DELETE DATA")
print("6.EXIT")
 
action = 1
 
while(action!=6):
    try:
        action = int(input("Enter 1/2/3/4/5/6: "))
        if action==1:
            create_table(cursor)
        elif action==2:
            id = int(input("Enter the employee id: "))
            name = input("Enter the employee name: ")
            insert_data(cursor, conn, id, name)
            print("Data inserted successfully!")
        elif action==3:
            view_data(cursor)
        elif action==4:
            id = int(input("Which employee's data? ID: "))
            name = input("Enter the updated name: ")
            update_data(cursor, conn, id, name)
            print(f"Data updated successfully for id={id}")
        elif action==5:
            id = int(input("Which employee to delete? ID: "))
            delete_data(cursor, conn, id)
            print(f"The employee {id} is deleted.")
        else:
            print("Please enter a valid input!")
    except ValueError:
        print("Please enter a valid integer!")
 
 
conn.close()