Intro to SQLAlchemy

Why I'm giving this talk

• Have worked with SQLAlchemy for several years
• Never really dove deep into different parts
• Excellent chance to educate myself and help others at the same time.

If you have questions, please interrupt or put them in the chat.

whois:Bauke Brenninkmeijer

• MSc in CS and Data Science @Nijmegen

• Data Scientist @ABNAMRO since 2019

  • 1.5 years in D&MA/CADM
  • 2 years in Global Markets

• @baukebrenninkmeijer

Key differences

PyODBC vs. SQLAlchemy

• SQLAlchemy provides higher level of abstraction and expressiveness. More readable and maintainable code.
• SQLAlchemy supports more advanced features such as:
  • Connection pooling
  • Migrations
  • Schema reflection
• SQLAlchemy allows different styles of querying, such as declarative, classical or hybrid.

Database support

• SQLAlchemy supports multiple dialects and backends, including
  • MySQL
  • PostgreSQL
  • SQLite
  • All ODBC enabled databases
• PyODBC supports any database with DB API 2.0, which include:
  • SQL Sever
  • Access
  • Excel
  • Oracle

What is an ORM?

• Object Relational Mapping
• Method to align code and database structures
• Facilitates interactions with databases in code, rather than raw SQL.
• Generally done using classes or other types of attribute-wise data capture

What does that mean?

• SQLAlchemy has ORM features that allows defining of classes that represent database tables, manipulating data using python syntax.
• PyODBC does not have any ORM features. You can use other ORMs on top of PyODBC, such as Django, PeeWee, and SQLAlchemy.

4 APIs

SQLAlchemy actually has 2/4 different APIs

• SQLAlchemy ORM 2.0 style
• SQLAlchemy ORM 1.x style
• SQLAlchemy Core 2.0 style
• SQLAlchemy Core 1.x style

SQLAlchemy APIs

• ORM provides high level interface, mapping python classes to database tables
• Core provides a low-level interface for executing SQL statements and manipulating metdata. More similar to PyODBC.

What it is not

• SQLAlchemy is not an analytics interface.
  • Large aggregations and complex groupbys are not supported.
• It is not a web framework data layer, such as Django.
  • Can be used as such with flask.
  • But can also be used with an API or data libraries such as pandas.

Query Construction

• SQLAlchemy
  • Supports constructing SQL queries using Python expressions and operators.
  • Textual SQL is also supported.
• PyODBC
  • requires raw SQL statements as string, which are passed to the cursor object.
  • Placeholders and parameters are needed for dynamic usage and avoiding SQL injection attacks.

Creating tables

# SQLAlchemy
# imports...
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

# Define a class that represents the users table
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

Base.metadata.create_all(engine) # create the table in the database

Creating tables

# PyODBC
import pyodbc

conn = pyodbc.connect('DRIVER={SQLite3};DATABASE=test.db')
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    )
''')
conn.commit()
conn.close()

Inserting data

Python typehints

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

# Insert some data into the table using the User class
session.add_all([
    User(name='Alice', age=25),
    User(name='Bob', age=30),
    User(name='Charlie', age=35)
])
session.commit()

Inserting data

Just raw text. Hard to programmatically extend reliable. No SQL injection prevention.

# PyODBC
cursor.execute('''
    INSERT INTO users (name, age) VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 35)
''')

Retrieving data

from sqlalchemy import select
session.scalars(
    select(User)
).all()

[User(id=1, name=Alice, age=25),
 User(id=2, name=Bob, age=30),
 User(id=3, name=Charlie, age=35)]

Retrieving data

# PyODBC
cursor.execute('SELECT * FROM users')

# Fetch and print the rows from the query result
rows = cursor.fetchall()
for row in rows:
    print(row)

(1, 'Alice', 25)
(2, 'Bob', 30)
(3, 'Charlie', 35)

More advanced

with table User and Address

result = session.execute(
    select(User.name, Address.email_address)
    .join(User.addresses)
    .order_by(User.id, Address.id)
)

WHERE

session.scalars(select(User).where(User.age > 28)).all()

[User(id=2, name=Bob, age=30),
 User(id=3, name=Charlie, age=35)]

Granular inserts

new_user = User(name='dennis', age=58)

session.add(
    new_user
)
session.commit()

Granular updates

from sqlalchemy import update
stmt = (
    update(User)
    .where(User.name == "Alice")
    .values(name="Alice the Third von Baumgarten")
)
session.execute(stmt)
session.commit()

Pythonic updates

user = session.query(User).filter_by(id=1).first()
user.name = "Bob"
session.commit()

Delete

from sqlalchemy import delete
stmt = delete(User).where(User.name.in_(["Bob"]))
session.execute(stmt)

In conclusion

• Both frameworks have their time and place.
• SQLAlchemy generally more feature rich and better maintainable.
• In more complex and larger projects, often SQLAlchemy should be preferred.

Something extra

Looking at an API?

• SQLAlchemy is great for API usage
• Have a look at SQLModel, which combines SQLAlchemy and pydantic for APIs.
• Also integrates neatly with FastAPI (Same author).

Want to get started?

1. Blue book of coding - SQLAlchemy
2. SQLAlchemy docs data tutorial