SQLite with Python allows developers to store, manage, and retrieve structured data directly from a Python program without setting up a separate database server. Python provides the built-in sqlite3 module, which makes it easy for beginners to create databases, tables, insert records, update data, delete records, and execute SQL queries.
 

Table of Contents

---

What is SQLite?

SQLite is a lightweight, serverless, file-based relational database management system.

1. It stores the complete database in a single file, making it suitable for small applications, learning projects, testing, and applications that do not require a separate database server.
2. Unlike databases such as MySQL or PostgreSQL, SQLite does not require a database server to be installed and running.
3. The database can be created directly from a Python program.

Why Use SQLite with Python?

SQLite is useful when a Python application needs to store data permanently instead of keeping it only in variables or lists.
Some common uses include:

1. Simple Setup: SQLite does not require a separate database server.
2. Easy to Learn: SQLite uses SQL commands that are simple to understand for beginners.
3. Lightweight: SQLite requires very few system resources.
4. Portable: The database can be stored in a single file and moved easily.
5. Useful for Projects: SQLite is suitable for small applications such as expense trackers, student management systems, and to-do applications.

What is the sqlite3 Module?

Python provides the built-in sqlite3 module for working with SQLite databases. It allows a Python program to connect to a database, execute SQL statements, retrieve records, and manage database transactions.
Since sqlite3 is included with standard Python installations, you usually do not need to install an additional package.
The basic import statement is:

import sqlite3

Creating a SQLite Database

A database connection can be created using the sqlite3.connect () function. If the specified database file does not exist, SQLite creates it automatically.

# Python program to create a SQLite database
import sqlite3
connection = sqlite3.connect("students.db")
print("Database created successfully.")
connection.close()

Output:
Database created successfully.

In this example, students.db is the SQLite database file. The connection object represents the connection between the Python program and the database.

Creating a Table

After connecting to the database, a table can be created using the CREATE TABLE SQL statement.

# Python program to create a table in SQLite
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,
    course TEXT
)
""")
connection.commit()
connection.close()
print("Table created successfully.")

Output:
Table created successfully.

The students table contains four columns:

• id: Stores a unique number for each student.
• name: Stores the student's name.
• age: Stores the student's age.
• course: Stores the student's course.

The IF NOT EXISTS clause prevents SQLite from showing an error if the table already exists.

Understanding the Database Cursor

A cursor is used to execute SQL statements and retrieve results from the database.
A cursor can be created using:

cursor = connection.cursor()

The cursor allows Python to communicate with the database by executing SQL commands such as CREATE, INSERT, SELECT, UPDATE, and DELETE.

Inserting Data into SQLite

The INSERT INTO SQL statement is used to add records to a table.

# Python program to insert data into a SQLite table
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute(
    "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
    ("Tanvi", 20, "BSc IT")
)
connection.commit()
connection.close()

print("Student record inserted successfully.")

Output:
Student record inserted successfully.

The question marks are placeholders for values. Using placeholders is safer than directly joining user input into an SQL statement.

Inserting Multiple Records

Multiple records can be inserted using executemany().

# Python program to insert multiple student records into SQLite
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()

students = [
    ("Tanvi", 20, "BSc IT"),
    ("Rahul", 21, "BCA"),
    ("Priya", 20, "BSc CS")
]
cursor.executemany(
    "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
    students
)
connection.commit()
connection.close()

print("Multiple records inserted successfully.")

Output:
Multiple records inserted successfully.

The executemany() method executes the same SQL statement for multiple sets of values.

Reading Data from SQLite

The SELECT statement is used to retrieve data from a SQLite table.

# Python program to read student records from SQLite
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
for record in records:
    print(record)
connection.close()

Output:
(1, 'Tanvi', 20, 'BSc IT')
(2, 'Rahul', 21, 'BCA')
(3, 'Priya', 20, 'BSc CS')

The fetchall() method returns all rows returned by the query.

Using fetchone()

The fetchone() method retrieves one record from the query result.

# Python program to retrieve one student record from SQLite
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
record = cursor.fetchone()
print(record)
connection.close()

Output:
(1, 'Tanvi', 20, 'BSc IT')

fetchone() is useful when only one record needs to be retrieved.

Using fetchmany()

The fetchmany() method can be used to retrieve a specified number of records.

# Python program to retrieve multiple selected records from SQLite
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
records = cursor.fetchmany(2)
for record in records:
    print(record)
connection.close()

Output:
(1, 'Tanvi', 20, 'BSc IT')
(2, 'Rahul', 21, 'BCA')

Updating Data in SQLite

The UPDATE statement is used to modify existing records.

# Python program to update a student record in SQLite
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute(
    "UPDATE students SET age = ? WHERE name = ?",
    (21, "Tanvi")
)
connection.commit()
connection.close()
print("Student record updated successfully.")

Output:
Student record updated successfully.

The WHERE clause identifies which record should be updated.

Deleting Data from SQLite

The DELETE statement is used to remove records from a table.

# Python program to delete a student record from SQLite
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute(
    "DELETE FROM students WHERE name = ?",
    ("Rahul",)
)
connection.commit()
connection.close()
print("Student record deleted successfully.")

Output:
Student record deleted successfully.

The WHERE clause is important because it specifies which record should be deleted.

Using Parameters in SQL Queries

Parameterized queries allow values to be passed separately from the SQL statement.

For example:

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

The ? acts as a placeholder for the value.
Parameterized queries are recommended because they help prevent SQL injection and avoid problems caused by directly inserting user input into SQL statements.

Committing and Closing the Database

The commit() method saves changes made to the database.

connection.commit()

It should generally be used after operations that modify the database, such as INSERT, UPDATE, and DELETE.
The connection should also be closed after database operations are completed.

connection.close()

Closing the connection releases the database resources used by the program.

How SQLite with Python Works

The basic process of working with SQLite in Python is simple.

1. Import the Module: Python first imports the built-in sqlite3 module.
2. Connect to the Database: The program creates a connection to an existing database file or creates a new database file.
3. Create a Cursor: A cursor is created to execute SQL statements.
4. Execute SQL Commands: The cursor executes commands such as CREATE, INSERT, SELECT, UPDATE, or DELETE.
5. Commit Changes: Changes made to the database are saved using commit().
6. Retrieve Results: Queries such as SELECT can retrieve records using methods such as fetchone() or fetchall().
7. Close the Connection: The database connection is closed after the required operations are completed.

Complete SQLite Program

The following program creates a student database, creates a table, inserts records, displays them, updates a record, and displays the updated data.

# Python program to create and manage a SQLite student database
import sqlite3
connection = sqlite3.connect("students.db")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    course TEXT
)
""")
cursor.execute(
    "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
    ("Tanvi", 20, "BSc IT")
)
connection.commit()
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
print("Student Records:")
for record in records:
    print(record)
