Building data-driven web apps with Pyramid and SQLAlchemy Transcripts
Chapter: 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
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.
