Brought to you by Michael and Brian - take a Talk Python course or get Brian's pytest book

Transcript #237: Separate your SQL and Python, asynchronously with aiosql

Return to episode page view on github
Recorded on Wednesday, Jun 9, 2021.

00:00 Hello and welcome to Python Bytes, where we deliver Python news and headlines directly to your earbuds.

00:05 This is episode 237, recorded June 9, 2021.

00:09 I'm Brian Okken.

00:10 I'm Michael Kennedy.

00:12 And I'm Mike Groves.

00:13 Hey Mike, thanks for showing up today. Tell us a little bit about yourself.

00:16 Yeah, well, thank you guys for having me. This is actually really fun.

00:18 My name is Mike Groves. I am leading the engineering at a company called Assembly AI.

00:23 We are mainly a Python shop, but we do a lot.

00:28 We do speech to text for developers.

00:32 We're an API company.

00:33 So you can really think of us as we are trying to make speech to text and all of the ways that you can take that text and do things with it really easy for developers so that they can integrate this value into their products and make their customers happier.

00:49 - Yeah, that's really cool.

00:50 I had somebody who, I had spoken to you guys at Assembly AI for some talk Python stuff, But someone else that I'm working with reached out to me and said, "Hey, I'm doing this stuff with this assembly AI API, and I don't know how to save the file that well.

01:04 Can you help me?" I'm like, "Oh, this looks really simple. This is really nice." So yeah, you guys are doing good work.

01:08 >> Yeah, we try to make it really simple.

01:09 >> Yeah.

01:10 >> Yeah. Fantastic. Brian, shall we kick it off?

01:13 >> I think we should.

01:14 >> With the TUI?

01:15 >> Oh, yeah.

01:16 >> Part of a GUI, a graphical user interface, and this new trend of taking stuff that happens in the terminal and making it nicer.

01:25 We've covered things like rich and stuff before, right?

01:28 >> Yeah, a lot. I love rich.

01:29 >> Yeah, I do too. I'm a super big fan.

01:32 So I want to cover something that's like a derivative of rich from Will McGann.

01:37 See, here we are messing up names already.

01:40 Sorry, Will. The idea is, if I wanted to create like Emacs or a richer UI with something like rich, I can control stuff on the screen and how it prints, but it's not full-on curses.

01:53 It's not, I want to write in this section over here and I want to dock this other bit to the right and have Rich automatically put stuff into it and so on.

02:00 So I introduce you to Textual.

02:03 It was called, T-U-I, as in text terminal user interface, but it's now called Textual.

02:10 And the idea is we can have these cool things, like I would like a thing to be a header of my terminal app and the footer of my terminal app.

02:17 And I want to dock a 30 column wide section to the left and then just fill out the rest in a little area that just takes the rest of the space.

02:25 How cool is that?

02:26 Yeah, it's great.

02:28 Yeah. And then each one of these areas is written to and controlled by Rich.

02:32 So all the nice stuff that we've already talked about with Rich and the really nice things you get from there.

02:38 So, you know, if people remember like Rich lets you have spectrums and like centered text or right aligned text and tables and just all sorts of good stuff.

02:49 So think of doing that, but on top of this, like, it's almost like a layout for terminals.

02:55 Yeah, he's kind of rethinking how to do terminal stuff. So this, I know you can do all of this stuff with Cursus libraries and things like that, but Cursus isn't always easy on all platforms.

03:07 So he's rethinking all of it. And I think it's really kind of fun.

03:12 Yeah, I do want to point out something here really quick. There's a comment in the readme I mean, it says this project is currently a work in progress and may not be usable for a while.

03:22 So, you know, just kind of maybe watch the repo and get notifications and stuff and see, you know, keep your eye out for it.

03:29 On the other hand, this kind of thing, these early stages, a lot of people contact us and say, I really wanna get into open source.

03:36 I want to contribute to some project.

03:38 What should I work on?

03:39 Should I work on Django?

03:40 It's like, well, that's gonna be a little bit complicated.

03:42 It's highly polished and there's a ton of dependencies.

03:45 Like projects like this that are in their early stages are really good for jumping in and getting involved if it sparks your interest.

03:52 - Yeah, and there's a lot of room for people to do things like add to the testing, do documentation, test on different platforms, and really help it so that Will can concentrate on doing more features.

04:03 - Yeah, yeah, exactly.

04:03 Even documentation, like a little tutorial or whatever.

04:06 And Kim, out in the live stream, hey Kim, says, "I would argue Cursus isn't easy anywhere." I agree, it always feels like, oh boy, we're going down this path.

04:15 Mike, what do you think?

04:16 - Well, it looks like a really cool library.

04:19 And I think for me, I try to think of what would I use it for and certainly a lot of internal tooling, that we could use this for to, so that we can make better ways of monitoring our machine learning systems and how that all works.

