Inquiry F
In all of the above examples, the filters we constructed only compare field values to some constant we set ourselves. What if we want to compare two field values?
Django provides F() to do such comparisons. instances of F() can reference fields in a query to compare the values of two different fields in the same model instance.
Example 1:
Search for products with more sold than in-stock
from import F
ret1=(maichu__gt=F('kucun'))
print(ret1)
F can help us get the value of a field in the table as my filtering conditions, rather than I think that the conditions of the custom constants, to achieve the effect of dynamic comparison
Django supports addition, subtraction, multiplication, division and modulo operations between F() objects and between F() objects and constants. Based on this, you can perform mathematical operations on the numeric types in the table
Raise the price of each item by 50 bucks
(price=F('price')+50)
Derivation:
What to do if you want to modify the char field (never use the above operations on numeric types!!!!) ?
eg: add 'New' to the end of all book titles, (this time you need to splice the string Concat operation, and to add the splice value Value)
from import Concat
from import Value
ret3=(name=Concat(F('name'),Value('New')))
Concat represents the string splicing operation, the position of the parameter determines whether the splice is spliced at the head or at the tail, and the Value is the value to be added to the splice.
Q Query
filter()The comma-separated conditions in methods such as are relations to. If you need to perform a more complex query (such as theORstatement), you can use theQ Objects。
Example 1:
Check if the number of sells is more than 100 or the price is less than 100 dollars.
from import Q (Q(maichu__gt=100)|Q(price__lt=100))
When wrapping a layer of Q around a condition, the filter can support cross-merge comparators.
Example 2:
Query products with an Inventory of 100 and a Sell Count of 0
(Q(kucun=100)&~Q(maichu=0))
We can combine&cap (a poem)|operator and grouping with parentheses to write arbitrarily complexQObject.
Meanwhile.QObjects can be configured using the~operator inversion, which allows combining normal queries and inversions (NOT) Query.
Example 3:
Search for products whose product name contains new models and whose stock number is greater than 60.
(Q(kucun__gt=60), name__contains="New.")
Query functions can be mixedQ Objectsand keyword parameters. All arguments supplied to the query function (keyword arguments orQobjects) will be "ANDed" together. However, if theQobject, which must precede all keyword arguments.
(political, economic etc) affairs
Transaction Definition:Multiple sql statement operations become atomic operations, either successful at the same time, there is a failure inside the rollback to the original state, to ensure data integrity and consistency (NoSQL databases are partially supported for transactions).
# Affairs
# Buy a copy of Learn Linux with the Old Boys book #
# Things to do at the database level
# 1. Create an order data
# 2. Go to the product table and add +1 to the number sold and -1 to the number in stock.
from import F
from import transaction
# Enable transaction processing
try:
with ():
# Create an order data
(num="110110111", product_id=1, count=1)
# Can be implemented successfully
(id=1).update(kucun=F("kucun")-1, maichu=F("maichu")+1)
except Exception as e:
print(e)
Other lesser-known operations
Django ORM Executes Native SQL
conditions assumptions: take the blog garden, for example, we write the blog is not in accordance with the year to file, but in accordance with the year and month to file, and our DateField time format is the form of the year, month and day, that is, we need to get from the database to the time format of the data and then a time to get the time we want to process the format, such a need, Django is not to give us a way to provide Django does not provide us with a way to do this, so we need to write our own processing statements.
# extra
# Continue sub-statements on top of QuerySet
# extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)
# select and select_params are a group, where and params are a group, and tables are used to set which table to from.
# (select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
# (where=['headline=%s'], params=['Lennon'])
# (where=["foo='a' OR bar = 'a'", "baz = 'a'"])
# (select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
give an example：
(
select={'newid':'select count(1) from app01_usertype where id>%s'},
select_params=[1,],
where = ['age>%s'],
params=[18,],
order_by=['-age'],
tables=['app01_usertype']
)
"""
select
app01_userinfo.id,
(select count(1) from app01_usertype where id>1) as newid
from app01_userinfo,app01_usertype
where
app01_userinfo.age > 18
order by
app01_userinfo.age desc
"""
# Execute native SQL
# A more flexible way to execute native SQL statements
# from import connection, connections
# cursor = () # cursor = connections['default'].cursor()
# ("""SELECT * from auth_user where id = %s""", [1])
# row = ()
QuerySet Methods
A few of the more important methods:
Differences between update() and save()
Both are modified to save the data operation, but save () function is to rewrite all the data columns of all the data items all over again, and update () is to modify the item for the update of the efficiency of the less time-consuming
So future changes to the data are saved with update()
select_related and prefetch_related
def select_related(self, *fields)
Performance related: join concatenated table operations between tables to get the associated data at once.
Summary:
1. select_related main needle one-to-one and many-to-one relationship optimization.
2. select_related uses SQL's JOIN statement to optimize and improve performance by reducing the number of SQL queries.
def prefetch_related(self, *lookups)
Performance related: multiple table concatenation operation will be slow, use it to execute multiple SQL queries to implement concatenation operation in Python code.
Summary:
1. For many-to-many fields (ManyToManyField) and one-to-many fields, prefetch_related() can be used for optimization.
2. prefetch_related() is optimized by querying each table separately and then processing the relationships between them in Python. lated
bulk_create
Batch insertion of data
Requirement: Insert multiple pieces of data at once
data = ["".join([str((65, 99)) for i in range(4)]) for j in range(100)] obj_list = [(name=i) for i in data] .bulk_create(obj_list)
QuerySet Methods
##################################################################
# PUBLIC METHODS THAT ALTER ATTRIBUTES AND RETURN A NEW QUERYSET #
##################################################################
def all(self)
# Get all data objects
def filter(self, *args, **kwargs)
# Conditional queries
# Conditions can be: parameters, dictionaries, Q
def exclude(self, *args, **kwargs)
# Conditional queries
# Conditions can be: parameters, dictionaries, Q
def select_related(self, *fields)
Performance Related：between tablesjoinconcatenate table operation，Get associated data in one go。
summarize：
1. select_relatedOptimize mainly one-to-one and many-to-one relationships.。
2. select_relatedutilizationSQL(used form a nominal expression)JOINstatement for optimization，By reducingSQL查询(used form a nominal expression)次数to optimize、improve performance。
def prefetch_related(self, *lookups)
Performance Related：多表concatenate table operation时速度会慢，utilization其执行多次SQLInquiry inPython代码中实现concatenate table operation。
summarize：
1. For many-to-many fields（ManyToManyField）and one-to-many fields，可以utilizationprefetch_related()to optimize。
2. prefetch_related()(used form a nominal expression)优化方式是分别查询每个表，followed byPython处理他们之间(used form a nominal expression)关系。
def annotate(self, *args, **kwargs)
# Used to implement aggregated group by queries
from import Count, Avg, Max, Min, Sum
v = ('u_id').annotate(uid=Count('u_id'))
# SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id
v = ('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 = ('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)
# For distinct de-duplication
('nid').distinct()
# select distinct nid from userinfo
classifier for sums of money：Only inPostgreSQL中才能utilizationdistinctPerform de-duplication
def order_by(self, *field_names)
# For sorting
().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, e.g., subqueries
(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
(where=['headline=%s'], params=['Lennon'])
(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])
def reverse(self):
# Reverse order
().order_by('-nid').reverse()
# Note: if order_by exists, reverse is inverted, and if more than one sort is inverted one by one
def defer(self, *fields):
('username','id')
maybe
(...).defer('username','id')
# Exclude a column of data from the mapping
def only(self, *fields):
# Fetch only the data in a table
('username','id')
maybe
(...).only('username','id')
def using(self, alias):
指定utilization(used form a nominal expression)数据库，Parameters are aliases（setting中(used form a nominal expression)设置）
##################################################
# PUBLIC METHODS THAT RETURN A QUERYSET SUBCLASS #
##################################################
def raw(self, raw_query, params=None, translations=None, using=None):
# Execute native SQL
('select * from userinfo')
# If the SQL is another table, the name must be set to the primary key column name of the current UserInfo object.
('select id as nid from Other tables')
# Setting parameters for native SQL
('select id as nid from userinfo where nid>%s', params=[12,])
# Convert the fetched column name to the specified column name.
name_map = {'first': 'first_name', 'last': 'last_name', 'bd': 'birth_date', 'pk': 'id'}
('SELECT * FROM some_other_table', translations=name_map)
# Specify the database
('select * from userinfo', using="default")
################### Native SQL ###################
from import connection, connections
cursor = () # cursor = connections['default'].cursor()
("""SELECT * from auth_user where id = %s""", [1])
row = () # fetchall()/fetchmany(..)
def values(self, *fields):
# Get each row of data in dictionary format
def values_list(self, *fields, **kwargs):
# Get each line of data as a meta-anchor
def dates(self, field_name, kind, order='ASC'):
# De-duplication of a part of the search and interception of the specified content according to time
# kind can only be: "year", "month", "day".
# order can only be: "ASC" "DESC"
# And get the converted time
- year : surname Nian-01-01
- month: surname Nian-moon-01
- day : surname Nian-moon-date
('ctime','day','DESC')
def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
# Convert the time to a specified time zone by de-duplicating a part of the time and intercepting the specified content.
# kind can only be "year", "month", "day", "hour", "minute", "second"
# order can only be: "ASC" "DESC"
# tzinfo timezone object
('ctime','hour',tzinfo=)
('ctime','hour',tzinfo=('Asia/Shanghai'))
"""
pip3 install pytz
import pytz
pytz.all_timezones
(‘Asia/Shanghai')
"""
def none(self):
# Empty QuerySet objects
####################################
# METHODS THAT DO DATABASE QUERIES #
####################################
def aggregate(self, *args, **kwargs):
# Aggregate function to get the result of dictionary type aggregation
from import Count, Avg, Max, Min, Sum
result = (k=Count('u_id', distinct=True), n=Count('nid'))
===> {'k': 3, 'n': 4}
def count(self):
# of acquisitions
def get(self, *args, **kwargs):
# Get a single object
def create(self, **kwargs):
# Create objects
def bulk_create(self, objs, batch_size=None):
# Batch insertion
# batch_size indicates the number of inserts at a time
objs = [
(name='r11'),
(name='r22')
]
.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 when created
obj, created = .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, update it, otherwise, create it
# defaults Specify additional fields at creation time or update time
obj, created = .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
def in_bulk(self, id_list=None):
# Lookup by primary key ID
id_list = [11,21,31]
.in_bulk(id_list)
def delete(self):
# Delete
def update(self, **kwargs):
# Updates
def exists(self):
# Are there results
to this article on the use of django F and Q query article is introduced to this, more related to django F and Q query content, please search for my previous posts or continue to browse the following related articles I hope you will support me in the future more!