Building data-driven web apps with Flask and SQLAlchemy Transcripts
Chapter: Modeling data with SQLAlchemy classes
Lecture: Introducing SQLAlchemy

Learn more about this course
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.