04:33 And so, yeah, I think that's would be something I would like to look at.

04:37 And yeah, and also just a lot of times too, is just trying something out, even if you have like a day or two to just try it out and hit the bug so you can really communicate that with the community on what is broken and what could be fixed.

04:49 - Yeah, absolutely.

04:50 I'm excited to see where this goes.

04:52 - Yeah, so next up, we wanna talk about pip-tools.

04:57 So we actually, one of the listeners, John Hagen, contacted us a while ago and said, "Hey, have you covered this?" And we thought we had, but I don't think we have.

05:06 So I'm gonna cover it now.

05:08 pip-tools, so we've talked about other things around PIP, like Poetry and there's other ways to, and some virtual environment controllers and stuff.

05:18 This isn't that, this isn't an all-in-one thing.

05:21 But one of the things that, like for instance Poetry gives you is, and a couple of other packaging things is lock files and stuff.

05:30 A lock file really isn't what it does.

05:34 I don't know if it's a lock file or pinned requirements, but the idea around, PIPTools does a whole bunch of stuff, but right now I'm just going to concentrate on the compile part.

05:44 The problem it solves is, so let's say I'm going to give an example.

05:49 I've got a requirement, my dependency is rich and typer is two libraries I'm depending on.

05:56 I could just have a simple file that has this, just those two labels in it.

06:03 Then with PIPtools, I can take that and take that file and compile it.

06:10 I run pip compile and then I get a pinned list.

06:15 It actually just goes in and writes the requirements.txt file.

06:19 Just from an easy, I require these things and it'll pop out the actual pinned dependencies.

06:27 Now I can deploy this and all the other developers and stuff can use it and be okay with it.

06:33 Of course, before you do the deployment, please test this.

06:39 That's the idea, is you update your requirements and then test it before you deploy it.

06:46 But this little extra step of taking, I've got a loose list of requirements, and I want to create a pinned list.

06:54 pip-tools also has a way to just say, let me get the syntax right, upgrade.

07:00 You say --upgrade and it updates your requirements.

07:04 One of the things I love about it also is it doesn't just do one file.

07:07 If you've got a requirements.txt and a dev requirements.txt, for instance, with your testing tools, pytest and such, you can throw that in a different file and it'll update those too.

07:19 So you can have actually as many requirements files as you want, and it handles it just fine.

07:25 This is a really cool thing.

07:26 Jonathan also gave us an example project, this Python blueprint that has example code in it, and it uses this workflow and it has the readme.

07:38 If you want to check out a project that uses this, go and do that.

07:41 I don't really, actually to be honest, I don't use the pip-tools for anything other than this.

07:47 This is what I use pip-tools for, it's just this.

07:49 I know it does other things too, but this is what I use it for.

07:52 >> I'm psyched about this. This looks fantastic.

07:55 I have a requirements.txt and a dev requirements-dev.txt.

08:00 And in there, I've been putting like at the top, here's the stuff that I really wanted to install.

08:07 And below it, these are the things I want to pin, not because I actually care about them or you would actually need to mention them, but I want things like to pin to bot to see that there's a security problem in the dependency I'm using, not the thing itself.

08:19 And so it'll bump the version and fix it, right?

08:21 - Yeah.

08:22 - And that means, I just noticed a couple of days ago that there's these other libraries.

08:26 I'm like, where did this come from?

08:28 This is in my virtual environment and I didn't, it's not in either of those scenarios, right?

08:33 Well, some new dependency was added to the main dependency that I didn't pay attention to.

08:36 So now I'm like babysitting the bottom half of my requirements TXT file, which seems like a thing I don't wanna do with life.

08:43 This is beautiful.

08:44 I'm all about this.

08:45 I'm very much thinking that this is gonna be something I'm using out there.

08:49 My guys seem to agree.

08:50 - Yeah, I absolutely agree.

08:51 And I actually have, we do not use this.

08:53 I do not use this, but I will be looking into this right after this podcast.

08:57 So, you know, like, why am I not doing well?

09:00 This is this. This will make my life a lot easier, and I'm definitely looking to it.

09:04 Yeah.

09:04 Now, Juergen brings up, oh, you, the PIPTools is awesome.

09:10 Yes, it is.

09:12 And he also brings up that it's all about the app versus library story.

09:16 So PIPTools is definitely on the app side.

09:18 So you you wouldn't want completely pinned dependencies in a library because it might conflict with the rest of the application.

09:27 So, right.

09:28 Or pin you to say, I have to have the one that has the vulnerability in it rather than the newer one that fixes it.

09:33 Right?

09:33 You don't want that.

09:34 Yeah, right.

09:35 Yeah.

09:35 Awesome.

09:36 Yeah.

09:37 So I think, yeah, I think you're up.

09:39 I think I'm up.

09:40 All right.

09:40 Now you, okay.

