005_mike_bayer_SQLAlchemy_and_data_access_in_Python.vtt

WEBVTT

00:00:00.001 --> 00:00:07.960
Talk Python To Me, episode number five, with guest Mike Baer, recorded Thursday, April 9th, 2015.

00:00:07.960 --> 00:00:37.380
Hello and welcome to Talk Python to Me, a weekly podcast on Python,

00:00:37.380 --> 00:00:40.000
the language, the libraries, the ecosystem, and the personalities.

00:00:40.000 --> 00:00:41.600
This is your host, Michael Kennedy.

00:00:41.600 --> 00:00:45.000
Follow me on Twitter, where I'm @mkennedy,

00:00:45.000 --> 00:00:49.080
and keep up with the show and listen to past episodes at talkpythontome.com.

00:00:49.080 --> 00:00:53.260
This episode, we'll be talking with Mike Baer about SQLAlchemy.

00:00:53.260 --> 00:00:54.600
Let me introduce Mike.

00:00:54.600 --> 00:01:00.120
Mike Baer is the creator of several prominent Python libraries, including SQLAlchemy,

00:01:00.120 --> 00:01:04.760
Mako Templates for Python, Alembic Migrations, and Dogpile Caching.

00:01:04.940 --> 00:01:09.040
He's been working with open source software and databases since the mid-1990s.

00:01:09.040 --> 00:01:14.540
Today, he's active in the Python community, working to spread good software practices to an ever-wider audience.

00:01:14.540 --> 00:01:17.460
Mike is a semi-regular presenter at PyCon US,

00:01:17.460 --> 00:01:22.360
and has also spoken at many smaller events and conferences in the United States and Europe.

00:01:22.360 --> 00:01:24.540
Follow Mike on Twitter, where he's Zeke.

00:01:24.540 --> 00:01:27.240
That's at Z-Z-Z-E-E-K.

00:01:27.980 --> 00:01:28.900
Welcome to the show.

00:01:28.900 --> 00:01:30.800
Thanks. Thanks for having me.

00:01:30.800 --> 00:01:33.920
Yeah, I've been a longtime fan of SQLAlchemy.

00:01:33.920 --> 00:01:37.120
And, you know, when I started this podcast, I was thinking,

00:01:37.120 --> 00:01:38.700
who do I have to have on the show?

00:01:38.700 --> 00:01:39.940
And you were definitely on the list.

00:01:39.940 --> 00:01:41.040
That's very flattering.

00:01:41.040 --> 00:01:43.880
Yeah, you built some great stuff.

00:01:44.600 --> 00:01:49.600
So, before we get into the details of SQLAlchemy and how we should use it and why it's so awesome,

00:01:49.600 --> 00:01:53.280
maybe let's take a trip down memory lane, step back a little bit,

00:01:53.280 --> 00:01:55.620
and just, you know, how and when did you get into Python?

00:01:55.620 --> 00:02:00.920
I got into Python after kind of hesitating to get into it, meaning I knew about it,

00:02:00.920 --> 00:02:03.460
and it seemed like something that was appealing to me.

00:02:03.720 --> 00:02:06.980
But I kind of resisted mostly because of the white space thing.

00:02:06.980 --> 00:02:09.680
This is back in probably 2003.

00:02:09.680 --> 00:02:11.160
I was working at Major League Baseball.

00:02:11.160 --> 00:02:18.040
Most of my programming career before Python was doing a lot of Java and a lot of Perl.

00:02:18.040 --> 00:02:23.620
And I really liked the idea of object-oriented languages,

00:02:23.620 --> 00:02:25.360
and I liked the idea of scripting languages.

00:02:25.360 --> 00:02:28.280
So Java was very object-oriented.

00:02:28.280 --> 00:02:30.120
Perl was not too good at objects.

00:02:30.120 --> 00:02:32.500
But it was a scripting language.

00:02:32.500 --> 00:02:37.060
So I really wanted there to be some scripting language where I could write much cleaner code than you get in Perl.

00:02:37.060 --> 00:02:40.760
Yeah, so you kind of had half of what you wanted in one language.

00:02:40.760 --> 00:02:41.200
Yeah.

00:02:41.200 --> 00:02:43.500
But you wanted to bring it all together, right?

00:02:43.500 --> 00:02:45.380
And Python really looked like it.

00:02:45.380 --> 00:02:48.760
Python really looks like it, but it's got that stupid white space thing.

00:02:48.760 --> 00:02:50.380
And I just didn't want to get into it.

00:02:50.380 --> 00:02:53.020
And then one day when I was working at baseball,

00:02:53.020 --> 00:02:58.720
we were rolling out this client application called WinCVS, I think it was.

00:02:58.720 --> 00:03:00.320
It was a Windows CVS client.

00:03:00.600 --> 00:03:02.780
And we had to roll it out to people that were not programmers.

00:03:02.780 --> 00:03:10.160
And we needed to add hooks to it so that people could tag things and send things to different servers by tagging.

00:03:10.160 --> 00:03:12.920
And WinCVS's scripting language was Python.

00:03:13.600 --> 00:03:18.020
So I had to learn, you know, 10 lines of Python to do this script.

00:03:18.020 --> 00:03:21.620
And basically I spent like two hours with the white space thing.

00:03:21.620 --> 00:03:26.080
And I was like, oh, we do that white space anyway when the code is clean.

00:03:26.080 --> 00:03:27.940
And I'm actually totally fine with it.

00:03:27.940 --> 00:03:32.160
So let me just learn Python now that I've spent 10 minutes getting used to white space.

00:03:32.160 --> 00:03:36.520
That's really funny that it was like a real barrier to you, right?

00:03:36.520 --> 00:03:37.860
And then it just came down.

00:03:37.860 --> 00:03:40.920
It really bugged me because I was a really sloppy coder back then.

00:03:40.920 --> 00:03:43.560
Python really made me a much cleaner programmer.

00:03:43.560 --> 00:03:45.040
Yeah, that's a good point.

00:03:45.040 --> 00:03:46.180
I hadn't really thought about that.

00:03:46.180 --> 00:03:49.020
But it definitely does make you think about that.

00:03:49.020 --> 00:03:52.860
And, you know, a lot of the modern editors make it almost transparent to you.

00:03:52.860 --> 00:03:55.080
But I think there's the mental concept of it.

00:03:55.080 --> 00:03:56.280
And then there's the reality of it.

00:03:56.280 --> 00:03:58.660
And the reality is that it's actually not a big deal at all.

00:03:58.660 --> 00:03:59.400
It's super smooth.

00:03:59.400 --> 00:04:05.260
But when you're coming from, say, Java or C# or C++ or something, then it seems like a huge deal.

00:04:05.260 --> 00:04:08.360
Yeah, you get over it really quickly.

00:04:08.360 --> 00:04:09.080
Excellent.

00:04:09.080 --> 00:04:10.920
Oh, you bring up WinCVS.

00:04:10.920 --> 00:04:15.180
Boy, I used to use that thing back in the late 1990s or something.

00:04:15.180 --> 00:04:16.260
That brings back some memories.

00:04:16.260 --> 00:04:17.080
Yeah, that's kind of what it was, yeah.

00:04:17.080 --> 00:04:18.580
That's awesome.

00:04:18.580 --> 00:04:21.880
Let's talk about something more modern than WinCVS.

00:04:21.880 --> 00:04:23.020
Let's talk about SQLAlchemy.

00:04:23.020 --> 00:04:23.560
Sure.

00:04:23.920 --> 00:04:31.600
So can you just tell, I suspect that, you know, 90% of the listeners are fans of and familiar with SQLAlchemy.

00:04:31.600 --> 00:04:34.680
But there's going to be some folks who don't know or maybe they know it as a buzzword.

00:04:34.680 --> 00:04:37.020
Can you just tell everyone what SQLAlchemy is?

00:04:37.020 --> 00:04:41.860
So SQLAlchemy is this library that is, it's all about Python.

00:04:41.860 --> 00:04:47.580
And it's all about SQL and databases and interacting with databases.

00:04:47.580 --> 00:04:56.460
So it's basically when you need to work with a relational database, say you're going to work with like Postgres or MySQL, there's all kinds of things you need to do with the database.

00:04:56.460 --> 00:04:58.460
You need to write, you know, programs that talk to it.

00:04:58.460 --> 00:04:59.480
You need to get results back.

00:04:59.480 --> 00:05:01.280
You need to create schemas.

00:05:01.280 --> 00:05:04.560
You might need to be given some existing database and look at it.

00:05:05.420 --> 00:05:08.960
There's scripting and migrations of schemas you want to do.

00:05:08.960 --> 00:05:15.560
You probably are writing applications that have higher level business concepts that you want to map to that database.

00:05:15.560 --> 00:05:24.100
So SQLAlchemy started out with a really ambitious goal of having a place, a way to do that, a way to do all those things in Python.

00:05:24.100 --> 00:05:36.780
Not just being able to map like an object to a table, but all the features of managing schemas and creating schemas and reflecting them and working with SQL scripts and everything else.

00:05:36.780 --> 00:05:44.480
Working with data type problems, making all kinds of different database backends look as similar as is feasible.

00:05:44.480 --> 00:05:50.400
You know, like data types in Oracle are very weird compared to those in MySQL and SQLite's a whole different story.

00:05:51.340 --> 00:05:56.880
Those databases are always going to be different, but you can at least try to get some semblance of sanity across all of them.

00:05:56.880 --> 00:06:01.740
So that's why if you go to SQLAlchemy's website, the first thing it says is like the database toolkit for Python.

00:06:01.740 --> 00:06:02.820
It doesn't say it's an ORM.

00:06:02.820 --> 00:06:04.240
That's just one component.

00:06:04.240 --> 00:06:11.600
It's a toolkit that has kind of helpers for whatever you have to do with a database.

00:06:11.600 --> 00:06:13.040
SQLAlchemy can definitely have a role.

00:06:13.040 --> 00:06:13.920
That's excellent.

00:06:13.920 --> 00:06:20.940
You have at least two really nice sort of comprehensive walkthroughs, one for the core and one for the ORM component.

00:06:20.940 --> 00:06:23.700
That's SQLAlchemy.org, right?

00:06:23.700 --> 00:06:25.440
Yes, SQLAlchemy.org.

00:06:25.440 --> 00:06:30.320
So when did you get started with, when did you create SQLAlchemy and what inspired you to create it in the first place?

00:06:31.060 --> 00:06:39.580
So I was kind of writing the first iteration of SQLAlchemy in late 2005, and our first release was early 2006.

00:06:39.580 --> 00:06:45.100
And really when I was writing SQLAlchemy, it was not the first time I was creating a tool like that.

00:06:45.700 --> 00:06:55.280
When you work, or at least in the old days, when you used to work in the 90s in all these various internet shops, we had really little tools to work with.

00:06:55.280 --> 00:07:00.580
I mean, there were a lot of tools, but we were, you know, using Perl and libraries to talk databases were pretty crude.

00:07:01.300 --> 00:07:08.340
And I was using Java from day one when it was, you know, version 1.0, and there was literally no libraries before Hibernate.

00:07:08.340 --> 00:07:15.740
So throughout the 90s, you know, you get into the habit of writing little database access layers yourself.

00:07:15.740 --> 00:07:21.480
And every job you have, you're going to go and, now I'm going to write a really better database.

00:07:21.480 --> 00:07:23.900
You know, the database access layer I wrote last time sucked.

00:07:23.900 --> 00:07:24.980
Now I'm going to write a really good one.

00:07:25.320 --> 00:07:29.760
And you say that statement to yourself like five times, right?

00:07:29.760 --> 00:07:32.380
Yeah, every job you have, it's like, okay, this time we're going to get it right.

00:07:32.380 --> 00:07:36.040
We're going to write the ultimate, you know, set of tools, you know.

00:07:36.040 --> 00:07:42.540
And then as you're doing this, you know, as the 2000s rolled along, ORMs, you know, Hibernate suddenly was around.

00:07:42.540 --> 00:07:48.700
And I was doing a lot of Perl at MLB, and there were some ORM-ish things with Perl that looked terrible to me.

