Adding a CMS to Your Flask Web App Transcripts
Chapter: Appendix: 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 are their data access and database systems. So we're going to talk about something called SQLAlchemy
0:10
and in many, many relational based web applications this is your programming layer to talk to your database. SQLAlchemy allows you to simply change
0:21
the connection string and it will adapt itself into entirely different databases. When you use a local file and SQLite for development
0:29
maybe MySQL for testing and Postgres for production I'm not really sure why you would mix those last two
0:36
but if you wanted to, you could with SQLAlchemy and not change your code at all just simply change the connection string.
0:42
So SQLAlchemy is one of the most well known most popular and most powerful data access layers in Python. SQLAlchemy, of course is open source
0:53
you'll find it over at sqlalchemy.org. It was created by Mike Bayer and his site is really good. It has tutorials and walkthroughs
0:59
for the various which you can work with SQLAlchemy one for the object relational mapper one for more direct data access, things like that.
1:09
So why might you want to use SQLAlchemy? Well, there's a bunch of reasons. First of all, it does provide an ORM
1:16
or Object Relational Mapper but it's not required. Sometimes you want to programming classes and monitor your data that way
1:22
but other times you want to just do more set based operations in direct SQL. So SQLAlchemy lets you work in a lower
1:30
level programming data language that is not truly raw SQL so it can still adapt to the various different types of databases.
1:39
It's mature and it's very fast 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 that 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 well, 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, more straightforward
2:22
and it much better matches the way you think about data in your Python application rather than how it's normalized in the database
2:30
so it has a really, really nice ORM or lots of features and this is what we're going to be focusing on in this course.
2:37
I'll also use this as the unit of work design pattern and so that concept is I create a unit of work I make, insert updates, delete, etc.
2:45
all of those within a transaction basically and then at the end, I can either commit or not commit all of those changes at once.
2:54
Cause this is an opposition to the other style which is called active record, where you work with every individual piece of data separately
3:02
and it doesn't commit all at once. There's a lot of different databases supported so SQLite, Postgres, MySQL Microsoft SQL Server, etcetera, etcetera.
3:11
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 get 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 will 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 eager loading and have SQLAlchemy do just one single operation in the database that is effectively adjoined 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.