09:41 So here, here's a hint on the pronunciation, but you're gonna have to give this a shot on your own.

09:45 Yeah.

09:46 So I'm going to say it's, it's pinegwin, but I guess you could call it penguin.

09:49 I don't know.

09:50 I don't know what the proper pronunciation, pronunciation there is. So Penguin is a library for automating the generation of tests. So basically what you do is at CLI, you set this up, you point it at your code, you can sometimes give it hints, and then it goes off and it processes your files and produces tests. Just disclaimer here. They mentioned this several times. And this is very important. It will run your code.

10:22 So and it will try many different inputs. So you have to be very careful with what that code is doing. So they suggest wrapping it in a Docker container to prevent it from affecting or poisoning your file system.

10:37 Yeah, maybe don't give it access to the production database, something like that.

10:41 Yes, exactly. Exactly. So yes, I didn't go deep into it, but I know that they're using some interesting search techniques. This is actually developed by a colleague at a university and there's a white paper behind it. I don't think it's here, but if you look it up, I'm sure you could find the white paper that's associated, but it's supposed to be a very interesting technique around searching for the inputs that would find good edge cases.

11:12 So it does take a while to run, they say, so you can give it some hints and they talk about that within the documentation. But I really think this is something for me, when I look at tools, I'm like, "Well, what would I use it for?" And I think a lot of times we're busy and we don't get the coverage that we really want to get because deadlines and we want to land this customer and so we have to work really quick to get this one feature done and we only get half coverage instead of getting 90% or plus.

11:42 So I think this tool could help maybe bridge that gap in those cases and maybe give you a jumpstart to getting the coverage that you're looking for on your projects.

11:54 So that's what I'm looking at it for and yeah, I think it's a cool project.

11:58 It looks like one of those systems a little bit like hypothesis.

12:01 Are you familiar with hypothesis?

12:03 - Only by name.

12:04 I haven't actually looked into it.

12:05 - Where it kind of tries to determine, oh, what are some edge cases and some, like you give it bounds of data and it'll test different scenarios for you.

12:13 It looks quite interesting.

12:14 Brian, are you familiar with this?

12:15 - I'm not.

12:16 I'm actually often leery of test generators, but I don't know if people find it useful.

12:23 Maybe it's worth checking out.

12:24 - Yeah. - Yeah.

12:25 - Yeah, it looks neat.

12:26 - Yeah, I'm the same.

12:27 I would say I'm the same, Brian.

12:29 I did, you know, I was a little bit leery, I guess, about the idea of it.

12:33 For me, as I said, I think it's just really that gap that, you know, if you have a gap in coverage and you're really just trying to get a quick jumpstart on your coverage, this might be a good tool to start with.

12:45 That's my thought.

12:46 - Yeah, yeah, very cool.

12:47 That's good.

12:48 Although, Brian, I don't know if you really wanna encourage it too much.

12:51 You were getting out there.

12:53 If I'd known about Penguin earlier, I might not have bought Brian's textbook.

12:56 >> You didn't need to highlight that.

12:58 >> Indeed, indeed.

13:01 All right. You got the next one?

13:03 >> Well, I think we're at the sponsor bit.

13:07 >> I believe we are.

13:08 >> Yeah. I don't have our sponsor up.

13:13 >> I got you.

13:14 >> There we go. This episode of Python Bytes is brought to you by Sentry.

13:17 How would you like to remove a little stress from your life?

13:20 Well, do you worry that users may be having difficulties or encountering errors in your app?

13:26 Right now, would you even know until they sent you a support e-mail?

13:30 How much better would it be to have error and performance details immediately sent to you, including the call stack and values of local variables and the active user recorded in the report?

13:41 That'd be awesome. With Sentry, it's not only possible but simple.

13:45 In fact, Michael uses Sentry on all of his web properties including Python bytes here.

13:50 He actually fixed a bug triggered by a user and had the upgrade ready to roll out as he got the support email.

13:57 That's also Sentry, but also because Michael's awesome.

13:59 But anyway, surprise and delight your users today.

14:03 Create your Sentry account at

14:06 Please, when signing up, click the "Got a promo code" and redeem and enter Python bytes, all one word.

14:14 It's good for two free months of Sentry's team plan, which will give you up to 20 times as many monthly events, as well as other features.

14:23 That's Python bytes slash Sentry and promo code Python bytes.

14:28 - Yep, thanks Sentry.

14:29 All right, I guess I got the next one.

14:30 This one is sent in by a friend of the show, Brian Skin.

14:34 Thank you, Brian.

14:35 And he let us know that there is something out there for all of us who love these external packages and all of these amazing Lego building blocks that are PyPI, and yet that's running other people's code with dependencies on them getting things right.

14:52 And this is under the PyPA.

14:54 So this is sort of pretty neat in terms of, you know, being officially the Python Packaging Authority Group, right?

15:02 I believe.

