ORM: The Sequel

Speaker 1: Hello everyone and welcome to our second set of talks for this morning. It is my distinct pleasure this morning to introduce you to Katie. Katie has worn many hats over the years. She's been a software developer for many languages, a systems administrator for multiple operating systems, and a speaker on many topics and many continents. When she's not changing the world, she enjoys making tapestries, cooking, and seeing just how well various application stac handle emoji. Here presenting her talk ORM The Sequel, Katie.

Speaker 2: Guten Morgen, Django Con. G'day mates. Hi, I'm Katie. I do a lot of stuff. In Django. I've spoken before at DjangoCon Europe uh in Budapest in two thousand and sixteen. I spoke and was a mentor at DjangoCon US last year. I also organized DjangoCon Australia last year. We had really cool t-shirts. Um currently I am a site reliability engineer at DVO. The wonderful people that brought you our uh live captioning. They're also the ones behind Django CMS. I'm also currently serving as a director of the Django Software Foundation and I don't know Django.

Speaker 2: Um I'm an absolute novice and I am not afraid to admit it. I still see the admin and go, oh my god, what kind of magic is this that makes it all go? It's just like you you start it up and there's all this stuff that's just there and it works and there's filtering out of the box and it's cool. But Just because I say I'm a beginner at Django doesn't mean I'm a beginner at tech or at programming in general. I've got Oh gosh, over a decade's worth of experience in software development, just not in Python. Before I started doing Python and Django, I worked with Haskell. and Rails and Ruby. And before that, back in the day, around the time that the magic was being removed from Django, I was working on

Speaker 2: a bit of software that probably no one in this room has ever heard of Oracle Application Express. Who here has heard of Oracle Apex? Four! Wonderful! Um It's this wonderful bit of software that runs for free if you have the free version of Oracle XE. And there's this wonderful administration page that no other bit of software ever has where you can go in and you can define your models and your queries and your filters and it's all just magic and it works. Nothing like Django at all. Um I I may have done this a little bit so much that I was a technical reviewer on a book for it. Um but this is going back a while now. So even though I haven't used Oracle or Application Express

Speaker 2: for years, there's one thing I still remember. SQL. Who here knows SQL? Great, you're in the right talk. Um SQL, structured query language, is A way that you can talk to databases in a structured query language. And given that SQL is one of the first base level technologies that I ever used and keep using, I still look at systems today and go, oh can't I just throw in a like clause? It would make my life so much easier. But getting back to Django, I'm still getting my head around how Django works. I mean, for a sufficiently set up Django project, it should be easy for someone coming in as a user into the admin section to just

Speaker 2: get it to work. But if you were to try to tell me how things worked underneath, I would just get this fog of war thing going on where I can't tell you what's going on. If you tell me, oh look at the models, look at this, look at that, I'm like What do you mean? And even worse, when it's like I want to debug something, I just get told, oh, just use the shell. What is the shell? What what am I doing here? What is that concept? This has only recently been filled with information, so it was a couple of weeks ago just an empty space when someone just said use the shell. I'm like, Seashell? But the ORM is this wonderful thing that lives in this magic shell, and that's the object

Speaker 2: relational mapper, and it's the m not magic. It's not magic, I'm not allowed to call it magic. It's the technology that makes Django go. But outside of context, what does that even mean? And context is so very important. But Let's step away from the normal way that these talks kind of go where you start with a blank slate and you create a new project and everything else. That's great for first-time developers. That's great for people that haven't done HTML before, know the concept of a blog, but want to make their own. But it's not helpful for developers who have done that so many times in so many frameworks that they want to start using something that exists or they need to debug something that already exists for their job.

Speaker 2: They don't have time to go make a blog and then come back to working on the production issue that's setting fires everywhere. So What I want to do is I want to be a bug hunter. I want to be a noisy miner, an Australian native bird, and I want to go around this field of intrinsically intricate kale with all its Fibonacci spirals and I want to be able to get my beak right down into there and work out where all the bugs are. So, for this context, we're not going to do a blog, we're not going to do a news portal or whatever the cool kids are doing nowadays. Instead, we're going to use a sample application for something that's near and dear to my heart Emoji. So

Speaker 2: here is our application. If you've seen my talk from uh DjangoCon in Budapest, you would be familiar with something like this, I call this uh Unicodex and it is a project where I can list a couple of different types of emoji Here we have the official conference emoji, the European Castle, also sparkles in unicorns because it's a Django conference. And what we do is if we click one of these we get to see the list of how these different emoji appear on different mobiles, laptops and the rest. So if I click on the sparkles, I can see that there's the Windows and the Android and the oh one of them's not working. That's that's embarrassing. So How would I fix that? Well, I'd I'd just use the shell, right?