00:07:48.700 --> 00:07:49.520
They looked really simplistic.

00:07:51.180 --> 00:07:55.560
I had worked, by the time I was at MLB, I was working, that was, they were a big Oracle shop.

00:07:55.560 --> 00:07:58.700
I had already had a lot of gigs where I had worked a lot with Oracle.

00:07:58.700 --> 00:08:00.320
I had worked a lot with Postgres.

00:08:00.320 --> 00:08:03.500
I had worked a lot with Sybase, Microsoft SQL Server.

00:08:03.500 --> 00:08:10.380
I had worked a lot with not just MySQL, but its earlier MSQL incarnations in the early 90s.

00:08:10.780 --> 00:08:22.640
So I had, and I had written database code in Perl, Java, and C, C++, you know, because back in the early 90s, you know, we didn't, Perl wasn't always available for some cases.

00:08:23.300 --> 00:08:30.900
So I really had iterated a lot on this whole back and forth with the database, sending a SQL string, getting a result back, getting data back from the result.

00:08:30.900 --> 00:08:33.720
I knew a lot about that.

00:08:33.720 --> 00:08:40.020
So, and I always wanted to have a system that would be the last time I have to do this, you know.

00:08:40.020 --> 00:08:41.040
Yeah, I had that feeling.

00:08:41.040 --> 00:08:43.760
A long time, it was like, I was writing things in Java.

00:08:43.760 --> 00:08:49.340
I was going to write like a big toolkit in Java, like the big, you know, web framework, everything you need Java thing.

00:08:49.340 --> 00:08:50.220
And it never really happened.

00:08:50.220 --> 00:08:54.500
But eventually when I got into Python, that's when I really began doing it.

00:08:54.500 --> 00:08:58.660
I, you know, Python had kind of not a lot of libraries around.

00:08:58.660 --> 00:09:06.740
The only library that was feasible for database access on Python was SQL Object, which is a great library and was a huge influence on SQLAlchemy.

00:09:08.440 --> 00:09:14.560
And I started doing it and it was kind of based on the latest techniques I iterated at baseball.

00:09:14.560 --> 00:09:20.160
Like the idea is that if you want to talk to a database table, you make a data structure that represents what the table looks like.

00:09:20.160 --> 00:09:22.460
And we call that table metadata.

00:09:22.460 --> 00:09:28.200
But one critical thing I did with SQLAlchemy was I actually decided to read a book beforehand.

00:09:28.200 --> 00:09:32.220
So I had this book called Patterns of Enterprise Architecture by Martin Fowler.

00:09:32.220 --> 00:09:33.340
Yeah, that's a great book.

00:09:33.340 --> 00:09:38.120
And I read that book and half of the book was like, oh, that, oh, this, oh, this.

00:09:38.240 --> 00:09:39.340
All these things that I've done.

00:09:39.340 --> 00:09:40.720
And he kind of put names to them.

00:09:40.720 --> 00:09:46.060
And then as he did all that, there were some other patterns that I was not familiar with, like the unit of work pattern.

00:09:46.060 --> 00:09:46.960
I was like, wow, look at that.

00:09:46.960 --> 00:09:47.580
I've never heard of that.

00:09:47.580 --> 00:09:48.180
That's pretty cool.

00:09:48.180 --> 00:09:51.540
And does that appear as the session in SQLAlchemy now?

00:09:51.540 --> 00:09:52.160
Yeah, the session.

00:09:52.160 --> 00:09:52.380
Yeah.

00:09:52.380 --> 00:09:54.140
And actually session is from Hibernate.

00:09:54.140 --> 00:09:55.240
Hibernate's got the same thing.

00:09:55.820 --> 00:09:58.600
So I, you know, really made a go at it.

00:09:58.600 --> 00:10:02.840
And I said, I'm actually going to read a book beforehand and really try to get all these patterns right.

00:10:02.840 --> 00:10:05.220
And that's kind of how it started.

00:10:05.220 --> 00:10:09.140
And it took a very long time for SQLAlchemy to be any good.

00:10:09.220 --> 00:10:15.860
I mean, I think when I first released it, it had a lot of the special things going on that made people like it.

00:10:15.860 --> 00:10:17.660
But it was not executed very well.

00:10:17.660 --> 00:10:22.180
But, you know, over the years, over really 10 years, it's gotten very refined.

00:10:22.180 --> 00:10:23.080
But it's taken a long time.

00:10:23.500 --> 00:10:24.800
Yeah, it's quite a mature product.

00:10:24.800 --> 00:10:25.700
And that's cool.

00:10:25.700 --> 00:10:26.340
Yeah.

00:10:26.340 --> 00:10:29.420
Did you use it internally before you actually released it?

00:10:29.420 --> 00:10:31.500
Or was it kind of, I'm building it out in public?

00:10:31.500 --> 00:10:33.180
Yeah, no, it was funny.

00:10:33.180 --> 00:10:35.340
When I was starting in Python, I was writing open source.

00:10:35.340 --> 00:10:42.780
The first thing I wrote was this template language called Mighty, which was basically almost a line for line port of a template thing in Perl called HTML Mason.

00:10:42.780 --> 00:10:45.640
That was the first open source thing I published.

00:10:45.640 --> 00:10:48.820
And then Mighty was kind of an embarrassment eventually.

00:10:48.820 --> 00:10:50.400
And I wrote Mako to replace it.

00:10:50.800 --> 00:10:55.480
But I was writing these libraries at first without having any job.

00:10:55.480 --> 00:10:57.920
The gigs I was doing were still Java jobs.

00:10:57.920 --> 00:11:00.880
I was still doing, after I left baseball, I was still doing Java work.

00:11:00.880 --> 00:11:06.820
Python was certainly used in the mid-2000s, but it was not as dominant as it is today.

00:11:06.820 --> 00:11:11.960
I didn't actually get to use SQLAlchemy for a real gig until version 0.5.

00:11:11.960 --> 00:11:17.620
So maybe four years into it, I finally was on a gig where like, hey, we can do this thing in Python and we can do this.

00:11:17.620 --> 00:11:20.060
And everyone was like, okay, use the Python thing.

00:11:20.620 --> 00:11:22.200
Yeah, use that toy language.

00:11:22.200 --> 00:11:22.880
Yeah, yeah, yeah.

00:11:22.880 --> 00:11:24.200
It was kind of like that, yeah.

00:11:24.200 --> 00:11:28.120
And it was actually the website for a TV show called Charlie Rose, which is on PBS.

00:11:28.120 --> 00:11:28.720
Oh, yeah, yeah.

00:11:28.720 --> 00:11:29.020
Great.

00:11:29.020 --> 00:11:37.740
Yeah, we wrote, I don't know if it's still up in that incarnation anymore, but at the time, this is maybe 2007, it was written using early version of pylons.

00:11:38.300 --> 00:11:40.740
And that was the first time I used SQLAlchemy on the job myself.

00:11:40.740 --> 00:11:45.620
And SQLAlchemy improved by leaps and bounds on that gig because I had like, oh, this sucks.

00:11:45.620 --> 00:11:46.480
Oh, look at this thing.

00:11:46.480 --> 00:11:47.120
This thing is terrible.

00:11:47.120 --> 00:11:49.060
How could people have been using this for three years?

00:11:49.060 --> 00:11:50.120
Yeah, that's excellent.

00:11:50.880 --> 00:11:51.380
Yeah.

00:11:51.380 --> 00:11:52.960
So it's weird.

00:11:52.960 --> 00:11:53.540
Yeah.

00:11:53.540 --> 00:11:55.460
It was written kind of in a vacuum.

00:11:55.460 --> 00:11:58.520
Like I only knew what my users told me for several years.

00:11:58.940 --> 00:11:59.960
Yeah, very cool.

00:11:59.960 --> 00:12:06.620
The previous show that's not out yet, but was recorded just before this is a guy named Mahmoud Hashemi from eBay PayPal.

00:12:06.620 --> 00:12:12.520
And the whole show is about sort of enterprise Python and sort of using it as a real proper language.

00:12:12.520 --> 00:12:15.940
And I think, you know, it's probably that time frame and that era.

00:12:15.940 --> 00:12:23.800
There's a lot of similar ideas about, hey, we could actually build real websites and, you know, web scale professional apps with this language.

00:12:23.800 --> 00:12:24.240
It's great.

00:12:24.240 --> 00:12:24.880
Yeah.

00:12:24.880 --> 00:12:25.480
Yeah.

00:12:25.540 --> 00:12:33.180
I mean, Python as enterprise was something that I think, you know, it existed in the early 2000s.

00:12:33.180 --> 00:12:40.200
Like I think a lot of people were using, I mean, people were using like Zope or Plone, I think was the early, you know, incarnation of enterprise Python.

00:12:40.200 --> 00:12:48.540
But I think, you know, really you got to hand it to like, I mean, first Ruby on Rails got people more comfortable with scripting languages for enterprise.

00:12:48.540 --> 00:12:51.280
And then Django really helped a lot too.

00:12:51.280 --> 00:12:53.360
Django really brought a lot of people to Python.

00:12:54.200 --> 00:13:02.080
And I think the critical mass kind of started happening for Python kind of in 2005 on forward.

00:13:02.080 --> 00:13:03.440
Absolutely.

00:13:03.440 --> 00:13:14.380
And I think, you know, to shoot your horn and everybody else's a little bit, the stuff that's in PyPy, all those packages out there make it really hard to not consider Python.

00:13:14.380 --> 00:13:19.360
The fact that you can just pip install magic, you know, and build stuff so quickly.

00:13:19.360 --> 00:13:21.500
Like, why would I start from scratch, you know?

00:13:21.500 --> 00:13:21.780
Right.

00:13:21.780 --> 00:13:22.220
Absolutely.

00:13:22.840 --> 00:13:24.940
We now know what SQLAlchemy is, if we didn't.

00:13:24.940 --> 00:13:28.920
Can you kind of give us like a, I know it's hard to talk about code, so don't get too detailed.

00:13:28.920 --> 00:13:32.880
But can you give me a little walkthrough of like what is involved in getting started?

00:13:32.880 --> 00:13:37.280
Like what do I do in code to maybe make a basic query or connect to the database?

00:13:37.800 --> 00:13:40.760
So there's different ways that you might want to use the system.

00:13:40.760 --> 00:13:43.880
I mean, there's the level, the layer of I want to just make a query.

00:13:43.880 --> 00:13:45.320
And that's pretty easy.

00:13:45.320 --> 00:13:49.020
I mean, SQLAlchemy has a lot of different levels that it can be used at.

00:13:49.020 --> 00:13:52.300
Like if you want to just connect to a database and make a query, that's like a really two line.

00:13:52.420 --> 00:13:57.260
You make an object called an engine, which you give it a URL for a database.

00:13:57.260 --> 00:14:01.460
And then the engine can just accept queries as strings directly.

00:14:01.460 --> 00:14:08.480
And right there, you've already saved maybe 10 lines of code versus what it would be with the raw Python database.

00:14:08.480 --> 00:14:09.540
Yeah, that's fantastic.

00:14:09.540 --> 00:14:11.800
Maybe we should take a step back and talk about the layers.

00:14:11.800 --> 00:14:15.180
And then we could talk about, you know, well, this layer, that layer, it looks like such and such.

00:14:15.180 --> 00:14:17.220
So you have the core and you have the ORM.

00:14:17.220 --> 00:14:19.220
Can you talk about that a little?

00:14:19.400 --> 00:14:25.320
Yeah, so I actually wrote SQLAlchemy in this inside-out way where I didn't even worry about the –

00:14:25.320 --> 00:14:28.420
I mean, I kind of knew what I wanted to do for the ORM, but I didn't even worry about that for a few months.

00:14:28.420 --> 00:14:34.860
I wrote first just the engine, just like the concept of here's how to connect to a database using the Python DB API.

00:14:34.860 --> 00:14:44.020
And here's a way that we can execute a query and get results back in a way that's a little bit nicer than what the raw Python database API gives you.

