#100DaysOfWeb in Python Transcripts
Chapter: Days 33-36: Database access with SQLAlchemy
Lecture: SQLAlchemy 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 thousand 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. So this is a standard API.
0:21 Actually, it's DB API 2 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 to SQLAlchemy. There's a SQLAlchemy Core which defines schemas and types a SQL expression language, that is a generic
0:49 query language that can be transformed into a dialect that the different databases speak. There's an engine which manages things
0:59 like the connection, and connection pooling and actually which dialect to use. You may not be aware, but the SQL query language
1:06 that you use to talk to Microsoft SQL Server is not the same that you use to talk to Oracle it's not the same you use to talk to Postgres.
1:12 They all have slight little variations that make them different, and that can make it hard to change between database engines.
1:19 But, SQLAlchemy does that adaptation for us using it's core layer. So if you want to do SQL-like programming and work mainly in set operations
1:29 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. You'll find most people though
1:35 when they're working with SQLAlchemy it will be using what's called an Object Relational Mapper. Object being classes, relational, database
1:43 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 define a bunch
1:59 of classes that model our database in SQLAlchemy, and then SQLAlchemy will actually create the database, the database schema
2:07 everything, and we're just going to talk to SQLAlchemy. It'll be a thing of beauty.


Talk Python's Mastodon Michael Kennedy's Mastodon