Speaker 2: Well that's the thing Just using the shell is actually what you do. But the context is important because when someone just says just use the shell and you happen to be talking about Django, what they mean is this. What they mean is you want to navigate into your project, which you've helpfully already got local a local copy of your code, and you want to run manage. py shell And what you get is you get an interactive console where the path and environment information has already been loaded so you can start interacting with your Django project. And from here, you can use the shell. But how do I use the shell? Well, I'm an SQL dev, I deal in databases, so

Speaker 2: If I get into a project and I know that there's a database hiding behind there somewhere, the first thing I want to do is I want to be able to find all the tables. This is the bare basis of what any database programmer would want to do. They want to know the schema. So if you're using MySQL, you'd use show tables, Postgres. slash dt in Oracle, select star from DBA tables. In the ORM, there's no good way to do this out of the box. Now I made the mistake of discussing some of this content beforehand and I was told that there was this thing called uh Django extensions that may or may not solve this out of the box. But I don't know how to install that. I don't know how to add that in.

Speaker 2: All I have is a shell. I just want to copy-paste some content from Stack Overflow and I want to get a list of tables So here's some I prepared earlier. With the help of some wonderful core developers, I was told that this is the way you can do that. So We import from Django, Django apps, and then we do a nested loop over all the apps that are configured And then for all those apps, what models are configured, and then we print out a nicely formatted from something models import something else And this is something you can just copy and paste and dump into your terminal and you can start getting the ball rolling. So, back in our terminal. I Just copy and paste this information in like a good programmer does, and what I get is a whole bunch of import

Speaker 2: statements. And this is useful to me because it tells me, okay I've got some Django stuff in there. There's some contributor stuff. Oh yeah, that's the thing with the um uh the users and stuff. I've I've seen that before. Um there's this thing called Aldrin. Oh yeah, that's right. This is running on uh Divio Cloud. Um if you want to know more about Divio Cloud, we're holding a workshop on Saturday afternoon. Come talk to me at lunch Um, but the tables I'm really interested in is the stuff at the bottom, which has been helpfully named Unicodex, which also happens to be the name of my project, so that's probably very useful to me. So what we've got is we've got code points, vendors, vendor versions, and designs. So

Speaker 2: What we could also do is we can for any of those models find the literal table that's associated to it in the database. And this is going to help me later when I want to start running SQL statements because I know databases, I still don't know what a model is, or what an app is, or really what Django is, or why there's a pony but not a pony, but we're not allowed to say there's a pony because it's not real. Um so what I can do is if I have a model, I can call it underscore meta um thing and then I can ask it for the database table. This meta stuff is really cool. I don't yet understand it, but apparently there's people here that helped write it, so I'm gonna be asking you later so I can fill in that yet bit. Because I don't know Django

Speaker 2: yet. Just ask me again whether I know Django after the sprints. So, back in my terminal. I can copy and paste that import statement like a good little developer and I can import the code point model And I don't get any errors, which is really good. And then I can run this statement that I had before, and I can see that there's a table This is useful because what I want to do is I want to see all the table columns because I know the tables. Now I want to know what's in those tables So I can do this again with the uh meta stuff. I don't even know what it's called. It's just meta. Somebody will tell me later, but I'm not taking questions because I don't want to know yet. I want to do this as a beginner still. So

Speaker 2: We run get fields and back in our terminal, we run this and we get a whole bunch of fields. So this is This is familiar to me as an SQL developer. I can see that there's a uh automatically generated ID, there's a name, a description, and a code point. I don't have to do this via the ORM. What I could do is I can get it all by this other useful uh shell interface called db shell. where depending on what database I'm using, I get dumped directly into a shell for that database. And I can see right there that it says PSQL, and I know that that means Postgres, which means I can use the slash DT from before, and I can get all my tables. And I can see that very usefully there is all these auth tables and then there is the Unicodex stuff, which is the stuff that I'm interested in.

Speaker 2: So from here, any good database developer would go, okay, I want to select star from a table. So in the URM we do that by going our model. objects. all and that just lists them all out. I'd really appreciate if people stopped talking while I'm trying to talk. I've got the microphone. Thank you. Back in our console. If I copy and paste this statement before, and then I load it and nothing breaks, which is great, and then if I run this I get a list of all my code point objects. And this maps directly to the three that I saw on my homepage earlier. So I know I'm in the right application, which is really useful to know. uh that you're in the right place. So this is a set of objects

