Building Data-Driven Web Apps with Flask 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
0:20
the connection string and it will adapt itself
0:23
into entirely different databases.
0:25
When you use a local file and SQLite for development
0:28
maybe MySQL for testing and Postgres for production
0:33
I'm 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 most popular
0:45
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 and his site is really good.
0:57
It has tutorials and walkthroughs
0:58
for the various 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
1:15
or Object Relational Mapper but it's not required.
1:18
Sometimes you want to programming classes
1:20
and monitor your data that way
1:21
but other times you want to just do more
1:23
set based operations in direct SQL.
1:26
So SQLAlchemy lets you work in a lower
1:29
level programming data language that is not truly raw SQL
1:34
so it can still adapt to the various
1:36
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, who wouldn't want that?
1:53
What that mean is, by default SQLAlchemy
1:56
will generate SQL statements based
1:59
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
So if the DBA says well, there's no
2:07
way we're going to run this all the time
2:09
you can actually change how some
2:11
of the SQL is generated and run.
2:13
Well, the ORM is not required
2:15
I 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
2:25
data 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 or lots of features
2:32
and this is what we're going to be focusing on in this course.
2:36
I'll also use this as the unit of work design pattern
2:39
and so that concept is I create a unit of work
2:42
I make, insert updates, delete, etc.
2:44
all of those within a transaction basically
2:48
and then at the end, I can either commit
2:50
or not commit all of those changes at once.
2:53
Cause this is an 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
3:08
Microsoft SQL Server, etcetera, etcetera.
3:10
There's lots of different database support.
3:13
And finally, one of the problems
3:15
that we can hit with ORMs 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 when I touch
3:29
their releases relationship, it will actually
3:32
go back to the database and do another query.
3:35
So if I get 20 packages back, I might do
3:38
21 overall database operations separately.
3:41
That's super bad for performance.
3:43
So you can do eager loading and have SQLAlchemy
3:47
do just one single operation in the database
3:50
that is effectively adjoined or something like that
3:53
that brings all that data back.
3:54
So if you know that you're going to work
3:56
with the relationships ahead of time
3:57
you can tell SQLAlchemy, I'm going to be going back
4:00
to get these so also load that relationship.
4:04
And these are just some of the reasons
4:05
you want to use SQLAlchemy.