15:03 And so it's called the Advisory-DB.

15:06 It's a security advisory database for Python packages published on

15:11 And the idea is if somebody finds some major problem with the package, or maybe even worse, maybe it's like a type of squatting scenario, but more like, you know that part where they were supposed to check the input in that form, and then they did this direct SQL query, they didn't do that, now they are, you really, really should change that so that they're using like parameterized queries now.

15:33 And so there's a vulnerabilities directory that is just a bunch of YAML files.

15:38 You come over here, like, let's take one here that maybe is security conscious, Bleach.

15:44 I love the name of this package.

15:45 The idea is you take user input and you sanitize it by putting bleach on it, which is fantastic, but even the sanitizers can have problems, right?

15:53 So we checked this out back apparently in 2020, there's a problem with bleach in PyPI.

15:59 It said in Mozilla bleach before 3.12, a mutation XSS cross-site scripted in bleach.clean when RC data and either the SVG or math tags are whitelisted and such and such is set, like, oh my gosh, but here's all the version it affects and here's the fixed version.

16:17 And then there's some more info about like where that was spoken about, like where the problem was discovered and so on.

16:22 This is really cool.

16:23 So if you depend on these packages, and we already spoke at the beginning about how having a tracking your dependencies, not just the things you directly install, lets you be more aware of this, right?

16:33 You could look at that pip tools generated requirements.txt file and see this problem.

16:38 And then very likely GitHub through dependent bot would even be proposing a fix.

16:42 What do you guys think?

16:43 - Yeah, that's great.

16:44 - I think this is cool.

16:46 So how do you use it?

16:47 Do you just look stuff up or?

16:49 - You let it help you.

16:50 I mean, you can come over here and obviously look it up.

16:53 It depends on what's going on.

16:54 I think more if you're maintaining a package, you can do a PR to let people know.

17:00 There's a triage service, which goes through the NIST.

17:04 It pulls a lot of data from the NIST, what's it called?

17:07 The National Vulnerability Database Data Feed.

17:10 Woo, say that again.

17:11 So like if it gets submitted there, it gets a lot of the data gets pulled back in.

17:16 They have a tool to perform some heuristics to match that back up.

17:19 And then you can do most importantly is there's an API that you can use.

17:23 So these vulnerabilities when submitted to this GitHub repository are then submitted outwardly to this place called the open source vulnerability or the database for open source vulnerabilities.

17:34 And then this has an API that people can call to learn about these problems.

17:39 So if you ran, I don't know, if you were an API company you wanted to make sure your API wasn't getting hacked by having a bad dependency, maybe you make this part of your CI/CD or something, Mike.

17:50 - Yeah, absolutely.

17:51 I'm gonna have to tell our DevOps guru, Mitch, to look into this.

17:55 (laughing)

17:56 - Exactly.

17:57 So, I mean, for the most part, having stuff pinned in GitHub will trigger an immediate security notification to you when GitHub finds out about it, when it makes it out.

18:07 But if you wanna be a little more proactive or you're a security researcher or something like that, This looks like a good one.

18:13 So in the longer term, we're looking, we're working with the PyPI team to build a pipeline to automatically get these vulnerabilities into PyPI.

18:21 So maybe like you could see it or something like that.

18:23 I'm not exactly sure what the story is there, but it's a start for basically storing that data and sharing that data.

18:31 And then hopefully like it makes its way over to PyPI and such.

18:34 Sam Morley, welcome.

18:35 Glad you made the live show, man.

18:37 Said, is there a tool that can run like a pre-commit hook?

18:41 I feel like maybe with just a tiny bit of wrapper, you could call that API over at the open source vulnerabilities project and get some information back.

18:49 - Yeah, that'd be cool.

18:50 - Yeah, that would be a cool thing.

18:50 Like don't check that in, it's got a problem.

18:52 - Yeah, don't push it.

18:55 - Yeah, exactly, exactly.

18:56 So anyway, I think this is a pretty cool one.

18:59 Thanks Brian Skin for sending it in.

19:01 - Yeah, so I don't know.

19:03 - Sorry, Brian, I was gonna say, yeah, you're a C++ developer, right?

19:06 - Yeah.

19:07 - And C++ is all about overriding functions by type.

19:10 Like, oh, it takes an int, it takes a float.

19:11 This one takes a string and it's totally different.

19:13 Python doesn't have that.

19:15 - Yeah, what's funny is I don't actually use it that much in C++.

19:18 I mean, you learn about it a lot, but I don't, I mean, I normally don't write a whole bunch of functions that have the same name but take different parameters.

19:26 But you can do, yes, you can do that, definitely do it in C++ and C.

19:30 And there's probably other languages you can do that in, but those are the two main languages that I work with.

19:35 - C#, for example, does it.

19:37 - Java as well.

19:39 The Python, you cannot.

