Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
Lecture: Architecture
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Before we actually start writing code for SQLAlchemy let's get a quick, 50,000 foot view by looking at the overall architecture.
0:10
So when we think of SQLAlchemy there's really three layers. First of all, it's built upon Python's DB-API.
0:18
So this a standard API, actually it's DB-API2 these days but we don't have the version here. This is defined by PEP 249 and it defines a way
0:30
that Python can talk to different types of databases using the same API. So SQLAlchemy doesn't try to reinvent that they just build upon this.
0:40
But there's two layers of SQLAlchemy. There's a SQLAlchemy core which defines schemas and types
0:46
a SQL expression language that is a generic query language that can be transformed into a dialect that the different databases speak.
0:57
There's an engine which manages things like the connection and connection pooling and actually which dialect to use.
1:03
You may not be aware, but the SQL query language that used to talk to Microsoft SQL Server is not the same that used to talk to Oracle.
1:10
It's not the same that used to talk to Postgres. They all have slight little variations that make them different. And that can make it hard
1:16
to change between database engines. But SQLAlchemy does that adaptation for us using its core layer. So if you want to do SQL-like programming
1:27
and work mainly in set operations, well here you go you can just use the core and that's a little bit faster and a little bit closer to the metal.
1:34
You'll find most people, though when they're working with SQLAlchemy will be using what's called an Object Relational Mapper.
1:40
Object being classes, relational, database and going between them. So what you do is you define classes and you define fields and properties on them.
1:50
And those are mapped, transformed into the database using SQLAlchemy and its mapper here. So what we're going to do is
1:58
we're going to define a bunch of classes that model our database. Things like packages, releases, users, maintainers
2:04
and so on in SQLAlchemy and then SQLAlchemy will actually create the database, the database schema
2:10
everything and we're just going to talk to SQLAlchemy. It'll be a thing of beauty.