How Model Queries Work in Django

hey everyone in this video we're going to be working with queries in Django so for any web app that you build your going to want to write query so you can retrieve information from your database so in this video I'll be showing you all the different ways that you can write queries and it should cover about 90% of the cases that you'll have when you're writing your code the other 10% depend on your specific use case you may have to get more complicated but for the most part what I cover in this video should be able to help you so before we get into the video I just want to mention that I have the Django cheat sheet available you can go to pretty printed.com / Django to get it or you can go to the link in the description below so that's all I want to say before the video and now let's get to the video I hope you enjoy it alright so let's start with the model that I have I've already created it here and basically it's just an extended model from what I created in the last video so it has three classes a company a language and a programmer representing a company a language and a programmer table in the database and then here's what my database looks like I have a company table with five companies in it I have this language table with five languages in it I have a programmer table with 26 programmers and then I have this query programmer languages table with quite a few rows it looks like 56 so this is where I'll be querying from and just note the relationships between the particular tables the programmer has a relationship to the language table a foreign key meaning it's a once a mini relationship meaning that one programmer can work at any company and then the languages there is a mini some mini relationship meaning one programmer can know many languages and each language can have different programmers that know that language so that should be enough to understand the model that I have and the data that I have in the database so now let's get started with actually using the queries so first thing I'll do is I'll start up the shell and then what I'm going to do is I'm going to import those models so from query dots model import company programmer and language and no time using query here because that's the name of the app that I created not a very creative name but that is the name of the app so the first thing you need to know when dealing with queries is that each class that you have that represents a model has something called a manager object and that manager object looks like this so if I type company dot objects that manager object is what I'm going to use to perform queries so I have to add a little more on to the manager object there's a method that I'll be calling but that's basically the start of each one so each query you write will have company objects that's something or programmer objects or a language objects not something and then the way the queries work is once you write a query it won't actually get executed at that particular spot in the code unless you need the results right then and there and what I mean by this is simply you can write ten queries but they won't actually be executed against the database until you need to view the results of those queries so if you were to have a view that has a query in it and you pass the query results to the template inside the template you will loop over the results that's when the query would get executed against the database so just keep that in mind when writing queries they don't happen right as you write them in the code then we happen when you want to view what is inside of the queries so this will make a little more sense once I show you what's going on and I'll mention it again once I write my first query which we'll be in a moment so to start once you understand the objects the manager objects then you need to know that you can add certain things on to the end of it and the most simple thing that you can add is all so as you can imagine all returns all of the rows in at that particular table so if I were to type company that objects dot all and then have the parentheses then I would get a query set in return and I would see there's a company Google company Apple company Microsoft company Facebook and company there and that represents the roles that I have in the company table if I were to do language and said objects dot all then I would get Python Java C Haskell and Ruby and let's assign this to a variable so call this variable languages and I use language objects not all again look at languages it's a query set and if I wanted to look at the first language it will tell me it's Python and if I wanted the name then it will tell me it's Python and then if I want it the Creator I can see that if I wanted the date create it I can also see that so for any other language let's say Java then I know that's at index 1 that's when it was created the name Java and so on so to actually see those results once I request some that's when I actually have the query executed against the database before then nothing happens in the code so just keep that in mind it's like when you can see the results in a sense like visually when you're using the shell that's when it gets executed in the database but when you're writing it in your source files you can write all these queries but they won't actually get executed in the code until you have to view the results in some way you're like you're looping over them or you're getting some key from a particular result so now the two main things that you need to know to narrow down your query so most of the time you don't want to get all the results for something and say you want to narrow it down by a little bit what you would use is either filter or exclude so let's start with filter if I were to type language objects and then filter I can pass in something here that would allow me to filter the results and the way the filtering works is first you pass in the name of the column that you want to filter so the attribute so let's look at language here let's say name so I can pass in a name and then what happens after that is you pass in some kind of filter so I can say underscore underscore exact and I'll explain that in just a moment but if I say Python and type enter it returns me a query said that says the language is Python if I change this language to Java then it returns Java so just by looking at the code you can probably tell what's going on by using an exact it is finding the exact match in the database where the name column equals Java or Python in the first case so the way this works when you're passing things to filter you first pass in the column name or the attribute name here and then two underscores and then you use one of the field lookups that jingle has and I'll cover all the field lookups or not all of them but quite a few of them and then you pass in the actual value that you want to use with that field lookup so in this particular case I'm saying give me the name where it is exactly equal to Java if I were to type Java with a lowercase J I would get nothing but if I use a different field type if I use a different field lookup and I use I exact which means give me the exact match but it's case insensitive meaning you can have upper case or lower case it doesn't matter and then I run it then it gives me Java back so basically what these are like is where clauses in sequel so basically what this will look like is select star from language where name equals Java so pretty simple Shango does the work of translating these queries that you write into actual sequel queries but the syntax here in the way of writing it is a lot more expressive in some cases so it's pretty simple and another thing you should know is that these queries are independent so when I write this query it has none to do with this query so I can chain them and nothing will happen so let me give you an example with that if I were to say language objects filter name exact equals C++ I don't have that but if I use C I get an answer and then if I added a second filter on it so I chain another filter I pass a name exact Python I get nothing because what this is saying is give me something from languages where the name is equal to C and it's equal to Python obviously that doesn't make sense because it can only have one name at a time so when you combine the two you get no result back so as you can see filter is pretty straightforward if I wanted to use a different type of query I can use exclude instead of filter and just by the name you can imagine that it means give me everything but what matches in exclude so if I tape language objects exclude instead of filter use name underscore underscore exact equals Python then this will give me everything except for Python so I see Java see Haskell and Ruby but I don't see Python because I'm using exclude so it's all fairly simple how these queries work and now what I want to show you is basically the other field lookups so let's look at our table again and let's go to C programmer and what ever going to do is I'm going to search for some of the things in this particular table so I showed you exact and I exact so now let's try a different one so let me go back to my shell and now I'll run programmer dot objects so that's the beginning and we're going to filter out the programmers who have an age that is greater than let's say 25 and when I run that it gives me a list of programmers who are older than 25 if they are exactly 25 then they don't get returned because then I would have to use greater than or equal to which is gt/e and then I can't tell if that list is longer it actually is because it extends a little bit further out so there's probably one programmer in here who is 25 exactly and likewise I can do less than so programmer objects filter age underscore underscore because I'm doing something to the age column I want to say less than 25 and now it gives me the programmers that I have in my database who are younger than 25 and of course I can do less than or equal to if I just add a after the T and it gives me a slightly longer list and includes the programmer who is 25 so let me clear that out let's try another one let's try the equivalent of a like query so if I were to type programmer that objects filter and then I want the name underscore underscore and then contains if I say the name contains the letters in T let's see what gets returned just Anthony so the name Anthony has the letters NT in it at a certain point so Anthony gets returned if I try see th still Anthony so how about I try something else how about the names that have the letter e in them so now I see quite a few Cecilia Desiree Emily Fred George Janice leo Karen Peter Whitney Xander and Yanis so all those names have the letter e in them and just note because I'm using sequel lights contains is case insensitive your if you're using a different database then it may be case sensitive so then you would use I contains so just like I exact I contains means it is case insensitive but since I'm using sequel light there's no difference between the two so that's just a minor difference between the two databases but it is fairly simple now if I want to search for programmers who let's say filter name underscore underscore let's say in so with this one I'm going to supply some kind of lists or tuple and I can even supply a query set from a previous query and basically what it's saying is if the programmer name is in this list that follows then it will return them so if I say Anthony Quincy and Peter I get those three programmers and of course if I change this to exclude then as you can imagine it will give me all the programmers except for Anthony Quincy and Peter so in is very straightforward you can supply a tuple as well if you would like let's try another one so programmer objects and then let's say filter name starts with and then I can pass in some values so let's take a look Jan it returns Janice if I were to type in Jan with the lowercase J it still returns Janice because I'm using sequel Lite but if I were using a different database then I would put the I before starts with to give me Janice and likewise I can do ins with and let's say name ends in a Y so that gives me Anthony Emily Nancy Quincy Stacy and Whitney so all those names end in Y and of course if I wanted to exclude then I would leave out the programmers whose names in in Y so filter and excluder just the opposite of each other so basically if you filter and then you exclude you get all the results in the database so you get like one part with filter and then you get the compliment of that part with exclude and if you were to combine them together then you would get all the results from the database so I don't have any null columns in any of my rows anywhere but if you wanted to check null you could do something like programmer objects and then we'll say filter age underscore underscore is null is true and that returns nothing and if I say is known as false that means that the age column is not null and that should return all of the programmers I have in the database because each programmer has an age if I wanted to figure out how many results that I have I could do programmer objects and accounts and that will give me 26 meaning 26 programmers if I wanted to add on a filter first so filter name ends with Y and then I add the dot count after that it gives me 6 so it's telling me that 6 programmers in the database have a name that ends in Y so exclude I have 20 so like I was saying about the complement thing with filter I get 6 with exclude I get 20 and I add them together and I get the 26 that are in the database in total so let's see what else we can do if I want to know something about the company that someone works for then I can say programmer object filter name let's get the exact name for Anthony and I forgot the s on objects so let me just at that okay so if I assign this to a variable I'll call it the variable Anthony and then I say Anthony dot company and let's see if I have that right in the model so right it's returning a list so Anthony give me the first item in the list and then company and that tells me the company is Google and that actually reminds me that I want to get a single object so if I want one object instead of a list of them I can use get instead of using the basic filters so how that will work is let me use the original query and if I just pass in gets it gives me programmer Anthony directly so let's just take a look at the difference between the two so here I have programmer Anthony so it's giving me the object directly and then here I have a query set which is more like a list so by calling it I am getting just one particular object from the database and get only works if you get one and only one result if you get no results you'll get an error and if you get more than one result you'll get an error so if I do something like name ends with Y so I know for sure that there are six programmers whose names in and Y and if I type get I get an error it tells me multiple objects returned and if I do something like a name that ends in XYZ gets it tells me does not exist so just be careful when you're using it you have to make sure that you are getting one and one item only if you get more than one then it just won't work for you and if you get none then it won't work for you as well so now let's try offsetting so let's use all just to give me the most results so all now what I want to do is I want to limit the results so if I do the list slicing syntax and I put five on the second part what happens is it gives me five programmers so one two three four five and if I want to have an offset so that's a limit an offset would be skip over the first in results and then start from there and give me the rest so if I say five I'm telling you this to skip over the first five results and then give me the next five so it's basically going from five to ten and the results so that wouldn't work because I should say give me five through ten so now Fred George Isaiah Janice and Kong are returned instead of nothing here in the first five there so what this means is basically look at the one on the right first so it makes more sense to you so you're looking for the first ten results and then you're going to skip over the first five so it's a little confusing reading backwards but if you think of this the number on the right first then it makes more sense so the reason why this doesn't make sense as because we're saying give me five results and then skip over the first five which results in nothing and then finally let's talk about order by really quick where actually I want to show you deep so and then I'll show you order by so company objects filter and then date now I want to say the date is greater than I can just pass in a string and it's going to convert the string to a date for me so if I say a company was started in 1990 or greater and it's not date that's why I gave me that err it's actually date created so date underscore create it and now it shows me all the companies that were created after 1990 if I do less than it shows me the companies that I have in the database that were created before 1990s so Apple and Microsoft were created in the 70s and then Google and the 90s Facebook in the 2000s and Amazon in the 90s as well ok so now I just want to talk about water by so I'll go back to the programmer table objects and then all and if I wanted to order by this then I don't have to use them all but I can if I want and I use order by so order by if I say name I get the same results because the names are in alphabetical order if I add a minus before the name then that gives me them in reverse order so you see it starts at z Y X WVU and so on and of course I can do that with other columns so name or excuse me H if I want to order by the oldest to the youngest I can do that and then if I wanted to order from the youngest to the oldest I can do that as well as long as I take away the - so basically when you don't have the - it starts from the smallest and it goes to the largest and if you at the - it does it in the reverse so it starts out the largest and goes down to the smallest and of course if I did this with the date so company objects that order by so like I say you don't need the all order by date to create it tells me that Microsoft was created first then Apple then Amazon then Google and then Facebook and the reverse if I just add a - tells me that Facebook is the youngest company followed by Google than Amazon than Apple and then Microsoft so pretty simple stuff there are many more queries that you could write of course but these are just the basics just note if you want to see something like you know all the companies that a programmer works for or all the languages that a programmer knows so if I get one late one programmer let's see Anthony is equal to programmer objects gets or say filter name exact Anthony gets I see Anthony here and then Anthony and then languages and let's say the language set will go on the other one so languages all Anthony has Python and C and then if I were to get let's say Python so language objects filter name underscore underscore exact Python and then just add this to a variable and then Python programmer set and should be programmer set dot all that's what I want so it shows me all the programmers that know Python here so the commands before I sometimes get confused with where to put the parentheses but yeah when you're looking at a class or an object that doesn't have the column on it and then you use the name of the table that is in the other part of the relationship underscore set and then you can use all so these are all the programmers that I have in my database that know Python but I cover that more in my last video on models in Django so if you want to refresher on that you can look at that but I want to focus on more pure queries that get translated directly to sequel these many some many things with the relationship this is more of a Django magic even though of course it ultimately gets converted to sequel so that's really all I want to show in this video using what you've seen in this video you should be able to write most of the queries that you would need to write of course there are other ways of writing queries a lot of the queries that I wrote they have equivalent queries that you can write you get the exact same results but this is just one way to do it so as you get more familiar with Django you'll learn other ways of doing the same thing and you'll discover if you like those things better than what I did or you like the way that I did it but that just comes with experience I don't want to force what I think is good on to you because everyone is going to have a different view of what that is so that's about it for this video I'll add the code to a link in the description below if you want to download the code of course if you have any questions about writing queries in Django feel free to leave me a comment and I will answer the question you have and that's about it so thank you for watching this video and I will talk to you next time you

Channel: Pretty Printed

Views: 84,736

Keywords: django queries, django model queries, model queries, sql queries, sqlite, django, python, sql

Id: WimXjp0ryOo

Length: 27min 47sec (1667 seconds)

Published: Fri Jan 05 2018