Full Web Apps with FastAPI Transcripts
Chapter: Appendix: Modeling data with SQLAlchemy classes
0:00 Before we actually start writing code
0:02 for SQLAlchemy, let's get a quick 50,000 foot view
0:05 by looking at the overall architecture.
0:09 So when we think of SQLAlchemy
0:11 there's really three layers.
0:13 First of all, it's build upon Python's DB-API.
0:17 So this is a standard API, actually it's DB-API 2.0
0:22 these days, but we don't have that version here.
0:25 This is defined by PEP 249
0:27 and it defines a way that Python can talk
0:31 to different types of databases using the same API.
0:34 So SQLAlchemy doesn't try to reinvent that
0:37 they just build upon this.
0:39 But there's two layers of SQLAlchemy.
0:41 There's a SQLAlchemy core, which defines schemas and types.
0:45 A SQL expression language, that is a kind of
0:48 generic query language that can be transformed
0:52 into a dialect that the different databases speak.
0:56 There's an engine, which manages things like the connection
0:59 and connection pooling, and actually which dialect to use.
1:02 You may not be aware, but the SQL query language
1:05 that used to talk to Microsoft SQL server
1:07 is not the same that used to talk to Oracle
1:09 it's not the same that used to talk to Postgre.
1:11 They all have slight little variations
1:13 that make them different, and that can make it hard
1:15 to change between database engines.
1:18 But, SQLAlchemy does that adaptation for us
1:21 using its core layer.
1:23 So if you want to do SQL-like programming
1:26 and work mainly in set operations
1:28 well here you go, you can just use the core
1:30 and that's a little bit faster
1:31 and a little bit closer to the metal.
1:33 You'll find most people, though
1:34 when they're working with SQLAlchemy
1:36 it will be using what's called
1:37 an Object Relational Mapper, object being classes
1:40 relational, database, and going between them.
1:43 So what you do is you define classes
1:45 and you define fields and properties on them
1:49 and those are mapped, transformed into the database
1:53 using SQLAlchemy and its mapper here.
1:56 So what we're going to do is we're going to
1:57 define a bunch of classes that model our database
2:00 things like packages, releases
2:02 users, maintainers and so on in SQLAlchemy
2:05 and then SQLAlchemy will actually create the database
2:08 the database schema, everything
2:10 and we're just going to talk to SQLAlchemy.
2:12 It'll be a thing of beauty.