Python SQLite Tutorial – Database Programming with Examples

Introduction

SQLite is one of the most popular lightweight database systems available today. Unlike MySQL or PostgreSQL, SQLite does not require a separate server installation. It stores the entire database in a single file, making it ideal for beginners and small to medium-sized applications.

Python provides built-in support for SQLite through the sqlite3 module, allowing developers to create and manage databases without installing additional packages.

What is SQLite?

SQLite is a lightweight, serverless, self-contained relational database management system (RDBMS).

Importing SQLite Module

Python provides the built-in sqlite3 module.

import sqlite3

No installation is needed because it comes with Python.

Creating a Database Connection

A database connection allows Python to communicate with SQLite.

Syntax

sqlite3.connect("database_name.db")

Example:

import sqlite3
conn = sqlite3.connect(
    "company.db"
)
print("Database Connected")

Output:

If the database file does not exist, SQLite automatically creates it.

Creating a Cursor Object

A cursor executes SQL commands.

cursor = conn.cursor()

The cursor acts as a bridge between Python and SQLite.

Creating a Table

Let’s create an Employees table.

query = """
CREATE TABLE employees(
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
)
"""
cursor.execute(query)
conn.commit()

Viewing Tables

cursor.execute(
    """
    SELECT name
    FROM sqlite_master
    WHERE type='table'
    """
)
print(cursor.fetchall())

Output:

Insert Data into SQLite

The INSERT statement adds records.

Example:

cursor.execute(
    """
    INSERT INTO employees
    VALUES(
        1,
        'John',
        'IT',
        50000
    )
    """
)
conn.commit()

Output:

Insert Multiple Records

employees = [
    (2, "Mike", "HR", 45000),
    (3, "Sara", "Finance", 60000),
    (4, "David", "IT", 55000)
]
cursor.executemany(
    """
    INSERT INTO employees
    VALUES(?,?,?,?)
    """,
    employees
)
conn.commit()

Fetch Data from SQLite

The SELECT statement retrieves data.

Fetch All Records

cursor.execute(
    "SELECT * FROM employees"
)
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

Fetch One Record

cursor.execute(
    """
    SELECT *
    FROM employees
    WHERE id=1
    """
)
row = cursor.fetchone()
print(row)

Output:

Update Data

The UPDATE statement modifies existing records.

Example:

cursor.execute(
    """
    UPDATE employees
    SET salary=70000
    WHERE id=1
    """
)
conn.commit()

Output:

Delete Data

The DELETE statement removes records.

Example:

cursor.execute(
    """
    DELETE FROM employees
    WHERE id=4
    """
)
conn.commit()

Output:

Using WHERE Clause

Retrieve specific records.

cursor.execute(
    """
    SELECT *
    FROM employees
    WHERE department='IT'
    """
)
rows = cursor.fetchall()
print(rows)

Using ORDER BY

Sort records.

cursor.execute(
    """
    SELECT *
    FROM employees
    ORDER BY salary DESC
    """
)
rows = cursor.fetchall()

Using LIMIT

Retrieve a limited number of records.

cursor.execute(
    """
    SELECT *
    FROM employees
    LIMIT 2
    """
)
rows = cursor.fetchall()

Parameterized Queries

Instead of:

query = (
    "SELECT * FROM employees "
    "WHERE id=" + user_input
)

Use:

cursor.execute(
    """
    SELECT *
    FROM employees
    WHERE id=?
    """,
    (user_id,)
)

Benefits:

• More secure
• Prevents SQL injection
• Cleaner code

Closing Database Connection

Always close resources after use.

cursor.close()
conn.close()

This improves performance and resource management.

Complete SQLite Example

import sqlite3
conn = sqlite3.connect(
    "company.db"
)
cursor = conn.cursor()
cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS employees(
        id INTEGER PRIMARY KEY,
        name TEXT,
        salary INTEGER
    )
    """
)
cursor.execute(
    """
    INSERT INTO employees
    VALUES(
        1,
        'John',
        50000
    )
    """
)
conn.commit()
cursor.execute(
    "SELECT * FROM employees"
)
print(cursor.fetchall())
cursor.close()
conn.close()

Real-Life Example: Student Management System

A school application can use SQLite to:

• Store student records
• Update marks
• Retrieve student details
• Delete old records

Example table:

students

Fields:

id
name
class
marks

Common SQLite Commands

Advantages of SQLite

Common Mistakes

1. Forgetting commit()

Incorrect:

cursor.execute(query)

Correct:

cursor.execute(query)
conn.commit()

2. Not Closing Connection

Incorrect:

conn = sqlite3.connect("db.db")

Correct:

conn.close()

3. Missing WHERE Clause

Dangerous:

DELETE FROM employees

This deletes all records.

4. Using String Concatenation

Incorrect:

query = (
    "SELECT * FROM users "
    "WHERE id=" + user_id
)

Use parameterized queries instead.

5. Ignoring Exception Handling

Always use:

try:
    # database code
except sqlite3.Error as e:
    print(e)

Conclusion

SQLite is one of the easiest databases to learn and use with Python. Since the sqlite3 module is built into Python, developers can quickly create databases, tables, and applications without installing additional software.

SQLite provides a lightweight and powerful database solution for Python developers.

Related Python Tutorials