19:40 Python, if you try to do that, the second one wins and you just have another name that is attached to the function.

19:49 I never really thought that I had a problem with this, but there are definitely times where it would be simpler to not try to put a switch in place or something and just have several functions that dealt with related things but in different functions.

20:06 there's an article called The Correct Way to Overload Functions in Python.

20:12 Maybe it is, but I'm not somebody that likes to use the phrase, the correct way because somebody will tell me I'm wrong.

20:20 But anyway, it's an interesting article.

20:22 >> You might, for example, hear like, you shouldn't overload functions in Python.

20:25 You're doing it wrong if you do it at all, for example.

20:27 >> Yeah, exactly.

20:28 >> Although this is a cool solution. I totally like it.

20:31 >> Definitely, it's a cool solution.

20:32 There's apparently a built-in way with FuncTools.

20:38 FuncTools has a decorator called single dispatch.

20:44 What it does is you can decorate basically a function signature with a single function.

20:50 You say, I'm going to single dispatch this function.

20:53 Then you register all, you do the name of the function.register as decorators around other functions.

21:03 The example shown in the articles just uses underscore.

21:07 I'm guessing that's just because you don't care what the name is.

21:10 I don't know, but that seems like a good way to do it.

21:13 Then after that, you just have that works.

21:17 It works like you've done function overloading.

21:19 >> That's awesome. In the example it takes, you can call this format function with a string, with a date, with a date time and a time, and you actually get, there's like three different functions that get called.

21:31 One for a string, one for a date, one that just falls back or something.

21:35 >> Yeah. It's a pretty simple example and it's pretty clean code.

21:41 I would definitely try to keep all these things together in one module of course.

21:47 >> You would be mean to put them in different places.

21:49 That would be so wrong.

21:50 >> Or even like spread out in the same function would be terrible.

21:54 but keeping them together, this is great.

21:58 One of the things that the article mentions is that, it's called single dispatch because it's based on the type of the first parameter.

22:10 So if you want to do multi-parameter ones, there's a third-party plugin called multiple dispatch.

22:18 >> Wow.

22:19 >> You can just pip install.

22:21 Then it works the same, although-

22:23 - Ooh, I kind of like this one better, honestly.

22:25 - The syntax is a little different and it probably does single dispatch too, I'm guessing.

22:31 - Yeah, just one parameter.

22:34 - But in this one, yeah, similar sort of thing.

22:37 It just works on multiple parameters, multiple options.

22:41 - I'm digging the multiple dispatch style where you just say dispatch, this is arguments are list str.

22:46 You know, we're gonna maybe get to where Mike is going at the end of the show, but it would be even cooler you could just say at dispatch and then put a type like a colon list B colon stir.

22:57 So I want to dispatch on types and stuff like that and have the dispatch decorator look at the function that's passed in and look at the type parameters and then just do that.

23:07 So you don't have to say the types more than once anyway.

23:10 We'll, we come back to that as well.

23:12 Yeah.

23:13 So, so I, you know, I'm in a past life.

23:15 I was, I was a Java engineer, so we, you know, we call it dynamic dispatch in Java.

23:22 And it's actually, there's a lot of patterns, object-oriented patterns that kind of are derived from that feature, from the, you know, from again, from the C++, well, from the old days.

23:34 And yeah, no, to me, this actually, you know, when I saw it, I was like, okay, I could definitely use this for a lot of cases.

23:43 I know that like when you might wanna build, when you have like a piece of code that has to work with a variety of different types, but they're very similar types maybe, and you want to do something with that family of types, you know, I think that would be something I would, you know, I would think to look at, to solve with something like this.

24:02 - Yeah, yeah.

24:03 I kind of had the same thought, and I do not think I would make this any sort of normal workflow use case for my code, but there are times where you have some code that says, if, you know, argument is, the type of this is a list, do this thing.

24:17 If it's not a list, then make an empty list, put it in and then do this other thing.

24:21 You know, like if you're doing that type switching already, like this dispatch thing might actually make it more clear.

24:27 Right? It's not common to do it in Python, but there are times you're like, I kind of want to be able to take a string or a date time and then just parse the string of it.

24:33 You know, those kinds of things.

24:35 - So, as an API company, this is actually kind of interesting, it fits into what we do as an API company.

24:40 We have to, a lot of times, you know, we're not very, we're not overly strict on our input types.

24:45 We allow like truthy and falsy kind of types to come in for like Booleans and I could see us using this for that.

24:53 Right, so that way, instead of using all the helper code we've already written for that, have it more along the lines of, okay, if it's this type of input, you know, then you can convert it this way.

25:01 If it's this type of input, you can convert it that way.

25:04 And I think we could probably write some code to handle our inputs differently using this pattern.

25:09 - Yeah, yeah, very neat insight there.

25:11 Brian, you blew up our chat with this.

25:13 (Brian laughs)

