What is SQLite?

SQLite is a lightweight, serverless, self-contained relational database engine. It stores the entire database in a single file, making it easy to use for embedded applications, small projects, and testing. Unlike MySQL or PostgreSQL, it does not require a separate server process.

SQLite is useful when we need a simple, reliable database without the overhead of setting up and maintaining a database server. Common use cases include desktop applications, small tools, mobile apps, prototyping, and testing.

Syntax

If you already know MySQL, SQLite will feel familiar (most commands work similarly). Key differences include:

• No strict data types: SQLite uses dynamic typing, which means we can store any type of data in any column, though we can declare types for clarity.
• Auto-increment columns use INTEGER PRIMARY KEY AUTOINCREMENT.
• No user management or privileges - everything is handled by file permissions.
• Limited ALTER TABLE support: adding columns is easy, but dropping or renaming them is restricted.

Python implementation

SQLite comes built into Python as the sqlite3 module, so no additional installation is required.

import sqlite3

conn = sqlite3.connect("dbtest.db") # connecting to the database (creating the file if it doesn't exist)
cursor = conn.cursor() # creating a cursor object to execute SQL commands

# Creating a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")

# Inserting data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 25))

conn.commit() # committing changes

# Querying data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)
    
# Closing connections
cursor.close()
conn.close()
                                    

We can also interact with SQLite directly from the terminal without writing Python code. SQLite provides a command-line shell that allows us to open a database file, execute SQL statements, create tables, insert data, and run queries in real time. To start, open the terminal and run sqlite3 dbtest.db (creating the file if it doesn’t exist). Once inside the shell, we can type SQL commands directly. The shell also supports helpful commands like .tables to list tables and .schema to see table structures.

What is an ORM?

An ORM (Object-Relational Mapping) is a programming technique that allows us to interact with a database using objects and classes, rather than writing raw SQL queries. In simple terms, an ORM maps database tables to classes and table rows to objects. Instead of manually writing SELECT, INSERT, or UPDATE statements, we interact with the database using normal Python code. The ORM automatically translates our Python instructions into SQL.

For example, instead of writing SQL like this:

SELECT * FROM users WHERE age > 25;
                                    

We can write Python code like this:

users = session.query(User).filter(User.age > 25).all()
                                    

Why use an ORM?

• Cleaner and more readable code - no large SQL strings scattered across the project.
• Less error-prone - ORMs help prevent SQL injection attacks.
• Database independence - switching from SQLite to MySQL or PostgreSQL often requires minimal changes.
• Easier maintenance - changes to the database structure can be handled through models.

Popular Python ORMs

• SQLAlchemy - the most powerful and flexible ORM for Python.
• Django ORM - built into the Django framework.
• Peewee - lightweight and beginner-friendly.

SQLAlchemy + SQLite example

Below is a simple example showing how to use SQLite with SQLAlchemy.

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, declarative_base

engine = create_engine("sqlite:///dbtest.db")
Base = declarative_base()

# Defining a table as a class
class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key = True)
    name = Column(String)
    age = Column(Integer)

Base.metadata.create_all(engine)

Session = sessionmaker(bind = engine)
session = Session()

# Inserting data using objects
session.add(User(name = "Alice", age = 30))
session.add(User(name = "Bob", age = 25))
session.commit()

# Querying data
users = session.query(User).all()
for user in users:
    print(user.name, user.age)
                                    

SQLite VS ORM - when to use what?

• Use raw SQLite when learning SQL, writing small scripts, or when full control over queries is needed.
• Use an ORM for larger applications, long-term projects, and when working with complex data models.