Introduction to SQLite in Python

SQLite is a lightweight, fast and embedded SQL database engine. SQLite is perfect
for small to medium-sized applications, prototyping, embedded systems and local
data storage in Python applications because it doesn't require a separate server
process like other relational database management systems (RDBMS)
like MySQL or PostgreSQL.
Features of SQLite:
1. Serverless
2. Self-Contained
3. Zero-Configuration
4. Transactional
5. Single-Database
We don't need to install anything additional to get started because Python has
built-in support for SQLite through the sqlite3 module. Let's understand each of
the features in detail.
1. Serverless
Generally, an RDBMS such as MySQL, PostgreSQL, etc., needs a separate server
process to operate. The applications that want to access the database server
use TCP/IP protocol to send and receive requests and it is
called client/server architecture. The diagram below illustrates the working of
relational databases:

Working of Relational Databases

SQLite does not require a server to run. SQLite database read and write directly
from the database files stored on disk and applications interact with that SQLite
database. It is one of SQLite's biggest advantages is that it is serverless. Here's
what that means:
• There is no separate server process to manage. The SQLite engine is embedded
directly into the application.

[1]
• All you need is the SQLite database file, your program can read from
and write to it without connecting to a remote service.
• This reduces overhead, setup complexity and dependencies, making SQLite
perfect for desktop apps, mobile apps, IoT devices or lightweight data-driven
Python scripts.

Working of SQLite
2. Self-Contained
SQLite is self-contained, here's what it means:
• It has no external dependencies. Everything it needs is included in a single
library.
• The entire database (schema + data) is stored in a single .sqlite or .db file.
• This file can be copied, backed up, shared or moved like any other document.
Key benefits of being Self-Contained:
• Portability: Move the database file across machines or platforms without
worrying about data loss or corruption.
• Integration: Easily bundle SQLite databases within applications, especially
Python packages or tools.
3. Zero-Configuration
Unlike other databases, SQLite requires zero setup:
• No configuration files or startup services are needed.
• You can start using SQLite as soon as you import the sqlite3 module in Python.
• We can simply connect to a database file and if it doesn’t exist, SQLite
automatically creates it.
Example: Connecting to a SQLite Database in Python
import sqlite3
conn = [Link]('[Link]') # Creates a new database file if it doesn’t
exist
cursor = [Link]()
4. Transactional
• SQLite supports full ACID (Atomicity, Consistency, Isolation, Durability)
transactions:
• Every operation in SQLite is atomic, i.e changes are either fully applied or not
applied at all.
• By default, SQLite wraps commands like INSERT, UPDATE and DELETE
inside implicit transactions, ensuring data integrity.

[2]
 We can also manage transactions explicitly using:
• BEGIN
• COMMIT
• ROLLBACKs.
Transaction Control Example:
[Link]("BEGIN")
# perform database operations
[Link]() # or [Link]() if something fails
5. Single-Database
SQLite uses a single-file database architecture, meaning:
• The entire database, i.e. tables, indexes, triggers and data, lives in a single file
only.
• This simplifies database management, as there's no need to manage
multiple configuration or log files.
• The file can be used across different platforms, tools and programming
languages.
Benefits of Single-File Storage:
• Easy to deploy and backup.
• Makes testing and debugging easier.
• Supports concurrent read operations (though writes are serialized).
Working of SQLite in Python
Python includes built-in support for SQLite via the sqlite3 module, which
conforms to the Python Database API Specification v2.0 (PEP 249).

Working:
• We write Python code.
• The sqlite3 module handles connections, queries, transactions, etc.
• It interacts directly with the .db file (no server needed).
Advanced Features in SQLite with Python

[3]
While SQLite is simple to use, it offers several advanced features:
Parameterized Queries
To prevent SQL injection:
[Link]("SELECT * FROM users WHERE id = ?", (user_id,))
Row Factory
Fetch rows as dictionaries:
conn.row_factory = [Link]
In-Memory Databases
For temporary, fast operations:
conn = [Link](':memory:')
Using with Statement for Safe Resource Handling:
with [Link]('[Link]') as conn:
cursor = [Link]()
[Link]("SELECT * FROM users")
Explanation:
import sqlite3: imports Python’s built-in SQLite module, which allows
interaction with SQLite databases.
[Link]('[Link]'): establishes a connection to a SQLite database file
named [Link].
• If the file does not exist, SQLite will automatically create it.
• This method returns a connection object that lets you interact with the
database.
[Link](): creates a cursor object from the connection, which is used to
execute SQL queries and fetch results.

1. connect()

Purpose:
Establishes a connection to an SQLite database file.

Syntax:

connection = [Link]('database_name.db')

Details:

• If the file doesn’t exist, SQLite creates it automatically.