00:14:44.020 --> 00:14:46.660
It's a little bit nicer and a little more consistent.

00:14:47.160 --> 00:14:54.500
Then the next thing I did, which, you know, was soon after, was I wrote the table metadata system, which I had already done many times at different jobs.

00:14:54.500 --> 00:14:56.940
You know, kind of looked in Fowler's book.

00:14:56.940 --> 00:15:06.260
So you wrote – you create this concept of a Python data structure that mirrors what structures you have in your database.

00:15:06.400 --> 00:15:12.380
So when you have a relational database, it kind of – without using it at all, it has a fixed schema.

00:15:12.380 --> 00:15:17.400
You have, like, tables that are kind of – they're not – we might call them physical tables, which they're really not physical.

00:15:17.400 --> 00:15:18.340
They're just on a disk.

00:15:18.340 --> 00:15:23.280
But they're tables, and the tables have columns, and they're kind of like the things that are going to store your data.

00:15:24.280 --> 00:15:35.760
So you model a mirror of all that stuff in Python so that you can write Python code that refers to this object structure that can then relate directly to how the database is built.

00:15:36.000 --> 00:15:38.020
So that's called database metadata.

00:15:38.020 --> 00:15:41.620
So it means it's information about the structure of your database.

00:15:42.380 --> 00:15:52.960
Then from that, the table metadata objects in SQLAlchemy have all these methods on them, like select and update and, you know, table.select.where.

00:15:52.960 --> 00:15:58.940
And when I say, you know, x.y.z, the dot in Python means that we're calling a method on an object.

00:15:58.940 --> 00:16:05.400
And if you have this pattern where you say an object.call this, and then you get something back, and then you say .call that,

00:16:05.800 --> 00:16:13.180
that's a pattern called method chaining, which means that you keep calling methods on this object that keeps returning a new copy of itself.

00:16:13.180 --> 00:16:20.700
So that's a common pattern used in database query libraries because the method chaining is kind of like you're building up this data structure

00:16:20.700 --> 00:16:23.100
that looks like basically a syntax tree.

00:16:23.100 --> 00:16:31.300
A syntax tree basically represents like some code, but it represents the code as a set of objects connected together in a tree.

00:16:31.300 --> 00:16:32.260
Yeah, that's fantastic.

00:16:32.260 --> 00:16:37.820
I really like the fluent API that you put together there where basically you start with a query, and whatever you do to it,

00:16:37.820 --> 00:16:40.040
what comes out of the query and just lets you build up.

00:16:40.040 --> 00:16:41.840
Here's a where clause.

00:16:41.840 --> 00:16:42.600
Here's an order by.

00:16:42.600 --> 00:16:44.300
Here's a distinct or whatever.

00:16:44.300 --> 00:16:44.900
Yeah.

00:16:44.900 --> 00:16:48.340
At this point, I mean, that pattern is pretty commonplace these days.

00:16:48.340 --> 00:16:50.560
I mean, when I first did it, it was not quite as commonplace.

00:16:50.560 --> 00:16:58.100
I mean, basically Java Hibernate kind of has that pattern, but not in a nice way because Java is just not as slick of a language as Python.

00:16:58.100 --> 00:17:01.540
And some of the, you know, it was a pattern that was around.

00:17:01.540 --> 00:17:03.500
I think SQL object had it as well, you know.

00:17:03.500 --> 00:17:07.100
It builds on that, but then we also did this thing.

00:17:07.100 --> 00:17:16.140
I took this thing right from Ian Bicking's SQL object, which was that you overload Python operators like the double equals operator or the greater than operator,

00:17:16.140 --> 00:17:25.040
so that when you have these little objects that refer to columns and values in a database, you can compare them with the Python comparison operators,

00:17:25.160 --> 00:17:29.860
and they kind of auto-magically create another object like a comparison.

00:17:30.860 --> 00:17:41.060
And if you go through the ORM or the core tutorial on SQL object, the core tutorial would really show how this works at the expression level.

00:17:41.060 --> 00:17:46.000
The ORM tutorial kind of refers to it, but it's more about how to get the ORM going.

00:17:46.420 --> 00:17:48.960
But what I'm talking about here is how the core query system works.

00:17:48.960 --> 00:17:55.560
So basically these Python objects can kind of expressly be combined to create SQL queries.

00:17:56.460 --> 00:17:57.820
And that's the core, really.

00:17:57.820 --> 00:18:02.840
That's really what you get, as well as the fact that it returns objects, it returns Python objects.

00:18:02.840 --> 00:18:06.480
Like if you query for dates, you'll get a Python date object back.

00:18:06.480 --> 00:18:09.700
If you query for intervals, you can get a Python time delta back.

00:18:09.700 --> 00:18:14.720
And there's all systems for building your own custom types if you want to get JSON back, things like that.

00:18:15.300 --> 00:18:17.260
And then the ORM builds on top of that.

00:18:17.260 --> 00:18:22.540
So if you're starting out, I mean, if you really want to learn the library from the ground up, I would start with the core and then move to the ORM.

00:18:22.540 --> 00:18:29.880
If you want to get some quick results for a program you're writing, you might want to start with the ORM first and then do a deeper dive later.

00:18:29.880 --> 00:18:34.920
I've kind of switched those directions around over the years, and I think there's just two ways to learn.

00:18:34.920 --> 00:18:37.220
You can learn from the outside in or learn from the inside out.

00:18:37.800 --> 00:18:45.380
And it's kind of your choice as to do you want immediate gratification or do you want more fundamental knowledge first?

00:18:45.380 --> 00:18:45.800
Right.

00:18:45.800 --> 00:18:48.540
Well, and how much are you going to be depending on the library?

00:18:48.540 --> 00:18:50.020
Right.

00:18:50.020 --> 00:18:55.540
If it's your core business app and it's really important that this thing keeps working, maybe you should deeply understand it.

00:18:55.540 --> 00:18:59.720
If it's just a little blog you're throwing together, do it quick and then come back and learn it more deeply.

00:18:59.720 --> 00:18:59.980
Right.

00:18:59.980 --> 00:19:02.060
Start with the ORM and then go to the core to understand that.

00:19:02.060 --> 00:19:02.400
Yeah.

00:19:02.400 --> 00:19:05.040
Well, everyone I've seen, everyone's got a different way to do it.

00:19:05.040 --> 00:19:06.660
So I just try to present both.

00:19:07.440 --> 00:19:11.920
And also for a really long time, the core didn't have that name core, and that was a problem.

00:19:11.920 --> 00:19:17.300
It was just called SQLAlchemy, and then there was SQLAlchemy ORM, and people didn't really, you know, obviously understand.

00:19:17.300 --> 00:19:20.280
There's this whole thing with SQLAlchemy that is not the ORM.

00:19:20.280 --> 00:19:27.360
So I introduced the word core probably about, you know, five years ago to say, hey, there's this whole other thing.

00:19:27.360 --> 00:19:33.780
And I divided the docs into like two big left and right sections so that you can see there's this whole other thing.

00:19:33.780 --> 00:19:37.400
There's only the core, and it's like an entire set, the right half of the docs.

00:19:37.820 --> 00:19:38.100
Yeah.

00:19:38.100 --> 00:19:40.480
I think you have them almost as columns next to each other.

00:19:40.480 --> 00:19:40.620
I do.

00:19:40.620 --> 00:19:41.660
I kind of imitated.

00:19:41.660 --> 00:19:45.680
I think Django's documentation at the time had that thing going on with the two columns.

00:19:45.680 --> 00:19:48.380
So I did that to say like, look, this is whole core thing.

00:19:48.380 --> 00:19:52.340
If you don't like the ORM, because I knew the ORM was going to be, the ORM is more opinionated.

00:19:52.340 --> 00:19:54.460
I knew it would be controversial.

00:19:54.960 --> 00:19:57.160
I knew that I personally didn't like ORMs very much.

00:19:57.160 --> 00:19:59.580
And this was going to be the ORM that I was actually going to like.

00:19:59.580 --> 00:20:02.000
But I knew all the other commercials like me were going to hate it.

00:20:02.000 --> 00:20:04.320
And those people still around today who hate ORMs.

00:20:04.320 --> 00:20:06.620
So I wanted to say like, look, I get it.

00:20:06.620 --> 00:20:07.620
Don't use the ORM.

00:20:07.620 --> 00:20:08.640
Use the core part.

00:20:08.720 --> 00:20:12.620
And then when you kind of use it for a long time, you realize, hey, this redundant thing

00:20:12.620 --> 00:20:14.920
I'm doing over and over again could just kind of be automated by the ORM.

00:20:14.920 --> 00:20:17.820
Then you can use the ORM when you see there's a need for it.

00:20:17.820 --> 00:20:19.880
So that's kind of how I saw it being used.

00:20:19.880 --> 00:20:21.120
But other people go the other way.

00:20:21.120 --> 00:20:21.780
Sure.

00:20:21.940 --> 00:20:26.360
So you've sort of talked about the learning perspective of when I might use the core versus

00:20:26.360 --> 00:20:26.840
the ORM.

00:20:26.840 --> 00:20:29.720
But are there like performance or other patterns?

00:20:29.720 --> 00:20:33.380
Like what other considerations do I choose between the core and the ORM model?

00:20:33.380 --> 00:20:35.260
There's a lot of performance.

00:20:35.260 --> 00:20:36.300
There's a lot of, yeah.

00:20:36.300 --> 00:20:42.040
So kind of the unspoken drama of SQLogamy for all the years it's been out has been the performance

00:20:42.040 --> 00:20:42.360
drama.

00:20:42.360 --> 00:20:50.280
Because coming from a Java background, you know, in the 90s, early 2000s, at least back then,

00:20:50.340 --> 00:20:55.140
when we did Java programming, we really didn't pay too much attention to the way our code

00:20:55.140 --> 00:20:57.100
was written might be slower or faster.

00:20:57.100 --> 00:21:00.500
I mean, obviously, you know, if you're doing like order of N, if you want to do something

00:21:00.500 --> 00:21:02.400
once rather than N times, things like that.

00:21:02.400 --> 00:21:07.960
But just if you do two nested loops or you do this special library call instead of the

00:21:07.960 --> 00:21:09.180
loop, is that faster or slower?

00:21:09.180 --> 00:21:13.080
You don't see that much in Java because all the standard libraries in Java are written in

00:21:13.080 --> 00:21:13.880
Java as well.

00:21:13.880 --> 00:21:18.280
But with Python, it's mostly CPython.

00:21:19.060 --> 00:21:24.240
There's this big gap where if you do something in a for loop, it might be very slow.

00:21:24.240 --> 00:21:28.320
And you do the exact same thing with like the zip call.

00:21:28.320 --> 00:21:31.320
It's a million times faster because the zip call is written in C.

00:21:31.820 --> 00:21:37.080
So there's this whole thing with Python where you have to kind of use the standard library

00:21:37.080 --> 00:21:42.480
as much as you possibly can because you want as much data processing to happen in native C

00:21:42.480 --> 00:21:45.260
functions rather than in Python.

00:21:45.260 --> 00:21:48.080
So I didn't know that in the first few years of writing SQLAlchemy.

00:21:48.080 --> 00:21:50.960
And I didn't really know much at all about what was fast and what was slow.

00:21:50.960 --> 00:21:53.260
And that's pretty common when someone starts with Python.

00:21:54.080 --> 00:21:58.640
So up and through to version three, the thing performed horribly.

00:21:58.640 --> 00:22:03.340
And then there was a lot of blog posts and other competitors that came out to try to challenge

00:22:03.340 --> 00:22:04.720
the performance of SQLAlchemy.

00:22:04.720 --> 00:22:09.100
And I went on kind of a multi-year mission to improve the performance of the system.

00:22:09.100 --> 00:22:14.000
So these days, and the most recent incarnation of that has been more performance improvements

00:22:14.000 --> 00:22:19.340
in version 1.0, but also a lot of new documentation that I've added in version 1.0 that refers specifically

00:22:20.020 --> 00:22:22.140
to the notion of performance.

