Create a Library Database
using SQLAlchemy

Tech Stack

• Python 3, Pandas

• SQLAlchemy

• Marimo notebook

Project

• Creation of an SQLite3 simple Library database leveraging SQLAlchemy Core approach.

• Execution of various SQL queries, demonstrating practical professional usage.

Benefits

Understanding and leveraging the Core approach of SQLAlchemy.

Goals

Deepening my SQL skills

I spent 18 hours divided this way and in this order:

• 10 hrs: completing the 15 tasks of the exercice

• 8 hrs: refactoring code into functions and cleaning up

Structured Query Language (SQL) is a fundamental tool for working with databases, but Python developers often need an efficient and Pythonic way to interact with databases. SQLAlchemy Core provides a powerful toolkit that allows developers to write database-agnostic, declarative SQL queries in Python.

This blog post is based on a notebook that presents 15 hands-on SQL tasks using SQLAlchemy Core to create, query, and manipulate a library database. This exercise will help you become comfortable with SQLAlchemy's Core API and appreciate its flexibility when working with databases in Python.

Below is the full notebook where you can follow along with the exercises: click here to scroll down to it.

If you want to first know the basics of SQLALchemy, go to this article.

What is SQLAlchemy Core?

SQLAlchemy is a Python SQL toolkit that provides an Object Relational Mapper (ORM) and a Core approach for database interaction. The Core approach is a declarative SQL expression language, which enables Python developers to construct SQL statements using Pythonic syntax without relying on an ORM.

Instead of defining Python classes for database tables (as in ORM), SQLAlchemy Core represents tables, queries, and database transactions explicitly using SQL constructs. This makes it an excellent choice for scenarios where direct control over SQL statements is required, such as:

• Database migrations

• Data analytics workflows

• High-performance SQL query tuning

Why is SQLAlchemy Core Important?

The Core approach is beneficial when developers need:

• Fine-grained control over SQL statements

• Optimized database transactions without ORM overhead

• Compatibility with multiple databases (PostgreSQL, MySQL, SQLite, etc.)

• Explicitly defined table structures and relationships

For projects that involve raw SQL writing but need the flexibility of Python, SQLAlchemy Core provides a robust and scalable solution.

Advantages of SQLAlchemy Core for Python Developers

1. Better Handling of JOINs Using Foreign Keys

One of the key benefits of using SQLAlchemy Core is its ability to automatically manage foreign key relationships. Unlike writing raw SQL, where you need to explicitly declare ON clauses in JOINs, SQLAlchemy Core simplifies this process by recognizing foreign key constraints internally. This leads to cleaner and more maintainable queries.

For example, to join Books with Authors, instead of writing:

>>> SELECT Books.title, Authors.lastName || ' ' || Authors.firstName AS authorName
>>> FROM Books LEFT OUTER JOIN Authors ON Books.author_id = Authors.id;

We can write the equivalent SQLAlchemy Core query like this:

>>> stmt = select(
>>> books.c.title.label("bookTitle"), func.coalesce(authors.c.lastName + literal(" ") + authors.c.firstName, literal("Unknown")) ).outerjoin(authors)

2. Efficient Query Construction with join_from() and outerjoin()

join_from() allows you to specify a natural join order, making multi-table queries more readable.

• outerjoin() ensures that even if related records don’t exist, data from the main table is still returned, making LEFT OUTER JOINs more intuitive.
  

For example, retrieving all books along with their borrowers, even if they haven't been borrowed:

>>> stmt =
>>> (select(borrowers.c.lastName, books.c.title)
>>> .outerjoin(borrowings, books.c.id == borrowings.c.book_id)
>>> .outerjoin(borrowers, borrowings.c.borrower_id == borrowers.c.id) )

This eliminates the need for complex ON conditions and simplifies query composition significantly.

3. Simplified SQL Queries with SQLAlchemy Core Syntax

Raw SQL requires explicit ON, FROM, and JOIN conditions, making complex queries difficult to read. SQLAlchemy Core, however, abstracts much of this complexity, reducing redundancy.

Example of counting books per genre:

>>> stmt = (
>>> select(genres.c.genreName, func.count(books.c.id).label("# Books/Genre"))
>>> .outerjoin(books, genres.c.id == books.c.genre_id) .group_by(genres.c.genreName)
>>> )

With raw SQL, the same logic requires more explicit syntax:

>>> SELECT Genres.genreName, COUNT(Books.id) AS "# Books/Genre"
>>> FROM Genres LEFT OUTER JOIN Books ON Genres.id = Books.genre_id
>>> GROUP BY Genres.genreName;

By removing the need for explicit table references, SQLAlchemy Core keeps the code cleaner while preserving the power of SQL.

To navigate better throughout this long notebook, you can access the outline of this notebook by hovering over the right-side scroll bar (the stack of small horizontal lines).

Conclusion: Introducing SQLAlchemy ORM

While SQLAlchemy Core is powerful and flexible for raw SQL workflows, some applications require object-oriented database interactions. This is where SQLAlchemy ORM (Object Relational Mapper) comes into play.

SQLAlchemy ORM allows you to define Python classes as database tables, enabling Python developers to work with databases using objects instead of raw SQL statements. This is particularly useful for web applications and enterprise software where business logic needs to be abstracted from database logic.

Explore SQLAlchemy to unlock a smarter way to manage your data workflows!

When to Use SQLAlchemy Core vs. ORM?

Use SQLAlchemy Core If:

• You need fine-grained control over SQL queries

• You're working with high-performance database queries

• You need to optimize SQL statements manually

Use SQLAlchemy ORM If:

• You want to use Python classes to model data

• You prefer automatic query generation

• You want to integrate with web frameworks like Flask or Django

In an upcoming blog post, we’ll explore how SQLAlchemy ORM differs from the Core approach and when it makes sense to use each.

You may be interested in this article where I introduce SQLAlchemy and explore its basics.

For now, if you're looking to master SQLAlchemy Core, try running the 15-task exercise linked in this notebook.