• Returns a Connection object used to interact with the database.

Example:
[4]
import sqlite3
conn = [Link]('[Link]')

2. cursor()

Purpose:
Creates a Cursor object to execute SQL commands and fetch results.

Syntax:

cursor = [Link]()

Example:

cur = [Link]()

3. execute()

Purpose:
Executes an SQL query (like CREATE, INSERT, SELECT, UPDATE, DELETE).

Syntax:

[Link](SQL_command)

Example:

[Link]("CREATE TABLE IF NOT EXISTS students (id INTEGER, name

TEXT)")
[Link]("INSERT INTO students VALUES (1, 'Harish')")

To fetch data:

[Link]("SELECT * FROM students")

rows = [Link]()
for row in rows:
print(row)

4. close()

Purpose:
Closes the Cursor or Connection to free resources.

[5]
Syntax:

[Link]()
[Link]()

Example:

[Link]()
[Link]()

Complete Example:

import sqlite3

# 1. Connect to database (or create it)

conn = [Link]('[Link]')

# 2. Create cursor
cur = [Link]()

# 3. Execute SQL commands

[Link]("CREATE TABLE IF NOT EXISTS students (id INTEGER, name
TEXT)")
[Link]("INSERT INTO students VALUES (1, 'Harish')")
[Link]("INSERT INTO students VALUES (2, 'Saran')")

# Commit changes
[Link]()

# Retrieve data
[Link]("SELECT * FROM students")
for row in [Link]():
print(row)

# 4. Close cursor and connection

[Link]()
[Link]()

5. commit()

[6]
Purpose:
Saves (commits) all the changes you made to the database.

Used with:
The Connection object (not Cursor).

Syntax:

[Link]()

Why it’s important:

• Without commit(), changes like INSERT, UPDATE, or DELETE won’t be

permanently saved.
• It finalizes the current transaction.

Example:

conn = [Link]('[Link]')
cur = [Link]()

[Link]("INSERT INTO students VALUES (1, 'Anil')")

[Link]("UPDATE students SET name = 'Sunil' WHERE id = 1")

# Save changes permanently

[Link]()

6. fetchone()

Purpose:
Retrieves one record (row) from the result of a SELECT query.

Syntax:

row = [Link]()

Example:

[Link]("SELECT * FROM students")

row = [Link]()
print(row)

[7]
Output (example):

(1, 'Anil')

Note:

• Each time you call fetchone(), it returns the next row.

• When no more rows are available, it returns None.

7. fetchall()

Purpose:
Retrieves all remaining rows from the result of a SELECT query.

Syntax:

rows = [Link]()

Example:

[Link]("SELECT * FROM students")

rows = [Link]()
for r in rows:
print(r)

Output (example):

(1, 'Anil')
(2, 'Sunil')
(3, 'Kiran')

Full Example with All Methods

import sqlite3

# Connect to database
conn = [Link]('[Link]')

# Create cursor
cur = [Link]()

# Create table

[8]
[Link]("CREATE TABLE IF NOT EXISTS students (id INTEGER, name
TEXT)")

# Insert data
[Link]("INSERT INTO students VALUES (1, 'Anil')")
[Link]("INSERT INTO students VALUES (2, 'Sunil')")
[Link]("INSERT INTO students VALUES (3, 'Kiran')")

# Save changes
[Link]()

# Retrieve one row

[Link]("SELECT * FROM students")
print("First row:", [Link]())

# Retrieve all rows

[Link]("SELECT * FROM students")
rows = [Link]()
print("All rows:")
for row in rows:
print(row)

# Close everything
[Link]()
[Link]()

Would you like me to include fetchmany(n) too (used to fetch a specific number
of rows at a time)?

8. fetchmany(n)

Purpose:
Fetches a specific number of rows (n) from the result of a SELECT query.

Syntax:

rows = [Link](n)

Details:

• Returns a list of tuples, each tuple representing one row.

[9]
 • If fewer than n rows remain, it returns only the available rows.
• Useful for handling large datasets efficiently, where fetchall() could
consume too much memory.

Example:

import sqlite3

# Connect to database
conn = [Link]('[Link]')
cur = [Link]()

# Select all rows

[Link]("SELECT * FROM students")

# Fetch 2 rows at a time

rows = [Link](2)
print("First 2 rows:", rows)

# Fetch next 2 rows

next_rows = [Link](2)
print("Next 2 rows:", next_rows)

# Close connection
[Link]()
[Link]()

Output:

First 2 rows: [(1, 'Anil'), (2, 'Sunil')]

Next 2 rows: [(3, 'Kiran')]

When to use which fetch method

Method Description Best Use Case

fetchone() Fetches 1 row at a time Step-by-step processing
fetchmany(n) Fetches n rows at a time Medium data sets, memory control
fetchall() Fetches all remaining rows Small data sets or for quick queries

