Introduction to SQLAlchemy at PyConZA 2012

I covered how to use SQLAlchemy, primarily as an ORM layer, but also explaining the other parts. This was intended as a hands-on session for people who aren't familiar with SQLAlchemy or are fairly new to Python.

The majority of the talk is interactively stepping through the standard ORM tutorial from the documentation. More information at http://westmarch.sjsoft.com/2012/10/introduction-to-sqlalchemy-tutorial/ and http://lanyrd.com/2012/pycon-za/syyfz/

Transcript

1. https://github.com/davidfraser/sqlalchemy-orm-tutorial Introduction to SQLAlchemy
2. https://github.com/davidfraser/sqlalchemy-orm-tutorial Introduction • How to use SQLAlchemy • Primarily as an ORM layer • But also explaining the other parts • Relational databases • Python DBAPI • ORM layers
3. https://github.com/davidfraser/sqlalchemy-orm-tutorial Philosophy of SQLalchemy • Abstraction (leaky) • But Hand-Coded • Database Independence (SQL dialects, data types) • Logical set of classes that relate to database constructs
4. https://github.com/davidfraser/sqlalchemy-orm-tutorial SQLalchemy concept map • Engine <=> Database • Session <=> Database Connection • (Data Type) <=> Column Type • Schemas: Table, Column, Index, ForeignKey, Sequence... • SQL statements/xepressions: Select, Insert, Update, Join ... • And so on...
5. https://github.com/davidfraser/sqlalchemy-orm-tutorial ORM Tutorial (using declarative) Working through the standard tutorial from the documentation. Code on github, if you want to follow along • Basics • Querying • Relationships • Eagerness • Deleting • Bonus Round
6. https://github.com/davidfraser/sqlalchemy-orm-tutorial Basics • Importing declarative and setting up a table • Creating records, adding and simple querying • Record statuses, flushing and committing • Rollback
7. https://github.com/davidfraser/sqlalchemy-orm-tutorial Querying • Querying - record objects, named tuples • Aliases • Filtering • Ordering, selecting ranges, first/one/all • Literal SQL • Counting
8. https://github.com/davidfraser/sqlalchemy-orm-tutorial Relationships • Declaring relationships with foreign keys • Working with related records • Querying with joins • Table aliases • Subqueries and mapping to entities • exists, has, any • Relationship operators
9. https://github.com/davidfraser/sqlalchemy-orm-tutorial The importance of being eager • Eager loading • Joined load
10. https://github.com/davidfraser/sqlalchemy-orm-tutorial Deleting • Deleting • Cascading
11. https://github.com/davidfraser/sqlalchemy-orm-tutorial Bonus Round • Many to many relationships
12. https://github.com/davidfraser/sqlalchemy-orm-tutorial Questions • What is your name? • What is your favourite colour?
13. https://github.com/davidfraser/sqlalchemy-orm-tutorial Other interesting topics • Concurrent connections and avoiding deadlocks • You can replace anything on any layer