#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
0:03 are their data access and database systems.
0:06 So we're going to talk about something
0:07 called SQLAlchemy and in many many relational based
0:12 web applications this is your programming layer
0:16 to talk to your database.
0:18 SQLAlchemy allows you to simply change the connection
0:21 string and it will adapt itself
0:23 to entirely different databases.
0:25 I want to 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, 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.
0:56 and his site is really good, it has tutorials
0:58 and walkthroughs for the various ways
1:00 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
1:15 or Object Relational Mapper, but it's not required.
1:18 Sometimes you want to program in classes
1:20 and model 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 lower level
1:29 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:54 What they mean is by default SQLAlchemy
1:56 will generate SQL statements based on the way
1:59 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 whoa there's no way
2:07 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
2:20 more straightforward and it much better matches
2:23 the way you think about 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 with lots of features
2:32 and this is what we're going to be
2:34 focusing on in the next 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 those within a transaction basically
2:48 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
2:58 where you work with every individual piece of data
3:00 separately 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:09 There's lots of different database support.
3:13 And finally one of the problems that
3:15 we can hit with ORMs is through relationships.
3:19 Maybe I have a package, and the package has releases
3:22 So I do one query to 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'll actually go back
3:33 to the database and do another query.
3:35 So if I get 20 packages back I might do 21
3:38 overall database operations separately.
3:41 That's super bad for performance.
3:43 So you can do eagerr loading and have SQLAlchemy
3:47 do just one single operation in the database
3:50 that is effectively a join or something like that
3:53 that brings all that data back
3:54 so if you know that you're going to
3:56 work with the relationships ahead of time
3:57 you can tell SQLAlchemy I'm going to be going back to get these
4:01 so also load that relationship.
4:03 And these are just some of the reasons
4:05 you want to use SQLAlchemy.