#328: Piccolo: A fast, async ORM for Python (updated) Transcript
00:00 ORMs are one of the main tools to put first class data access in the hands of non-SQL-loving developers. And even for those who do love SQL, making them way more productive. When you hear about ORMs in Python, we often hear about either SQLAlchemy or Django ORM. And we should, they're great. But there are newer ORMs that take better advantage of modern Python. On this episode, you'll meet Daniel Townson, he's the creator of Piccolo ORM. A great ORM that is async first, but also has a synchronous API. It has a super clean query syntax, and it's easy to learn. This is Talk Python to Me Episode 328, recorded July 22 2021.
00:52 Welcome to talk Python to me, a weekly podcast on Python, the language, the libraries, the ecosystem, and the personalities. This is your host, Michael Kennedy, follow me on Twitter, where I'm @mkennedy, and keep up with the show and listen to past episodes at 'talkpython.fm' and follow the show on Twitter via @talkpython. This episode is brought to you by Linode & Us over at talk Python training and the transcripts are brought to you by 'Assembly AI', please check out what we're offering during our segments. It really helps support the show.
01:21 You want to learn Python, but you can't bear to subscribe to yet another service at talk Python training. We hate subscriptions too. That's what our course bundle gives you full access to the entire library of courses. For one fair price. That's right, with the course bundle, you save 70% off the full price of our courses, and you own them all forever. That includes courses published at the time of the purchase, as well as courses released within about a year of the bundle. So stop subscribing and start learning at 'talkpython.fm/everything'. Dan, welcome to talk Python to me. Yeah, thanks for having me. I'm a big fan of the show. So it's kinda like a little dream to be on the show. Oh, how nice. That means a lot. Thank you. It's great to have you here. And you built some really neat software that I'm looking forward to diving into. It's interesting, because when we talk about your ORM, but it's also there's so many different areas in which now that we're kind of past Python 2, and there's a lot of folks who are saying, you know what, we can really put Python 2 behind us. And let's just build for the future that just opens up so many doors, right? Like, Oh, well type hints, of course type hints, why not type hints? Well, Python 2 is why not type hints, but not anymore, right? And async and all these other things. And just it's really great to have these frameworks like yours coming along that just go modern Python, what can we build now? Yeah, I totally agree. I felt like Python 3.6 kind of close the door on the should we still be using Python 2 conversation? Because by that point in how async type annotations, data classes, I think with Python 3.7 enums, it just goes on and on. Like, it just felt like the Python community knocked out of the park, python 3.6. And I have a theory that is completely unsubstantiated, that actually a lot of the the progress has to do with F strings. So many people are like, I just want to easily format this code. What do I need? I need Python 3.6, fine. Well, actually, I want to switch and it's such a minor feature. So minor, the pain of going back will be horrible. I couldn't imagine using Python 2 now. Actually. I know. Yeah. Same quick. Welcome to some folks that live stream Chris May. Hey, happy to hear Paul Everett. So glad you can stop by Teddy as well. We'll get some questions in from y'all, I'm sure. Now before we get into Piccolo, and all the cool stuff that I was alluding to there. Let's just start with your story. Dan, how did you get into programming and Python? Yeah, so I've been programming for quite a long time. Now, I might look quite youthful on this camera. But that's just because my webcams got a filter on it. But if you've ever touch up, my appearance is checked. Yeah, I've actually been getting programming with Python for about 14 years. So Django was kind of one of my first frameworks and just really fell in love with it. Because in university, I learned C and then it's like, oh, this this world of Python exists and more easy than C. And so I've used it extensively. Since then, I've mostly been working for startups and design agencies, and then really fell down the async rabbit hole over the last few years to really enjoy working with WebSockets. And building kind of interactive front ends and also really kind of excited the way that Postgres continues to grow and have new features. So the things that really excited me in the Python world, yeah, those things are super exciting. And the web frameworks are coming along to embrace them language features, you know, async and await, we had async IO and Python 3, 4, it didn't really gain a ton of adoption, because there was it sort of had this callback style of working which is not the same as you know, a weight a thing and just keep rockin. I totally agree. So I think they pretty much took what twisted it created and ported it over and they say is all callbacks and it was still better than he still good to have that option in the standard library. But I totally agree async await really kind of set it on fire because it was quite hard to approach async i/o first it was you don't have to think about how you write code differently. You just put the awaits in the spots and then you know, everything kind of flows. It's I know it's not the same but from how you think about structuring your code is real similar. Yeah, and I think the maturity is there now with the library.
15:00 List of dictionaries Yeah. And then it also has a feature like Django has this feature, which is invaluable. And it's called values list. And so if you just want one value back, it will then condense it down to just a list of values, right? Like, I want all the IDs of the bands that play pop music or something. Yeah. And it's so much more efficient than doing like in Django, you know, bounded objects.all and then looping through to get the IDs. It's just
15:23 like as if you use Django for ages, you just learn these little tricks, right? Another one that stood out to me is the ability to do set based operations. So is when I think of ORMs, I just for everyone listening, I adore ORM. I think they're really empowering for people, I think they take a lot of the modern tooling that we love, like refactoring and allow you to apply that like over to your query, because your if you wanted to, like change the casing of band.name, you could refactor, rename that, and it would affect your queries, because that's still Python code, right? That said, there are places where people either abusing it, or it's just inappropriate. So the places where it gets abused a lot would be the (n+1) problem, right? Where you've got a lazy reference to something else. And you don't know that that's going to be a separate query for every time you touch one of those and you get a list of objects back and you loop over I mean, you access like, in your example, we got band.manager for be in band band.manager, right? That could be 101 queries for what should have been one, right? Yeah, that's, that's a really good point. And even experienced, developers get this wrong, because they might use serializers, which are calling properties under the hood on the table, which are triggering SQL query. So this is another design intent behind Piccolo is whenever a query is run, it's very explicit. You're literally calling .run or a run sync. So there's none of this magic, you can't accidentally create an(n+1) query, you might accidentally end up with a code routine or something. Yeah. So yeah, it's a really good point, because I think n plus one is kind of the scourge of developers with performance. And also coming back to your point about ORM. s. As a back end developer, we can spend hours a day using ORMs is kind of like one of the main tools in our tool belt. So it's kind of quite nice to start from a blank sheet of paper and drink. How can I make that experience maybe like slightly better if I can.
17:01 This portion of talk Python to me is sponsored by Linode. Visit 'talk python.fm/linode'. To see why Linode has been voted the top infrastructure as a service provider by both G2 and TrustRadius. From their award winning support, which is offered 24/7 365 to every level of user, the ease of use and setup. It's clear why developers have been trusting Linode for projects both big and small, since 2003. deploy your entire application stack with Linode to one click app marketplace, or build it all from scratch and manage everything yourself with supported centralized tools like Terraform. The Linode offers the best price performance value for all compute instances, including GPUs, as well as block storage Kubernetes. And their upcoming BareMetal release. Linode makes cloud computing fast, simple and affordable, allowing you to focus on your projects, not your infrastructure, visit 'talkpython.fm/linode'. and sign up with your Google account, your GitHub account or your email address. And you'll get $100 in credit, as 'talkpython.fm/linode'. Or just click the link in your podcast player show notes. And thank them for supporting talk Python.
18:11 The (n+1) problem I believe is a either there's some tool doing something behind the scenes you don't know. But often it's just a lack of understanding Oh, that actually is a lazy loaded property, which is going to trigger a query. So I should have put a join and then I'd be in a better place. That's a programmer pattern thing that you should pay attention to and work with the one where I don't know how to fix it is more like the serialization thing. Like what if I want to go through my database and go to 10,000 Records and make some changes to them? So often, it's do the query loop over the 10,000 things and make a change, call save? Yeah, maybe it's in one giant transaction that you finally push the changes back, but but you're pulling all the data back. And one of the things I really like about your ORM here is like this update section here, where you can do set based operations without pulling the records back. Yeah, so you can do stuff like so this example here, weight band, update, bundle, popularity. 10,000. But then you can also do band.popularity is (band.popularity+10). And then in the database, it will then just add 10 to all of the numbers. Oh, really. And then it's, it's all just magic around, you know, Python magic methods. It's just as a library offer, it gives you so much power. It's one of the things I love about Python is when you're building like query languages, like ORM I think very few languages can really rival Python with its flexibility. Yeah, that's that's really why a lot this stuff's possible. It's really neat. And I think the ability to push these changes without actually you're still programming in this ORM classes and the models but you're not actually pushing a whole bunch of them back and forth to make the changes but to do the set based operations like delete them where or make this update to this value where this is true and then just push that make that happen in the best way you can a SQL right? Yeah, exactly. I think I'm actually point around the n plus one. I think properties are something that can be a little bit evil, and I really shy away from them in the piccolo code because it can you call it property
20:00 And you think you're getting a value back, but it could be doing any kind of magic. And then once you've defined something as a property, you then can't add arguments to it without breaking your API. So yeah, I think that that's something I've tried to steer away from in general Piccolo properties. Yeah. But a lot of hidden stuff. Yeah, it's not entirely clear, I think they're super useful, but certainly in something where I thought I was accessing a field or the class, but what I actually did is make a network call, like that distinction is possibly too big of a bridge to just make that automatic. A lot of the times, yeah, there's no async properties in Python as well. So that's kind of one of the reasons why it doesn't use any async properties.
20:37 If they add it maybe I'll put a comment on the pep saying don't do it. Yeah, exactly. Another thing that's interesting here is an all this code, every code you've written is 'awaitband.select', or 'awaitband.delete', and so on or update. And then at the end, you say, run, this is the explicit part that you're talking about in your API, like, I know, here's where it's happening. And it probably makes a lot of sense to do that as well. Because on the flip side of it, that's where you have to await it anyway, right? Yeah. So what happens is, you build up this query, you just chain methods to it. And then at any point, you can print out that object, and I'll give you the SQL. And then until you realize, yeah, and then until you're actually await it, there's something under the hood, that they're only publicized, you don't need the dot run, if you're await, it will run as a convenience because people forget, but then it just makes it easier from a documentation perspective to say when it's async, use run. And when it's synchronous, use run sync, right? And then if you do run sync, then I've got like a bit of magic in where it tries to create an event loop to run it or tries to figure out if there's already an event loop if there is run in there. So you can use Piccolo in an old school WSGI app, if you want to, to just synchronously? Yeah, well, let's dive into that. Because that's one of the things that really stood out to me, many frameworks or API's packages tell you, you're going to either have to go, you know, go take a fork in the road, you're going to go down the async fork, and you're gonna use the async library, like HTTP x, or you're going to go on another fork, and you're gonna use the request library that has no async. And you're gonna go down that path, and you choose, and then you just go and with Piccolo, you can actually run, I guess the default behavior would be to be async, and await, but it has this dot run sync, which will kind of cap where the asynchronous behaves, and goes, and it'll just that you could run it in a regular flask app or Django app or whatever, and not worry about it being async at all right? Yeah, that's right. And it's actually one of the design challenges for Piccolo is how do you create an API, which is synchronous, and asynchronous. And there's only really two ways of achieving it is with a method like run or run sync or with context managers. So some of them, you'll create either an async context manager or synchronous, and then that will then impact whether the underlying query is synchronous or not. But then it adds a little bit more boilerplate. If every time you want a query, you need a context manager to tell it to be async. So this was kind of the best outcome I could think of was just how to run or not run sync, I think this is great, especially since even if you forget to run, it'll still run async. But there's a way to kind of cap it. So something I wanted to talk about, it's driven me crazy, ever since async, and await were introduced because I don't find it to be true. But I hear it all the time spoken about in the community as async in a way, they're super neat, but they're like viruses. And in the sense that soon is like one of your functions way way at the bottom has to do something async well, then the thing that calls it has to be async and await it and the things that call that function now all have to await it. And that percolates all the way to the top of your app. And so now you've I use in any async library, you've turned your entire thing into this, like async, vertical call stack, what your example here shows that that's not it doesn't have to be that way. Right? That's sort of the naive, I'm just gonna, like write the code without thinking about it. But if you want to, say, have your data access layer, do three things, it's just got to pull up some stuff from different places, you want that to be async, it doesn't mean that function has to be async. It could just start its own event loop, do the three things faster than without it and then return the answers, right, you can kind of cap it at any level that you want. And your your run sync is kind of an example of that, like you can choose to not have it just turn your entire app async you can jump between them. Just typically, if people use an async, then it's like the argument is if you need async, the whole app probably should be async. Because otherwise, why use an async. But then you can flip between them quite easily. So if you've got a synchronous app, and you want to call some async code, there's async IO .run and you can also do stuff likes spin up an event loop in a different thread, and then send work to that. Yeah, absolutely is quite fluid, you can flip between them quite easily. I mean, just one example that comes to mind is what if I wanted to go web scrape every page at a certain domain? So I've got a function that gives me a domain, I give it a domain, and then I want it to return or store into the database, all the pages. Yeah, right. That would be perfectly reasonable to have that thing. Okay, well, let's do a request, figure out what all the pages are. And then just, you know, recursively, sort of grab them all asynchronously, you would get a huge boost on getting every page off a site, even if that function blocked. You know what I mean? Because it itself could just go crazy against the server. Maybe it shouldn't, but it could, yeah, I'm a huge fan of async io.gov as well. So that's Yeah, that's a really like beautiful API just for saying do these 50 things now please? And
25:00 Let me know when they're done. Yeah. And block, right? You don't give me give me a list of answers or errors. Yeah. Also on the livestream Chris man, Hey, Chris says I'm so excited to use Piccolo with unsync have a workflow that'd be nice to paralyze. And yeah, so I think unsync is another really interesting library that wraps up async i O plus threading, plus multi processing, but then gives you a nice way to cap it as well. Because you can go like be given a task that comes back from there, if you just ask for the result, and it's not done it'll just block like a regular regular thing. And it does what kinda what you're talking about, it'll have a, it has a background thread with its own event loop, and it just pushes all the work over to there. So cool library. Yeah, it is at least for the size, right? Like 126 lines and it unifies those three API's and add some more stuff. That's that's pretty big bang for the the Python byte. Yeah, that's impressive. I wish Piccolo was not. I think 1000s 10s of 1000s of lines by now. Yeah. So another one of the new Python 3.6 onward type of things that's really cool is the type annotations. Yeah, I love type annotations. So part of my day job in the past was using swift and Swift is almost like pythons, like brother or sister, it was very heavily inspired by Python, right? It's like if Python all the sudden decided it was incredibly strict about typing and type definitions. Yeah, it would be a lot like swift right? Didn't work compiled. Yeah, it's a combination of type annotations and the tooling to support it. So in pythons case, it's VS code, and in Swift cases, Xcode. And it just means that when you're refactoring, it just makes it so much more confident about what's going on. Yeah, it provides documentation because previously, people were putting in a doc string anyway, so why not put it in your function definition, and then you can introspect it. And MyPy is incredibly powerful. I honestly don't think I could have built Piccolo without type annotations, because it makes the code so much more maintainable. That the click to go to VS code as well. It's just a beautiful usability improvement. And then kind of one of the hidden benefits is it makes tab completion. So good. Yeah. So a lot of python, auto completions, they use a library called Jedi under the hood. So when I was building Piccolo, I had a look at the source code to try to figure out how it does its magic. And if you give a type annotation to something like this is what this function returns is a really strong indicator to Jedi that this is what's going to get returned. I don't need to do any magic anymore. Yeah, I would say that half the time. That's why I do it is to make the editor better. So both VS code and PyCharm, like take a good look at what the type annotations are. Yeah, right. He just say, oh, you're trying to pass a string. And that's really supposed to be an integer. But then also, like you say, tab completion or autocomplete all over the place is fantastic. Yeah, I think there's a distinction as well, where I think if you're building an application, let's say you're building a Django app or a flask app, you don't need to care quite as much like I personally would still add type annotations for libraries. I think it's just absolutely essential. Like I don't think any new Python libraries should be written without it. Because you kind of shortchanging your users in a way because I totally agree. It's worse. Yeah, yeah. In the flask app, for example, that you've mentioned, you know, I would say, on the boundaries, right, like, here's a data access layer, like put the type annotations on those little bits there. And then the rest of the app, usually the editors, and the tools will just pick it up and continue to tell you what you're working with. Yeah, but but if you're doing a library, right, you want every function or every class to be kind of standalone and know everything it can. Yeah, definitely. And one more thing about type annotations is it's probably the greatest source of interview questions ever made. Because you can ask people in an interview, what's the difference between like a sequence and an iterable. And like, when you use type annotations, you really start to think about what's going on. And it's it's a great learning experience to, like, I want to pass a generator here, but it takes a list of things and it says that won't work. Maybe you just need to relax your type annotations to an iterator, iterator, thing, quick question from roller out there. in live stream, hey, roller, can I use Piccolo in place a Mongo engine? Or is it just for a relational stuff? Yeah, it's just relational. And it's, you can use SQL lite locally, but it's mostly Postgres. It was really built to take advantage of Postgres, because Postgres is it's like the fastest growing SQL database in the world, which is remarkable to think it's how old it is. And yeah, I think, you know, I think adding over SQL databases would be quite easy. But adding some, like, Mongo would be a bit trickier. I wouldn't say it was impossible, but a bit more work. Yeah, I would think so. Yeah, it's not certainly not impossible, but like joints and stuff would get tricky. What about SQL injection? I mean, many of us have heard about the little Bobby tables XKCD, which is delightful. You know, sort of SeanFreud a sort of way.
29:17 We all kind of don't relish in somebody else suffering this, but I find that this is actually one of the really nice things about ORMs. Most of the time is that they scrape off the ability to do SQL injection, because you're not building the SQL. Yeah, definitely. So you database adapter. So something like async, PG or psycho PG in the synchronous world, what you want to do is you want to pass it the query string with placeholders for any user submitted values, and then you submit the values that separately like an a list, and it sounds like a parameterised query, basically. Yeah, yeah. And as long as you do that you're safe. But then for a library when people are programmatically creating very complex SQL queries, and then you need to try and make sure that you've got the right values that match the right placeholders to pass to the adapter.
30:00 It is quite challenging. There's some like recursive code where it has to. So we use some query strings internally, we've been Piccolo, so he never concatenate strings for SQL statements, it just uses query strings. And then it compiles them before sending it to the database adapter. And it basically looks through all of the sub query strings it might have if it's a really complex query, and then it kind of passes it to the adapter. But yeah, it's just one of the complex things about building ORMs for sure. And also one of the most dangerous to get wrong. Yeah, yeah, it absolutely is. There's an untold number of bad things that can happen with SQL injection. And it's so easy, all you have to do is put a single little tick to comment out stuff cynical and finish that statement. And then you can run arbitrary code. And a lot of times you can even some database engines will let you run sub process type things, which is even worse, but yeah, you definitely want avoid it. Yeah, it's a good argument for using ORMs and query builders, because it will make it less likely, I think, for sure. Another thing that I wanted to touch on a little bit here is the actual filtering or projection statement type bits. So I mentioned using Mongo engine before, which I'm a big fan of, and it's basically a Mongo equivalent of the Django ORM. So in that regard, they're all similar and you do things like if I wanted to, say, where the band popularity, or say, let's say the band name is PythonIstas, right, you would just say name=PythonIstas, as a part of the filter. And there's two things that are crummy about that. One is you get no autocomplete that there's a column called name, because it doesn't really know what class even though you started out like band.objects, it doesn't in the filter part, it no longer knows that the name came from the band, right? That's not part of the language. And then the other one is you're doing n equals or, you've got like weird operators, like in the name like name__ gt for greater than and stuff like that. Whereas yours, you just write what you would put into an if statement or a loop or something like that. So you would say like Band. popularity, less than 1000? That's the thing you sent in there. Yeah, that's right. So I've been caught up, tripped up so many times in the past with Django, where I've had something like name, double underscore something else, and then it can't really understand that's wrong, like a linter. Or Yeah, when you while you're coding early windows at runtime, and you've got a 500 errors. So the idea here is a linter, would be able to pick up these problems exactly, because so much of the pieces are there just using **kwarcs. And then they figure out how to generate a query out of like, looking for special keywords in the key names, and then turning those columns also the refactoring thing, right, the linters. And if I want to do a refactoring to rename popularity, it's not going to check popular _gt as a keyword argument. It has no idea that was related yet. Yeah, definitely. So the way it's implemented this, the double equals and all these operators is the amazing things about Python is how you can just overload like fundamental things about the language. So you can overload what addition means when someone first tells you that it sounds like the most mental thing in the world, because why would you want one plus one for five, but then it turns out when you're building an ORM, it's golden. And this is one reason why I find Python just so compelling over and over again, is because as a library author, you can do this stuff, you can get closer to more of like a DSL than like a, like normal programming language. Yeah, absolutely. Yeah. So is this be done with descriptors, or what's the magic for the less than there's like Dunder like double underscore lt and you can override that. And then what happens is when you call that method, it returns a where object and then you can also you can do in brackets band.popularity, less than 1000. And then && sign as well. And the popularity greater than 500. So you can combine them with AND and OR statements. So the where statements, and Piccolo can get, like really powerful. So you just have to teach the where clauses how to and and then structure it in a way that Python I'll let it kind of go through, right? Yeah. Or you can do dot where for some stuff, and then another dot where statement, if you've got multiple where statements, it becomes like, it's an and yeah, but yeah, it's just, it's just all Python magic, which is one of the reasons I love Python. Yeah, speaking of over written things, the thing that I think is the most insane, but I'm starting to love but took a while to get used to is the way the path object works for defining paths.
33:59 The / often means like drive separation on the POSIX systems, and you know, it's close enough, you could actually put / in your strings in Python on Windows, and it'll still like, Okay, fine. backslash is what you meant. So they overrode the divide operator in code to allow you to concatenate strings and paths together. And that's just crazy. Yeah, the first time I saw it, I was very confused. But when you understand that, it's okay. But yeah, totally. Yeah. I'm, I've gotten okay with it as well. And I start to use it and I really like it now, but it's like, I don't know if I can get behind this is this is a bridge too far. This is that's division. What are we doing here? Now it's cool. It's talking magic, typical low light all around using metaclasses a lot. And there's something that got added in identify in Python 3.7 or 3.6. But they actually changed metaclasses slightly. So there's now like a Dunder magic method called init subclass. And Piccolo uses this a lot. And it's, it's actually an amazing hidden feature of hyphen where you can now add keyword arguments to a class definition. So if you had the class foo, open brackets, inherits from bar, comma, and then you can start adding keywords
40:00 Either one, but so yeah, and I really loved that as it's quite exciting that you could kind of build an app from multiple frameworks and be like, well, this part of the app will be better served by fast API. But this bit, I just need style to I want black sheep or whichever ASCII framework people can dream up. So it's, I think it's really exciting for the Python community, the ASCII spec, yeah. And we did talk a little bit about the challenges and the like cascading effect of async and await. But if you're already running a framework that has async view methods, there's just nothing to it, right? You just write your code and you just await the bids, you got to await in the VUE method. And it just like the server and the framework handle the event loops and all that business. Yeah, I think as well, what kind of happened is async, io came out, and it doesn't directly affect speed. It's more of a free port. But it's like the Python community took it as a challenge to build faster frameworks. And so there's a lot of them have really quite fast internals, and they do feel quite cutting edge. Yeah, like UV loop and stuff like that. They're like, how can we do this, but have the minimal overhead of adding this, I mean, people do talk about, okay, async, and await will make your code go faster. Well, he won't make CPU code go faster. But so often what we're doing, especially in web apps is waiting, I want to wait on a database and then wait on the network. And then I want to wait on the database again. And then I'm going to wait on an API and then I'm going to send back three lines of JSON, right, like 99.9% of that is just waiting on something else. And when you're using async, and await, like, you can just do other things while you're doing that 99% awaiting Yeah, so you'd have to do a database query, it takes a few milliseconds. But then if you use the time it module in Python, and you see how long basic operations take, they're more like microseconds. So it's like orders of magnitude difference in how long a database query takes to basic Python stuff. But this is why having stuff like UV loops really important because if you had a really slow event loop, it kind of wouldn't make much difference, because the event loops fast as well. And like, a lot of the projects I did in the past, the free ports are really important, because some apps, you won't have a lot of traffic, and then all of a sudden, you'll get 1000 users. So I'm just doing like live events, and you get 1000 people at once. And in that situation, throughput is incredibly important, right? I mean, look at how the whole healthcare rollout in 2008 went right, I just can't help but think there must have been more awaits available to those that those frameworks and those web apps, it just kept crashing and stuff was timing out. And I'm sure it's just like, well, we're just gonna wait on this other slow government API. And we're gonna do it for a lot of people, and it just gonna overwhelm it right. And it's just, it's crazy. It just feels natural. Because like in the web server world, you had Apache and a lot of people move to nginx. That's very similar. It's like event loop driven. And we've kind of seen how beneficial nginx was. So it just makes perfect sense to build your back end in the same way. Yeah, absolutely. Absolutely. All right, a couple more things, we got some time to talk about. Over here, you've got a Django comparison page, which I guess also could be slightly a Mongo engine comparison page, because like I said, they're basically the same thing without the nesting. So if somebody is familiar with Django, and they're like, I would like to consider using this for my framework, or for part of my code, or whatever. It's like one of these, but they already know how to do stuff. In Django, you have like, Well, here's how you would create an object and save it in the different frameworks. Here's how you would update an object and make changes and so on. And you can just go through one at a time and just sort of compare the different pieces, right? Yeah. It's quite heavily inspired by Django. But then I think Django, Django ORM. It's more pythonic. So rather than using where it uses filter for them with Piccolo, it's it's meant to be very, very close to SQL is that the theory is, if you know SQL, it'll be super easy to learn. And when you do need to drop down to a SQL query, there's no like mismatch, you just like, well, I'm always sort of working in a SQL mindset. But there are a lot of similarities with Django still, like, I think people can pick it up quite quickly. Yeah, I agree. Like instead of object that values list, you have a select projection, or instead of filter, you have a wire, but it's honestly not a huge mental jump to make. Yeah. And Piccolo also takes like huge inspiration from Django migrations, which I think is kind of like the gold standard of migrations in any language. So a lot of efforts gone into that. Yeah, that's another one of the batteries that you were kind of touching on right is the migrations. But yeah, but it's migrations are incredibly hard to do, right. I think the Django way is I can only imagine, I don't even want to try to imagine writing that because it seems really hard. Yeah. So the way Django does it is it looks at your tables, and then it creates a migration file, it then adds up the migration files to build a picture of what the schema looks like. And then that's how it then creates subsequent migrations by doing a diff, right? That's why we got to go from this level to that level. So here's the five migrations to basically use an order, yeah. And then you've got it, you've got to do code generation, as well. So like with Piccolo, it has to actually create a Python file. And that's harder than what it seems actually writing a Python file. But if you look at the Piccolo migrations, they're actually really quite beautiful Python code. And there's a little trick I use internally, I use, like the black format or on it before I write out the auto generated code out. That's cool. So at your generated code is Pip and all the goodness. Yes. So if you look at it, you're like, that's quite nice. That's clever, actually. Yeah, that's really clever. Yeah, it's cool. Yeah, yeah. I feel like other frameworks, like for example, the cookie cutter stuff, you know, you're just generating code files like crazy. I feel like you could apply that same technique is after we inject all the user enter values, let's just do a quick format on them and then drop them. Yeah, that makes sense because otherwise you'll you'll run your linters or on your product.
45:00 And they'll fail because your migrations aren't correctly formatted. So yeah, that's cool. A quick question from Teddy out in the live stream says I don't use ORM too much in my day to day, what are good use cases besides web apps for them? And where does Piccolo performs better? So some questions? So I think that data science is obviously a big bit. So another reason for building Piccolo is, is data scientists so much on the ascendancy in the Python world, and people are just, you know, still dealing with databases on a day to day basis. So you can use it in a script if you like. And there's maybe a couple of examples in the docs where, you know, you might be scraping some data from a website, and then you just need to stick into Postgres. So that would be another good example for using picollo. Yeah, there's a good example. And then where it performs better, it's, it's really way like you need the async. Or you might still want a web app component. Even if it's just like a data science script, you still might want an admin screen to view that data. Yeah, I think that performs better, you could maybe break into two pieces, where doespciccolo perform better? And I think the async stuff is really important there, like you say, and then where does an ORM perform better? You know, when you talk about performance and getting stuff done, like sometimes it's how fast is the code run? But sometimes it's how fast do I get the final thing built? Right? And I think ORM is even if they're not always the most efficient way, sometimes they're really efficient, but not always. But you know, they could help you safely get to the other side, especially if you don't know SQL super well. Yeah, they hold your hand a little bit. And yeah, I created this while working at design agency. And in speeds really important design agency not not really execution speed in terms of SQL queries, but in terms of scaffolding up and being productive. So there's so Piccolo has something called Piccolo asking you and then to use that command that will basically scaffold your web app. So I support fast API starlet black sheep. That's really cool. So yeah, it's easy to kind of create your starter code and your starter structure from that, right. Yeah, it's a bit like Django where, you know, you create a project on the command line. But with Piccolo, you get an option of different ASCII frameworks. And over time, I'd like to add way more, because there's this, there's many more exciting ASCII frameworks like Quartz, Sonic Django itself is actually an ASCII app, so it could support Django. Django has come along. So if people were out there listening, and their favorite framework wasn't listed in one of those three, or wasn't one of those three prs are accepted, I guess, and they could integrate, you know, their from their favorite Sanic, or whatever they're after? Yeah, definitely. Like any feedbacks, really appreciate it. So like, the community has definitely helped me a lot with Piccolo just just as much as trying it and giving feedback or pull requests are also like really valued, even if you just want to raise an issue to say, Well, how did you do this? Like, you know, that's still welcome. Yeah, awesome. Well, well, maybe that's a good place to talk about where things are going in the future, and kind of wrap up our conversation a bit. Yeah, so I feel like I'll never be bored with Piccolo because Postgres is continuously developing and adding new features. And I almost feel like Postgres has almost like an operating system and awake, like the amount it does is kind of insane. So even has like a pub sub built in, you can do like, Listen notify, well, I'd like to do post j.s support, timescale DB is a really up and coming extension, as well for Postgres, the time series data, but then a lot of the stuff I'm excited about is like on the admin side, so as I mentioned before, Piccolo admin effectively turns pydentic models into UI. So the next thing I want to add is you can basically give it arbitrary pydantic models, and it will render them as forms and the admin. So if you have, for example, you want to send an email, you'll just create a Send Email model give it to picollo admin, and it'll generate a form that stuff I'm really excited about as well just to increase the utility of picollo. admin. So a back end developer could build a functional app for a business without actually writing any code. That's kind of the dream is to build like a really, really great admin. Yeah, these self serve, like once you create the app and hand it off, how far can people go before they have to hire, hire your design agency again, or something like that, the more that they can just run with it, the better I suspect. Yeah, it's such a huge benefit from Django, like having that admin. So I just want to kind of see what I can do with the latest technologies to build really great one. Yeah. What's the story with Django and Piccolo, is there a reasonable way to click them together? Or is it really not too much so far? I think you can. I haven't really tried it much. But it's very configurable Piccolo. So you know, and none of the names they deliberately don't clash with Django. So Django has a settings.py, Piccolo, Piccolo_com.py. And then Django has a migrations folder, but Piccolo has Piccolo underscore migrations. So there's there's no clash there. So in theory, they would work together. There's no like compatibility layer between the admins. So you'd have like two separate admins, or I'd like to add support for Django as it is an ASCII app. And it's the originator of the ASCII standard. And I still think Django is one of the great kind of masterpieces in the framework world that it's lasted so long, and it's still such a rock solid choice. I would like to see what I can do there. Yeah, the closer those could be I think, right like this having the Genesis and so many similar ideas to Django, it seems like they should be somehow working together, which is great. Yeah, that'd be cool. All right. Well, Dan, I think that might be about it for time that we got let me ask you the final two questions that I always ask if you're going to write some code you can work on picollo what editor to use I think I caught a hint of it earlier but go ahead. Yeah, VS code all the way I was a huge Sublime Text and text mate user and out try out this VS code. See what all the hypes about and after five minutes, I was never going back. I just think it's such a great gift to the world for Microsoft. Just
50:00 is better and better as well. Love VS code around, and then notable PyPI package you want to give a shout out to. So I'm gonna cheat and pick two so pydantic because I think it's such a nice serialization library. And I think it could almost be in the standard library, it feels so pythonic and natural and then style it because I think it's just a beautiful like foundation for the ASCII world. And I'd really encourage people to look at the code to see the power of ASCII how it is this like turtles all the way down, every ASCII is quite interesting. So those would be my two shoutouts. Yeah, very cool. You know, fast API so popular now. But fast API is kind of like an opinionated view on top of starlette. to a large degree. Yeah.
50:38 Yeah. Well, I mean, it takes the two things he said, pydantic and Starlit and puts them together and like a nice way, which I think is pretty neat. Yeah, it's got great taste. Yeah, for sure. I just like to say one thing really quick. And just thanks to everyone who's contributed to Piccolo because there's been people who've been contributing for several years by this point, and have put a lot of work in so yeah, just a shout out to anyone in the piccolo community. Yeah, and you know, final call to action. If people are interested in using this. It's, it's good to go it's ready for production, web apps and all that kind of stuff. I didn't really want to promote it before it was ready and I use it in production have done for years and I am quite conservative about pushing stuff out there. Unless I think it's, you know, solid high score 100 unit tests, you know, it's it's solid. I'm not saying there's not some edge case I haven't discovered yet in some version of, you know, Postgres or something, but I use in production every single day. Well, congrats on building a really cool Pythonic ORM. I really like the way that you put things together. And yeah, looks great. And a lot of nice, modern Python features and people should definitely check it out. Cool. Yeah. Thanks a lot, Michael, that you bet. See you later this point. This has been another episode of 'Talk Python to me'. Our guests on this episode was Daniel towncenter. And it's been brought to you by Linode. And Us over at talk Python training. and the transcripts are brought to you by Assembly AI. Simplify your infrastructure and cut your cloud bills in half with Linode. Linux virtual machines develop, deploy and scale your modern applications faster and easier. Visit 'talk python.fm/linode' and click the Create free account button to get started. Transcripts for this and all of our episodes are brought to you by Assembly AI. Do you need a great automatic speech to text API get human level accuracy and just a few lines of code visit 'talkpython.fm/assembly AI'. On level up your Python we have one of the largest catalogues of Python video courses over at talk Python. Our content ranges from true beginners to deeply advanced topics like memory and async. And best of all, there's not a subscription in sight. Check it out for yourself at 'training.talkpython.fm' Be sure to subscribe to the show, open your favorite podcast app and search for Python. We should be right at the top. You can also find the iTunes feed at /iTunes, the Google Play feed at / play and the direct RSS feed at /RSS on talkpython.fm. We're live streaming most of our recordings these days. If you want to be part of the show and have your comments featured on the air, be sure to subscribe to our YouTube channel at 'talkpython.fm/youtube'. This is your host Michael Kennedy. Thanks so much for listening. I really appreciate it. Now get out there and write some Python code