25:14 - Well, you wanna pull out some highlights?

25:16 - Yeah, all right.

25:17 So John Cian says, "Function overloading was one of those things like switch statements that I missed when I first started Python, but then I found I rarely needed it." Yeah, I'm kind of with you on that.

25:26 Like I said, there's a few cases for me that I'm like, where I'm doing a type check or is instance of, that I think is the one time.

25:32 But that's a couple of times in a whole program.

25:34 What do you think, Brian?

25:36 >> Yeah, I definitely missed it at first, and now I just don't even really notice it.

25:41 >> Yeah.

25:42 (laughing)

25:42 - Then you've got, you're gonna get into this, Luciano Romano, also explains this in Fluent Python.

25:49 I just read the pre-version of the second edition, getting some polish, yeah, very nice.

25:54 That's a really good book, Fluent Python as well.

25:57 Came out there says, "That's the kind of thing I was thinking of." Yeah, oh, sorry, that's a different comment.

26:02 I'll come back to that in a minute.

26:03 And then, (laughing)

26:04 Sam Morley, "I don't tend to find this kind of dispatching "that useful, I generally just write a master function that takes star, star, KW args and dispatch to other functions.

26:13 - Yeah. - Yep.

26:14 - But if you're switching on this instance, it could be a time.

26:18 - Yeah.

26:19 - And then stepping back one quick topic, Kim VanWijk says, "Would be handy if some pip tooling "could automatically check the PyPA advisory "before downloading a package." That would be slick, yeah.

26:28 I just did a typo squatting and PyPI security episode over on Talk Python and we had a lot of interesting ideas, like almost like a have I been pwned, like remember what I installed and if you ever see I installed something, and how to vulnerability quick, shoot me a note, let me know, hey, a few weeks ago, you installed this thing, you might wanna get rid of it now, stuff like that.

26:45 I think this is another interesting thing along those lines.

26:48 All right, like how to cache that data and then just say, you know, I know you wanna install it.

26:53 And maybe this is what they're talking about with integrating that into itself, who knows?

26:57 But very cool.

26:58 All right, Mike, you got the last one, some secret.

27:00 - Yeah, so AIO SQL is a, as it says, simple SQL in Python.

27:06 It's a SQL templating framework or library that really, what you do is you give it some SQL files with some, it has some conventions around how you define queries.

27:18 And then it essentially gives you a query mapping that you can then use in your code.

27:22 And this is kind of a, it's a minimalistic way of accessing your database.

27:27 It is, with the A in front of it, it's natively asynchronous.

27:33 It works really well with async-pg.

27:35 I know that we use Postgres, so that was a highlight for us to look at it.

27:44 And I think we all have worked with ORMs.

27:48 I've come back and forth on ORMs over the years.

27:51 I think this is a nice fit for when you don't have to touch a lot of different tables or do a lot of joins.

28:00 Maybe you have a small microservice that just needs to do a couple, you know, reads and writes updates.

28:07 And it also I think this really helps because ORMs, especially when you're dealing at scale, you have to really know the ORM at a very deep level to understand what it's doing.

28:16 And this kind of exposes all of, you know, it really just brings you down to the SQL level.

28:21 So now you know exactly what you're doing.

28:22 And you can be a little bit more direct on what you want to do with your data.

28:26 - Yeah, yeah, sometimes you're like, well, I want to do the join and then the filter not the filter, then the, I don't know.

28:32 Like if you're really good at SQL, you can put these little tricks and like, oh, it's actually likes it better if we do it this way rather than that way.

28:40 And, oh, I'm probably not gonna let you do that.

28:42 So let me just describe this to people out there 'cause it's breaking some paradigms for me.

28:46 So this is a library that allows you to write queries against a database, but you do it almost with data access, data access layer style of things.

28:57 So you don't do a quote select star from such and such.

29:01 You would say like query.getAllGreetings or getUserByUsername and you pass a username equals such and such in an asynchronous way, which is pretty interesting.

29:12 But the way that that has meaning is you create a .sql file that has like a doc string like thing and you write a bunch of SQL, almost like stored procedures, but just in a text file.

29:23 This thing parses it and then it becomes like a smart query data access layer.

29:28 - Correct, yeah.

29:28 So it basically builds out other data access layer or data access object kind of for you where you can basically map in these queries.

29:38 And it's not just queries, you can do mutations and you can do DDL and all of that.

29:42 So yeah, it gives you--

29:44 - Like create tables or whatever, give you a function you call, yeah.

29:47 - Absolutely, so--

29:48 - Brian, what do you think?

29:49 Do you touch SQL much these days?

29:51 I'm touching it more and more and I love this actually.

29:55 Because I always assume if there's a bug, it's probably in my SQL statement.

29:59 But this looks pretty cool.

30:01 So get your selects and stuff figured out what queries you want to do ahead of time and then use them in your code. This is cool.

