CodingNic
Sign In Book Session

Databases

SQLAlchemy

Databases 50 min read

SQLAlchemy

SQLAlchemy

After learning what an ORM is, the next step is using one of the most popular Python database tools: SQLAlchemy.

SQLAlchemy is widely used in professional Python projects.

It can work with SQLite, PostgreSQL, MySQL, and more.

What Is SQLAlchemy?

SQLAlchemy is a Python library for working with databases.

It provides:

  • ORM features
  • SQL tools
  • database connections
  • flexible query building

You can use it with multiple database systems.

Install:

bash
pip install sqlalchemy

Why SQLAlchemy Matters

It helps you:

  • write cleaner database code
  • use Python classes as tables
  • avoid repeating raw SQL
  • switch databases more easily
  • build real applications

Basic ORM Workflow

You usually:

  1. Create engine
  2. Define model class
  3. Create tables
  4. Add data
  5. Query data
  6. Update data
  7. Delete data

Step 1: Import Tools

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

Step 2: Create Database Engine

Using SQLite:

python
engine = create_engine("sqlite:///school.db")

This creates or connects to:

text
school.db

Step 3: Create Base Class

python
Base = declarative_base()

Models will inherit from this base.

Step 4: Define a Model

python
class Student(Base):
    __tablename__ = "students"

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

This class maps to a database table.

Step 5: Create Tables

python
Base.metadata.create_all(engine)

Creates tables if they do not exist.

Step 6: Create Session

A session is used to talk to the database.

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

CREATE (Insert Data)

python
student = Student(name="Maya", age=22)

session.add(student)
session.commit()

READ (Query Data)

Get all rows:

python
students = session.query(Student).all()

for student in students:
    print(student.name, student.age)

Filter One Row

python
student = session.query(Student).filter_by(name="Maya").first()
print(student.age)

UPDATE Data

python
student.age = 23
session.commit()

DELETE Data

python
session.delete(student)
session.commit()

Full Example

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

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

class Student(Base):
    __tablename__ = "students"

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

Base.metadata.create_all(engine)

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

student = Student(name="Tom", age=25)
session.add(student)
session.commit()

for s in session.query(Student).all():
    print(s.name, s.age)

Why Session Matters

The session tracks changes and sends them to the database when you commit.

Common Beginner Errors

Forgetting commit()

Changes may not save.

Wrong Database URL

Check:

text
sqlite:///school.db

Missing Package

Install SQLAlchemy first.

Using Wrong Column Types

Choose types carefully.

Code Along

Build:

text
library.db

Create model:

text
Book(id, title, year)

Insert two books and print them.

Mini Challenge

Build a product app.

Tasks:

  1. Create SQLite database

  2. Create model Product

    • id
    • name
    • price

  3. Insert:

  • Pen, 2
  • Bag, 20
  1. Print all products

Real World Use Case

SQLAlchemy is used in APIs, dashboards, admin systems, SaaS tools, and business applications.

Quiz

  1. What is SQLAlchemy?
  2. What does create_engine() do?
  3. What is a session?
  4. Why call commit()?
  5. What does a model class represent?

Assignment

Create your own SQLAlchemy model with 4 columns and perform CRUD operations.

Summary

You learned how SQLAlchemy connects Python classes to databases and performs CRUD operations using ORM style.