PyConDE & PyData Berlin 2024

OUTLINE
- Introduction [15 min]
- What is SQLAlchemy?
- Why use SQLAlchemy and advantages?
- Components Overview such as engine, dialect, connection pool, etc.
- Initial setup for the hands-on workshop with GitHub Codespaces [5 min]
- Run and explore example service that has database queries with raw SQL
- Adding SQLAlchemy to the example service
- Set up SQLAlchemy [10 min]
- Set up engine & dialect to connect with the DB
- Use SQLAlchemy Core to query the DB
- Add ORMs [20 min]
- What are ORMs?
- How to represent a basic table?
- Modeling different relationships (e.g., 1-1 and 1-many) between the classes
- Using ORMs to query the DB
- Convert other queries using SQLAlchemy [5 min]
- Improve performance by changing relationship loading techniques [10 min]
- Consequences of certain models: Talk about N+1 problem and bidirectional relationships
- Work with different loading techniques, such as lazy loading and eager loading
- The SQLAlchemy.asyncio extension
- Brief description of asyncio [10 min]
- Understanding coroutines
- Scheduling tasks on the asyncio event loop
- A hands-on walkthrough of SQLAlchemy’s asyncio extension [15 min]
- Setting up SQLAlchemy in async mode
- Performing a query and inserting it into the database
- Using ORMs in queries using asyncio

FORMAT
This is an interactive tutorial where we will guide participants through the use of SQLAlchemy and ORMs to interact with a database. Participants will gain an understanding of SQLAlchemy and be well-versed enough to use it in their next project.
Participants will be working on a repository via GitHub Codespaces, and they will be building on that throughout the tutorial. The Codespaces dev environment will include all required modules and a Dockerized PostgreSQL database, enabling a seamless setup. The repository will have a branch corresponding to each section of the workshop, so participants who have trouble with a step or aren’t able to finish on time can check out the corresponding branch and follow the rest of the workshop from there.
We’ll start with an introduction to SQLAlchemy and its advantages. The rest of the tutorial will be hands-on. For each section, we will start by explaining the concept, then allowing participants to complete the relevant steps on the example service on their own laptops, and ask questions. We expect this to last around 10 minutes per concept. We will then give participants time to complete the steps on their own laptops and ask questions.

AUDIENCE
This tutorial is for Python developers of any level who write applications that interact with databases and want to learn how to leverage a tool like SQLAlchemy to seamlessly interact with their database and manage their data in a Pythonic way.
Having a basic understanding of databases and SQL (such as inserting or reading data from a table) is sufficient. Participants should also be familiar with git and have a GitHub account, as we would use GitHub Codespaces to enable easy set-up for Python and the database. However, they do not need any prior knowledge of SQLAlchemy or ORMs, since we will explain that first. For the last part of the tutorial, it would help if attendees have some familiarity with coroutines or asynchronous programming, but it is not required, since we will be explaining these fundamental concepts first.
Participants will walk out of this tutorial having learned how to:
- Use SQLAlchemy for database operations in Python, enhancing the readability and maintainability of the code
- Build Python classes (ORMs) that represent the database tables
- Experiment with different relationship-loading techniques to improve querying performance
- Utilize SQLAlchemy’s asyncio extension to interact with databases asynchronously