cursor.execute(
    "UPDATE students SET course = ? WHERE name = ?",
    ("BSc Information Technology", "Tanvi")
)
connection.commit()
print("Student record updated successfully.")
connection.close()

Output:
Student Records:
(1, 'Tanvi', 20, 'BSc IT')
Student record updated successfully.

If this program is executed multiple times, it will insert the same student again each time. In a real application, the program should include appropriate checks or use a unique field when duplicate records need to be prevented.

Advantages of SQLite with Python

• Easy Setup: SQLite works without a separate database server.
• Lightweight: SQLite is small and requires minimal system resources.
• Built-in Support: Python provides the sqlite3 module as part of its standard library.
• Portable Database: The database is stored in a file that can be moved between systems.
• Simple Development: SQLite is convenient for small applications, prototypes, and learning projects.

Common Beginner Mistakes

1. Forgetting to Commit Changes: Changes made by INSERT, UPDATE, or DELETE may not be saved if commit() is not called.
2. Forgetting to Close the Connection: Leaving database connections open can unnecessarily consume resources.
3. Using Unparameterized Queries: Directly inserting user input into SQL statements can create security problems and SQL errors.
4. Forgetting the WHERE Clause: An UPDATE or DELETE statement without an appropriate WHERE clause can affect multiple records.
5. Using the Wrong Database File: Connecting to a different file than expected can make it appear that database records are missing.

Conclusion

SQLite with Python provides a simple way to store and manage structured data without installing a separate database server. The built-in sqlite3 module allows Python programs to create databases, create tables, insert records, retrieve data, update records, and delete records using SQL commands. It is especially useful for beginners, small applications, prototypes, and projects that need a lightweight database.

Frequently Asked Questions

1. What is SQLite in Python?

SQLite is a lightweight, serverless database that can be used in Python through the built-in sqlite3 module.

2. Do I need to install SQLite separately to use sqlite3 in Python?

Python's standard library includes the sqlite3 module, so it normally does not require a separate Python package installation.

3. What is the use of commit() in SQLite?

The commit() method saves changes made to the database, such as inserted, updated, or deleted records.

4. What is the difference between fetchone() and fetchall()?

fetchone() retrieves one record from the query result, while fetchall() retrieves all available records.

5. Is SQLite suitable for every application?

SQLite is suitable for many small applications and local data-storage requirements, while larger applications with high concurrency or server-based requirements may use databases such as PostgreSQL or MySQL.