Python library centre

Comprehensive Introduction to SQLAlchemy for Effective Python Database Management

SQLAlchemy is a popular Python library for working with databases. It allows you to interact with databases using Python code. SQLAlchemy supports many…

⬇ Download notebook Open in Colab ↗

CoursePython library centre
Video20 min
FormatJupyter notebook · 19 code cells

SQLAlchemy

What you'll learn

SQLAlchemy Core Concepts
Understanding Tables in SQLAlchemy
Inserting Data with SQLAlchemy Core
Selecting Data with SQLAlchemy Core
beginner example: Filtering Data
beginner example: Updating Data
beginner example: Deleting Data
Intermediate Example: Autoincrement primary key and fetching one row

Data

No separate download needed — the notebook creates or downloads everything it uses.

📓 Full notebook

Download .ipynb

Introduction to SQLAlchemy#

SQLAlchemy is a popular Python library for working with databases.
It allows you to interact with databases using Python code.
SQLAlchemy supports many types of databases, such as SQLite, MySQL, PostgreSQL, and more.
It can make database code easier, safer, and cleaner.
Real-world uses include web applications, data analysis, and automation.
In this lesson, you will learn the basics of using SQLAlchemy.

import warnings; warnings.filterwarnings("ignore")
import sys
# For Windows, install SQLAlchemy using pip if needed
# pip install SQLAlchemy
import sqlalchemy

SQLAlchemy Core Concepts#

SQLAlchemy uses engines to connect to databases.
An engine knows how to talk to your database.
SQLAlchemy can build tables and works with table objects.
It uses metadata, which stores information about tables.
You can write SQL queries with Python objects.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
print(type(engine))

<class 'sqlalchemy.engine.base.Engine'>

Understanding Tables in SQLAlchemy#

Tables define the structure of your data.
SQLAlchemy lets you define tables using Python code.
Columns describe what goes in each row.

from sqlalchemy import MetaData, Table, Column, Integer, String
metadata = MetaData()
users = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String),
              Column('age', Integer)
)
metadata.create_all(engine)
print(users)

users

Inserting Data with SQLAlchemy Core#

You can add rows of data to your tables using the insert() method.
The table object has an insert() method for this purpose.

with engine.connect() as conn:
    insert_stmt = users.insert().values(name='Alice', age=30)
    result = conn.execute(insert_stmt)
    print('Inserted user:', result.inserted_primary_key)

Inserted user: (1,)

with engine.connect() as conn:
    many_users = [{'name': 'Bob', 'age': 24}, {'name': 'Carol', 'age': 29}]
    result = conn.execute(users.insert(), many_users)
    print('Inserted multiple users.')

Inserted multiple users.

Selecting Data with SQLAlchemy Core#

You can use select() to fetch rows from a table.
You can loop through the results.

from sqlalchemy import select
with engine.connect() as conn:
    stmt = select(users)
    results = conn.execute(stmt)
    for row in results:
        print(row)

beginner example: Filtering Data#

You can use where() to add filters to your queries.
For example, select only users older than 25.

with engine.connect() as conn:
    stmt = select(users).where(users.c.age > 25)
    results = conn.execute(stmt)
    for row in results:
        print(row)

beginner example: Updating Data#

You can change information in your table with update().
For example, update Bob's age to 25.

from sqlalchemy import update
with engine.connect() as conn:
    stmt = update(users).where(users.c.name == 'Bob').values(age=25)
    result = conn.execute(stmt)
    print('Updated rows:', result.rowcount)

Updated rows: 0

beginner example: Deleting Data#

You can remove rows from your table with delete().
For example, remove users named Carol.

from sqlalchemy import delete
with engine.connect() as conn:
    stmt = delete(users).where(users.c.name == 'Carol')
    result = conn.execute(stmt)
    print('Deleted rows:', result.rowcount)

Deleted rows: 0

Intermediate Example: Autoincrement primary key and fetching one row#

