Building data-driven web apps with Pyramid 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 the connection string
0:21 and it will adapt itself into entirely different databases.
0:25 When you use a local file in SQLite for development
0:28 maybe MySQL for testing and Postgres for production.
0:33 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
0:44 most popular 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.
0:56 And his site is really good.
0:57 It has tutorials and walk throughs
0:58 for the various ways in 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 or object relational mapper
1:16 but it's not required.
1:18 Sometimes you want a program
1:19 and classes and model your data that way
1:21 but other times you want to just do
1:23 more set based operations in direct SQL.
1:26 So SQLAlchemy lets you work
1:28 in a lower level programming data language
1:32 that is not truly raw SQL.
1:34 So it can still adapt
1:35 to the various 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.
1:52 Who wouldn't want that?
1:53 What they mean is by default
1:56 SQLAlchemy will generate SQL statements
1:59 based 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 And so if the DBA says, whoa
2:06 there's no way we're going to run this all the time
2:09 you can actually change
2:10 how some of the SQL is generated and run.
2:13 While the ORM is not required
2:15 I'd 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 data
2:25 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 with lots of features
2:32 and this is what we're going to be focusing on in this course.
2:36 It also uses the unit of work design pattern.
2:39 So that concept is I create a unit of work
2:42 I make, insert updates, deletes, etc.
2:44 All of those within a transaction, basically.
2:48 And then at the end I can either commit or not commit
2:51 all of those changes at once.
2:53 This is in 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, Microsoft SQL Server, etc.
3:10 There's lots of different database support.
3:13 And finally, one of the problems that we can hit with ORMs
3:17 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
3:28 when I touch their releases relationship
3:32 it will actually go back to the database
3:34 and do another query.
3:35 So if I get 20 packages back
3:37 I might do 21 overall database operations separately.
3:41 That's super bad for performance.
3:43 So you can do eager loading
3:45 and have SQLAlchemy do just one single operation
3:49 in the database that is effectively a join
3:51 or something like that that brings all that data back.
3:54 So if you know that you're going to work with
3:56 the relationships ahead of time, you can tell SQLAlchemy
3:59 I'm going to be going back to get these.
4:01 So also load that relationship.
4:04 And these are just some of the reasons
4:05 you want to use SQLAlchemy.