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
------------------------------------------------------------
************************************************************