Each new user gets a unique id automatically.
You can also fetch just one row from a select result.

with engine.connect() as conn:
    stmt = select(users).where(users.c.name == 'Alice')
    result = conn.execute(stmt).fetchone()
    print('Single row:', result)

Single row: None

Intermediate Example: Using text-based SQL#

Sometimes you may want to write SQL directly.
SQLAlchemy can run raw SQL text if you need it.

from sqlalchemy import text
with engine.connect() as conn:
    result = conn.execute(text('SELECT name, age FROM users'))
    for row in result:
        print('Name:', row.name, 'Age:', row.age)

Intermediate Example: Transactions#

SQLAlchemy can use transactions to make changes safer.
Transactions let you commit or rollback changes.

with engine.begin() as conn:
    conn.execute(users.insert().values(name='Dave', age=40))
    print('Transaction committed for Dave.')

Transaction committed for Dave.

Advanced Example: SQLAlchemy ORM Basics#

SQLAlchemy also has an ORM (Object Relational Mapper).
ORM lets you use Python classes instead of tables.
You define classes and use them like regular Python objects.

from sqlalchemy.orm import declarative_base, Session
Base = declarative_base()
class User(Base):
    __tablename__ = 'orm_users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
 
Base.metadata.create_all(engine)
session = Session(engine)
user1 = User(name='Eve', age=28)
session.add(user1)
session.commit()
print('Inserted user:', user1.name)

Inserted user: Eve

Advanced Example: Querying with the ORM#

You can use the session to query ORM classes.
Queries are simple and use Python expressions.

results = session.query(User).filter(User.age > 25).all()
for user in results:
    print(user.id, user.name, user.age)

1 Eve 28

Error Handling with SQLAlchemy#

You may make mistakes, like trying to insert duplicate primary keys.
SQLAlchemy will raise exceptions for errors.
You can handle errors using try and except.

from sqlalchemy.exc import IntegrityError
try:
    session.add(User(id=1, name='Frank', age=35))
    session.commit()
except IntegrityError as e:
    print('Integrity error:', e)
    session.rollback()

Integrity error: (sqlite3.IntegrityError) UNIQUE constraint failed: orm_users.id
[SQL: INSERT INTO orm_users (id, name, age) VALUES (?, ?, ?)]
[parameters: (1, 'Frank', 35)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Best Practices with SQLAlchemy#

Always use transactions to keep your data safe.
Use sessions for working with the ORM.
Close connections when you are done.
Handle exceptions to avoid data loss.

def safe_add_user(session, name, age):
    try:
        user = User(name=name, age=age)
        session.add(user)
        session.commit()
        print('Added user:', name)
    except Exception as e:
        print('Error adding user:', e)
        session.rollback()
 
safe_add_user(session, 'Grace', 23)

Added user: Grace

Mini-Project: Simple Address Book#

Now, let us create a table for contacts.
We will add, fetch, and list contacts using SQLAlchemy.

class Contact(Base):
    __tablename__ = 'contacts'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)
    phone = Column(String)
 
Base.metadata.create_all(engine)
contact_session = Session(engine)
 
contact1 = Contact(name='Henry', email='henry@example.com', phone='1234567890')
contact2 = Contact(name='Irene', email='irene@example.com', phone='9876543210')
contact_session.add_all([contact1, contact2])
contact_session.commit()
print('Added two contacts.')

Added two contacts.

results = contact_session.query(Contact).all()
for contact in results:
    print(contact.id, contact.name, contact.email, contact.phone)

1 Henry henry@example.com 1234567890
2 Irene irene@example.com 9876543210

found = contact_session.query(Contact).filter(Contact.name=='Henry').first()
if found:
    print('Found contact:', found.name, found.email)

Found contact: Henry henry@example.com

YouTube Next Steps#

Please like and subscribe for more Python tutorials!
Watch our next SQLAlchemy video for advanced projects.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help