Adding a CMS to Your Pyramid 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.
0:19 SQLAlchemy allows you to simply change the connection string and it will adapt itself into entirely different databases.
0:26 When you 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.
0:58 It has tutorials and walk throughs 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 a program
1:20 and classes 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
1:29 in a lower 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 they mean is by default
1:57 SQLAlchemy will generate SQL statements based on the way you interact with the classes. But you can actually swap out those
2:04 with hand optimized statements. And so if the DBA says, whoa there's no way we're going to run this all the time you can actually change
2:11 how some of the SQL is generated and run. While the ORM is not required I'd recommend it for about 80%, 90% of the cases.
2:19 It makes programming much simpler, 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
2:33 and this is what we're going to be focusing on in this 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 of those within a transaction, basically. And 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
2:59 with every individual piece of data separately and it doesn't commit all at once. There's a lot of different databases supported.
3:07 So SQLite, Postgres, MySQL, Microsoft SQL Server, etc. There's lots of different database support.
3:14 And finally, one of the problems that we can hit with ORMs is through relationships. Maybe I have a package and the package has releases.
3:24 So I do one query to get a list of packages and I also want to know about the releases. So every one of those package
3:29 when I touch their releases relationship it will actually go back to the database and do another query. So if I get 20 packages back
3:38 I might do 21 overall database operations separately. That's super bad for performance. So you can do eager loading
3:46 and have SQLAlchemy do just one single operation in the database that is effectively a join or something like that that brings all that data back.
3:55 So if you know that you're going to work with the relationships ahead of time, you can tell SQLAlchemy I'm going to be going back to get these.
4:02 So also load that relationship. And these are just some of the reasons you want to use SQLAlchemy.


Talk Python's Mastodon Michael Kennedy's Mastodon