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 .