Adding a CMS to Your Pyramid Web App Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
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.