Python 3.x - SQLite 3 Database CRUD Operations Examples

We can make use of the sqlite3 module in Python to make use of the lightweight disk-based database SQLite that doesn’t require a separate server process.

One may use this database for devices that do not have support or enough storage for installing databases like Oracle, MySQL, or PostgreSQL, prototyping an application, or saving internal data.

Let's get started with how to create an SQLite database using sqlite3 module in Python.

Step 1: Creating an SQLite Database

import sqlite3

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

We first import the sqlite3 module and then create the database connection using the connect() method from the sqlite3 module. If the database does not exist it will be created, else we connect it to the existing database.

Step 2: Create a cursor object to interact with our database

cursor = conn.cursor()

Next we need to create a cursor object that we will need to interact with our database and perform the CRUD - Create - Read - Update - Delete operations.

Step 3: Create a table

cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        emp_id INTEGER PRIMARY KEY,
        emp_name TEXT,
        emp_dept TEXT)
''')

Now we are good to create a SQLite Database table, the above example will create an employees table.

Below is the list of datatypes in SQLite.

SQLite Data Type	Description	Example
NULL	Represents a missing value.	NULL
INTEGER	Whole numbers, signed/unsigned.	101
REAL	Floating-point numbers.	22.44
TEXT	Text strings.	'Sammy'
BLOB	Binary data.	[some binary data]
NUMERIC	Any numeric value.	1, 5, -11

Note: There is no DATE and BOOLEAN datatypes available in SQLite, you can make use of TEXT and INTEGER data types to represent such values.

Step 4: Insert Operation Example

cursor.execute("INSERT INTO employees (emp_name, emp_dept) VALUES (?, ?)", ('Sam', 'Finance'))
conn.commit()

cursor.execute("INSERT INTO employees (emp_name, emp_dept) VALUES (?, ?)", ('Mike', 'IT'))
conn.commit()

Step 5: Select and Print Results

cursor.execute("SELECT * FROM employees")
data = cursor.fetchall()

for row in data:
    print(row)

(1, 'Sam', 'Finance')
(2, 'Mike', 'IT')

Step 6: Update Example

cursor.execute("UPDATE employees SET emp_dept = ? WHERE emp_name = ?", ('IT', 'Sam'))
conn.commit()

Step 7: Delete Example

cursor.execute("DELETE FROM employees WHERE emp_name = ?", ('Sam',))
conn.commit()

Complete Code:

import sqlite3

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

cursor = conn.cursor()

# Create
cursor.execute('''
    CREATE TABLE IF NOT EXISTS employees (
        emp_id INTEGER PRIMARY KEY,
        emp_name TEXT,
        emp_dept TEXT)
''')

# Insert
cursor.execute("INSERT INTO employees (emp_name, emp_dept) VALUES (?, ?)", ('Sam', 'Finance'))
conn.commit()

cursor.execute("INSERT INTO employees (emp_name, emp_dept) VALUES (?, ?)", ('Mike', 'IT'))
conn.commit()

# Select 
cursor.execute("SELECT * FROM employees")
data = cursor.fetchall()

for row in data:
    print(row)

# Update
cursor.execute("UPDATE employees SET emp_dept = ? WHERE emp_name = ?", ('IT', 'Sam'))
conn.commit()

# Delete
cursor.execute("DELETE FROM employees WHERE emp_name = ?", ('Sam',))
conn.commit()