SQLite Methods

[10]
 Method Belongs To Purpose
connect() sqlite3 module Connect to (or create) a database
cursor() Connection object Create a cursor for executing SQL
execute() Cursor object Run SQL statements
commit() Connection object Save changes permanently
fetchone() Cursor object Fetch one record
fetchmany(n) Cursor object Fetch n records
fetchall() Cursor object Fetch all records
close() Cursor/Connection Close the connection or cursor

SQLite methods (connect(), cursor(), execute(), commit(), fetchone(),

fetchmany(), fetchall(), and close()) together in one flow.

Mini Student Database Example (SQLite in Python)

import sqlite3

# 1 Connect to (or create) the database

conn = [Link]('student_db.db')
print("Database connected successfully!")

# 2 Create a cursor object

cur = [Link]()

# 3 Create a table
[Link]('''CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY
KEY, name TEXT, marks INTEGER)''')
print("Table created successfully!")

# 4 Insert some records

[Link]("INSERT INTO students (name, marks) VALUES ('Harish', 85)")
[Link]("INSERT INTO students (name, marks) VALUES ('Saran', 92)")
[Link]("INSERT INTO students (name, marks) VALUES ('Kiran', 78)")
[Link]("INSERT INTO students (name, marks) VALUES ('Anil', 88)")

# 5 Commit changes (save data)

[Link]()
print("Data inserted and saved successfully!")

# 6 Retrieve data

[11]
# (a) Fetch only one row
[Link]("SELECT * FROM students")
print("\nFirst row (fetchone):")
print([Link]())

# (b) Fetch next 2 rows

print("\nNext 2 rows (fetchmany):")
print([Link](2))

# (c) Fetch all remaining rows

print("\nAll remaining rows (fetchall):")
print([Link]())

# 7 Update a record
[Link]("UPDATE students SET marks = 90 WHERE name = 'Kiran'")
[Link]()
print("\nRecord updated successfully!")

# 8 Delete a record
[Link]("DELETE FROM students WHERE name = 'Anil'")
[Link]()
print("Record deleted successfully!")

# 9 View final table contents

[Link]("SELECT * FROM students")
print("\nFinal table data:")
for row in [Link]():
print(row)

# Close cursor and connection

[Link]()
[Link]()
print("\nDatabase connection closed!")
Output
Database connected successfully!
Table created successfully!
Data inserted and saved successfully!

First row (fetchone):

[12]
(1, 'Harish', 85)

Next 2 rows (fetchmany):

[(2, 'Saran', 92), (3, 'Kiran', 78)]

All remaining rows (fetchall):

[(4, 'Anil', 88)]

Record updated successfully!

Record deleted successfully!

Final table data:

(1, 'Harish', 85)
(2, 'Saran', 92)
(3, 'Kiran', 90)

Database connection closed!

Key Learning Points

• connect() → Open or create a database

• cursor() → Run SQL commands
• execute() → Perform SQL operations
• commit() → Save changes permanently
• fetchone() / fetchmany(n) / fetchall() → Read query results
• close() → Cleanly close everything

Connect to Database:

To connect to a database using SQLite3 in Python, you use the built-in sqlite3
module.
Here’s a clear step-by-step example

Step 1: Import the sqlite3 module

import sqlite3
Step 2: Connect to a Database
conn = [Link]('[Link]')

[13]
 • If the database [Link] does not exist, SQLite will create it
automatically.
• The object conn is your connection to the database.

Drop Records

The DROP command is used to delete an entire table (or other database objects
like views or indexes).
Once dropped, all data and structure of that table are permanently removed.

Example: Drop a Table in SQLite using Python

import sqlite3

# Connect to the database (or create if not exists)

conn = [Link]('[Link]')

# Create a cursor object

cursor = [Link]()

# Drop table if it exists

[Link]("DROP TABLE IF EXISTS students")

# Commit the change

[Link]()

print("Table dropped successfully!")

# Close the connection

[Link]()

Explanation:

• DROP TABLE table_name → deletes the specified table.

• IF EXISTS → prevents an error if the table doesn’t exist.
• commit() → applies the changes to the database.
• close() → closes the database connection properly.

Example Output:

Table dropped successfully!

[14]
Explanation
Operation Command Purpose
CREATE CREATE TABLE IF NOT EXISTS Creates table (no
TABLE students (name, age, grade) primary key)
INSERT INSERT INTO students VALUES (...) Adds data
SELECT SELECT * FROM students Displays all rows
UPDATE UPDATE students SET grade='A+' Changes data
WHERE name='Bob'
DELETE DELETE FROM students WHERE Removes a row
name='Charlie'

[15]