🗄️ SQLite3 in Python

This content originally appeared on DEV Community and was authored by likhitha manikonda

If you’re new to databases and want a lightweight, easy-to-use solution, SQLite3 is perfect!

This guide explains all essential concepts in simple language with examples you can copy and run.

✅ What is SQLite3?

SQLite is a lightweight, file-based database.
No server required – your data is stored in a single .db file.
Perfect for small projects, prototypes, and learning SQL.

📦 Install SQLite3

SQLite comes built-in with Python, so no extra installation needed!

Just import:

import sqlite3

✅ How SQLite Works

Explanation of Diagram:
Great question! Let’s break down what “Python Application → interacts with SQLite Methods via the Python DB API” means in the diagram:

✅ Python Application

This is your Python program (the code you write) that needs to store, retrieve, or manipulate data.
Example: A script for managing users, invoices, or any data-driven app.

✅ Python DB API

A standard interface defined in Python (PEP 249) for interacting with relational databases.
It ensures consistency across different database modules (SQLite, MySQL, PostgreSQL).
Provides methods like:
- connect() → Connect to the database
- cursor() → Create a cursor for executing queries
- execute() → Run SQL commands
- fetchall() → Retrieve query results

✅ SQLite Methods

These are the actual functions implemented by the sqlite3 module that follow the DB API standard.
Examples:
- sqlite3.connect('my_database.db')
- cursor.execute("SELECT * FROM users")

✅ How It Works

Your Python Application calls sqlite3.connect() → creates a connection to the .db file.
Through the DB API, you use methods like execute() to send SQL queries.
SQLite engine processes the query and returns results.
Your Python code handles the results (e.g., prints them, stores them in variables).

✅ Visual Flow:

Python Code → sqlite3 (DB API methods) → SQLite Database → Results back to Python

In case if your a java developer SQLite is similar the H2 in memory database.

✅ What is `'my_database.db'`?

It’s the name of the SQLite database file.
When you run:

  conn = sqlite3.connect('my_database.db')

If the file exists, Python connects to it.
If it doesn’t exist, Python creates a new database file in your current directory.
- You can use any name (e.g., data.sqlite, students.db).

✅ Basic Operations (CRUD OPERATIONS)

Connect to Database

conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

Create Table

cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')
conn.commit()

Insert Data

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 30))
conn.commit()

Fetch Data

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Delete Data

import sqlite3

# Connect to the database
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Delete a specific record
cursor.execute("DELETE FROM users WHERE name = ?", ('Alice',))

# Delete All Rows (Keep Table):
# This clears the table but does not remove its schema.
cursor.execute("DELETE FROM users")

# Commit changes and close connection
conn.commit()
conn.close()

✅ Key Points:

Always use a WHERE clause to avoid deleting all rows unintentionally.
Use parameterized queries (?) to prevent SQL injection.
After deletion, call conn.commit() to save changes.

✅ Advanced Topics (Expanded)

1. Transactions

Transactions ensure atomicity (all-or-nothing execution).
Use BEGIN, COMMIT, and ROLLBACK:

conn.execute('BEGIN')
cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
conn.commit()  # or conn.rollback() if something fails

Why? Prevents partial updates when errors occur.

2. Indexes for Speed

Indexes make queries faster by reducing search time.

cursor.execute("CREATE INDEX idx_name ON users(name)")

Tip: Use indexes on columns frequently used in WHERE clauses.

3. JOIN Queries

Combine data from multiple tables:

cursor.execute('''
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
''')

Types of JOIN:

INNER JOIN → Matches in both tables
LEFT JOIN → All from left + matches from right
CROSS JOIN → Cartesian product

4. Foreign Keys

Enable relationships between tables:

cursor.execute("PRAGMA foreign_keys = ON")

Example:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
)

5. Backup Database

Create a backup easily:

with sqlite3.connect('backup.db') as backup_conn:
    conn.backup(backup_conn)

6. Row Factory for Dict Output

Get results as dictionaries instead of tuples:

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(dict(row))

7. Error Handling

Use try-except for safe operations:

try:
    cursor.execute("SELECT * FROM users")
except sqlite3.Error as e:
    print("Error:", e)

8. Prepared Statements

Prevent SQL injection:

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Charlie', 35))

9. Performance Tips

Use executemany() for bulk inserts:

data = [('Tom', 22), ('Jerry', 24)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)

Use PRAGMA cache_size and PRAGMA synchronous for tuning.

✅ Summary Table

Concept	Command Example
Connect DB	`sqlite3.connect('file.db')`
Create Table	`CREATE TABLE ...`
Insert Data	`INSERT INTO table VALUES (?, ?)`
Fetch Data	`SELECT * FROM table`
Update Data	`UPDATE table SET column=? WHERE condition`
Delete Data	`DELETE FROM table WHERE condition`
Index	`CREATE INDEX idx_name ON table(column)`
Join	`SELECT ... JOIN ...`
Backup	`conn.backup(backup_conn)`