00:22:22.140 --> 00:22:23.400
It's actually in our FAQ now.

00:22:23.400 --> 00:22:24.540
There's a whole section of my performance.

00:22:24.540 --> 00:22:28.280
There's entire sections of how to profile an application.

00:22:28.280 --> 00:22:35.180
It's, and there's a lot of sample tests, example suites in version 1.0 where you can compare

00:22:35.180 --> 00:22:42.100
different methods of doing the same thing for their performance slash ease of use tradeoff.

00:22:42.100 --> 00:22:47.300
So I have examples of, you know, if you want to do this query, how fast is this query using

00:22:47.300 --> 00:22:52.400
the core by itself, how fast is this query using the ORM, but loading individual columns,

00:22:52.400 --> 00:22:57.060
how fast is this query if we use the ORM full blown, and what are the tradeoffs, and how

00:22:57.060 --> 00:22:59.640
can we switch between those two systems?

00:22:59.640 --> 00:23:01.300
So that's really interesting.

00:23:01.300 --> 00:23:04.680
I have a couple of performance questions I'd like to ask about.

00:23:04.680 --> 00:23:09.700
First, you said you went on this multi-year mission to speed it up, which is, that's a lot

00:23:09.700 --> 00:23:10.820
of dedication, and that's awesome.

00:23:10.820 --> 00:23:13.460
What kind of performance gains did you get?

00:23:13.460 --> 00:23:15.760
Did you like double the speed, or what happened?

00:23:15.820 --> 00:23:20.040
Over the course of, since version 3, I'm sure we've quadrupled the speed of it, because

00:23:20.040 --> 00:23:23.460
it was really, really slow in version 2 and 3.

00:23:23.460 --> 00:23:27.440
I mean, there's things, there's all kinds of Python idioms that change performance so

00:23:27.440 --> 00:23:32.980
dramatically, and it's kind of a way of life for me now, but if you don't know, if you come

00:23:32.980 --> 00:23:36.280
from like C#, it's probably going to be really painful to learn.

00:23:36.400 --> 00:23:40.800
Like, if you do things like, if you want to check if a key is in a dictionary and add

00:23:40.800 --> 00:23:46.640
a value, if you do that check with using, if you basically try to get the key, and then

00:23:46.640 --> 00:23:49.680
it raises a key error, and you catch the key error, and you say, okay, let's put the value

00:23:49.680 --> 00:23:54.440
in now, that's going to be way, way, way, like five times slower than doing it by just doing

00:23:54.440 --> 00:23:56.280
a if key in dict first.

00:23:56.280 --> 00:23:57.420
Right, absolutely.

00:23:57.420 --> 00:24:01.220
Assuming the dictionary is usually not going to have the value.

00:24:02.080 --> 00:24:06.200
Yeah, because it's actually the throwing the exception that is the raise, yeah, it's super

00:24:06.200 --> 00:24:06.560
expensive.

00:24:06.560 --> 00:24:07.400
Yeah, crazy.

00:24:07.400 --> 00:24:08.720
So that's one.

00:24:08.720 --> 00:24:13.420
Then there were things like, if you have an object, and you want, there's this pattern called

00:24:13.420 --> 00:24:16.800
the proxy pattern, it's a programming pattern, where if you have an object, it has a bunch

00:24:16.800 --> 00:24:20.800
of attributes, and as you call those attributes, it's actually sending the message to some other

00:24:20.800 --> 00:24:24.220
object that's kind of embedded into it, it's called a proxy, and that's a very common pattern.

00:24:24.220 --> 00:24:28.280
If you'd use the, there's a really easy way to do the proxy pattern in Python, which is

00:24:28.280 --> 00:24:31.580
use this magic method called get at her, it's double under get at her.

00:24:31.580 --> 00:24:35.460
And you get at her is past the name of the attribute trying to get, and then you say,

00:24:35.460 --> 00:24:38.520
oh, let's call that attribute from our little nested object.

00:24:38.520 --> 00:24:44.980
Crazy slow if you do it that way, compared to if you actually put explicit descriptors on

00:24:44.980 --> 00:24:48.440
the proxy class that go directly to the inner proxy class.

00:24:48.440 --> 00:24:53.020
So very early SQL alchemies, I use a lot of get at her, and people said, hey, this get

00:24:53.020 --> 00:24:53.940
at her is crazy slow.

00:24:53.940 --> 00:24:57.440
This thing is using get at her everywhere, and it's super slow, because they're all function

00:24:57.440 --> 00:24:59.600
calls, and function calls are very expensive in Python.

00:24:59.600 --> 00:25:01.700
So, got rid of all the get at her.

00:25:01.700 --> 00:25:07.420
You do this procedure in programming, you do a lot with Python, which is called inlining.

00:25:07.420 --> 00:25:13.540
Inlining means if you have some operations where method A calls method B, calls method C, calls

00:25:13.540 --> 00:25:15.340
method D, you inline them.

00:25:15.340 --> 00:25:20.360
You take all the code and you unwrap it into one big function, so that there's no function

00:25:20.360 --> 00:25:21.320
calls within it.

00:25:22.540 --> 00:25:28.040
Inlining is something that you kind of get someone for free if you use a newer interpreter

00:25:28.040 --> 00:25:33.000
like the PyPy interpreter, which has a just-in-time compiler that does a lot of inlining for you.

00:25:33.000 --> 00:25:33.760
That's really interesting.

00:25:33.760 --> 00:25:38.920
I was going to ask you how SQLAlchemy works with PyPy, and have you tried Pysten from the

00:25:38.920 --> 00:25:40.720
Dropbox project?

00:25:41.100 --> 00:25:42.800
I haven't used Pysten yet.

00:25:42.800 --> 00:25:46.660
Pysten looks like, I mean, I haven't looked at it much.

00:25:46.660 --> 00:25:51.960
It looks like kind of the next incarnation of a thing called Unladen Swallow, which, if you

00:25:51.960 --> 00:25:56.800
remember, that was, I think, a Google project where they were trying to use the LLVM compiler,

00:25:56.800 --> 00:25:59.560
and I think that's what they're doing with the Pysten thing.

00:25:59.560 --> 00:26:00.300
Oh, interesting.

00:26:00.300 --> 00:26:01.600
The same one that Apple uses, yeah.

00:26:02.040 --> 00:26:08.160
Yeah, I don't know what I'm talking about, but there's a compiler just-in-time platform

00:26:08.160 --> 00:26:09.800
called the LLVM, and I don't know much about it.

00:26:09.800 --> 00:26:14.780
Yeah, that was Chris Lattner's project, and he's the guy who created Swift, and I think

00:26:14.780 --> 00:26:17.620
they're using it there for Swift as well, so interesting.

00:26:17.620 --> 00:26:19.260
Yeah, I haven't used Pysten.

00:26:19.260 --> 00:26:22.300
Yeah, I mean, I see some news about it.

00:26:22.300 --> 00:26:25.340
I know Guido works at Dropbox now, so I guess we'll see.

00:26:25.340 --> 00:26:31.420
Yeah, that should give Pysten a little more credibility than maybe just a random project,

00:26:31.420 --> 00:26:31.680
right?

00:26:31.860 --> 00:26:32.100
Yeah.

00:26:32.100 --> 00:26:52.840
Michael here.

00:26:52.840 --> 00:26:57.260
Thank you so much for listening to and spreading the word about Talk Python TV.

00:26:57.260 --> 00:27:00.900
The response to the podcast continues to be wonderful and humbling.

00:27:01.220 --> 00:27:03.780
I have a quick comment about supporting and sponsoring the show.

00:27:03.780 --> 00:27:07.480
I'm still looking to line up stable corporate sponsorships, but I wanted to tell you about

00:27:07.480 --> 00:27:11.760
a community-based campaign I'm launching to allow listeners to directly support the show.

00:27:11.760 --> 00:27:14.180
We are running a Patreon campaign.

00:27:14.180 --> 00:27:17.920
You might not have heard about Patreon, but it's kind of like Kickstarter for things like

00:27:17.920 --> 00:27:22.800
podcasts, which release frequent small deliverables rather than one-off large engineering projects.

00:27:23.200 --> 00:27:31.980
Visit patreon.com/mkennedy.com/mkennedy and watch the video to see how you can donate as little

00:27:31.980 --> 00:27:34.940
as $1 per episode to support Talk Python To Me.

00:27:34.940 --> 00:27:41.200
This is your chance to ensure that the Python community continues to have a strong public voice.

00:27:41.200 --> 00:27:45.200
Consider supporting us today at patreon.com/mkennedy and thanks for listening.

00:27:45.200 --> 00:27:55.460
It works with pypy though pretty well, Sequel Alchemy.

00:27:55.460 --> 00:28:12.100
Yeah, it took a long time for pypy because the pypy itself had a lot of quirks years ago.

00:28:12.100 --> 00:28:18.360
And yeah, pypy is on our continuous integration system.

00:28:18.360 --> 00:28:22.920
pypy doesn't use the same kind of garbage collection that CPython does.

00:28:22.920 --> 00:28:30.220
And garbage collection is kind of a big deal when you write a database library because it affects

00:28:30.220 --> 00:28:34.760
when you have reference cycles getting garbage collected.

00:28:34.760 --> 00:28:39.680
A lot of our tests would fail because they were relying upon things being garbage collected automatically.

00:28:39.940 --> 00:28:42.520
And that doesn't really necessarily happen as much with pypy.

00:28:42.520 --> 00:28:47.760
So basically, if you have a test that doesn't tear itself down correctly and you still have a connection open

00:28:47.760 --> 00:28:54.100
that access a table and you run Postgres, the teardown of your test will refuse to drop a table

00:28:54.100 --> 00:28:55.460
because Postgres still has a lock on it.

00:28:55.460 --> 00:28:59.120
So there's a lot of issues like that that pypy keeps us very honest on.

00:28:59.120 --> 00:29:00.160
So that's good.

00:29:00.160 --> 00:29:06.460
pypy seems to use more memory, but it runs definitely much faster.

00:29:06.460 --> 00:29:09.580
And I think it's a great interpreter and I'm really hopeful for pypy.

00:29:09.580 --> 00:29:10.700
Yeah, that's really interesting.

00:29:10.700 --> 00:29:11.680
So we're into it.

00:29:11.680 --> 00:29:12.560
Yeah, for sure.

00:29:12.560 --> 00:29:13.860
Cool.

00:29:13.860 --> 00:29:23.820
So one thing that I think is nice about the ORM version or the ORM layer is I can get these classes back from my database

00:29:23.820 --> 00:29:27.300
and make changes to them and just basically call commit on the session.

00:29:27.300 --> 00:29:29.460
So can you talk about the change tracking?

00:29:29.460 --> 00:29:31.600
So that's the unit of work.

00:29:31.600 --> 00:29:32.320
So this is the thing.

00:29:32.320 --> 00:29:36.760
When I first read SQLogamy, that was the part of this whole thing that I was not even familiar with myself

00:29:36.760 --> 00:29:41.580
because I read Fowler's book and he's like, you know, enterprise software uses this unit of work thing

00:29:41.580 --> 00:29:44.700
where you just kind of accumulate the changes and then you just push them all in one shot.

00:29:44.700 --> 00:29:46.340
And I was like, wow, that's crazy.

00:29:47.180 --> 00:29:49.680
And, you know, I could see how it worked.

00:29:49.680 --> 00:29:50.860
I'm like, that's really, you know, pretty easy.

00:29:50.860 --> 00:29:57.740
You just track it so that all your inserts and your deletes and your updates get kind of put into a big bucket and you sort them out.

00:29:58.220 --> 00:30:02.120
And I wrote a very simplistic version of that for SQLogamy 0.1.

00:30:02.120 --> 00:30:06.380
It was really bad because I really didn't quite know how to write it.

00:30:06.380 --> 00:30:15.140
I was like, I mean, I knew how to write it, but I did this kind of coding, which I did more back then probably that I maybe would term seat of your pants coding,

00:30:15.140 --> 00:30:19.820
where you kind of know that this part connected that part and this part connected that part.

