Building Data-Driven Web Apps with Pyramid and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Introducing SQLAlchemy
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
One of the absolute pillars of web applications
0:03
are their data access and database systems.
0:06
So we're going to talk about something called SQLAlchemy
0:09
and in many many relational based web applications
0:13
this is your programming layer to talk to your database.
0:18
SQLAlchemy allows you to simply change the connection string
0:21
and it will adapt itself into entirely different databases.
0:25
When you use a local file in SQLite for development
0:28
maybe MySQL for testing and Postgres for production.
0:33
Not really sure why you would mix those last two
0:35
but if you wanted to you could with SQLAlchemy
0:38
and not change your code at all.
0:39
Just simply change the connection string.
0:41
So SQLAlchemy is one of the most well known
0:44
most popular and most powerful data access layers in Python.
0:49
SQLAlchemy of course is open source.
0:52
You'll find it over at sqlalchemy.org.
0:54
It was created by Mike Bayer.
0:56
And his site is really good.
0:57
It has tutorials and walk throughs
0:58
for the various ways in which you can work with SQLAlchemy.
1:02
One for the Object Relational Mapper
1:04
one for more direct data access, things like that.
1:08
So why might you want to use SQLAlchemy?
1:10
Well, there's a bunch of reasons.
1:12
First of all it does provide an ORM or object relational mapper
1:16
but it's not required.
1:18
Sometimes you want a program
1:19
and classes and model your data that way
1:21
but other times you want to just do
1:23
more set based operations in direct SQL.
1:26
So SQLAlchemy lets you work
1:28
in a lower level programming data language
1:32
that is not truly raw SQL.
1:34
So it can still adapt
1:35
to the various different types of databases.
1:38
It's mature and it's very fast.
1:40
It's been around for over 10 years.
1:43
Some of the really hot spots are written in C.
1:45
So it's not some brand new thing.
1:46
It's been truly tested and is highly tuned.
1:51
It's DBA approved.
1:52
Who wouldn't want that?
1:53
What they mean is by default
1:56
SQLAlchemy will generate SQL statements
1:59
based on the way you interact with the classes.
2:01
But you can actually swap out those
2:03
with hand optimized statements.
2:05
And so if the DBA says, whoa
2:06
there's no way we're going to run this all the time
2:09
you can actually change
2:10
how some of the SQL is generated and run.
2:13
While the ORM is not required
2:15
I'd recommend it for about 80%, 90% of the cases.
2:18
It makes programming much simpler, more straightforward
2:21
and it much better matches the way you think about data
2:25
in your Python application
2:27
rather than how it's normalized in the database.
2:29
So it has a really really nice ORM with lots of features
2:32
and this is what we're going to be focusing on in this course.
2:36
It also uses the unit of work design pattern.
2:39
So that concept is I create a unit of work
2:42
I make, insert updates, deletes, etc.
2:44
All of those within a transaction, basically.
2:48
And then at the end I can either commit or not commit
2:51
all of those changes at once.
2:53
This is in opposition to the other style
2:56
which is called active record where you work
2:58
with every individual piece of data separately
3:01
and it doesn't commit all at once.
3:04
There's a lot of different databases supported.
3:06
So SQLite, Postgres, MySQL, Microsoft SQL Server, etc.
3:10
There's lots of different database support.
3:13
And finally, one of the problems that we can hit with ORMs
3:17
is through relationships.
3:19
Maybe I have a package and the package has releases.
3:23
So I do one query to get a list of packages
3:25
and I also want to know about the releases.
3:27
So every one of those package
3:28
when I touch their releases relationship
3:32
it will actually go back to the database
3:34
and do another query.
3:35
So if I get 20 packages back
3:37
I might do 21 overall database operations separately.
3:41
That's super bad for performance.
3:43
So you can do eager loading
3:45
and have SQLAlchemy do just one single operation
3:49
in the database that is effectively a join
3:51
or something like that that brings all that data back.
3:54
So if you know that you're going to work with
3:56
the relationships ahead of time, you can tell SQLAlchemy
3:59
I'm going to be going back to get these.
4:01
So also load that relationship.
4:04
And these are just some of the reasons
4:05
you want to use SQLAlchemy.