Introduction

SQLite is a lightweight, serverless, self-contained relational database management system (RDBMS). It is built into Python through the sqlite3 module, so no additional installation is required.

SQLite is widely used for:

• Desktop Applications

• Automation Frameworks

• Test Data Storage

• Logging Systems

• Small Web Applications

• Data Analysis Projects

• Local Data Persistence

In this tutorial, you will learn SQLite basics in Python, including database creation, tables, CRUD operations, practical examples, automation testing use cases, common mistakes, and best practices.

---

What is SQLite?

SQLite is a file-based database that stores all data in a single file.

Unlike databases such as MySQL or PostgreSQL:

• No server is required

• No configuration is needed

• Easy to use and portable

---

Why Use SQLite?

SQLite offers:

• Lightweight database

• Built into Python

• Easy setup

• Fast performance

• Portable database files

• Suitable for automation projects

---

Importing SQLite

SQLite support is provided through Python’s built-in sqlite3 module.

import sqlite3

---

Creating a Database

Example

import sqlite3

connection = sqlite3.connect("students.db")

print("Database Created")

Output

Database Created

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

---

Creating a Table

Example

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")

connection.commit()

print("Table Created")

Output

Table Created

---

Understanding Common Data Types

SQLite Type	Description
INTEGER	Whole numbers
REAL	Decimal numbers
TEXT	Strings
BLOB	Binary data
NULL	Empty values

---

Inserting Data

Example

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("""
INSERT INTO students(name, age)
VALUES (?, ?)
""", ("John", 25))

connection.commit()

print("Record Inserted")

Output

Record Inserted

---

Viewing Data

Example

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM students"
)

records = cursor.fetchall()

for record in records:
    print(record)

Output

(1, 'John', 25)

---

Fetch Methods

fetchone()

Returns a single row.

record = cursor.fetchone()

---

fetchall()

Returns all rows.

records = cursor.fetchall()

---

fetchmany()

Returns a specified number of rows.

records = cursor.fetchmany(5)

---

Inserting Multiple Records

Example

students = [
    ("Alice", 22),
    ("Bob", 24),
    ("David", 26)
]

cursor.executemany("""
INSERT INTO students(name, age)
VALUES (?, ?)
""", students)

connection.commit()

---

Updating Data

Example

cursor.execute("""
UPDATE students
SET age = ?
WHERE name = ?
""", (30, "John"))

connection.commit()

Output

Record Updated

---

Deleting Data

Example

cursor.execute("""
DELETE FROM students
WHERE name = ?
""", ("John",))

connection.commit()

Output

Record Deleted

---

Using WHERE Clause

Example

cursor.execute("""
SELECT *
FROM students
WHERE age > 23
""")

records = cursor.fetchall()

for record in records:
    print(record)

---

Closing Database Connection

Always close connections after use.

connection.close()

---

Using Context Manager

Example

import sqlite3

with sqlite3.connect("students.db") as connection:

    cursor = connection.cursor()

    cursor.execute(
        "SELECT * FROM students"
    )

    records = cursor.fetchall()

    print(records)

Connection closes automatically.

---

Complete CRUD Example

Create

cursor.execute("""
INSERT INTO students(name, age)
VALUES (?, ?)
""", ("John", 25))

Read

cursor.execute(
    "SELECT * FROM students"
)

Update

cursor.execute("""
UPDATE students
SET age = 30
WHERE name = 'John'
""")

Delete

cursor.execute("""
DELETE FROM students
WHERE name = 'John'
""")

---

SQLite in Selenium Automation

SQLite can store test execution results.

Example

cursor.execute("""
CREATE TABLE IF NOT EXISTS test_results (
    test_name TEXT,
    status TEXT
)
""")

cursor.execute("""
INSERT INTO test_results
VALUES (?, ?)
""", ("Login Test", "PASS"))

connection.commit()

Output

Login Test Saved

---

Selenium Automation Use Case

Store:

• Test Results

• Execution Time

• Browser Details

• Failed Test Cases

• Screenshots Metadata

Example:

cursor.execute("""
INSERT INTO test_results
VALUES (?, ?)
""", ("Checkout Test", "FAIL"))

---

SQLite in API Automation

Store API response data.

Example

cursor.execute("""
CREATE TABLE IF NOT EXISTS api_logs (
    endpoint TEXT,
    status_code INTEGER
)
""")

cursor.execute("""
INSERT INTO api_logs
VALUES (?, ?)
""", (
    "/users",
    200
))

Output

API Log Saved

---

Parameterized Queries

Always use placeholders.

Correct

cursor.execute("""
SELECT *
FROM students
WHERE name = ?
""", ("John",))

---

Incorrect

cursor.execute(
    f"SELECT * FROM students WHERE name='{name}'"
)

This can lead to SQL injection vulnerabilities.

---

Common Mistakes Beginners Make

Forgetting commit()

Incorrect

cursor.execute("""
INSERT INTO students
VALUES (1, 'John', 25)
""")

Changes won’t be saved.

---

Correct

connection.commit()

---

Forgetting close()

Incorrect

connection = sqlite3.connect(
    "students.db"
)

Connection remains open.

---

Correct

connection.close()

---

Using String Formatting in SQL

Incorrect

query = f"""
SELECT *
FROM students
WHERE name='{name}'
"""

---

Correct

cursor.execute(
    "SELECT * FROM students WHERE name=?",
    (name,)
)

---

Forgetting IF NOT EXISTS

Incorrect

CREATE TABLE students

May fail if the table already exists.

---

Correct

CREATE TABLE IF NOT EXISTS students

---

Best Practices

Use Parameterized Queries

WHERE name = ?

---

Always Commit Changes

connection.commit()

---

Close Connections

connection.close()

or use:

with sqlite3.connect():

---

Use Meaningful Table Names

Examples:

users
employees
test_results
api_logs

---

Handle Exceptions

try:
    pass

except sqlite3.Error:
    pass

---

Advantages of SQLite

• Built into Python

• No server required

• Lightweight

• Easy to deploy

• Fast for small projects

• Portable database file

---

Limitations of SQLite

• Not suitable for very large systems

• Limited concurrency

• Fewer advanced features than enterprise databases

---

SQLite vs MySQL

Feature	SQLite	MySQL
Server Required	No	Yes
Installation	Minimal	Required
Portability	High	Moderate
Enterprise Scale	No	Yes
Built into Python	Yes	No

---

Conclusion

SQLite is one of the easiest databases to learn and use with Python. Because it is lightweight, serverless, and included with Python, it is an excellent choice for beginners, automation engineers, and small applications.

Whether you’re storing Selenium test results, API logs, configuration data, or application records, SQLite provides a simple yet powerful database solution.

Learning SQLite is an important step toward understanding database programming and data persistence in Python.

---

Frequently Asked Questions (FAQs)

What is SQLite?

SQLite is a lightweight, file-based relational database.

---

Does SQLite require installation?

No.

Python includes SQLite support through the sqlite3 module.

---

Which module is used for SQLite?

import sqlite3

---

How do I save changes?

connection.commit()

---

How do I retrieve records?

cursor.fetchall()

or

cursor.fetchone()

---

Key Takeaways

• SQLite is a lightweight, serverless database.

• Python provides SQLite support through the sqlite3 module.

• Databases are stored as files.

• Use connect() to create/open databases.

• Use SQL statements for CRUD operations.

• Always call commit() after modifications.

• Use parameterized queries to prevent SQL injection.

• Close database connections when finished.

• Useful for Selenium test results and API logs.

• SQLite is ideal for learning database programming and small-to-medium projects.