Speaker 2: and it's not a list. It's a set because it says query sets there and sets are important. So if I want to show the contents of the particular table, instead of just going star, I'd need a WHERE clause. So if I wanted to go just show everything that sparkles, what I would do is I would add a filter. So I want to filter on the name where the name is Sparkles. So put that into my terminal and I get a query set of one element of Sparkles. But if I want to get a specific record, not a set, what I need to use instead of filter is use get, which will return one record and one record only. And if you try to tell it to get more than one record, it explodes. But it helpfully tells you why it exploded

Speaker 2: and to let you know that, oh, I actually returned three. And this is really useful when you're still trying to get your head around the ORM. And now we come to the fun bit, joins. I've forgotten more about how inner joins and outer joins work with uh explain plans on Oracle Nine I then I care to remember. But thankfully the ORM makes this really easy and I don't have to remember how to do all that stuff, and that makes me happy. So if I wanted to join something where I wanted to say get all the designs for Sparkles, I would have to join the code point and the design table together. In the ORM, all I have to do is run a filter where I go code

Speaker 2: point double underscore name equals sparkles. Okay, well what's that double underscore doing? This is still not magic, but it's pretty close. So, new terminal, dump in all the code that I have, and then I get a list of sparkles. This is a what does my notes say? I'm still not sure what all these things mean. I'm a beginner. Um we have a whole bunch of fields that are separated by double underscores that end in a lookup. So back when we had all the fields on our model table, which we can pull up again with GitFields, we saw that we had a vendor version, a code point, and an image for This is now design. So what I can do is I can check for my other tables as well.

Speaker 2: Okay, I've got the the the name, the description, and the code point. That's great. So what I can do is I can concatenate all these fields with double underscores and I can start walking through my models if they're joined properly, which is Still something I have no idea how it's done, but right now this is all I need to do to try to find that bug from right at the beginning of the presentation. So We can connect all these things together, but the documentation says it goes field, field, field, lookup. And this is because by default, if you don't specify anything, it's an exact lookup. Which makes sense. And it's also really cool because if you remember back from earlier we had this slide, you can see that there's a double underscore exact up there in the URL. So we've been using this all along, we just haven't understood what it actually meant.

Speaker 2: And it's like, oh my goodness, it's doing the thing in the admin. This must mean it works, because it's using itself. Yay! I'm on the right path. So Back to our terminal. We can take all our models and all our fields and work out where the joins are and we can keep on going for as long as we can until we start scrolling around our terminal because that sounds like fun. So we clear our terminal. and this time I want to import from vendor version. Let's see how far we can walk along this one. So I can get the fields and I can check, okay, we've got a name and we've got a vendor. Ah, so this is vendor version VendorVersion has a vendor, so let's check vendor. Vendor has a name and an ID and it links back to vendor version. So that's cool.

Speaker 2: So what we can do is we can go uh design objects filter and then we can go uh what's going to be the longest names here? Uh vendor version, double underscore vendor, double underscore name, double underscore contains, because that's a really long one. Micro. There you go. A really, really, really long filter. And we get a list of all the designs. that have a vendor version connected to a vendor connected to a name that contains the word micro. So we happen to get all the Microsoft emoji And we can do a whole lot of different field lookups here. We don't just have to use contains. We can use uh ranges and nulls and begins with and starts with and all this other kind of fun and there's really useful documentation if you want to look up uh query set

Speaker 2: field lookups which is super helpful to know the term query set field lookups because if you don't know that term you don't know what to search for in the documentation. And the documentation is really useful but only when you know what to search for. So, here's my really long thing. Can I make it longer Allegedly, yes, because I can just add another filter onto this. I can go code point double underscore name uh ends with uh corn and I can get the Microsoft Unicorn emoji. And that's great Um, but so far we've been dealing with ands. I want this and this and this and this. But we can do other stuff We can use this wonderful thing that I learnt about uh twenty-four days ago today called Q. Uh not the race from Star Trek Next Generation, a very good sequel, um, but Q

Speaker 2: as in Query So what we can do is instead of having this filter with the double underscores and the keyword arguments or whatever it is, what we can do is we can have a query like before. So for the net this one we're going to be querying where the name is Sparkles and the description is shiny. And the equivalent SQL for this would be select staff from the code point table where the name is Sparkles and the description is shiny. We learned before that unless we have a lookup, what we're implying is double underscore exact. And that's the exact same SQL. What we can also do is we don't have to have all our filters in one go. We can go filter and then filter on our query sets again because query sets can be chained together.

