Python for .NET Developers 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.