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