Speaker 2: So you can run a filter on a query set and then keep going. We don't have to do that though. What we can do is we can wrap it up in a queue, and that means something, I guess. Um I'm still new to Python, I'm sure I'll hit an error and work out what's going on in a couple of slides. So I can wrap this around, the queue, and wrap it around, and then I can send a couple of arguments to filter and I have the same SQL happening I can also, instead of using a comma, use and apparently. Um, not sure how that works, but it's an and. And it's still the same SQL. And what I can also do is optionally wrapping Q, and it still generates the same SQL. Um and I don't have to do that. What I can do is I can also do it instead of the f first one being wrapped, I can wrap the no

Speaker 2: wait. How come I can't wrap the second one? What does this error say? Syntax error, positional argument follows keyword. Oh, we're in Python We have to like follow Python syntax laws? That sucks. Okay. Um, so for this particular one The top one is a keyword argument. The bottom one is a positional argument. You can't have it in that order, because Python's rude like that. So let's undo that change. So what we could do, of course, is instead of uh doing it this way, what we could do is uh I I don't want to just search for description anymore. I want to search for uh unicorns and sparkles and Oh yeah, sorry, Python again. Um, you can't repeat keyword arguments

Speaker 2: because Python doesn't let you say that two things uh have the same name because they're never gonna have the same name, which is annoying. So how am I gonna get around that? Well, I can wrap everything up in this queue again, and then what I can do is oh I don't want to see both unicorns and sparkles. What I want to do is see not and but or So what I can do is I can use a pipe. And that pipe, it looks like an or because you've got and is and pipe is or. So how does that work? Well, turns out that there's this actual magic in Python, and I refuse to ever call it technology, it's actually magic. where you can do things like um overload how operators work

Speaker 2: underneath and it's called metaprogramming and it's amazing. So To start off with, this pipe and ampersand that we were seeing before, what we're doing here is bitwise operations. Quick review. A bitwise operation, if I declare a whole bunch of ones and zeros, so one zero, one zero, one zero, and then I declare uh a whole bunch of ones and a whole bunch of zeros, what I can do is a bit mask. So if I ask it to show me X and Y, it'll only show me things where both of the numbers lining up is one And if I did OR, it's where either OR is one. So what I can do is I can use that concept and start building SQL statements like Venn diagrams, where I go, I want this and this or I want this or this.

Speaker 2: And it 's really quite clever, um, because we're doing these operations on query objects. So what happens in the back of Django is it literally does these operations where it says I have this query object and this query object and because syntactical flavor I'm going to use the pipe operator and I can do that in Python. Because the next slide is actual raw Django code. It's not scary, I promise. This is actual Django code that's been in there for like 10 years and was written by Australians. So in the class Q we define a double underscore or double underscore, or also known as dunder or method. Where what it's doing is if it sees two query objects, it's literally overriding what happens when it sees that bitwise

Speaker 2: operator in Python, which means that you can declare interesting things based on how you want your different objects to interact. Which means that we can overload how Python works with these particular objects and we can start making code that makes sense to a human as well as to a computer, which is really, really cool. And it's magic and I will defend the my use of the word magic to d to d to define this because it is magic. I come from Ruby So this is cool. Um so as well as and and as well as or we can also do negation which is really useful. So like before we can just do say I want uh things that are sparkles or not unicorns. So I can put a tilde in front of my queue and I can start building these really useful

Speaker 2: complex um filters without having to remember how to do inner joints, which is really useful. But consider we want uh code points where we want to start filtering things So instead of just saying I want name unicorn name sparkles, what I want is uh designs that contain PNG and starts with design. So if I copy this into my terminal like a good little rote programmer I am, I import my code point, I copy in my stuff, and I get a whole lot of results That seems like too many results. That also seems like the results are being truncated, which is a really useful feature of the ORM because allegedly somebody tried to do a select star on a million

Speaker 2: point table once and wondered why everything was breaking. So it truncates it for you, which is super useful. So what we can do, instead of printing out all the results, we can count them, which is another super useful SQL thing. So we can see that there are 44 results. Okay, that probably makes sense. Um so with this count, what would be the SQL for that? Well What we can do is we can ask Django what the SQL is that it just ran by importing a connection from Django DB and then checking the queries list. Like a good little road programmer, I copy and paste this in and I can check the most recent query and I can see that there's SQL. I know this It's like a Unix

Speaker 2: system. Um and because I like formatted code, let's throw this up and format it. So what we're doing, select count from table, join on this other table where the IDs are the same. Okay, that's good. We're not gonna have like Cartesian products that we don't want. And we've got our filters, which is great. Except when we start chaining together these filters, you have to be careful because if I was to filter things and filter things and filter things. What I would end up with is if I run this and check the count, I get 712. And from my database days, I know that I've done something wrong when I haven't joined stuff together properly So let's check the query and let's look what's happening here and ah

