Saturday 1:55 p.m.–2:25 p.m.

Introduction to SQLAlchemy Core

Jason Myers

Audience level:
Python Libraries


This talk provides a broad-based introduction to SQLAlchemy Core library. It is focused on someone new to SQLAlchemy Core, who has experience with other database technologies such as Django or SQLAlchemy ORM. We'll cover the differences between the domain-centric models of those tools compared to the schema-centric model and how we can use that to deal with common and unusual data sources.


As the layer below the popular SQLAlchemy ORM, the Core portion of SQLAlchemy is often overlooked; however, it can be extremely handy when the data can not be modeled prior to access, needs to be dynamically created, or there is no other value to having a class model. ### Basic Usages We'll first explore the basics of setting up SQLAlchemy in your project, and how to create tables on the fly. Once the tables are created, we'll move into performing all the common sql operations including querying with filters, groups, and joins. SQLAlchemy Core also exposes many SQL functions such as counts, sum, and time related statements. ### Speaking the Database Language SQLAlchemy uses dialects to know how to speak to specific types of databases. Many of the common database engines are supported such as PostgreSQL, MySQL, Oracle and MSSQL. Sometimes, you need need to build a custom dialect to accomidate an unusual function. A recent example is Amazon Redshift, which sort of speaks postgres. We'll look at an example of the custom unload statement built for SQLAlchemy. ### Dynamic Table Introspect and Query Building Finally, we'll cover how to introspect an existing table to build a pythonic representation we can use for SQLAlchemy Core operations. Since some datatypes can be unknown depending on the table structure, we'll discuss how to accommodate that. We'll wrap up with a brief look at query chaining and the use of conditional to build dynamic queries, which builds directly on top of the simple sql operations we covered earlier.