Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
0:00 Before we actually start writing code for SQLAlchemy
0:03 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:10 there's really three layers.
0:13 First of all, it's built upon Python's DB-API.
0:17 So this a standard API, actually it's DB-API2 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 of SQLAlchemy.
0:41 There's a SQLAlchemy core which defines schemas and types
0:45 a SQL expression language that is a generic query language
0:49 that can be transformed into a dialect
0:53 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 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 Postgres.
1:11 They all have slight little variations
1:12 that make them different.
1:14 And that can make it hard
1:15 to change between database engines.
1:17 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, well here you go
1:29 you can just use the core 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 will be using what's called an Object Relational Mapper.
1:39 Object being classes, relational, database
1:41 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:52 using SQLAlchemy and its mapper here.
1:56 So what we're going to do is
1:57 we're going to define a bunch of classes
1:58 that model our database.
2:00 Things like packages, releases, users, maintainers
2:03 and so on in SQLAlchemy and then SQLAlchemy
2:06 will actually create the database, the database schema
2:09 everything and we're just going to talk to SQLAlchemy.
2:12 It'll be a thing of beauty.