Welcome to my blog, hey everyone in this article we are going to be working with queries in Django so for any web app that you build your going to want to write a query so you can retrieve information from your database so in this article 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 article should be able to help you so let’s start with the model that I have I’ve already created it.
Read More : How to make Chatbot in Python.
Read More : Django Admin Full Customization step by step
let’s just get into this diagram that I made so in here:
we’re making a simple query for the myModel table so we want to pull out all the information in the database so we have this variable which is gonna hold a return value and we have our myModel models so this is simply the myModel model name so whatever you named your model just make sure you specify that and we’re gonna access the objects attribute once we get that object’s attribute we can simply use the all method and this will return all the information in the database so we’re gonna start with all and then we will go into getting single items filtering that data and go to our command prompt.
Here and we’ll actually start making our queries from here to do this let’s just go ahead and run Python manage.py shell and I am in my project file so make sure you’re in there when you start and what this does is it gives us an interactive shell to actually start working with our data so this is a lot like the Python shell but because we did manage.py it allows us to do things a Django way and actually query our database now open up the command prompt and let’s go ahead and start making our first queries.
Firstly I have discuss about my all models that have I created.
so the first thing I will do is I will start up the shell and then what I’m going to do is I am going to import those models so from query dots model import company programmer and language the 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. objects that manager object is what I’m going to use to perform queries.
Start with Django Queries
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 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.objects.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. all then I would get all languages objects that have a presence in the language table and let’s assign this to a variable so call this variable language and I use language objects not all again look at languages it’s a queryset.
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 to 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.
you can access the first and last object in specific table using first and last function in Django shell queries.
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.
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 a 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 the language here let’s say the 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 name__exact and I will 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. when I was to type Java with a lowercase I would get nothing.
when I use iexact which means give me the exact match but it’s case insensitive meaning you can have an upper case or lower case it doesn’t matter and then I run it then it gives me Java back.
so pretty simple Django does the work of translating these queries that you write into actual SQL 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.
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 the filter is pretty straight forward if I wanted to use a different type of query I can use exclude instead of filter and just by the name then this will give me everything except for Python.
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.
now we are going to filter out the programmers who have an age that is greater than 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 gte means it gives me a list of a programmer who has equal or greater than 25 and same as last queries filter the programmer who has age less than 25 and less than or equal to 25.
let’s try another one let’s try the equivalent of a like query so if I were to type programmer objects filter I say the name contains the letters in nt let’s see what gets returned just Anthony so the name Anthony has the letters nt in it so how about I try something else how about the names that have the letter th or e in them so return the list of programmer contains a these letters but if you want to filter with insensitive then use contains see in the screenshots:
now if I want to search for programmers who let’s say filter name__in 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 is 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 it still returns Janice because I’m using SQLite but if I were using a different database then I would put the istartswith before starts with to give me Janice and likewise I can do endswith and let’s say name ends with Y so that gives me a list of objects so all those names endswith Y and of course if I wanted to exclude then I would leave out the programmers whose names endswith 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 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 will say filter age__isnull 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 endswith 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 endswith Y.
I want to know something about the company that someone works see in the screenshot.
it’s returning a list so Anthony give me the first item in the list and then the company and that tells me the company is Google. 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 to 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 queryset 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 endswith Y and if I type get I get an error it tells me multiple objects returned.
I want to limit the results so if I do the list slicing syntax and I put six on the second part what happens is it gives me six programmers in the second query would be skipping over the first five results and give me the next five so it’s basically going from five to ten.
finally, let’s talk about order_by really quick were actually I want to show you deeply 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 date for me so if I say a company was started in 1990 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 was created before 1990.
so I will go back to the programmer table objects and then all and if I wanted to order by this. I used the 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 and you can do that with other columns.
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.
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__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.
Now 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.
if you liked the article and share it with your friends and 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 hopefully I will see you again in another article.