Python for the .NET Developer Transcripts
Chapter: Database access and ORMs in Python
Lecture: Features of the SQLAlchemy ORM

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

Talk Python's Mastodon Michael Kennedy's Mastodon