00:30:19.820 --> 00:30:22.780
But my brain couldn't see the whole thing at once.

00:30:23.960 --> 00:30:30.460
And it produced, if you look, you can get the code, go look at old 0.2 of the unit of work and you will be like, what is that?

00:30:30.460 --> 00:30:31.820
It's crazy.

00:30:31.820 --> 00:30:37.280
But when I first saw it work, I was like, that's amazing.

00:30:37.280 --> 00:30:38.660
Yeah, it's super nice.

00:30:38.660 --> 00:30:39.500
It's really easy.

00:30:39.500 --> 00:30:40.520
Because I was logging to SQL.

00:30:40.520 --> 00:30:40.860
Yeah.

00:30:40.860 --> 00:30:44.880
And I was like, that's, I think I said, this, that's when it got really, got me really jazzed.

00:30:44.880 --> 00:30:46.180
Because like, this thing's going to be big.

00:30:46.180 --> 00:30:53.300
When I saw it do that commit the first time and I saw it, all the bros go, I was like, wow, people are going to love this thing.

00:30:53.580 --> 00:30:54.380
Yeah, that's really cool.

00:30:54.380 --> 00:30:59.200
You know, I actually don't know the answer to this question, but do you have some sort of like optimistic concurrency tracking?

00:30:59.200 --> 00:31:05.280
Like if two people pull it back, one person makes a change and saves it, the other one takes that object, tries to save it.

00:31:05.280 --> 00:31:06.080
What's the story there?

00:31:06.080 --> 00:31:12.920
There's a little bit of pattern that we have in there that I lifted entirely from what Hibernate does.

00:31:13.720 --> 00:31:23.980
There's a pattern using a version counter that I don't know that it's that widely used because it kind of interacts with the isolation level you're using in your transaction.

00:31:23.980 --> 00:31:32.720
I mean, really, the optimistic concurrency is something you can get in your database just if you set your isolation levels the way you want, if you set them higher.

00:31:34.180 --> 00:31:41.960
The version counter idea is that you load the row from the database and the row will have a special column that's the version and say the version is five.

00:31:41.960 --> 00:31:43.240
Do I have to indicate that?

00:31:43.240 --> 00:31:46.440
Sorry, do I have to indicate that specially on my class?

00:31:46.740 --> 00:31:49.460
Yeah, there's a whole, yeah, it's in the mapping option.

00:31:49.460 --> 00:31:58.180
So you have a column that you declare as part of the versioning and then the mapper will actually load that in that number or it could be a date stamp if you want.

00:31:58.180 --> 00:32:06.560
And it will add, when you go to update the row later, it will actually add that column to the criteria for the update statement.

00:32:06.560 --> 00:32:14.180
So it'll be like update row, set whatever, where primary key is blah and version equals five.

00:32:14.740 --> 00:32:18.660
And then if the update does not match any rows, that means version five is gone.

00:32:18.660 --> 00:32:21.360
It means someone else changed version five underneath you.

00:32:21.360 --> 00:32:22.740
And then they'll throw an error.

00:32:22.740 --> 00:32:26.680
And that's basically one simple way of doing optimistic concurrency control.

00:32:26.680 --> 00:32:29.840
It only applies to updates and deletes.

00:32:29.840 --> 00:32:32.160
It doesn't apply to inserts.

00:32:32.160 --> 00:32:33.860
Inserts is a whole different ballgame.

00:32:33.860 --> 00:32:36.360
People want to be able to insert some value.

00:32:36.360 --> 00:32:39.820
And if another thread tries to insert the same value, they want to use the one that's existing.

00:32:39.820 --> 00:32:40.620
That's a hard pattern.

00:32:40.620 --> 00:32:44.060
There's ways to do that in SQL, but none of them are automatic.

00:32:44.200 --> 00:32:45.880
You have to kind of roll it the way you want to roll it.

00:32:45.880 --> 00:32:46.900
Yeah, that's interesting.

00:32:46.900 --> 00:32:51.840
But I think even that level of updates is really a nice feature to have.

00:32:51.840 --> 00:32:52.280
Yeah.

00:32:52.280 --> 00:32:54.260
So you talked about the mapping a little bit.

00:32:54.260 --> 00:33:00.980
When I create these classes, basically what I do is I create a class that derives from some kind of declarative base instance I've created.

00:33:00.980 --> 00:33:03.040
And then I declare the columns.

00:33:03.040 --> 00:33:03.840
And how do I do that?

00:33:05.000 --> 00:33:13.460
So the declarative system is, again, that was a system that I added to SQLAlchemy back when I did version 5 and I worked for the Charlie Rose website.

00:33:13.460 --> 00:33:15.900
We didn't have any declarative system built.

00:33:15.900 --> 00:33:18.760
And we had a cheap one that was built into the library.

00:33:18.760 --> 00:33:22.320
And then we had a third-party thing called Elixir, which didn't quite do what I needed.

00:33:22.320 --> 00:33:29.360
So I wrote a new version, kind of the third version of the declarative system, which is basically you have this base class that you can inherit from.

00:33:29.360 --> 00:33:35.720
The original idea of SQLAlchemy was that you write – you would declare table metadata using just core.

00:33:35.720 --> 00:33:36.960
You have a table object.

00:33:36.960 --> 00:33:38.980
The table object has these column objects.

00:33:39.480 --> 00:33:42.000
The name of the table would match the table in your database.

00:33:42.000 --> 00:33:43.880
The names of the columns would match the names of the columns.

00:33:43.880 --> 00:33:47.420
They have little data types like a string or a date time or an integer.

00:33:47.420 --> 00:33:52.820
And then you would take your class, which would just be kind of a blank Python class, and say map.

00:33:52.820 --> 00:33:56.100
You'd map the class to the table.

00:33:56.100 --> 00:34:02.200
So the declarative idea is how most ORMs that we deal with today look, where you don't see those two separate things.

00:34:02.200 --> 00:34:05.580
You see kind of this class that has, like, class, my class.

00:34:05.660 --> 00:34:11.460
And then it has, like, id equals integer, and then first name equals string, and last name equals string.

00:34:11.460 --> 00:34:12.580
It looks like Django.

00:34:12.580 --> 00:34:14.440
It looks like every other ORM you see.

00:34:14.440 --> 00:34:16.400
So the declarative thing was added to do that.

00:34:16.400 --> 00:34:19.160
So you basically use the concept of a Python class.

00:34:19.160 --> 00:34:23.600
You say class, and then you my class, and then you put a colon.

00:34:23.600 --> 00:34:26.680
And then you indent, because Python, we're doing white space.

00:34:26.680 --> 00:34:33.760
And then you basically declare variables that refer to the columns that will be in your database table.

00:34:34.900 --> 00:34:39.500
And then as far as how that creates the database or the database exists already, that's kind of a separate concern.

00:34:39.500 --> 00:34:46.340
But you're basically defining what your schema will look like in line with your class definition.

00:34:46.340 --> 00:34:50.100
And it's a pretty common pattern that is familiar to everyone at this point.

00:34:50.100 --> 00:34:52.260
Yeah, I think it is a pretty familiar pattern.

00:34:52.260 --> 00:34:53.260
It's really nice.

00:34:53.460 --> 00:35:01.500
And it also ties into, when you're talking about doing the queries, those columns, basically, you can do operations on them, right?

00:35:01.500 --> 00:35:03.540
So I could, say, create, like, a query of book.

00:35:03.540 --> 00:35:10.420
And I'd say order by, you know, book.name.desc or descending or something like that, right?

00:35:10.480 --> 00:35:16.560
So they kind of play multiple roles, letting you do the queries as well as defining the schema and the constraints and all that, right?

00:35:16.560 --> 00:35:17.600
Yeah.

00:35:17.600 --> 00:35:21.060
So a funny story also is that it wasn't like that in the beginning.

00:35:21.060 --> 00:35:28.220
It used to be that you'd have your mapper and your class, but the class itself wouldn't have these attributes directly on it.

00:35:28.280 --> 00:35:30.680
It used to be you had to still talk to the table object.

00:35:30.680 --> 00:35:39.160
So if you had, you know, class user and it mapped to a table object called users, you would still have to query in terms of users.c.firstname.

00:35:39.160 --> 00:35:43.580
It wasn't actually on the class object because I didn't feel comfortable doing that for a while.

00:35:43.580 --> 00:35:47.780
And even though SQL object did do that, I kind of was hesitant to do that.

00:35:47.780 --> 00:35:58.460
But then once, I think maybe in version 4, I said, hey, you know, actually I can put the attributes on the class and have them be the actual column objects that we – they're actually not column.

00:35:58.460 --> 00:36:01.300
They're actually a wrapper around the column, but it looks like the column object.

00:36:01.300 --> 00:36:01.780
So, yeah.

00:36:01.780 --> 00:36:12.420
And then that led to this whole new thing called the hybrid property, which is like the fact that Python has this amazing symmetry in that everything is ultimately an object.

00:36:12.420 --> 00:36:14.100
If you have a class, it's still an object.

00:36:14.320 --> 00:36:20.500
So if you say myclass.foo versus an instance of myclass.foo, they look the same in code.

00:36:20.500 --> 00:36:23.460
And that kind of blew my mind.

00:36:23.460 --> 00:36:31.700
That was kind of a big discovery I made about Python like maybe five years into it that Python has this – I mean, I knew I loved Python's object model all the way through.

00:36:31.700 --> 00:36:35.540
But really, the hybrid thing really blew my mind.

00:36:35.740 --> 00:36:46.460
And there's a – I think I wrote a blog post a long time ago that I could probably point you to where I show – look at this hybrid thing where you can have a class with a method that does something at the class level and something else at the instance level.

00:36:46.460 --> 00:36:47.380
And it just was amazing.

00:36:47.380 --> 00:36:51.820
And I use it a lot on some subsequent jobs, and we did really amazing stuff with it.

00:36:51.920 --> 00:36:54.520
It's one thing to sort of realize that that feature is there.

00:36:54.520 --> 00:37:02.700
It's the other to say, let's use that to make a data access layer really work super well and make it – you define one thing, and it has this dual purpose, which is really cool.

00:37:02.700 --> 00:37:03.260
Yeah.

00:37:03.340 --> 00:37:11.100
There was a guy – I think his name was Ant Asma, and he was working on SQL for me a long time ago, and he first showed me a pattern that had that hybrid thing.

00:37:11.100 --> 00:37:16.620
And I was like, wow, and I really dove into it in the coming years, and I really realized it's a very handy pattern.

00:37:16.620 --> 00:37:17.780
Excellent.

00:37:17.780 --> 00:37:20.540
So you're talking about creating these columns and stuff.

00:37:20.540 --> 00:37:26.060
What if I've got a field that's – or a column that's not nullable or it needs a default value?

00:37:26.060 --> 00:37:26.760
How do I do that?

00:37:26.760 --> 00:37:27.080
Yeah.

00:37:27.080 --> 00:37:29.960
So the table metadata part – so say we're back in core.

00:37:29.960 --> 00:37:30.940
We're not doing over them again.

00:37:30.940 --> 00:37:38.420
So we're back in core – one of the goals of the core was that it would be this comprehensive system of building a whole schema from scratch.

00:37:38.420 --> 00:37:45.220
So meaning if you have a blank database, you can build up all of your objects in Python and then say, you know, metadata.createAll.

00:37:45.220 --> 00:37:54.480
And that will emit what we call data definition language, DDL, which we know as createTableStatements and createSequence and createIndex.

00:37:54.480 --> 00:37:56.640
It will emit all those things for you.

00:37:56.640 --> 00:38:11.080
So in order to make that feasible, we have to also have support for attributes like if a column is nullable or not, if a column is a primary key, if a column is indexed, if there's a unique constraint on a set of columns.

00:38:12.420 --> 00:38:17.420
So SQL Alchemist schema system has objects and flags that represent all of these concepts.

00:38:17.420 --> 00:38:20.340
And it's pretty comprehensive.