🔥 Pro Tip: Use SQLite for small apps, testing, or learning SQL. For large-scale apps, consider PostgreSQL or MySQL.

✅ Next Step:

Would you like me to bundle this article with the diagram and all Python scripts into a ZIP file for easy publishing, or create a combined infographic summarizing SQLite basics + advanced topics for Dev.to?

📦 Install SQLite3

SQLite comes built-in with Python, so no extra installation needed!

Just import:

import sqlite3

✅ How SQLite Works

https://your-image-host/sqlite-workflow.png

Explanation of Diagram:

Python Application → interacts with SQLite Methods via the Python DB API.
SQLite Methods → execute queries on the Database and return Results.
Flow includes:
- Query Execution
- Results retrieval
- SQLite methods for communication.

✅ What is `'my_database.db'`?

It’s the name of the SQLite database file.
When you run:

  conn = sqlite3.connect('my_database.db')

If the file exists, Python connects to it.
If it doesn’t exist, Python creates a new database file in your current directory.
- You can use any name (e.g., data.sqlite, students.db).

✅ Basic Operations

Connect to Database

conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

Create Table

cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')
conn.commit()

Insert Data

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 30))
conn.commit()

Fetch Data

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

✅ Advanced Topics (Expanded)

1. Transactions

Transactions ensure atomicity (all-or-nothing execution).
Use BEGIN, COMMIT, and ROLLBACK:

conn.execute('BEGIN')
cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
conn.commit()  # or conn.rollback() if something fails

Why? Prevents partial updates when errors occur.

2. Indexes for Speed

Indexes make queries faster by reducing search time.

cursor.execute("CREATE INDEX idx_name ON users(name)")

Tip: Use indexes on columns frequently used in WHERE clauses.

3. JOIN Queries

Combine data from multiple tables:

cursor.execute('''
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
''')

Types of JOIN:

INNER JOIN → Matches in both tables
LEFT JOIN → All from left + matches from right
CROSS JOIN → Cartesian product

4. Foreign Keys

Enable relationships between tables:

cursor.execute("PRAGMA foreign_keys = ON")

Example:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
)

5. Backup Database

Create a backup easily:

with sqlite3.connect('backup.db') as backup_conn:
    conn.backup(backup_conn)

6. Row Factory for Dict Output

Get results as dictionaries instead of tuples:

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(dict(row))

7. Error Handling

Use try-except for safe operations:

try:
    cursor.execute("SELECT * FROM users")
except sqlite3.Error as e:
    print("Error:", e)

8. Prepared Statements

Prevent SQL injection:

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Charlie', 35))

9. Performance Tips

Use executemany() for bulk inserts:

data = [('Tom', 22), ('Jerry', 24)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)

Use PRAGMA cache_size and PRAGMA synchronous for tuning.

✅ Summary Table

Concept	Command Example
Connect DB	`sqlite3.connect('file.db')`
Create Table	`CREATE TABLE ...`
Insert Data	`INSERT INTO table VALUES (?, ?)`
Fetch Data	`SELECT * FROM table`
Update Data	`UPDATE table SET column=? WHERE condition`
Delete Data	`DELETE FROM table WHERE condition`
Index	`CREATE INDEX idx_name ON table(column)`
Join	`SELECT ... JOIN ...`
Backup	`conn.backup(backup_conn)`

🔥 Pro Tip: Use SQLite for small apps, testing, or learning SQL. For large-scale apps, consider PostgreSQL or MySQL.

✅ Next Step:

Would you like me to bundle this article with the diagram and all Python scripts into a ZIP file for easy publishing, or create a combined infographic summarizing SQLite basics + advanced topics for Dev.to?

📦 Install SQLite3

SQLite comes built-in with Python, so no extra installation needed!

Just import:

import sqlite3

✅ How SQLite Works

https://your-image-host/sqlite-workflow.png

Explanation of Diagram:

Python Application → interacts with SQLite Methods via the Python DB API.
SQLite Methods → execute queries on the Database and return Results.
Flow includes:
- Query Execution
- Results retrieval
- SQLite methods for communication.

✅ What is `'my_database.db'`?

It’s the name of the SQLite database file.
When you run:

  conn = sqlite3.connect('my_database.db')

If the file exists, Python connects to it.
If it doesn’t exist, Python creates a new database file in your current directory.
- You can use any name (e.g., data.sqlite, students.db).

✅ Basic CRUD Operations

Connect to Database

conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

Create Table

cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')
conn.commit()

Insert Data

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Bob', 30))
conn.commit()

Fetch Data

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

✅ Advanced Topics (Expanded)

1. Transactions

Transactions ensure atomicity (all-or-nothing execution).
Use BEGIN, COMMIT, and ROLLBACK:

conn.execute('BEGIN')
cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
conn.commit()  # or conn.rollback() if something fails

Why? Prevents partial updates when errors occur.

2. Indexes for Speed

Indexes make queries faster by reducing search time.

cursor.execute("CREATE INDEX idx_name ON users(name)")

Tip: Use indexes on columns frequently used in WHERE clauses.

