Python for the .NET developer Transcripts
Chapter: Database access and ORMs in Python
Lecture: Features of the SQLAlchemy ORM
0:00 Before we jump into using SQLAlchemy
0:02 let's talk about some of its features
0:04 and compare those back to Entity Framework.
0:06 First of all, you don't have to use the ORM.
0:09 We don't have to create classes
0:11 that map over exactly to our tables.
0:14 There's two layers of SQLAlchemy.
0:16 There's the core, and there's the ORM.
0:19 People often do use the ORM
0:21 but they're popular sites that don't.
0:22 For example, Reddit is written in Python
0:25 and they only us the core, the don't use the ORM bit.
0:28 You might wonder, Why would I use this core thing
0:30 instead of just raw SQL?
0:32 This still creates the tables
0:34 and also adapts the query syntax from database to database.
0:38 So, if you're working with Oracle, Postgres, SQL Server
0:41 the fine details of how you specify parameters and stuff
0:45 actually varies across those different databases
0:48 even though it's all SQL.
0:49 And this, of course, would handle
0:51 that variation automatically for you.
0:53 So it's really nice that this low-level
0:55 database abstraction toolkit.
0:57 SQLAlchemy is not new, it's very polished.
1:00 It's very high-performance.
1:01 It's been around for over 10 years
1:03 and a lot of work and refactoring has been done
1:05 to make this a super fast ORM.
1:09 It's DBA-approved, which means the DBA
1:11 does not have to accept what it's going to give them.
1:14 You can swap out the generated SQL
1:17 with hand-optimized statements and things like that.
1:20 So, the DBAs who are very particular
1:22 or maybe you, maybe you're very particular
1:25 if you want to change that stuff out, of course you can.
1:28 While no ORM is required, it is recommended, at least by me.
1:32 I think, even though some ORM, even though ORMs in general
1:35 have some problems, most of the time
1:38 the 80, 90 percent use case, you massively benefit
1:41 from using an ORM, and you don't run into
1:44 to many problems, as long as you're careful.
1:45 And you can always not use the ORM
1:47 on the few edge cases where it matters, right?
1:50 So it has a very powerful and clean ORM.
1:52 The way you work with it is very much like Entity Framework.
1:55 Code first, you define some classes
1:58 you put some properties in place
1:59 or some what would be like attributes
2:01 put those on, and then you just start working
2:03 with the classes, and magic happens.
2:05 The database comes into existence.
2:07 The tables are set up correctly.
2:08 SQLAlchemy also leverages this concept of the unit of work.
2:12 This is the same way that you work
2:14 with databases in Entity Framework.
2:16 Remember in Entity Framework
2:17 you say, I am using this DB context,
2:20 and then I start doing queries
2:22 and at the end you can commit it or not commit it.
2:25 And when you leave the using statement, it rolls back.
2:27 So you prepare a bunch of work
2:28 you make a bunch of changes and queries
2:30 and then you call Save, Commit.
2:32 Boom, and then all those go in at once.
2:34 This is, as opposed to, say, the Django ORM
2:37 which is an active record.
2:39 You make changes right as you go
2:41 like, right on the objects.
2:43 It also supports a ton of different databases
2:46 including SQLite, Postgres, MySQL, Microsoft SQL Server
2:50 you know, Firebird, Sybase, a bunch.
2:53 SQLite and SQL Server, those are probably
2:56 the two that are most important for you.
2:58 Finally, one of the big challenges of ORMs
3:01 is this lazy loading, n+1 problem.
3:05 I do one query, but then I'm looping over the objects
3:07 that come back, and I'm trying to actually
3:09 I don't even realize it, but I'm accessing
3:11 a related entity through there.
3:13 And each one of those through the loops
3:15 is going back and doing another query
3:17 and another query and another query.
3:18 So I thought I did one query
3:19 but I actually did 21, if I got 20 objects back.
3:22 This n+1 problem is solved with eager loading.
3:25 You would say, I'm actually doing this query
3:27 but I'm also going to be navigating that relationship
3:29 so please do a join and just do it all at once.
3:32 Entity Framework supports this, so does SQLAlchemy.
3:35 So these are a bunch of great features
3:37 and they're actually really similar to Entity Framework.
3:40 If you're coming from that world
3:42 I think you'll be super comfortable over here.