00:38:20.340 --> 00:38:24.960
And we have full support for all kinds of data types and numerics and floats.

00:38:25.480 --> 00:38:28.280
And we have server defaults.

00:38:28.280 --> 00:38:31.740
And we have every common thing except for triggers.

00:38:31.740 --> 00:38:34.160
We don't have a system for triggers.

00:38:34.160 --> 00:38:36.540
You can define your own system for triggers.

00:38:36.540 --> 00:38:37.520
And we don't have store procedures.

00:38:37.840 --> 00:38:49.720
So all the main things that you normally, your day-to-day database schema stuff like tables, columns, constraints, foreign keys, indexes, nullability, and defaults are all first class.

00:38:49.720 --> 00:38:52.860
Other things you can define like sequences we have.

00:38:52.860 --> 00:39:01.480
Other things like triggers and store procedures, you can define your own Pythonic little objects that will do that for you but more in a database-specific way.

00:39:02.240 --> 00:39:10.080
So anyway, when you do the ORM and you're doing declarative mapping, you're still using core column objects in your declarative mapping.

00:39:10.080 --> 00:39:16.220
So when you make a column object, you can still specify all the table attributes as well as you do your declarative mapping.

00:39:16.220 --> 00:39:17.460
So that's kind of the answers.

00:39:17.460 --> 00:39:19.000
They kind of meld together.

00:39:19.000 --> 00:39:22.680
And it was not that way in the beginning, but it evolved that way over the years to work that way.

00:39:22.680 --> 00:39:23.840
Yeah, it's really smooth.

00:39:23.840 --> 00:39:28.660
So I have these classes, and I can map them to existing databases.

00:39:28.760 --> 00:39:32.980
And you also said it emits DDL, which I can create my tables from, right?

00:39:32.980 --> 00:39:33.720
Yeah.

00:39:33.720 --> 00:39:35.120
That's just a function call, isn't it?

00:39:35.120 --> 00:39:36.140
Go to my declarative base.

00:39:36.140 --> 00:39:37.540
Was it create all, something like that?

00:39:37.540 --> 00:39:37.800
Yeah.

00:39:37.800 --> 00:39:38.320
Yeah.

00:39:38.320 --> 00:39:44.040
So the metadata object is kind of this collection of tables and other things, mostly tables.

00:39:44.040 --> 00:39:51.020
And you could say metadata.createAll, and you give it an engine to point at, like a database connection.

00:39:51.020 --> 00:39:56.320
And it will go on that database and see which tables don't exist and create all the tables that don't exist yet.

00:39:56.320 --> 00:39:57.600
That'll create them from scratch.

00:39:57.680 --> 00:39:59.020
What if I say add a column?

00:39:59.020 --> 00:40:00.020
Right.

00:40:00.020 --> 00:40:03.020
So the whole thing with adding a column and doing that, we call that migration.

00:40:03.020 --> 00:40:06.200
So, you know, as we go through this, it's funny.

00:40:06.200 --> 00:40:07.860
Nothing in SQLAlchemy was there in day one.

00:40:07.860 --> 00:40:10.300
Everything was like I had to figure it out and learn it.

00:40:10.300 --> 00:40:15.580
Like I actually did a talk for SQLAlchemy version 4, and they asked me in the audience, what do you do about database migrations?

00:40:15.580 --> 00:40:18.820
I'm like, well, I don't really know much about migrations, Automata.

00:40:18.820 --> 00:40:19.380
I used to do them.

00:40:19.380 --> 00:40:20.160
I do them by hand.

00:40:20.660 --> 00:40:21.920
So I don't really have a migration tool.

00:40:21.920 --> 00:40:25.380
So some people wrote a tool called SQLAlchemy Migrate.

00:40:25.380 --> 00:40:28.760
And that went pretty well for a while and is still in use today.

00:40:28.760 --> 00:40:31.760
And then I eventually wrote a newer version called Alembic.

00:40:32.260 --> 00:40:35.580
So that's kind of a separate package that you can download.

00:40:35.580 --> 00:40:40.600
And Alembic builds on top of SQLAlchemy as does SQLAlchemy migrate, which is still in production.

00:40:41.820 --> 00:40:54.780
And that defines a whole bunch of new operations on top of SQLAlchemy that are specific to being able to add columns, to alter the types of columns, and alter the nullability, you know, to remove columns, rename them, things like that.

00:40:54.780 --> 00:40:58.940
And my database migrations is a whole big issue unto itself.

00:40:58.940 --> 00:41:09.280
So it took me several years to really get my head around that, to, again, use that in production on the job because the best way to write open source code is to be, you know, what we say, eating your own dog food on the job.

00:41:09.280 --> 00:41:10.480
So I've used Alembic a lot.

00:41:10.480 --> 00:41:12.640
Alembic is now really widely used.

00:41:12.640 --> 00:41:14.820
It's very prominent within OpenStack, which I work for.

00:41:14.820 --> 00:41:19.980
And that's kind of the path to doing migration is kind of separate from SQLAlchemy core itself.

00:41:19.980 --> 00:41:20.880
It's kind of built on top.

00:41:20.880 --> 00:41:22.240
Yeah, that's cool.

00:41:22.240 --> 00:41:24.140
How about relationships?

00:41:24.140 --> 00:41:29.680
Like if I've got a bookstore and I've got categories and there's books in the categories and reviews about the books and so on.

00:41:29.680 --> 00:41:40.200
So that aspect of object relational mapping is really where object relational mapping starts to look like this concept that is really going to change.

00:41:40.200 --> 00:41:46.500
The way a relational database looks compared to the way an object jointed data model looks.

00:41:46.540 --> 00:42:01.380
So in relational schemas, if we want to have a table that refers like a row in this table, it's like say it's an account row or something, and it needs to refer to like some other related concept like an address.

00:42:01.780 --> 00:42:18.020
In relational database, we use something called a foreign key constraint, which means that we're going to – you don't have to actually use the constraint, but you would say that this particular column or set of columns in our account table actually refers to the primary key of a different table somewhere else.

00:42:18.820 --> 00:42:20.240
And that's called a foreign key.

00:42:20.240 --> 00:42:26.620
And the constraint in the database makes sure that the value you put in those columns actually exists in the remote table.

00:42:26.620 --> 00:42:41.820
So in ORM, ORMs have this concept of either making a one-to-many or a many-to-many or a many-to-one relationship, which models in object-oriented terms what that looks like in terms of collections.

00:42:41.820 --> 00:42:53.720
So if I have – in all of our tutorials, we have this concept of a user object, and the user has one or more email addresses, and it's a really old example, but I keep going back to it because I just know it really well.

00:42:53.720 --> 00:43:04.360
So your address table in your database will have a foreign key called here's an email address, and what user is this – what user is the owner of this address?

00:43:04.360 --> 00:43:08.900
It'll have a column called user ID or something, which will refer to the primary key of the user table.

00:43:09.460 --> 00:43:14.560
So the user table kind of refers to – I mean, the address table refers to the user table.

00:43:14.560 --> 00:43:23.040
In your model, you want to have the user object has a one-to-many to the address class.

00:43:23.040 --> 00:43:29.520
So it's kind of – if you define a one-to-many, it's kind of defining it in the other direction, like user points to address.

00:43:29.520 --> 00:43:33.900
But in your relational database, it looks more like address points to user.

00:43:34.480 --> 00:43:40.940
But it turns out that every one-to-many relationship in object-oriented land is the same as the many-to-one in the other way.

00:43:40.940 --> 00:43:49.460
So SQLAlchemy basically lets you make the one-to-many and the many-to-one at the same time using this concept called a backref.

00:43:49.460 --> 00:43:55.360
This is a little bit rambly, but if you look at our tutorial, it's laid out in a much more organized fashion.

00:43:56.100 --> 00:44:05.780
So, yeah, there's basically an object – there's a function in SQLAlchemy or called relationship where you associate it with a parent class and then you give it a target class.

00:44:05.780 --> 00:44:13.740
And relationship will make sure that the foreign key setup that is expected exists between those two tables.

00:44:13.880 --> 00:44:20.400
And it will kind of mediate that pattern on behalf of the object model to the database schema.

00:44:20.400 --> 00:44:23.400
Yeah, that makes it really easy to almost not even worry about that.

00:44:23.400 --> 00:44:26.260
So is that like a lazily evaluated thing?

00:44:26.260 --> 00:44:30.940
So if I pull back a book, it's not going to go pull back all of its reviews instantly unless I touch that.

00:44:31.320 --> 00:44:45.060
Right. So that was one of the patterns in Fowler's books called lazy loading, which is the concept that if you have an object and you have some kind of method or attribute that represents data from your database, you might have the object and it didn't actually load that other data.

00:44:45.060 --> 00:44:51.220
It's only when you touch that attribute or method that it suddenly goes, oh, I have to load this stuff and I have to go out to the database and load more information.

00:44:51.220 --> 00:44:53.780
That's an efficiency pattern called lazy loading.

00:44:53.780 --> 00:44:58.840
It means you can load a lot of objects and not have to go through all the time of loading all the related things.

00:44:59.660 --> 00:45:03.080
All ORMs do lazy loading, just every ORM I've ever seen.

00:45:03.080 --> 00:45:12.580
But lazy loading is also at the core of this somewhat controversial concept in ORMs called the N plus one problem.

00:45:12.580 --> 00:45:19.540
Right. That's probably the most common performance problem people run into is they don't realize they're doing this N plus one query.

00:45:19.540 --> 00:45:23.580
So I get one book or I get one book.

00:45:23.580 --> 00:45:24.240
So that's the one.

00:45:24.240 --> 00:45:27.900
And then I try to touch the other items or a set of books.

00:45:27.980 --> 00:45:29.800
I loop through them every time I touch that property.

00:45:29.800 --> 00:45:30.940
I'm doing one more query.

00:45:30.940 --> 00:45:34.860
So for N books, I do N more queries against the database, right?

00:45:34.860 --> 00:45:35.600
Yep.

00:45:35.600 --> 00:45:36.640
That's the N plus one problem.

00:45:36.640 --> 00:45:45.080
And every article ever written that is against ORMs will have that entire example as the cornerstone of its premise.

00:45:45.780 --> 00:46:02.920
So the other thing that SQL Alchemist had from day one, besides the unit of work, the other cool thing it had was this thing called eager loading, which means that you can, if you kind of know you're loading these users and you want the addresses also, you can say to the query, hey, load the user.

00:46:03.000 --> 00:46:07.640
But then also, can you also load the addresses at the same time instead of me doing the N plus one problem?

00:46:08.280 --> 00:46:14.820
And in that way, you will only do, if you do what's called joined eager loading, it will actually do a join, an outer join.

00:46:14.820 --> 00:46:16.300
It will load your user row.

00:46:16.920 --> 00:46:25.800
And while it does the select for the user row, it will do an outer join to the address table so that it gets zero or more address rows at the same time.

00:46:25.800 --> 00:46:31.200
And then the query object knows how to organize the product that we get back.

00:46:31.200 --> 00:46:37.420
Because if you do a join in SQL from one table to a many, you'll get the one row repeated.

00:46:37.420 --> 00:46:42.340
So the ORM knows how to deduplicate those rows on the left side of the join.

00:46:42.340 --> 00:46:44.960
And that's called eager loading.

00:46:44.960 --> 00:46:53.300
And one of the tradeoffs of eager loading is that when we write a query, we say, hey, Mr. Object Database thing we don't know anything about.

00:46:53.300 --> 00:46:54.560
We want users.

00:46:54.560 --> 00:46:55.980
And then later I want addresses.

00:46:55.980 --> 00:47:05.760
If I have to say, hey, I want users, but I'm also going to want in the future these addresses, that is what you would call a leaky abstraction.

00:47:05.760 --> 00:47:12.420
Because it means that the way the system is going to do its load, you need to tell it up front how to do its job to some degree.

00:47:12.420 --> 00:47:17.600
You have a little bit of awareness that this is not just like some weird LDAP database.

00:47:17.600 --> 00:47:19.100
This is actually a relational database.

