ORM introduction
- An important part of MVC or MVC framework is ORM, which realizes the decoupling of data model and database, that is, the design of data model does not need to rely on specific database, and the database can be easily replaced through simple configuration, which greatly reduces the workload of developers and does not need to face the invalid labor caused by database change.
- ORM is the abbreviation of "object relation map".
If the database is migrated, the sql statements of different databases are different, and ORM is the upper layer encapsulation. Only according to the specified database engine, it can be translated into the corresponding sql statements, which is convenient for database migration. But its efficiency is not high.
There is no need to call sqmysql, the upper encapsulation of django; it can only achieve the table level.
sql table - > Python class
Field properties of class
Table record ----- > Python class object
#sql Table in #Create table: CREATE TABLE employee( id INT PRIMARY KEY auto_increment , name VARCHAR (20), gender BIT default 1, birthday DATA , department VARCHAR (20), salary DECIMAL (8,2) unsigned, ); #sql Table records in #Add a table record: INSERT employee (name,gender,birthday,salary,department) VALUES ("alex",1,"1985-12-12",8000,"Cleaning Department"); #Query a table record: SELECT * FROM employee WHERE age=24; #Update a table record: UPDATE employee SET birthday="1989-10-24" WHERE id=1; #Delete a table record: DELETE FROM employee WHERE name="alex" #python Class class Employee(models.Model): id=models.AutoField(primary_key=True) name=models.CharField(max_length=32) gender=models.BooleanField() birthday=models.DateField() department=models.CharField(max_length=32) salary=models.DecimalField(max_digits=8,decimal_places=2) #python Class objects #Add a table record: emp=Employee(name="alex",gender=True,birthday="1985-12-12",epartment="Cleaning Department") emp.save() #Query a table record: Employee.objects.filter(age=24) #Update a table record: Employee.objects.filter(id=1).update(birthday="1989-10-24") #Delete a table record: Employee.objects.filter(name="alex").delete()
In mysql:
mysql> create database orm; Query OK, 1 row affected (0.06 sec) mysql> use orm Database changed mysql> show tables; Empty set (0.17 sec) mysql> mysql> show tables; +----------------------------+ | Tables_in_orm | +----------------------------+ | app01_book #We added our own| | auth_group | | auth_group_permissions | | auth_permission | | auth_user | | auth_user_groups | | auth_user_user_permissions | | django_admin_log | | django_content_type | | django_migrations | | django_session | +----------------------------+ 11 rows in set (0.00 sec) mysql> desc app01_book; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(32) | NO | | NULL | | | pub_date | date | NO | | NULL | | | price | decimal(8,2) | NO | | NULL | | | publish | varchar(32) | NO | | NULL | | +----------+--------------+------+-----+---------+----------------+ 5 rows in set (0.04 sec)
Document is ORM
Class corresponding table, class object corresponding table record
Create an app named book, and create the model in models.py under book:
The files, classes, fields, and querysets in django correspond to the databases, tables, fields, and records in mysql, which are equivalent to excel, Sheet, Column, and Line in Excel. They are related to each other.
from django.db import models
# Create your models here.
class Book(models.Model):
id=models.AutoField(primary_key=True) #Auto increment, parameter
title=models.CharField(max_length=32) #
state=models.BooleanField()
pub_date=models.DateField()
price=models.DecimalField(max_digits=8,decimal_places=2) #Floating point number, 8 at most, 2 decimal places are 123456.78.
publish=models.CharField(max_length=32)
Each field has some unique parameters. For example, CharField needs the max? Length parameter to specify the size of VARCHAR database fields. There are also general parameters that apply to all fields. These parameters are defined in detail in the document. Here we will briefly introduce some of the most commonly used parameters:
More fields:
<1> CharField
String field for shorter strings.
CharField requires a parameter maxlength to limit the maximum number of characters allowed for this field from the database layer and Django verification layer.
<2> IntegerField
#Used to hold an integer.
<3> FloatField
A floating-point number. Two parameters must be provided:
Parameter description
Max? Digits (excluding decimal point and symbol)
Decimal place
For example, to save a maximum value of 999 (2 decimal places), you define the fields as follows:
models.FloatField(..., max_digits=5, decimal_places=2)
To save a maximum value of one million (10 decimal places), you need to define it as follows:
models.FloatField(..., max_digits=19, decimal_places=10)
admin uses a text box (< input type = "text" >
<4> AutoField
An IntegerField that grows automatically when you add records. You usually don't need to use this field directly.
Customize a primary key: my_id=models.AutoField(primary_key=True)
If you do not specify a primary key, the system will automatically add a primary key field to your model.
<5> BooleanField
A true/false field. admin uses a checkbox to represent such fields.
<6> TextField
A large text field.
admin represents the field data with a < textarea > (a multiline edit box).
<7> EmailField
A CharField with check Email validity does not accept the maxlength parameter.
<8> DateField
A date field. There are the following additional optional parameters:
Argument description
Auto now automatically sets the value of this field to the current time when the object is saved. It is usually used to represent the "last modified" timestamp.
When an object is created for the first time, the value of this field is automatically set to the current time. It is usually used to represent the creation time of the object.
(meaningful only in admin...)
<9> DateTimeField
A date time field. Similar datefields support the same additional options.
<10> ImageField
It is similar to FileField, but you need to verify whether the uploaded object is a legal image. It has two optional parameters: height field and width field.
If these two parameters are provided, the picture will be saved according to the height and width specifications provided.
<11> FileField
A file upload field.
A required parameter is required: upload to, a local file system path to save the uploaded file. This path must contain strftime formatting.
date/time of the file to be uploaded
So that uploaded files don't fill up the given directory.
admin uses a < input type = "file" > widget to represent the data saved in this field (a file upload widget).
Note: using FileField or ImageField in a model requires the following steps:
(1) in your settings file, define a full path to media? Root so that Django can save the uploaded file here.
(for performance reasons, these files are not saved to the database.) define the media u URL as the public URL of the directory. Make sure that the directory
The user account of WEB server is writable.
(2) add FileField or ImageField to your model, and make sure the upload to option is defined to tell Django
Use which subdirectory of media root to save the uploaded file. All you need to save in your database is the path of the file (relative to media root).
For example, if your ImageField
Called mug shot, you can get the absolute path of the image in the template in the way of {object.} get mug shot URL}.
<12> URLField
It is used to save the URL. If the verify ﹣ exists parameter is True (default), the given URL will be pre checked for existence (i.e. whether the URL is effectively loaded and
No 404 response returned).
admin uses a < input type = "text" > text box to represent the data saved in this field (a single line edit box)
<13> NullBooleanField
Similar to the Boolean field, but NULL is allowed as one of the options. It is recommended to use this field instead of the Boolean field plus the null=True option.
admin uses a selection box < Select > (three optional values: "Unknown", "Yes" and "No") to represent this field data.
<14> SlugField
"slug" is a newspaper term. slug is a small mark (short label) of something, containing only letters, numbers, underscores and hyphens. 3535; they are usually used for URLs.
If you use Django development version, you can specify maxlength. If maxlength is not specified, Django will use the default length: 50.
In the previous Django version, there was no way to change the length of 50.
This implies db_index=True.
It takes an extra parameter: prepopulate from, which is a list of fields from which to auto - ා.
the slug, via JavaScript,in the object's admin form: models.SlugField
(prepopulate from = ("pre name", "name")) prepopulate from does not accept DateTimeFields.
<13> XMLField
A TextField that verifies whether the value is legal XML must provide the parameter: schema? Path, which is the file system path of RelaxNG schema? Used to verify the text.
<14> FilePathField
The optional project is the file name in a specific directory. It supports three special parameters, the first of which must be provided.
Parameter description
Path required parameter. The absolute file system path of a directory. FilePathField gets the optional item accordingly.
Example: "/home/images".
match optional parameter. A regular expression, as a string, FilePathField will use it to filter filenames.
Note that this regular expression only applies to base filename instead of
Full path name. Example: "foo.*\.txt ^", will match file foo23.txt but not bar.txt or foo23.gif.
recursive optional parameter. Either True or False. The default value is False. Include all subdirectories under path.
These three parameters can be used at the same time.
match only applies to the base filename, not the full path name. Then, this example:
FilePathField(path="/home/images", match="foo.*", recursive=True)
... will match / home/images/foo.gif and not / home/images/foo/bar.gif
<15> IPAddressField
An IP address in the form of a string, (i.e. "24.124.1.30").
<16> CommaSeparatedIntegerField
Used to store comma separated integer values. Similar to CharField, maxlength parameter is required.
'''
More parameters:
(1)null If True, Django uses NULL to store NULL values in the database. The default is False. (1)blank If it is True, the field is allowed to be left blank. The default is False. Note that this is different from null. Null is purely a database category, while blank is a data validation category. If a field's blank=True, the form's validation allows the field to be null. If blank=False, the field is required. (2)default The default value of the field. It can be a value or a callable object. If callable, every new object created will be called. (3)primary_key If True, this field is the primary key of the model. If you do not specify the primary_key=True of any field, Django will automatically add an IntegerField field as the primary key, so unless you want to override the default primary key behavior, Otherwise, it is unnecessary to set the primary_key=True of any field. (4)unique If the value is set to True, the value of this data field must be unique throughout the table (5)choices An iteratable object (such as a list or tuple) consisting of two tuples, used to provide a selection for a field. If choices is set, the default form will be a selection box instead of a standard text box, < br > and the options of this selection box are those in choices.
settings configuration
If you want to convert the model to a table in mysql database, you need to configure it in settings:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME':'bms', # To connect to a database, you need to create a
'USER':'root', # User name to connect to the database
'PASSWORD':'', # Password to connect to the database
'HOST':'127.0.0.1', # Connect host, default current level
'PORT': 3306 # Port default 3306
}
}
Note 1: NAME is the NAME of the database. Before MySQL connection, the database must have been created. In the above SQLite database, db.sqlite3 is automatically created by the project. USER and PASSWORD are the USER NAME and PASSWORD of the database respectively. After setting up, start our Django item. At present, we need to activate our mysql. Then, start the project and an error will be reported: no module named MySQLdb. This is because Django imports MySQL dB by default, but MySQL DB has a big problem with py3, so we need PyMySQL as the driver. Therefore, we only need to find the "init" under the project NAME file, and write:
1 import pymysql 2 pymysql.install_as_MySQLdb()
Finally, you can create tables in the specified database through two database migration commands:
1 python manage.py makemigrations
2 python manage.py migrate
Note 2: make sure that the app name we created is written in the installed Apus in the configuration file
Note 3: if an error is reported as follows:
django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.3 or newer is required; you have 0.7.11.None
Currently, MySQL client only supports Python 3.4, so if you use a later version of python, you need to modify it as follows:
Find the path C: \ programs \ Python \ Python 36-32 \ lib \ site packages \ django-2.0-py3.6. Egg \ Django \ DB \ backends \ MySQL
The files in this path
if version < (1, 3, 3):
raise ImproperlyConfigured("mysqlclient 1.3.3 or newer is required; you have %s" % Database.__version__)
Just comment it out.
Note 4: if you want to print sql during orm conversion, you need to configure it in settings as follows:
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console':{
'level':'DEBUG',
'class':'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level':'DEBUG',
},
}
}
Add table record for single table operation (there are two methods)
increase
views
from django.shortcuts import render, HttpResponse
# Create your views here.
from app01.models import Book #Export the Book class
def index(request):
#Add table structure
##############################Add to
#Method 1 (directly instantiate an instance object, and Book obj is the instance object)
#book_obj = Book(id=1, title="python red book", price=100, pub_date="2012-10-08", publish = "people's Publishing House")
#book_obj.save() #Only in this way can this table record be generated.
#Method 2: the return value of create is the currently generated object record; there is an object manager under each table, which can be added, deleted, modified and queried.
book_obj = Book.objects.create(title="php", price=100, pub_date="2018-01-01", publish="Peking University Press" )#id can't be duplicated, just set auto increment
print(book_obj.title) #title Property under object
print(book_obj.price)
print(book_obj.pub_date)
return HttpResponse('OK')
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) INSERT INTO `app01_book` (`title`, `pub_date`, `price`, `publish`) VALUES ('php', '2018-01-01', '100.00', 'Peking University Press'); args=['php', '2018-01-01', '100.00', 'Peking University Press']
[27/May/2018 10:47:36] "GET /index/ HTTP/1.1" 200 2
php
100
2018-01-01
Query API table record of single table operation
Query API
< 1 > all(): query all results
< 2 > filter (* * kwargs): it contains objects that match the given filter criteria
< 3 > get (* * kwargs): returns the object matching the given filter criteria. Only one result is returned.
An error is thrown if more than one or none of the objects meet the filter criteria.
< 4 > exclude (* * kwargs): it contains objects that do not match the given filter criteria
< 5 > order menu by (* field): sort query results
< 6 > reverse(): reverse sorting of query results
< 8 > count(): returns the number of objects matching the query (QuerySet) in the database.
< 9 > first(): returns the first record
< 10 > last(): returns the last record
< 11 > exists(): returns True if the QuerySet contains data, False otherwise
< 12 > values (* field): returns a ValueQuerySet -- a special QuerySet, which is not a series of values.
The instantiated object of the model is an iterative dictionary sequence
< 13 > values_list (* field): it is very similar to values(), it returns a tuple sequence, and values returns a dictionary sequence
< 14 > distinct(): remove duplicate records from returned results
views
from django.shortcuts import render, HttpResponse # Create your views here. from app01.models import Book def index(request): #Add table structure ##############################Add to #One way #book_obj = Book(id=1, title="python red book", price=100, pub_date="2012-10-08", publish = "people's Publishing House") #book_obj.save() #Method 2: the return value of create is the currently generated object record # Book_object = book. Objects. Create (title = "PHP", price = 100, pub_date = "2018-01-01", publish = "Peking University Press") # print(book_obj.title) # print(book_obj.price) # print(book_obj.pub_date) ##############################Query table record API#################### ''' 1.Know the return value of the method; 2 the call value of the method (who can call it) :param request: :return: ''' #(1)all method, find out all results. The return value is a queryset object, which supports sequence objects. #book_list = Book.objects.all() #print(book_list) #[obj,obj1,obj2，，，] #The object of the direct print class is a object You can add one to the class def __str__(self)
#<QuerySet [<Book: Book object (1)>,Book:Book object (2)>]> # return self.title #And then it prints it. title Attribute
#<QyertSet [<Book: python>, <Book: php>]> # for obj in book_list: #Support for loop # print(obj.title, obj.price) #print(book_list[1].title) #Support index and slice. #(2) first,last; the caller is the queryset object and the return value is the models object (< class' app01.models.Book '> #book = Book.objects.all().first() #Get the first model object #book = Book.objects.all()[0] #It's the same thing. #(3) the return value of filter() is queryset object. Filter is the result of getting filter conditions, and all is the result of getting all results. # book_list = Book.objects.filter(price=100) #[obj,obj1,obj2...] # print(book_list) #< queryset [< book: Python red book >, < book: PHP >] > # book_obj = Book.objects.filter(price=100).first() #SQL---> SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`pub_date`, `app01_book`.`price`, `app01_book`.`publish` FROM `app01_book` WHERE `app01_book`.`price` = 100 LIMIT 21;
# ret = Book.objects.filter(title="go", price=200) #filter can also add multiple query conditions, both of which are indispensable. # print(ret) #<QuerySet []> #(4)get() has and onlyA query resultOnly when it is taken out can it be meaningful. Otherwise, it will report an error return value: model object # book_obj = Book.objects.get(title="go") # book_obj = Book.objects.get(title="price") #It will be wrong. # print(book_obj.price) SQL---> SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`pub_date`, `app01_book`.`price`, `app01_book`.`publish` FROM `app01_book` WHERE `app01_book`.`title` = 'php';
#(5)exclude() return value: queryset object #ret = Book.objects.exclude(title="go") #All books except title="go" are filtered out. filter is based on conditions #print(ret) #< queryset [< book: Python red book >, < book: PHP >] >
SQL---> SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`pub_date`, `app01_book`.`price`, `app01_book`.`publish` FROM `app01_book` WHERE NOT (`app01_book`.`title` = 'go') LIMIT 21; #(6)order_by() caller queryset object; return value queryset object #ret = Book.objects.all().order_by("id") #From small to large #ret = Book.objects.all().order_by("-id") #From big to small
#ret = Book.objects.all().order_by("price")#Sort by price size #ret = Book.objects.all().order_by("price", "id") #Combination. When the price is the same, it is sorted by id. #print(ret) #(7) count() caller queryset object, return value int # ret = Book.objects.all().count() #SQL--->> SELECT COUNT(*) AS `__count` FROM `app01_book`; # print(ret) #3 #(8) exist() asks if it exists # ret = Book.objects.all().exists() don't get all the data out, which is very slow. Under. exists, LIMIT 1 (the corresponding sql statement) is taken. # if ret: # print("ok") #(9)values method caller queryset object, return value queryset # ret = Book.objects.all() #Want to get the names of all the books. # for i in ret: # print(i.tittle) #You don't want to take an object. You want to take a field in the object. #ret = Book.objects.all().values("price", "title") #values () the field you want to take. It's the same thing to remove all (). values is. All (); you can only add a price value. ''' values Principle: temp=[] for obj in Book.objects.all() temp.append({ "price"=obj.price "title"=obj.title }) return temp ''' #print(ret) #Dictionary in the list #<QuerySet [{'price': Decimal('100.00')}, {'price': Decimal('100.00')}, {'price': Decimal('250.00')}]> #It's different from the previous QuerySet; instead of putting objects in the list, it's a dictionary, which takes the values of objects as key value pairs and puts them in the dictionary; after querying several objects, there are several dictionaries. #print(ret[0].get("price")) #100.00 #(10) values list method caller queryset object, return value queryset #ret = Book.objects.all().values_list("price", "title") #print(ret) #A dictionary has been changed into a tuple, a tuple has been placed in the list, the dictionary value has been removed, and the tuple only has value. #< queryset [(decimal ('100.00 '),'python red book'), (decimal ('100.00 '),'php'), (decimal ('250.00 '),'Go')] > '''values_list and values The difference is: one is a dictionary, one is a tuple; values: <QuerySet [{'price': Decimal('100.00'), 'title': 'python Red Treasure Book'}, {'price': Decimal('100.00'), 'title': 'php'}, {'price': Decimal('250.00'), 'title': 'go'}]> values_list <QuerySet [(Decimal('100.00'),'python Red Treasure Book'), (Decimal('100.00'), 'php'), (Decimal('250.00'), 'go')]> ''' #(11) distinct #ret = Book.objects.all().distinct() #It doesn't make sense to write in this way. If you distinguish all of them, a duplicate field will make sense. #print(ret) #ret = Book.objects.all().values("price") # SQL ---> SELECT `app01_book`.`price` FROM `app01_book` LIMIT 21; #print(ret) #<QuerySet [{'price': Decimal('100.00')}, {'price':Decimal('100.00')}, {'price': Decimal('250.00')}]>ret = Book.objects.all().values("price").distinct() #SQL---> SELECT DISTINCT `app01_book`.`price` FROM `app01_book` LIMIT 21; print(ret) #<QuerySet [{'price': Decimal('100.00')}, {'price': Decimal('250.00')}]>return HttpResponse('OK')
Fuzzy query with double underscores for single table query
###########################Fuzzy query of query table records######################
# ret = Book.objects.filter(price__gt=10, price__lt=200) #More than 10 but less than 200
# print(ret) #< queryset [< book: Python red book >, < book: PHP >] >
# ret = Book.objects.filter(title__startswith="py") #Beginning with py
# print(ret) #< queryset [< book: Python red book >] >
#ret = Book.objects.filter(title__contains="h") #As long as it contains h, you can find it out.
# ret = Book.objects.filter(title__icontains="h") #Regardless of case, as long as it contains #SQL---> SELECT `app01_book`.`id`, `app01_book`.`title`, `app01_book`.`pub_date`, `app01_book`.`price`, `app01_book`.`publish` FROM `app01_book` WHERE `app01_book`.`title` LIKE '%h%' LIMIT 21;
# print(ret)
# ret = Book.objects.filter(price__in=[100, 200, 300])#In this range 100-200-300 That's going to print out. #WHERE `app01_book`.`price` IN (200, 100, 300) LIMIT 21;
# print(ret) #< queryset [< book: Python red book >, < book: PHP >] >
ret = Book.objects.filter(pub_date__year=2018, pub_date__month=5) #It's OK not to write the back
print(ret)#<QuerySet [<Book: go>]> #SQL--> WHERE (`app01_book`.`pub_date` BETWEEN '2018-01-01' AND '2018-12-31' AND EXTRACT(MONTH FROM `app01_book`.`pub_date`) = 5) LIMIT 21;
return HttpResponse('OK')
Delete and modify
###########################Delete and modify records###################### #delete: the caller is the queryset object model object #ret = Book.objects.filter(price=250).delete() #No return value can be deleted #ret = Book.objects.filter(price=100).delete() #Delete both #print(ret) #(1, {'app01.Book': 1}) (2, {'app01.Book': 2}) #Book.objects.filter(price=100).first().delete() #Only one has been deleted
#The caller of update can only be a queryset object ret = Book.objects.filter(title="php").update(title="python")return HttpResponse('OK')
The delete method is delete(). It immediately deletes the object at run time without returning any value.
You can also delete multiple objects at once. Each QuerySet has a delete() method that deletes all objects in the QuerySet at once.
When Django deletes an object, it mimics the SQL constraint ON DELETE CASCADE. In other words, deleting an object also deletes the foreign key object associated with it.
Note that the delete () method is a method on the QuerySet, but does not apply to the Manager itself. This is a protection mechanism to avoid accidentally calling the Entry.objects.delete() method to cause all records to be deleted by mistake. If you are sure you want to delete all objects, you must explicitly call:
If you do not want to cascade delete, you can set it to:
pubHouse = models.ForeignKey(to='Publisher', on_delete=models.SET_NULL, blank=True, null=True)