Python Database Guide: SQLite & SQLAlchemy

📌 Introduction

Databases are the foundation of almost all applications, including automation and analytics tools, mobile apps, and web apps. Two strong technologies in the Python world facilitate database interaction for developers:

• SQLite is a file-based database engine that is lightweight.
• An advanced Python SQL toolkit and object relational mapper (ORM) is called SQLAlchemy.

🧩 What is SQLite?

SQLite is a serverless, self-contained SQL database engine that requires no setup. It is perfect for local development environments or small to medium-sized applications.

✅ Why Use SQLite?

• Data is stored in a single.db file, therefore no server is required.
• includes Python (Sqlite3) built in.
• Excellent for rapid testing or prototypes

🛠 Getting Started with SQLite in Python

The sqlite3 module is pre-installed in Python. To utilize it, follow these steps:

1. Create a Database and Connect

import sqlite3

conn = sqlite3.connect('my_database.db')  # Creates or connects to DB
cursor = conn.cursor()

2. Create a Table

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

3. Insert Data

cursor.execute('''
    INSERT INTO users (name, email)
    VALUES (?, ?)
''', ("Alice", "alice@example.com"))
conn.commit()

4. Query Data

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

5. Close Connection

conn.close()

✅ Quick Tip: Always use parameterized queries (?) to prevent SQL injection.

⚙️ Enter SQLAlchemy – ORM Made Easy

Although SQLite3 is excellent, SQLAlchemy goes above and beyond by providing an Object Relational Mapping (ORM) method and abstracting SQL syntax.

🧠 What is SQLAlchemy?

Instead of utilizing raw SQL queries, SQLAlchemy allows you to work with databases using Python classes and objects. With a common syntax, it supports a variety of databases, including PostgreSQL, MySQL, SQLite, and others.

🔧 Installing SQLAlchemy

pip install SQLAlchemy

🚀 Getting Started with SQLAlchemy (Using SQLite)

1. Setup Engine and Base

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///my_database.db', echo=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

2. Define Models (Tables)

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String, unique=True)

3. Create Tables

Base.metadata.create_all(engine)

4. Insert Records

new_user = User(name="Bob", email="bob@example.com")
session.add(new_user)
session.commit()

5. Query Records

users = session.query(User).all()
for user in users:
    print(user.id, user.name, user.email)

🆚 SQLite vs SQLAlchemy – Which One to Use?

Feature	sqlite3 (Raw SQL)	SQLAlchemy (ORM)
Learning Curve	Simple	Moderate
SQL Required?	Yes	Minimal
Abstraction	None	High
Code Readability	Lower	Cleaner
Portability	Good	Excellent
Recommended for	Quick tests, beginners	Scalable apps, larger projects

📚 Real-World Use Case Example

Imagine you’re building a user registration app:

• With sqlite3, you’d manually write SQL queries for every operation.
• With SQLAlchemy, you define your schema once and perform CRUD using Python objects. It improves readability, security, and maintainability.

🧪 Bonus: Combining SQLite + SQLAlchemy in a Flask App

# In Flask, using SQLAlchemy with SQLite
from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
db = SQLAlchemy(app)

This simple setup enables full-stack development with Python, Flask, SQLite, and SQLAlchemy.

🎯 Final Thoughts

If you’re starting out or working on a small project, SQLite is perfect. But as your application grows, SQLAlchemy becomes a must-have tool to manage your database cleanly and efficiently.

You can also read for :-

What is Web Technology? A Complete Guide for Beginners in 2025

What is DBMS? A Beginner’s Guide to Database Management Systems in 2025

Introduction to Serverless Databases: Firebase, AWS DynamoDB, & More

When choosing between SQLite and SQLAlchemy, consider the size and complexity of your project. For small to medium-sized applications, SQLite is a great choice due to its simplicity and ease of use. However, for larger projects, SQLAlchemy’s Object Relational Mapping (ORM) capabilities provide a more scalable and maintainable solution. By using SQLAlchemy, you can abstract away the underlying SQL syntax and focus on working with Python objects, making your code more readable and efficient.

In addition to its ORM capabilities, SQLAlchemy also provides a high level of abstraction, making it easier to switch between different database engines such as PostgreSQL, MySQL, and SQLite. This flexibility is particularly useful when working on projects that require database migration or when you need to support multiple database engines. With SQLAlchemy, you can write database-agnostic code and focus on the logic of your application, rather than worrying about the underlying database syntax.