00:47:19.100 --> 00:47:21.160
And these two things are in two different tables.

00:47:21.160 --> 00:47:34.960
One of the things SQLAlgmy embraced very early on, and one way that is very different from most other ORMs, is that it did not try to get around that issue by pretending the database doesn't exist.

00:47:34.960 --> 00:47:35.840
It's like, you know what?

00:47:35.840 --> 00:47:38.140
This thing is to automate our work.

00:47:38.140 --> 00:47:40.580
It's to make our lives easier and write less code.

00:47:40.580 --> 00:47:43.300
It's not there to hide that there's a database.

00:47:43.720 --> 00:47:53.980
So we need to be aware of things like if we want to load the 1 and also the N and not have N plus 1, we need to give the query a hint up front to do that.

00:47:53.980 --> 00:47:55.460
So that's ego loading in a nutshell.

00:47:56.020 --> 00:48:01.820
Yeah, I really like that philosophy that you guys take looking, you know, saying, look, we're not going to hide this from you.

00:48:01.820 --> 00:48:08.640
We're going to make your life easier and less error prone, but we're not going to treat you like an idiot or hide this stuff.

00:48:08.640 --> 00:48:10.380
Or, you know, you can still get to what you need to.

00:48:10.380 --> 00:48:10.840
That's great.

00:48:10.840 --> 00:48:11.420
Right.

00:48:11.780 --> 00:48:12.880
How do I do that in code?

00:48:12.880 --> 00:48:17.260
So what do I say on my query to say I want to eager load books and reviews?

00:48:17.900 --> 00:48:22.700
So modern SQLAlchemy, I mean, this is, again, APIs have changed dramatically.

00:48:22.700 --> 00:48:30.340
Modern SQLAlchemy, you would say query for a user class, and then there is a call on the query, which is a method chaining system.

00:48:30.340 --> 00:48:40.060
You say query.options, and then you add to the options function a bunch of constructs called join load or subquery load or immediate load.

00:48:40.060 --> 00:48:50.800
There's all these little directives we have that you can say, hey, query for user.options, join load user.addresses, join load address.related something else.

00:48:50.800 --> 00:48:59.600
And you can kind of give these directives in the options of the query as to what other things it should be loading onto the primary thing that you're loading from.

00:48:59.600 --> 00:49:00.720
Oh, very cool.

00:49:00.720 --> 00:49:04.020
You've been talking about the different versions of the API.

00:49:04.020 --> 00:49:07.240
How have you dealt with versioning that API over time?

00:49:07.240 --> 00:49:09.980
Well, I mean, it's been 10 years.

00:49:09.980 --> 00:49:19.580
And what I kind of did in the early days, you know, we were first 0.1, and then we were 0.2, and 0.3.

00:49:19.580 --> 00:49:25.160
And back around 0.3, someone wrote an O'Reilly book for us, and we're like, you should be 1.0.

00:49:25.160 --> 00:49:26.540
And I'm like, no way.

00:49:26.540 --> 00:49:27.720
We are so early.

00:49:27.720 --> 00:49:30.640
Like, this thing is totally not at all how it's going to be.

00:49:30.640 --> 00:49:36.600
So with 0.1, first of all, I didn't even publicize the library at all in those early days.

00:49:36.600 --> 00:49:38.680
I just maybe announced it on one mailing list.

00:49:38.680 --> 00:49:42.720
And people just came, kind of just came and started using it.

00:49:42.720 --> 00:49:43.620
They started using it in production.

00:49:43.620 --> 00:49:50.280
And 0.1, 0.2, I made enormous, enormous changes that just totally ripped the rug from everybody.

00:49:50.280 --> 00:49:51.480
But we were 0.1.

00:49:51.480 --> 00:49:51.900
Sure.

00:49:51.900 --> 00:49:56.080
Did it make you nervous to hear that they were, like, so heavily dependent on this new thing you created?

00:49:56.080 --> 00:49:58.560
People got way too dependent too fast.

00:49:58.560 --> 00:50:06.340
And in early days, I think Reddit started using SQLAlchemy, maybe version 0.4 or 0.5, you know.

00:50:06.340 --> 00:50:09.160
And there were some messages from them years ago.

00:50:09.160 --> 00:50:11.080
I don't know where they were, so whatever.

00:50:11.080 --> 00:50:15.020
Where they were kind of complaining about SQLAlchemy changing its API too much.

00:50:15.660 --> 00:50:21.620
And really, the API, I made the biggest changes before version 0.5.

00:50:21.620 --> 00:50:29.080
0.5, we really started settling down to this new kind of thing with the query, with the attributes linked to the class.

00:50:29.080 --> 00:50:32.640
And then we had to keep the API changes all the time.

00:50:33.400 --> 00:50:40.120
And from 0.5 on forward, even from 0.3 and 4, I always made sure that there was a backwards compatibility path.

00:50:40.120 --> 00:50:42.760
I made sure that everything that changed was always announced.

00:50:42.760 --> 00:50:48.160
And if something happened that was unexpected, that was a regression, and we would address it.

00:50:48.160 --> 00:50:52.440
And it was almost never that I said to someone, you're stuck.

00:50:52.440 --> 00:50:54.320
You'll have to just change your code now.

00:50:54.320 --> 00:50:57.180
Like, I always made sure that there was a backwards compatibility path.

00:50:57.680 --> 00:51:04.820
But it meant you had to kind of, especially in the early days, it meant you had to really kind of watch when we did the next major number, which is really the minor number.

00:51:04.820 --> 00:51:06.240
But we call it the major number.

00:51:06.240 --> 00:51:08.720
You needed to watch what was changing.

00:51:08.720 --> 00:51:10.360
You needed to test your stuff ahead of time.

00:51:10.360 --> 00:51:12.460
Things are better now.

00:51:12.460 --> 00:51:14.460
Now that we have pip is much better.

00:51:14.460 --> 00:51:18.260
And we can put up beta releases that won't automatically install for people.

00:51:18.260 --> 00:51:24.700
And I always put very, very, very comprehensive migration notes for every version.

00:51:25.560 --> 00:51:31.680
You can see them going back on the site, probably back to version 0.4 or 0.3, where I say, here's the things that are going to change.

00:51:31.680 --> 00:51:32.880
Here's the things to look out for.

00:51:32.880 --> 00:51:33.680
Here's the things to test.

00:51:33.680 --> 00:51:38.960
And after version 5 and version 6, version 7, it changes less and less each time.

00:51:38.960 --> 00:51:42.700
People went from version 7 to 8 with very few hiccups.

00:51:42.700 --> 00:51:44.940
You know, 8 to 9, 9 to 1.0.

00:51:44.940 --> 00:51:47.440
There's really nothing you have to change in your code anymore.

00:51:47.440 --> 00:51:51.900
We like people to test because it might be surprises that we didn't expect.

00:51:51.900 --> 00:51:53.680
And we fixed those.

00:51:54.860 --> 00:51:59.980
But APIs very rarely break in any kind of backwards incompatible way.

00:51:59.980 --> 00:52:07.620
The only time it's a problem is that if there's something that was broken in SQLAlchemy 8 or 9 and people were relying upon the broken behavior.

00:52:07.620 --> 00:52:09.760
That's the hardest one is when people rely upon broken.

00:52:09.760 --> 00:52:11.900
And they didn't know it was broken, so you can't blame them.

00:52:11.900 --> 00:52:18.700
When we fix things that were broken, we'll usually make it raise a warning in the current version they're using.

00:52:18.900 --> 00:52:20.400
Like 0.9 has something that's broken.

00:52:20.400 --> 00:52:24.300
We'll have it admit a warning, so they'll update, upgrade to 0.9.9.

00:52:24.300 --> 00:52:25.880
And all of a sudden they see these warnings.

00:52:25.880 --> 00:52:27.660
It'll say, this will be an exception to 1.0.

00:52:27.660 --> 00:52:29.580
So they'll kind of know to change it there.

00:52:29.580 --> 00:52:32.340
But you do need to be watching the versions and the changes.

00:52:32.460 --> 00:52:37.880
I've worked really hard to make all the change logs very easy to find on the site.

00:52:37.880 --> 00:52:40.500
I mean, other projects I can never find what changes.

00:52:40.500 --> 00:52:41.300
It's very hard to find.

00:52:41.980 --> 00:52:47.380
I think our site is very transparent, and you can see everything happening in many ways.

00:52:47.380 --> 00:52:51.000
And it's very highly linked as far as the development log.

00:52:51.000 --> 00:52:52.200
Yeah, that's excellent.

00:52:52.200 --> 00:52:53.400
It is super comprehensive.

00:52:53.400 --> 00:52:59.640
So one thing I think is interesting is maybe talking about some of the notable users of SQLAlchemy.

00:52:59.640 --> 00:53:02.420
I know you've talked a little bit about a couple of them.

00:53:02.420 --> 00:53:07.440
But you've got a whole list at sqalchemy.org slash organizations dot html, right?

00:53:07.440 --> 00:53:08.240
Yeah.

00:53:08.500 --> 00:53:12.460
So, yeah, I think I first saw MongoDB was doing that.

00:53:12.460 --> 00:53:14.600
They had this enormous list of people using their stuff.

00:53:14.600 --> 00:53:23.280
And I'm like, I want that, like a big wall of honor of all my – when some big company uses your product, it's like, wow, I'm important.

00:53:23.280 --> 00:53:24.840
I did this important thing.

00:53:24.840 --> 00:53:29.120
So up there, there's a lot of big – Reddit was the huge one.

00:53:29.120 --> 00:53:31.280
Reddit only uses the core.

00:53:31.280 --> 00:53:32.600
They don't use VRN.

00:53:32.600 --> 00:53:37.380
Their code is very unusual in the way they do things.

00:53:37.380 --> 00:53:40.320
And I'm glad they don't use the ORM because they would really be killing it.

00:53:40.320 --> 00:53:42.680
Yelp uses a lot of SQLAlchemy.

00:53:42.680 --> 00:53:43.980
I've been – I visited Yelp.

00:53:43.980 --> 00:53:45.740
They use the ORM a lot.

00:53:45.740 --> 00:53:48.960
Uber, you know, uses SQLAlchemy.

00:53:48.960 --> 00:53:50.840
Hulu.

00:53:50.840 --> 00:53:52.320
Dropbox is there, right?

00:53:52.320 --> 00:53:53.680
Yeah, Dropbox.

00:53:53.680 --> 00:53:55.600
Yeah, Dropbox is another one that uses the core.

00:53:55.600 --> 00:54:01.440
They're another – you know, they actually wrote some blogs that were a little bit critical of the ORM, which I was kind of annoyed about.

00:54:01.540 --> 00:54:02.620
But they use the core still.

00:54:02.620 --> 00:54:03.240
They use it.

00:54:03.240 --> 00:54:08.580
Yeah, a lot of animation studios use it.

00:54:08.580 --> 00:54:13.140
There's a lot of companies that use it who I know use it, but I don't really exactly have permission.

00:54:14.600 --> 00:54:19.200
You know, one of them is, you know, kind of a Mickey Mouse operation anyway.

00:54:19.200 --> 00:54:23.540
A lot of companies that are up there in organizations, you know, Fedora uses it.

00:54:23.540 --> 00:54:26.600
And, of course, now I work for Red Hat and I work for OpenStack.

00:54:26.600 --> 00:54:30.240
And OpenStack is this cloud computing platform that's open sourced.

00:54:30.240 --> 00:54:34.280
And a lot of companies like PayPal and IBM and AT&T use it.

00:54:34.740 --> 00:54:37.720
And that thing uses SQLAlchemy all throughout.

00:54:37.720 --> 00:54:40.680
It's super heavy SQLAlchemy throughout OpenStack code.

00:54:40.680 --> 00:54:42.160
So it's a very widely used library.

00:54:42.160 --> 00:54:43.680
Yeah, that's really excellent.

00:54:43.680 --> 00:54:44.900
You must be proud of it.

00:54:44.900 --> 00:54:46.100
Yeah.

00:54:46.100 --> 00:54:47.160
Cool.

