Django Database Operations

1. Create model table, add app name, configuration data in setting

From django.db import Models   class UserInfo (models. Model):   #如果没有models. Autofield, the default is to create an ID for the self-increment column    name = models. Charfield (max_length=30)    email = models. Emailfield ()    memo = models. TextField ()

Run: Python manage.py makemigrations python manage.py migrate generate the corresponding table
If you want to use the table in the background, you also need to register it in admin.py, as follows:

2, the database increases, deletes, changes, checks

Inquiry article

Models. UserInfo.objects.all () models. UserInfo.objects.all (). VALUES (' user ')             #只取user列models. UserInfo.objects.all (). Values_list (' id ', ' user ')   #取出id和user列 and generate a list of models. UserInfo.objects.get (id=1) models. UserInfo.objects.get (user= ' YANGMV ')

Add article

Models. UserInfo.objects.create (user= ' YANGMV ', pwd= ' 123456 ') or obj = models. UserInfo (user= ' YANGMV ', pwd= ' 123456 ') obj.save () or dic = {' user ': ' YANGMV ', ' pwd ': ' 123456 '}models. UserInfo.objects.create (**dic)

Delete Article

Models. UserInfo.objects.filter (user= ' YANGMV '). Delete ()

Modified article

Models. UserInfo.objects.filter (user= ' YANGMV '). Update (pwd= ' 520 ') or obj = models. UserInfo.objects.get (user= ' YANGMV ') obj.pwd = ' 520 ' Obj.save ()

Common methods:

# Get number # # # models. Tb1.objects.filter (Name= ' seven '). Count () # is greater than, less than # # models. Tb1.objects.filter (id__gt=1) # Gets the value of ID greater than 1 # models. Tb1.objects.filter (id__lt=10) # Gets the value of the ID less than 10 # models. Tb1.objects.filter (id__lt=10, id__gt=1) # Gets the value of ID greater than 1 and less than 10 # in # # models. Tb1.objects.filter (Id__in=[11, 22, 33]) # Gets the data # models with the ID equal to 11, 22, 33. Tb1.objects.exclude (id__in=[11, [+]) # # Not in # contains # # 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 # # Startswith,istartswith, EndsWith, Iendswith, # ORDER BY # # models. Tb1.objects.filter (Name= ' seven '). order_by (' ID ') # ASC # models. Tb1.objects.filter (Name= ' seven '). Order_by ('-id ') # desc # limit, offset # # models. Tb1.objects.all () [10:20] # GROUP by from Django.db.models import Count, Min, Max, Sum # models. Tb1.objects.filter (c1=1). VALUES (' ID '). Annotate (C=count (' num ')) # select "App01_tb1". " ID ", COUNT (" App01_tb1 "." Num ") as" C "from" App01_tb1 "WHERE" app01_tb1 "." C1 "= 1 GROUP by" app01_tb1 "." Id

3. Django Advanced Query

Filter query:

1.  equals exact. Example: Query name equals ' Xiao Ming ' student Student.objects.filter (name= ' Xiao Ming ') Student.objects.filter (name__exact= ' Xiao Ming ') # Exact can be omitted here by 2.  The fuzzy query like    contains contains example: Query the student whose name contains ' Xiao '. Student.objects.filter (name__contains= ' Xiao ') Start: StartsWith End: endswith Example: Query for students whose names begin with ' Xiao ' and whose students end with ' Ming ' Student.objects.filter (name__startswith= ' Xiao ') Student.objects.filter (name__endswith= ' Ming ') 3.  Empty query   IsNull Example: query students whose names are not empty Student.objects.filter (Name__isnull=false) 4.  Range Query  In example: Query age 12 or 15 or 16 student Student.objects.filter (age__in=[12,15,16]) 5.  Compare Queries GT lt (less than) GTE (equal) LTE example: query for students older than or equal to 12 Student.objects.filter (AGE__GTE=12) 6. Date query: Query for students born in 1994. Student.objects.filter (birthyear__date=1994) Example: query for students born after January 1, 1994. Student.objects.filter (birthyear__date__gt = Date (1994,1,1)) 7. Returns data that does not meet the criteria exclude example: A student with a query ID of not 3. Student.objects.exclude (id=3)

Multiple field parameter limits are passed at the same time, but the field parameters passed are limited to the operation

Student.objects.filter (name= ' Xiao Ming ', age=12)

But we want to query the name is ' Xiao Ming ' or age equals 12 how to use? Q objects can be used to perform logical operations between field limits (&,|,~)

From django.db.models import Q #引入Student. Objects.filter (q (name= ' Xiao Ming ') | Q (age=12))

Aggregation functions:

Queryset by aggregate this function to achieve the aggregation function

You need to import the aggregation class before using: From Django.db.models import sum,count,max,min,avg Example: Query the number of all students select Count (*) from student; Student.objects.aggregate (Count (' id ')) {' Id__count ': 5} Note the return value type and the key name example: Query all student ages and Student.objects.aggregate (Sum (' age ') {' Age__sum ': 120} Note return value type and key name

Field Sort:

QuerySet to sort fields by order_by

Sort the age from small to large Student.objects.all (). order_by (' age ') sorts the ages from large to small Student.objects.all (). order_by ('-age ')

Custom Query Statements :

Execute SQL statements directly from a Django connection object

cursor = Connection.cursor () sql= "Select Name,age from Student" "Cursor.execute (SQL) Fetchall=cursor.fetchall () Students=[] for object in Fetchall:students.append ({' name ': object[0], ' age ': object[1]})

Get the cursor through connection, then execute the SQL statement through the cursor, return the result of the query through the Fetchall function, notice that the result is a collection, each element is an array, returned in the order of the Select field

We can use the same method to increase the modification of the data delete operation, but a bit different from the query is the need to transaction commit changes

cursor = connection.cursor () sql= ' update student set age=13 where Name= ' Xiao Ming ' ' Cursor.execute (SQL) TRANSACTION.C Ommit_unless_managed ()

Django Database Operations