[Beginner] Get from Django Query database
The Django article for beginners I wrote a long time ago wasn't very good, but the one that was accessed relatively more often than others was getting from the DB in Django.
This time I will write how to get Query for beginners studying
Continued: Relation [Beginner] # 2 Django Query Database Acquisition One-to-Many and Many-to-Many
Past article: Get from database and render [Python Django] Web application for beginner programmers # 5 [Handling database values]
environment
- Python 3.8.5
- Django 3.1
If you CRUD with Django, check with shell
It is troublesome to display it on the page to check it one by one, so let's use shell.
Preparation
The model used this time looks like this The app name is accounts.
accounts/models.py
class Customer(models.Model):
name = models.CharField(max_length=100)
phone = models.CharField(max_length=20)
email = models.CharField(max_length=255)
age = models.integerField()
created_at = models.DateTimeField(auto_now_add=True)
How to create an application for practice and enter data
If you are studying, please make it as follows and try it.
- Various functions are described in manage.py. When using manage.py, please move to the directory and use it.
$ django-admin startproject test
$ cd test
$ python manage.py startapp accounts
Edit settings.py
Add the application to your project.
myproject\settings.py
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'accounts', #add to
]
Write the code for models.py earlier
$ python manage.py makemigrations
$ python manage.py migrate
Next if data already exists
If you don't have the data
$ python manage.py createsuperuser
After creating the management screen with, create user information by inserting superuser. It will not be displayed unless you register it in admin.py.
test/accounts/admin.py
from django.contrib import admin
from .models import Customer
admin.site.register(Customer)
Access http://127.0.0.1:8000/admin and log in.
You can add it with "ADD CUSTOMER".
Get Django data in shell and test
$ python manage.py shell
Please import it because it cannot be used unless the model is loaded.
>>> from accounts.models import *
>>> customer = Customer.objects.all()
>>> customer
<QuerySet [<Customer: Sato>, <Customer: Tanaka>]>
You can get it like this.
Get basic query
Model name.objects.all() #Get all data
Model name.objects.get(id=２) # id =2 data acquisition
Model name.objects.get(name='Sato') # name = 'Sato'Data acquisition
Model name.objects.first() #Get the very first data in the table
Model name.objects.last() #Get the last registered data in the table
Model name.objects.filter(name='Sato') #Condition specification
Model name.objects.filter(name__startwith='T') #Get people whose name starts with T
Model name.objects.filter(name='Sato', age__gte=20) #Mr. Sato over 20 years old
get and filter
If data cannot be acquired by get (), the error DoesNotExist is returned. If two or more conditions are met, an error will occur.
If data cannot be acquired by filter, an empty Query set is returned.
You should use get when the processing becomes strange if you cannot get reliable data.
It can also be specified by the primary key regardless of the name, such as ʻaccounts.objects.get (pk = 2)`.
Let's look at an example.
>>> customer_filter = Customer.objects.filter(id=1)
>>> customer_filter
<QuerySet [<Customer: Sato>]>
>>> customer_get = Customer.objects.get(id=1)
>>> customer_get
<Customer: Sato>
A QuerySet is an array or a group of records.
If you get it with filter, you need to retrieve it with for.
customer_filter.name is an error.
customer_get.name is processed properly. Roughly speaking, QuerySet is a collection of data that can be obtained by get.
So, if you take out one by one with for, you can handle it in the same way.
>>> customer_filter = Customer.objects.filter(id=1)
>>> customer_filter.name
Traceback (most recent call last):
File "<console>", line 1, in <module>
AttributeError: 'QuerySet' object has no attribute 'name'
>>> customer_filter = Customer.objects.filter(id=1)
>>> for c in customer_filter:
... c.name
...
'Sato'
Convenient condition acquisition method
startswit, endwith
I wrote it like filter (name__startwith ='T'),
Starts with startswit ~
ʻEndwith` ends with
It can be obtained under the following conditions.
Above, below, below, above
I wrote it like ʻage__gte = 20`, but you can use something like the following. Over 20 years old in the age column.
gt (greater than)Super
gte (greater than equal)that's all
lt (less than)Less than
lte (less than equal)Less than
It is possible to overlap various conditions
Model name.objects.all().order_by('id') #id column sort in ascending order
Model name.objects.all().order_by('-id') #id column sort in descending order
Model name.objects.all().filter(name='Sato').reverse() #Obtained by Mr. Sato. Get Queryset data in reverse
Model name.objects.all().filter(name='Sato').count() #Sato's number count
Model name.objects.values('email').distinct() #Remove duplicate data in email column
limit: Specify the number of acquisitions
Model name.objects.all()[:5] #Get 5(Limit 5)
Model name.objects.all()[5:10] # 5~Get 10(OFFSET 5 LIMIT 5)
I want to get it with dictionary
It may be easier to process if it can be obtained as a dictionary when it is obtained from the DB.
Reference: https://docs.djangoproject.com/ja/3.1/ref/models/querysets/#values
>>> Blog.objects.filter(name__startswith='Beatles').values()
<QuerySet [{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}]>
If you get it normally, it is difficult to process it because it is dictinary even if you turn the QuerySet with for and take it out, but if you add value as above, you can use it as if you can get the contents of the record with dictinary.
Get with tuples (if the key is in the way)
values_list is similar to values, but returns only the values.
Reference: https://docs.djangoproject.com/ja/3.1/ref/models/querysets/#values-list
When analysis, writing to CSV, needing to pass in list when using the library, or when the key is in the way.
>>> Entry.objects.values_list('id', 'headline')
<QuerySet [(1, 'First entry'), ...]>
>>> from django.db.models.functions import Lower
>>> Entry.objects.values_list('id', Lower('headline'))
<QuerySet [(1, 'first entry'), ...]>
Things to know once you get used to it
If you're just making QuerySets, you're not accessing the database.
>>> q = accounts.objects.filter(name__startswith="T")
>>> q = q.filter(created_at__lte=datetime.date.today())
>>> q = q.exclude(age__gte=30)
>>> print(q)
It looks like you're accessing the DB three times, but it's actually only the last print (q).
While the development is small, it may be what happens, but I wrote it because I am worried about throwing heavy processing to the DB many times.
reference
https://docs.djangoproject.com/ja/3.1/topics/db/queries/ https://docs.djangoproject.com/ja/3.1/ref/models/querysets/
We will continue to add more in the future.
Continued: Relation
[Beginner] # 2 Django Query Database Acquisition One-to-Many and Many-to-Many
Recommended Posts