30:10 >> Yeah. I'm very repelled by writing raw SQL statements in my code.

30:16 Very much like Kim VanWijk others.

30:18 This looks exactly what I like, like not writing SQL but not using ORM.

30:23 So it's no secret to the world out there that I'm a MongoDB fan.

30:26 So I don't have like direct use for this a lot, but I do find this super valuable, especially if people on the team are really good at SQL and they're like, "Oh, this library is inhibiting me "from like using my superpower on the database." Right, Mike? - Exactly.

30:41 Yeah, that's exactly.

30:42 And I was just about to say that, you know, if you have a DBA or you have someone who is, you know, basically they are focused or concentrated on the database and optimizing those queries.

30:51 You know, I think what this allows you to do, then you can go define your queries, you can write them yourself how they are as developer, and then maybe you can take that to the experts and have him refine them for you without actually changing the abstraction, right?

31:05 Like without, you still get that, yeah.

31:07 - Yeah, there's also times where like, let's say I've got an internal tool that I wanna put together and it needs a database, and it's really not that big of a deal to tell like your IT department and your supervisor and stuff, you're gonna throw up a MySQL database or something and interact with it or Postgres.

31:24 But to say, "Hey, I'm gonna throw Mongo in there," that might bring up just a discussion that you're just not ready to have.

31:31 - Yeah, for sure.

31:33 Let me suggest a pairing, kind of like as you pair wines, a pairing with this library.

31:39 I think Pydantic might go fantastic with this, right?

31:41 So you get these results back as tuples And then you could just, you know, star orgs that into your Pydantic models and you get your validation and all sorts of like stuff.

31:53 And then you get a list of Pydantic, like you're one list comprehension away from something beautiful here.

31:58 - Yeah, this is, that's exactly what we're doing.

32:01 You know, we're using, you know, I think right now we're building out a new stack and it's basically, it's FastAPI, Pydantic, you know, AIO SQL.

32:09 - Oh, interesting.

32:10 And you're finding it works well?

32:11 - Oh, it's, yeah, and it's very performant.

32:13 So it's, you know, because it's asynchronous, right?

32:15 We're using async PG all the way down to the database too.

32:18 So it's pretty much asynchronous from end to end.

32:21 - Fantastic.

32:22 This is a really good recommendation.

32:23 Yeah. You like it too, right, Brian?

32:24 It's pretty sweet.

32:25 - Yeah, this is great.

32:26 - Yeah, I do.

32:27 I really like the separation of concerns of the SQL statements and the Python code, but the flexibility of having both, it's pretty cool.

32:34 - Awesome.

32:35 - Brian, is that it for us?

32:37 - Yeah.

32:37 - Or maybe you have any other topics they want to bring up?

32:41 - Oh, you know I do.

32:42 - Kind of crazy, so not quite an extra, extra, extra, extra here all about it.

32:46 But a couple of things sent in from some listeners.

32:50 So let me make sure I credit these.

32:52 So Daniel Mulkey sent this one in and said, "There is now, has been for a little while, "from Science Direct, there's a journal for academics "called Software X, and this library is pretty cool.

33:06 "So it aims to acknowledge the impact of software "in today's research." So if you're in academics, one of the big challenges is that whole publish or perish sort of thing.

33:16 And it would be great to take this cool library and make it a thing that's out on PyPI or Conda that people can use.

33:22 Oh, but that's gonna take a week and a half and you really only get credit for your citations in articles, not in code, right?

33:30 So this kind of addresses that to try to give people a place to publish their projects in a meaningful way.

33:35 And I just wanna point out that there's a special issue on the software that contributed to the LIGO experiment, the gravitational wave discovery, which pretty sure got the Nobel prize.

33:45 I don't totally remember.

33:46 But if you just start poking around here, you know, you might find the word Python periodically in this thing here.

33:54 So over in this one, we got some Python.

33:57 I'm just randomly clicking articles and they're all coming up Python.

34:00 So I think that that's pretty sweet.

34:01 If people are in science and they do this computational side of thing, check this out.

34:06 - Very cool.

34:07 Yeah, that's number one.

34:08 Number two, PyCon has been finished for a little while and we were able to go through some like meeting platform type thing where we could watch the replays, but it's a little bit wonky in the way that the playback work.

34:20 Like I had to do some weird stuff to actually get logged back in after a while.

34:24 Anyway, it was fine for live stuff, but it was weird for playback.

34:27 YouTube is pretty awesome for playback.

34:29 And here's the, I put in the show notes, the playlist for all of PyCon 2021 US.

34:36 So that's pretty cool.

34:37 how many videos are in here, it looks like 86 videos.

34:39 So if you want to catch up on the PyCon talks, there's a really good way.

34:42 - Nice, it looks like Juergen had a lightning talk there.

34:47 - Yeah. - Cool.