Speaker 2: I've got design in there twice and I'm not linking them together. So that's useful to know. So We know how to filter, we know how to do ands and ors, we know how to do query set lookups, we know how to not make Python yell at us because we've got syntax errors. So let's go back to the field of Kale and let's go bug hunting. This is where I get my laptop out. So, we had an issue here where we had a broken image and it looks like it's associated to whatever Twitter 1. 0 is. So We know that these are designs and designs have images attached. So what we want to do is we want to find whatever image is attached to that particular design object. So I go into my terminal, I load up the shell, and I want to import, well, I really want to import everything, so let's just import everything because import

Speaker 2: star is useful to me because I'm just trying to make this work. So I want to filter by designs and I want to filter by, okay, uh code name equals sparkles, okay. And then what else could I add that's useful? Oh yeah, uh vendor version vendor The name is Twitter. Yeah? Okay, cool. I have two results. So I want to get just one of these. What I could do is I could assign that yeah, okay, I'll assign this to the variable D and then clear my terminal so you can see what's going on and then I've got my query set. So I could just get the first element because I'm lazy or I could do something better where I filter properly by what's going on here. Uh 1. 0 versus 2. 4, so let's check the 1. 0. Yep, so I've got one query set and because I know that get

Speaker 2: exists and I have one result, I can change filter for get and I can get one result. Great. Now I want to inspect this object. Okay, I have this object and what was I doing again? Oh yeah, let's just check the meta get fields and okay, so we got the thing. Oh yeah, it's a f Ah, file field, that's cool. Wonder how that works. So let's get this thing and let's check the image. And oh, there's a bug. I should really fix that up to my talk. Anyway, so that's the ORM. That was the ORM in practice. So what happens if the ORM doesn't do what you want? Well Allegedly. You could use RAW, but you really, really, really, really, really should check what RAW

Speaker 2: does. And if you look at the documentation, there's like all these warnings and notices and stuff before it tells you actually what happens. Um and as far as I'm aware, you really shouldn't be using that unless you have no other choice But Django does us this really cool stuff where I don't care whether you're running Postgres or Oracle behind the scenes if I use the ORM, it doesn't matter. But if you start using RAW, your database matters. But Sometimes the sequels are better. Like Star Trek Next Generation is arguably better than the original series. The whole point of the ORM is to make 80% of things m easier and more useful. But if you have to drop down into raw SQL to get around your day to make things uh a little bit faster

Speaker 2: Yeah, do it, but just consider the portability of your application afterwards. Um you can discover more about the ORM yourself. I've barely scratched the surface here today. There's uh creating objects, which apparently that's a thing you have to do to be able to query objects. They have to first exist allegedly. Um different types of fields, like there's file fields that does magic stuff and does things for you, which is useful. And also the relationships between things. So we were traversing one way down, you can do the other way with like many to many or something. Anyway, there's a whole bunch of documentation that is super useful and You should definitely read up more and find what's useful for you. Um one thing before I go, because I've got like less than a minute.

Speaker 2: If you're going to be doing this stuff Add iPython as a requirement in your project because it means you can take stuff like what I had before with the interactive console. and turn it into a iPython environment which means you get autocompletion, which is so very helpful and it negates the use of me having to like go through meta stuff and generate all this things to do it for me. This just makes it work. So hopefully now you are a very capable little noisy miner and you can Use your wonderfully curved beak and go get the little morsels of bugs that you have in your Django applications. And

Speaker 2: It might be a new stack to you if you're a new Django developer, but if you have experience in other systems, you can build from that. You never start from zero. I've been doing database stuff for gosh knows how long, and once I realized that the ORM was just interfacing on a database, it was like And it's really cool because I love to learn all this stuff. Plus, noisy miners are really cute. Um, and that's all I had. Thank you for your time. Just just just by the by, Lacey, have you heard about Django Con? Uh

Speaker 1: you know, I think I have. I I feel like I I know that there's one here in Europe somewhere. Isn't there one in Australia too?

Speaker 2: There is one in Australia. We happen to have our call for papers open right now. Um I think there's one in America too.

Speaker 1: You know, now that you mention it, I think there is, and I think that our call for papers Is open right now too.

Speaker 2: Oh my goodness. We should all submit to other Pycons.

Speaker 1: We should. Yes. Thank you so much for this talk. Everyone, let's give Katie one more round of applause.

Speaker 2: Woo!