3. JOIN Queries

Combine data from multiple tables:

cursor.execute('''
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
''')

Types of JOIN:

INNER JOIN → Matches in both tables
LEFT JOIN → All from left + matches from right
CROSS JOIN → Cartesian product

4. Foreign Keys

Enable relationships between tables:

cursor.execute("PRAGMA foreign_keys = ON")

Example:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY(user_id) REFERENCES users(id)
)

5. Backup Database

Create a backup easily:

with sqlite3.connect('backup.db') as backup_conn:
    conn.backup(backup_conn)

6. Row Factory for Dict Output

Get results as dictionaries instead of tuples:

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(dict(row))

7. Error Handling

Use try-except for safe operations:

try:
    cursor.execute("SELECT * FROM users")
except sqlite3.Error as e:
    print("Error:", e)

8. Prepared Statements

Prevent SQL injection:

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Charlie', 35))

9. Performance Tips

Use executemany() for bulk inserts:

data = [('Tom', 22), ('Jerry', 24)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", data)

Use PRAGMA cache_size and PRAGMA synchronous for tuning.

✅ Summary Table

Concept	Command Example
Connect DB	`sqlite3.connect('file.db')`
Create Table	`CREATE TABLE ...`
Insert Data	`INSERT INTO table VALUES (?, ?)`
Fetch Data	`SELECT * FROM table`
Update Data	`UPDATE table SET column=? WHERE condition`
Delete Data	`DELETE FROM table WHERE condition`
Index	`CREATE INDEX idx_name ON table(column)`
Join	`SELECT ... JOIN ...`
Backup	`conn.backup(backup_conn)`

🔥 Pro Tip: Use SQLite for small apps, testing, or learning SQL. For large-scale apps, consider PostgreSQL or MySQL.

This content originally appeared on DEV Community and was authored by likhitha manikonda

Print Share Comment Cite Upload Translate Updates

APA

likhitha manikonda | Sciencx (2025-10-25T12:43:01+00:00) 🗄️ SQLite3 in Python. Retrieved from https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/

MLA

" » 🗄️ SQLite3 in Python." likhitha manikonda | Sciencx - Saturday October 25, 2025, https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/

HARVARD

likhitha manikonda | Sciencx Saturday October 25, 2025 » 🗄️ SQLite3 in Python., viewed ,<https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/>

VANCOUVER

likhitha manikonda | Sciencx - » 🗄️ SQLite3 in Python. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/

CHICAGO

" » 🗄️ SQLite3 in Python." likhitha manikonda | Sciencx - Accessed . https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/

IEEE

" » 🗄️ SQLite3 in Python." likhitha manikonda | Sciencx [Online]. Available: https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/. [Accessed: ]

rf:citation

» 🗄️ SQLite3 in Python | likhitha manikonda | Sciencx | https://www.scien.cx/2025/10/25/%f0%9f%97%84%ef%b8%8f-sqlite3-in-python/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

✅ What is SQLite3?

📦 Install SQLite3

✅ How SQLite Works

✅ Python Application

✅ Python DB API

✅ SQLite Methods

✅ How It Works

✅ Visual Flow:

✅ What is 'my_database.db'?

✅ Basic Operations (CRUD OPERATIONS)

Connect to Database

Create Table

Insert Data

Fetch Data

Delete Data

✅ Advanced Topics (Expanded)

1. Transactions

2. Indexes for Speed

3. JOIN Queries

4. Foreign Keys

5. Backup Database

6. Row Factory for Dict Output

7. Error Handling

8. Prepared Statements

9. Performance Tips

✅ Summary Table

Would you like me to bundle this article with the diagram and all Python scripts into a ZIP file for easy publishing, or create a combined infographic summarizing SQLite basics + advanced topics for Dev.to?

📦 Install SQLite3

✅ How SQLite Works

✅ What is 'my_database.db'?

✅ Basic Operations

Connect to Database

Create Table

Insert Data

Fetch Data

✅ Advanced Topics (Expanded)

1. Transactions

2. Indexes for Speed

3. JOIN Queries

4. Foreign Keys

5. Backup Database

6. Row Factory for Dict Output

7. Error Handling

8. Prepared Statements

9. Performance Tips

✅ Summary Table

Would you like me to bundle this article with the diagram and all Python scripts into a ZIP file for easy publishing, or create a combined infographic summarizing SQLite basics + advanced topics for Dev.to?

📦 Install SQLite3

✅ How SQLite Works

✅ What is 'my_database.db'?

✅ Basic CRUD Operations

Connect to Database

Create Table

Insert Data

Fetch Data

✅ Advanced Topics (Expanded)

1. Transactions

2. Indexes for Speed

3. JOIN Queries

4. Foreign Keys

5. Backup Database

6. Row Factory for Dict Output

7. Error Handling

8. Prepared Statements

9. Performance Tips

✅ Summary Table

Related Posts

✅ What is `'my_database.db'`?

✅ What is `'my_database.db'`?

✅ What is `'my_database.db'`?