00:54:47.160 --> 00:54:48.280
And you should be.

00:54:48.280 --> 00:54:54.080
So what about the whole async movement with, like, Twisted and AsyncIO?

00:54:54.080 --> 00:54:55.680
How does that play with SQLAlchemy?

00:54:56.480 --> 00:55:03.040
So the async thing is something, another Python story where it's something that I wasn't really that interested in.

00:55:03.040 --> 00:55:05.440
But it's a big deal in Python, the async.

00:55:05.440 --> 00:55:12.260
Because when I first came to Python, the two, you know, 800-pound gorilla things that existed were Zope and Twisted.

00:55:12.260 --> 00:55:17.300
You know, the old school, you know, the old guard of Python is Zope and Twisted.

00:55:17.300 --> 00:55:18.620
So Twisted is great.

00:55:18.620 --> 00:55:19.500
It's still out there.

00:55:21.720 --> 00:55:31.360
Async, the problem with what we call explicit async is that it's not really compatible with this idea of lazy loading that we see Martin Fowler first described.

00:55:31.360 --> 00:55:39.480
The concept that when you access something on a class, it's going to automatically, behind the scenes, go and fetch data from a database.

00:55:39.480 --> 00:55:42.080
It means that it's going to do I.O.

00:55:42.260 --> 00:55:51.780
And when you do things with the async model, the async model is all about that input-output operations should never be implicit.

00:55:51.780 --> 00:56:02.840
They should always be something that you code into it and say, hey, this is about to do I.O., so please stop what we're doing, do the I.O., and then let the whole interpreter go and do something else.

00:56:02.940 --> 00:56:09.640
And we do that in the newest version of Python's async I.O., which is in Python 3, you do a yield.

00:56:09.640 --> 00:56:14.860
And it's very slick, but it still is a statement that is an interruption in your code.

00:56:14.860 --> 00:56:15.340
Right.

00:56:15.340 --> 00:56:18.000
You have to explicitly say, I'm waiting here, I'm waiting here, I'm waiting here.

00:56:18.000 --> 00:56:23.840
And at any of these points, you can take that thread and go do something else with it until I'm done talking to the database or something, right?

00:56:23.840 --> 00:56:24.760
Right.

00:56:24.860 --> 00:56:28.480
So people have wanted SQLAlchemy to work a twist for a long time.

00:56:28.480 --> 00:56:29.280
I've looked into it.

00:56:29.280 --> 00:56:32.100
I actually had some clients pay me to look into it.

00:56:32.100 --> 00:56:44.880
And every time I look into it, it's like, eh, it's really not worth it because we have to completely rewrite the whole core engine part of this thing to be this kind of inside-out callback routine.

00:56:44.880 --> 00:56:46.780
This is with classic Twisted.

00:56:46.780 --> 00:57:16.760
How about the async I.O.?

00:57:16.760 --> 00:57:17.120
Right.

00:57:17.120 --> 00:57:24.900
So with async I.O., there's a big blog post I just wrote in the past couple of months that's about – it's called Asynchronous Python and Databases.

00:57:24.900 --> 00:57:27.240
And it's gotten a lot of views.

00:57:27.240 --> 00:57:29.400
And it kind of sums up the whole situation.

00:57:29.400 --> 00:57:31.440
I went into async I.O.

00:57:31.440 --> 00:57:31.980
I looked at it.

00:57:31.980 --> 00:57:32.880
I think it's really great.

00:57:32.880 --> 00:57:34.360
And it's definitely a huge improvement.

00:57:34.360 --> 00:57:41.360
Async I.O., I actually could write – a lot of SQLAlchemy could be made to work very seamlessly with async I.O.

00:57:41.760 --> 00:57:47.380
In that it wouldn't require that much code to be replaced.

00:57:47.380 --> 00:57:57.540
What's nice about async I.O. versus the older callback style is that there's kind of some ways you can have the async thing be explicit on the beginning of where a method does something.

00:57:57.800 --> 00:57:59.900
And then up the call chain at the end.

00:57:59.900 --> 00:58:05.100
And you can kind of get away with the calls in the middle not knowing that they're doing async somewhat.

00:58:05.100 --> 00:58:07.480
There's a little bit of Python syntax helping here.

00:58:08.980 --> 00:58:13.400
So I looked into how I could make the core do async I.O. for real.

00:58:13.400 --> 00:58:14.560
And actually, it can be done.

00:58:14.560 --> 00:58:15.880
I started writing parts of the pool.

00:58:15.880 --> 00:58:18.260
And it would be a lot of work, but it could be done.

00:58:18.260 --> 00:58:21.980
But in this blog post, I wrote, like, do we really need to do it?

00:58:21.980 --> 00:58:26.880
Because when you use async packages, even when you use Twisted, and Twisted has been doing this from day one for databases,

00:58:28.180 --> 00:58:34.300
when you have code that really works better with threads and traditional, you know, preemptive, not preemptive, but traditional threading,

00:58:34.300 --> 00:58:38.100
you throw that code into what's called a thread pool.

00:58:38.100 --> 00:58:46.320
And then the asynchronous part of your at library can actually call into routines that are invoked synchronously within a thread pool,

00:58:46.320 --> 00:58:48.820
but they are given kind of an asynchronous facade.

00:58:48.820 --> 00:58:51.420
And I think that is the best way to do it.

00:58:51.420 --> 00:58:55.280
If you're writing a lot of database code, you really don't need to worry.

00:58:55.440 --> 00:58:58.680
You really shouldn't have to be worrying about deferring to I.O. within the database,

00:58:58.680 --> 00:59:00.920
because you really, in the database code, you're in a transaction.

00:59:00.920 --> 00:59:03.340
You definitely need to do A, B, C, D, and E.

00:59:03.340 --> 00:59:06.220
It's not really a mystery when the I.O. has to happen.

00:59:06.220 --> 00:59:08.260
It's very kind of boilerplate-y.

00:59:08.260 --> 00:59:14.700
And one of the entire reasons that SQL Alchemin exists is to reduce the need to type repetitive boilerplate.

00:59:14.700 --> 00:59:15.460
That's the whole point of it.

00:59:15.460 --> 00:59:16.980
You don't need SQL Alchemin to do anything.

00:59:16.980 --> 00:59:20.640
You can do it all by hand, but the point is to reduce boilerplate.

00:59:20.640 --> 00:59:24.760
So to that extent, I think you're better off putting your database code in a thread pool,

00:59:25.200 --> 00:59:29.000
or using a system like eventletg event that is an implicit system.

00:59:29.000 --> 00:59:34.240
And the blog post really goes into this in great detail as to show the benchmarks, why I think it's better.

00:59:34.240 --> 00:59:36.140
And I think a lot of people agree with it.

00:59:36.140 --> 00:59:37.200
Cool.

00:59:37.200 --> 00:59:37.440
Okay.

00:59:37.440 --> 00:59:39.380
You'll have to send me the link to that blog post.

00:59:39.380 --> 00:59:40.160
I'll put it in the show notes.

00:59:40.160 --> 00:59:41.440
Excellent.

00:59:41.440 --> 00:59:43.200
So I think we've been talking for a while.

00:59:43.200 --> 00:59:45.340
It's probably a good place to wrap up the show.

00:59:45.480 --> 00:59:53.120
And a question I ask the guests on my show on the way out the door is, you know, there's a ton of packages on PyPy and out there in GitHub and stuff.

00:59:53.120 --> 00:59:54.840
What ones are notable to you?

00:59:54.840 --> 00:59:55.600
What ones are your favorites?

00:59:55.600 --> 00:59:57.300
Seek walk me, of course.

00:59:57.300 --> 00:59:58.120
Yeah.

00:59:58.120 --> 01:00:00.160
On PyPy?

01:00:00.160 --> 01:00:01.400
What's your favorite PyPy package?

01:00:02.560 --> 01:00:04.640
I use pytest quite a lot.

01:00:04.640 --> 01:00:06.200
I recommend pytest.

01:00:06.200 --> 01:00:10.900
I recommend the mock library, which is actually part of the Python standard library now.

01:00:10.900 --> 01:00:15.320
Mock and PyPy are the ones I see myself downloading most of the time.

01:00:17.560 --> 01:00:19.400
You know, what else?

01:00:19.400 --> 01:00:22.980
There's probably others, but they're not coming to mind at the moment.

01:00:22.980 --> 01:00:24.800
I'd have to look and be like, oh, yeah, I use that thing a lot.

01:00:24.800 --> 01:00:27.560
You know, I mean, obviously, NumPy is the big one.

01:00:27.560 --> 01:00:28.480
Yeah, NumPy.

01:00:28.480 --> 01:00:30.540
Yeah, NumPy is good.

01:00:30.540 --> 01:00:34.100
Like I said, I think Sequel Alchemy is certainly one of the top ones out there.

01:00:34.100 --> 01:00:36.240
Yeah, yeah, it is.

01:00:36.240 --> 01:00:41.860
So before we call it a show, is there anybody or a thing you want to give a shout out to and call attention to?

01:00:42.880 --> 01:00:46.860
Oh, well, my cats for helping me with the show today.

01:00:46.860 --> 01:00:56.000
And, you know, just I'm really appreciative of the people that come to see my talks and the people that talk to me on Twitter and tell me how great I'm doing.

01:00:56.000 --> 01:00:58.660
And even the people that, you know, naysay me as well.

01:00:58.660 --> 01:00:59.680
I get good feedback from them.

01:00:59.680 --> 01:01:03.000
So I'm really glad that everyone is kind of following along with what I'm doing.

01:01:03.000 --> 01:01:03.400
Excellent.

01:01:03.400 --> 01:01:05.780
Can people come and contribute to Sequel Alchemy?

01:01:05.780 --> 01:01:07.020
It's on Bitbucket, right?

01:01:07.020 --> 01:01:11.300
Yeah, I mean, it's a big code base and people do contribute all the time.

01:01:11.300 --> 01:01:12.660
I get pull requests all the time.

01:01:12.660 --> 01:01:15.680
through both Bitbucket and GitHub and you can send a pull request.

01:01:15.680 --> 01:01:18.580
We haven't done any sprints in a long time.

01:01:18.580 --> 01:01:21.740
I mean, that's something that we could do again if I had people to help me with that.

01:01:21.740 --> 01:01:25.740
You know, and people in the OpenStack community help out.

01:01:25.740 --> 01:01:27.860
So it's a very open project.

01:01:27.860 --> 01:01:33.580
If you go to the website, there is a toolbar that has community links and it has a link called Develop.

01:01:33.580 --> 01:01:36.880
And it'll show you all the ways that you can get involved with helping develop Sequel Alchemy.

01:01:36.880 --> 01:01:37.920
Awesome.

01:01:37.920 --> 01:01:39.860
Mike, thanks so much for being on the show.

01:01:39.860 --> 01:01:41.280
It was a really interesting conversation.

01:01:41.980 --> 01:01:42.960
Yeah, thanks for having me.

01:01:42.960 --> 01:01:43.320
You bet.

01:01:44.520 --> 01:01:47.540
This has been another episode of Talk Python to Me.

01:01:48.200 --> 01:01:49.420
This is your host, Michael Kennedy.

01:01:49.420 --> 01:01:51.220
I want to say thank you for listening.

01:01:51.220 --> 01:02:01.980
And if you want to support the show, be sure to check out our Patreon campaign at patreon.com slash mkennedy or talkpythontome.com slash sponsor.

01:02:02.620 --> 01:02:09.980
Remember to check out the show notes at talkpythontome.com where you can find all the links from the topics we discussed in the show.

01:02:09.980 --> 01:02:13.620
And before you go, take a moment to become a friend of the show.

01:02:13.620 --> 01:02:19.000
Visit talkpython.com, click on friends of the show, give us your email address, and you'll be in the group.

01:02:19.000 --> 01:02:20.720
Smix, take us out of here.

01:02:20.720 --> 01:02:40.720
Stay tuned.

01:02:40.720 --> 01:02:41.720
Bye.

01:02:41.720 --> 01:02:43.720
.