[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]

image.png

environment

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.

$ 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. スクリーンショット 2020-09-21 0.35.07.png

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=2) # 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

[Beginner] Get from Django Query database
Get the value from the [Django] Form
Get the query string (query string) in Django
Get query parameters for GET requests in Django
Get only the text from the Django form.
Django memo # 1 from scratch
Get data from database via ODBC with Python (Access)
Get structural data from CHEMBLID
Django starting from scratch (part: 2)
Get started with Django! ~ Tutorial ⑤ ~
Django starting from scratch (part: 1)
Get query parameters for Flask GET
Generate URL query from JSON
Query Athena from Lambda Python
Get clipboard from Maya settings
Get started with Django! ~ Tutorial ⑥ ~
Use django model from interpreter
Template registration from Django Bootstrap
Django PostgreSQL installation, database construction