Full Web Apps with FastAPI 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 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.