#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
0:03 let's get a quick 50 thousand 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 built upon Python's DB API.
0:17 So this is a standard API.
0:20 Actually, it's DB API 2 these days
0:23 but we don't have the version here.
0:25 This is defined by PEP 249, and it defines a way
0:29 that Python can talk to different types of databases
0:33 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 to SQLAlchemy.
0:41 There's a SQLAlchemy Core
0:43 which defines schemas and types
0:45 a SQL expression language, that is a generic
0:48 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
0:58 like the connection, and connection pooling
1:00 and actually which dialect to use.
1:02 You may not be aware, but the SQL query language
1:05 that you use to talk to Microsoft SQL Server
1:07 is not the same that you use to talk to Oracle
1:09 it's not the same you use to talk to Postgres.
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 it's 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 an Object Relational Mapper.
1:39 Object being classes, relational, database
1:42 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 define a bunch
1:58 of classes that model our database
2:00 in SQLAlchemy, and then SQLAlchemy
2:02 will actually create the database, the database schema
2:06 everything, and we're just going to talk to SQLAlchemy.
2:08 It'll be a thing of beauty.