Python Road [Chapter 23]: Django Model Operations (II)
Keywords:
Python
Database
Django
SQL
Model operation
1. Operation summary:
# increase
#
# models.Tb1.objects.create(c1='xx', c2='oo') Add a data to accept dictionary type data **kwargs
# obj = models.Tb1(c1='xx', c2='oo')
# obj.save()
# check
#
# models.Tb1.objects.get(id=123) # Acquire single data, report error if nonexistent (not recommended)
# models.Tb1.objects.all() # Get all
# models.Tb1.objects.filter(name='seven') # Get data for specified conditions
# models.Tb1.objects.exclude(name='seven') # Get data for specified conditions
# Delete
#
# models.Tb1.objects.filter(name='seven').delete() # Delete data for specified conditions
# change
# models.Tb1.objects.filter(name='seven').update(gender='0') # Update the specified data to support ** kwargs
# obj = models.Tb1.objects.get(id=1)
# obj.c1 = '111'
# obj.save() # Modify single data
//basic operation
basic operation
# Get number
#
# models.Tb1.objects.filter(name='seven').count()
# Greater than, less than
#
# models.Tb1.objects.filter(id__gt=1) # Get a value with id greater than 1
# models.Tb1.objects.filter(id__gte=1) # Get a value whose id is greater than or equal to 1
# models.Tb1.objects.filter(id__lt=10) # Get a value with id less than 10
# models.Tb1.objects.filter(id__lte=10) # Get a value with id less than 10
# models.Tb1.objects.filter(id__lt=10, id__gt=1) # Get values with id greater than 1 and less than 10
# in
#
# models.Tb1.objects.filter(id__in=[11, 22, 33]) # Get data with id equal to 11, 22, 33
# models.Tb1.objects.exclude(id__in=[11, 22, 33]) # not in
# isnull #Is it empty?
# Entry.objects.filter(pub_date__isnull=True)
# contains #Contains the same sql statement like operation
#
# models.Tb1.objects.filter(name__contains="ven")
# models.Tb1.objects.filter(name__icontains="ven") # icontains case insensitive
# models.Tb1.objects.exclude(name__icontains="ven")
# range
#
# models.Tb1.objects.filter(id__range=[1, 2]) # Range bettwen and
# Other similar beginnings and endings
#
# startswith，istartswith, endswith, iendswith,
# order by #sort
# Writable multi-value, priority first value sort
# models.Tb1.objects.filter(name='seven').order_by('id') # asc from small to large
# models.Tb1.objects.filter(name='seven').order_by('-id',) # desc from big to bottom
# group by #Grouping
#
# from django.db.models import Count, Min, Max, Sum
# models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num')) #Grouping according to id, and getting the number of each group, can add Min, Max, Sum
# SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id" #Equivalent to this sentence
# limit ,offset #paging
#
# models.Tb1.objects.all()[10:20]
# regex Regular matching, iregex Case insensitive
#
# Entry.objects.get(title__regex=r'^(An?|The) +')
# Entry.objects.get(title__iregex=r'^(an?|the) +')
# date #time
#
# Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1)) #pub_date__date is queried by date, not including time and minutes.
# Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))
# year
#
# Entry.objects.filter(pub_date__year=2005)
# Entry.objects.filter(pub_date__year__gte=2005)
# month
#
# Entry.objects.filter(pub_date__month=12)
# Entry.objects.filter(pub_date__month__gte=6)
# day
#
# Entry.objects.filter(pub_date__day=3)
# Entry.objects.filter(pub_date__day__gte=3)
# week_day
#
# Entry.objects.filter(pub_date__week_day=2)
# Entry.objects.filter(pub_date__week_day__gte=2)
# hour
#
# Event.objects.filter(timestamp__hour=23)
# Event.objects.filter(time__hour=5)
# Event.objects.filter(timestamp__hour__gte=12)
# minute
#
# Event.objects.filter(timestamp__minute=29)
# Event.objects.filter(time__minute=46)
# Event.objects.filter(timestamp__minute__gte=29)
# second
#
# Event.objects.filter(timestamp__second=31)
# Event.objects.filter(time__second=2)
# Event.objects.filter(timestamp__second__gte=31)
//Advanced operation
Advanced operation
##################################################################
# PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET #
##################################################################
def all(self)
# Get all data objects
def filter(self, *args, **kwargs)
# Conditional query
# Conditions can be: parameters, dictionaries, Q
def exclude(self, *args, **kwargs)
# Conditional query
# Conditions can be: parameters, dictionaries, Q
def select_related(self, *fields)
//Performance-related: join tables between tables to get the associated data at one time.
model.tb.objects.all().select_related()
model.tb.objects.all().select_related('Foreign key field')
model.tb.objects.all().select_related('Foreign key field__Foreign key field')
def prefetch_related(self, *lookups)
//Performance-related: Multi-table table table join operation will be slow, using it to execute multiple SQL queries in Python code to achieve table join operation.
# Get all user tables
# Get the user type table where id in (All users found in the user table ID)
models.UserInfo.objects.prefetch_related('Foreign key field')
from django.db.models import Count, Case, When, IntegerField
Article.objects.annotate(
numviews=Count(Case(
When(readership__what_time__lt=treshold, then=1),
output_field=CharField(),
))
)
students = Student.objects.all().annotate(num_excused_absences=models.Sum(
models.Case(
models.When(absence__type='Excused', then=1),
default=0,
output_field=models.IntegerField()
)))
def annotate(self, *args, **kwargs)
# Used for aggregation group by query
from django.db.models import Count, Avg, Max, Min, Sum
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id'))
# SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1)
# SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1)
# SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
def distinct(self, *field_names)
# Be used for distinct Duplicate removal
models.UserInfo.objects.values('nid').distinct()
# select distinct nid from userinfo
//Note: Only in PostgreSQL can distinct be used for de-duplication
def order_by(self, *field_names)
# Used for sorting
models.UserInfo.objects.all().order_by('-id','age')
def extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
# Construct additional query conditions or mappings, such as: sub-queries
Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
def reverse(self):
# Reverse order
models.UserInfo.objects.all().order_by('-nid').reverse()
# Note: If it exists order_by，reverse It's an inversion, and if there are more than one sort, it's a one-to-one inversion.
def defer(self, *fields): #Do not retrieve data that has no choice, or you will request the database again, which will affect efficiency.
models.UserInfo.objects.defer('username','id')
//or
models.UserInfo.objects.filter(...).defer('username','id')
#Exclude a column of data in a map
def only(self, *fields):
#Take only data from a table
models.UserInfo.objects.only('username','id')
//or
models.UserInfo.objects.filter(...).only('username','id')
def using(self, alias):
//Specify the database to be used with an alias parameter (settings in settings)
##################################################
# PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS #
##################################################
def raw(self, raw_query, params=None, translations=None, using=None):
# Execution primordial SQL
models.UserInfo.objects.raw('select * from userinfo')
# If SQL When other tables are present, the name must be set to the current one. UserInfo Object's primary key column name
models.UserInfo.objects.raw('select id as nid from Other tables')
# Primordial SQL Setting parameters
models.UserInfo.objects.raw('select id as nid from userinfo where nid>%s', params=[12,])
# Converts the acquired column name to the specified column name
name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
Person.objects.raw('SELECT * FROM some_other_table', translations=name_map)
# Specify a database
models.UserInfo.objects.raw('select * from userinfo', using="default")
################### Native SQL ###################
from django.db import connection, connections
cursor = connection.cursor() # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
row = cursor.fetchone() # fetchall()/fetchmany(..)
def values(self, *fields):
# Get each row of data in dictionary format
def values_list(self, *fields, **kwargs):
# Get each row of data as a meta-ancestor
def dates(self, field_name, kind, order='ASC'):
# Re-locate and intercept specified content according to time
# kind It can only be:"year"(Year), "month"(year-Month), "day"(year-month-Day)
# order It can only be:"ASC" "DESC"
# And get the time after conversion
- year : year-01-01
- month: year-month-01
- day : year-month-day
models.DatePlus.objects.dates('ctime','day','DESC')
def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
# To retrieve and intercept the specified content according to a part of the time, and convert the time to the specified time zone
# kind Can only be "year", "month", "day", "hour", "minute", "second"
# order It can only be:"ASC" "DESC"
# tzinfo Time zone object
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC)
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))
"""
pip3 install pytz
import pytz
pytz.all_timezones
pytz.timezone('Asia/Shanghai')
"""
def none(self):
# empty QuerySet object
####################################
# METHODS THAT DO DATABASE QUERIES #
####################################
def aggregate(self, *args, **kwargs):
# Aggregation function to get dictionary type aggregation results
from django.db.models import Count, Avg, Max, Min, Sum
result = models.UserInfo.objects.aggregate(k=Count('u_id', distinct=True), n=Count('nid'))
===> {'k': 3, 'n': 4}
def count(self):
# Get number
def get(self, *args, **kwargs):
# Getting a single object
def create(self, **kwargs):
# create object
def bulk_create(self, objs, batch_size=None):
# Batch insertion
# batch_size Represents the number of inserts at a time
objs = [
models.DDD(name='r11'),
models.DDD(name='r22')
]
models.DDD.objects.bulk_create(objs, 10)
def get_or_create(self, defaults=None, **kwargs):
# If it exists, get it, otherwise create it
# defaults Specify the values of other fields at creation time
obj, created = models.UserInfo.objects.get_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 2})
def update_or_create(self, defaults=None, **kwargs):
# If it exists, it is updated; otherwise, it is created
# defaults Specify other fields when created or updated
obj, created = models.UserInfo.objects.update_or_create(username='root1', defaults={'email': '1111111','u_id': 2, 't_id': 1})
def first(self):
# Get the first
def last(self):
# Get the last one
def in_bulk(self, id_list=None):
# According to the primary key ID Search for
id_list = [11,21,31]
models.DDD.objects.in_bulk(id_list)
def delete(self):
# delete
def update(self, **kwargs):
# To update
def exists(self):
# Is there a result?
//Other operations