34:48 - Yeah, very cool, well done.

34:49 And also want to make sure I get the credit for this one right.

34:55 Madison Swain Bowden, who we both met, I believe, at PyCascades, shared this with us, that AICS image IO, which is used all over B2B used all over biological imaging and microscopy and that kind of stuff from the Allen Institute, Allen Brain Institute, I'm guessing, was just released.

35:16 So if you're doing anything with like graphics and pictures and file formats and stuff for that, check that out.

35:21 And yeah, that's it for me.

35:23 I think, Mike, you got one as well, right, at least?

35:26 - Yeah, so yeah, it's PEP 563 was, I think it's postponed evaluation of annotations And basically there was some, as I think I mentioned before, I'm a big fan of FastAPI and Pydantic.

35:44 And there was some worry a couple of months ago that this change would effectively break these projects in 3.10 if it was introduced.

35:54 And so I just wanted to give a shout out to the community for jumping on this and just being really open-minded about this and basically postponing this a little further so that better decisions can be made on how to navigate this change.

36:09 So I don't wanna go too deep into it, but I read some of the threads on this and it was just really positive, really positive exchange within the community.

36:19 And I think it turned out really well.

36:20 There was like a unanimous decision, I believe, to postpone it and it saved these projects from having to, well, basically--

36:27 - At a minimum scramble, right?

36:29 Worst case, not work.

36:31 - Yeah.

36:32 - Yeah, let me see if I can give the elevator pitch there was a proposal in Python 3.10, I guess 5.3.8, I know, sorry, 5.6.3, I don't know where that came from, 5.6.3, PEP 5.6.3, where there is an attempt to simplify and speed up type annotations.

36:48 And by, 'cause right now, if you wanna do a type annotation, if I'm gonna say I've created a Pydantic model, it's called user, and I'm gonna say, this thing returns a user, I have to import user at the top.

36:56 And it was like, well, you're not gonna get those evaluated or really dealt with unless you're doing type annotation analysis.

37:04 So we're gonna treat those as strings basically.

37:07 Right, and things like FastAPI and Pydantic and others were going, they would actually get the type information, standard Python style, and then use that to modify behavior, right?

37:18 Like to do, oh, it says it's a list of ints, so we're gonna convert from this list of those, or maybe a list of user, we're gonna convert these JSON things to users.

37:26 But with this change, it would have said, well, it's a string, and what does user mean?

37:30 If you don't know the actual type where it's coming from, there could be multiple users.

37:33 It could be a user that couldn't track down, has it been imported yet, all sorts of weirdness around it.

37:39 The runtime behavior of these type annotated things like typer, pydantic, fastAPI, we're not sure how they were going to keep working with this being adopted.

37:48 It sounds like it's postponed at least till 3.11.

37:51 >> Yeah, exactly.

37:52 >> Yeah. Thanks for bringing this up. That's a good one.

37:54 >> Yeah.

37:54 >> All right. Well, into the show, do we have any jokes?

37:59 I got something for you.

38:00 I'll tell you, let me know if it qualifies.

38:04 This is from Zach, RMRF on Twitter says, did you hear about the four Pythonistas that robbed a bank?

38:11 Three got caught and when questioned, they all stated that the fourth would never be caught, couldn't be caught because he knew RegX and was something of an escape artist.

38:20 (laughing)

38:22 - That's funny.

38:24 - Yeah, I think that's pretty good.

38:25 Well done, Zach.

38:26 Thanks for sending that in.

38:26 Brian, you got anything as well?

38:28 >> Yeah, let me pull it up.

38:31 Not Rich, Will McGugan asked about Rich and said, "Hey, if I wrote a book about Rich, how much money would you pay for it?

38:44 But also what should be in the book?" Of course, I said, it should cover how fast it is to install it, kind of a get rich quick guide.

38:53 (laughing)

38:55 - Love it, love it.

38:59 And I believe the response was something like, get out.

39:01 - Get out, yeah, definitely.

39:03 - Oh, nice.

39:04 Will's getting a lot of coverage this week.

39:06 That's beautiful.

39:07 He's doing good work.

39:08 All right, speed of coverage.

39:09 Yeah, thanks for being here, Mike.

39:10 - Well, thank you guys for having me.

39:11 This was really fun.

39:12 - Yeah, thank you.

39:13 Thanks for everybody for joining on the live stream as well.

39:16 - Wow.

39:16 Thank you for listening to Python Bytes.

39:18 Follow the show on Twitter via @PythonBytes.

39:20 that's Python Bytes as in B-Y-T-E-S.

39:24 And get the full show notes at

39:27 If you have a news item you want featured, just visit and send it our way.

39:31 We're always on the lookout for sharing something cool.

39:34 On behalf of myself and Brian Aukin, this is Michael Kennedy.

39:37 Thank you for listening and sharing this podcast with your friends and colleagues.

Back to show page