Adding a CMS to Your Flask 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 build upon Python's DB-API.
0:18 So this is a standard API, actually it's DB-API 2.0 these days, but we don't have that version here. This is defined by PEP 249
0:28 and it defines a way that Python can talk to different types of databases using the same API. So SQLAlchemy doesn't try to reinvent that
0:38 they just build upon this. 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 kind of 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 Postgre. 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 it will be using what's called an Object Relational Mapper, object being classes
1:41 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 we're going to
1:58 define a bunch of classes that model our database things like packages, releases users, maintainers and so on in SQLAlchemy
2:06 and then SQLAlchemy will actually create the database the database schema, everything and we're just going to talk to SQLAlchemy.
2:13 It'll be a thing of beauty.