Introduction to SQLite in Python

SQLite is a lightweight, serverless relational database engine used to store and manage structured data. Python provides the built-in sqlite3 module to interact with SQLite databases, allowing developers to execute SQL queries without installing or configuring a separate database server.

Features of SQLite

1. SQLite operates without a separate database server, reducing setup and maintenance requirements.
2. Database engine has minimal external dependencies and stores the database in a single file.
3. No server installation, configuration files or initialization procedures are required.
4. Supports ACID transactions, ensuring reliable database operations.
5. Database files can be transferred across compatible operating systems and platforms.

Working with SQLite

Python provides the sqlite3 module for connecting to databases, executing SQL statements and managing transactions.

1. Establishing a Database Connection

sqlite3.connect() method establishes a connection to an SQLite database. If the specified database file does not exist, SQLite creates it automatically.

import sqlite3

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

print("Database connected successfully")

conn.close()

Output:

Database connected successfully

Explanation:

• Connection objects manages communication with the database.
• Cursor object executes SQL statements and retrieves query results.

2. Creating Table and Inserting Data

It uses the CREATE TABLE statement to define a table and INSERT INTO to add records. Python executes these statements through the cursor object.

import sqlite3

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

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

cursor.execute(
    "INSERT INTO students (name, age) VALUES (?, ?)",
    ("Aarav", 20)
)

conn.commit()
conn.close()

Explanation:

• CREATE TABLE IF NOT EXISTS statement creates the students table with id, name and age columns only if it does not already exist.
• INSERT statement adds Aarav's record using parameterized placeholders (?), which safely binds values to the query.
• commit() method saves the inserted record to the database, while close() terminates the connection.

3. Retrieving Data from SQLite

SELECT statement retrieves records from a database table. The fetchall() method returns all remaining rows from the query result.

import sqlite3

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

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

Output:

(1, 'Aarav', 20)

Exaplanation:

• fetchall() method retrieves all records returned by the SELECT query as a list of tuples.
• for loop iterates through the retrieved records and prints each student's details.

4. Committing Changes in SQLite

COMMIT operation saves changes made during a transaction to the SQLite database. In Python, the commit() method is used to permanently apply INSERT, UPDATE and DELETE operations.

import sqlite3

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

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

cursor.execute(
    "INSERT INTO students (name, age) VALUES (?, ?)",
    ("Aarav", 20)
)

conn.commit()

print("Record inserted and changes committed.")

conn.close()

Output:

Record inserted and changes committed.

Explanation:

• connect() method establishes a connection to the students.db database, and cursor() creates a cursor for executing SQL statements.
• CREATE TABLE statement creates the students table if it does not already exist.
• INSERT statement adds a student record using parameterized placeholders.
• commit() method saves the changes to the database, while close() terminates the connection.

SQLite vs Other Databases

For detailed information, on using SQLite in Python, Please refer to our article, SQLite Tutorial.