9.17 model-level ORM single-table operation

1 Creating a Model

Create the app named book and create the model in models.py under book:

from django.db import models# Create your models here.class Book(models.Model):     id=models.AutoField(primary_key=True)     title=models.CharField(max_length=32)     state=models.BooleanField()     pub_date=models.DateField()     price=models.DecimalField(max_digits=8,decimal_places=2)     publish=models.CharField(max_length=32)

2 more fields and parameters
Each field has some unique parameters, for example, Charfield requires the max_length parameter to specify the size of the varchar database field. There are also common parameters that apply to all fields. These parameters are defined in detail in the documentation, where we'll simply describe some of the most common ones:

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 tier and the Django check layer .<2> Integerfield #用于保存一个整数 .<3> Floatfield A floating-point number. Must provide two parameters: parameter description max_digits total number of digits (excluding decimal points and symbols) Decimal_places For example, to save a maximum value of 999 (Save 2 digits after the decimal point), you will define the field as follows: Models. Floatfield (..., max_digits=5, decimal_places=2) to save the maximum value of 1 million (10 digits after the decimal point), you have to define this: models. Floatfield (..., max_digits=19, decimal_places=10) admin uses a text box (<input type= "text" >) to represent the data that the field holds. <4&gt ; Autofield a Integerfield, it will grow automatically when you add a record.       You do not usually need to use this field directly; Customize a PRIMARY key: My_id=models. Autofield (primary_key=true) If you don't specify a primary key, the system automatically adds a primary key field to your model.<5> Booleanfield a true/false field.       The admin uses a checkbox to represent such fields .<6> TextField a large text field. Admin uses a <textarea> (text area) to represent the field data. (a multi-line edit box) .<7> Emailfield a Charfield with the legality of checking email, maxlength parameters are not accepted .<8> DatefieLD a date field.       The following additional optional parameters are available: Argument description Auto_now When the object is saved, the value of the field is automatically set to the current time. Typically used to represent a "last-modified" timestamp.       Auto_now_add when an object is first created, the value of the field is automatically set to the current time. Typically used to represent object creation time. (only meaningful in admin ...) <9> Datetimefield A date-time field.       Similar to Datefield support the same additional option .<10> ImageField similar to Filefield, but verify that the uploaded object is a legitimate picture. #它有两个可选参数: Height_field and Width_field,    If both parameters are provided, the picture is saved according to the provided height and width specifications.    <11> Filefield a File upload field. Requires a required parameter: upload_to, a local file system path to save the uploaded file.    This path must contain the Strftime #formatting, which will be replaced with the date/time of the uploaded file (so uploaded files don ' t fill up the given directory).    The admin uses a <input type= "file" > part to represent the data saved by the field (a file upload part).           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.) Defines media_url as the public URL for this directory.           Make sure that the directory is writable for the Web server user account. (2) Add Filefield or ImageField to your model and ensure that the UPLOAD_TO option is defined to tell Django which subdirectory to use Media_rootSave the upload file. The path to the file that you want to save in your database (relative to Media_root). Out of habit you must be very interested in using the get_< #fieldname >_url function provided by Django. For example, if your ImageField is called Mug_shot, you can use {{object. #get in the template. _mug_shot_url}} This way to get the absolute path of the image .<12> Urlfield is used to save the URL.     If the verify_exists parameter is True (the default), the given URL is pre-checked for presence (that is, if the URL is loaded effectively and no 404 response is returned). The admin uses a <input type= "text" > text box to represent the data saved by the field (a single-line edit box) <13> Nullbooleanfield like Booleanfield, but allows NULL as An option. It is recommended to use this field instead of the Booleanfield plus null=true option Admin with a selection box <select> (three selectable values: "Unknown", "Yes" and "No") to represent this field data. &l t;14> Slugfield "Slug" is a newspaper term. Slug is a small mark (a short sign) of something that contains only letters, numbers, underscores, and hyphens. #它们通常用于URLs If you use the Django development version, you can specify MaxLength.  If MaxLength is not specified, Django will use the default length: 50.      #在 the previous Django version, there is no way to change the length of 50.      This implies a db_index=true. It accepts an additional parameter: Prepopulate_from, which is a list of fields from which to auto-#populate 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 if a checksum is a TextField of legitimate XML, the parameter must be supplied: Schema_path, It is a relaxng schema that is used to verify text #的文件系统路径 .<14> Filepathfield Optional items are file names under a specific directory.       Three special parameters are supported, the first of which is the one that must be provided. Parameter describes the path required parameter. The absolute file system path of a directory.       Filepathfield the optional items accordingly.       Example: "/home/images". Match Optional parameters.       A regular expression, which, as a string, Filepathfield uses to filter the file name. Note that this regular expression is applied only to base filename, not to the path full name.       Example: "foo.*\.txt^" will match the file Foo23.txt but does not match bar.txt or foo23.gif. Recursive optional parameters. Either True or False. The default value is False.       Whether to include all subdirectories below path.       These three parameters can be used simultaneously. Match applies only to base filename, not to the full name of the path. So, this example: Filepathfield (path= "/home/images", match= "foo.*", recursive=true) ... Matches the IP address of/home/images/foo.gif without matching/home/images/foo/bar.gif<15> Ipaddressfield as a string (i.e. "24.124.1.30") The .<16> Commaseparatedintegerfield is used to store comma-separated integer values. Similar to Charfield, there must be maxlength parameters.

More parameters:

(1)null如果为True，Django 将用NULL 来在数据库中存储空值。 默认值是 False.(1)blank如果为True，该字段允许不填。默认为False。要注意，这与 null 不同。null纯粹是数据库范畴的，而 blank 是数据验证范畴的。如果一个字段的blank=True，表单的验证将允许该字段是空值。如果字段的blank=False，该字段就是必填的。(2)default字段的默认值。可以是一个值或者可调用对象。如果可调用 ，每有新对象被创建它都会被调用。(3)primary_key如果为True，那么这个字段就是模型的主键。如果你没有指定任何一个字段的primary_key=True，Django 就会自动添加一个IntegerField字段做为主键，所以除非你想覆盖默认的主键行为，否则没必要设置任何一个字段的primary_key=True。(4)unique如果该值设置为 True, 这个数据字段的值在整张表中必须是唯一的(5)choices由二元组组成的一个可迭代对象（例如，列表或元组），用来给字段提供选择项。 如果设置了choices ，默认的表单将是一个选择框而不是标准的文本框，<br>而且这个选择框的选项就是choices 中的选项。

3 Settings configuration

To convert a model to a table in a MySQL database, you need to configure it in Settings:

DATABASES = {    ‘default‘: {        ‘ENGINE‘: ‘django.db.backends.mysql‘,        ‘NAME‘:‘bms‘,     　　 　  # 要连接的数据库，连接前需要创建好        ‘USER‘:‘root‘,　　　　　　  # 连接数据库的用户名        ‘PASSWORD‘:‘‘,　　　　　　  # 连接数据库的密码        ‘HOST‘:‘127.0.0.1‘,       # 连接主机，默认本级        ‘PORT‘：3306    　　　     #  端口 默认3306    }}

Note that 1:name is the name of the database, the database must have been created before the MySQL connection, and the db.sqlite3 under the SQLite database above is the project automatically create user names and passwords for the users and password, respectively, of the database. Once setup is complete, we need to activate our MySQL before starting our Django project. Then, start the project, will error: no module named MySQLdb. This is because Django defaults to the driver you are importing is MYSQLDB, but MySQLdb is a big problem for py3, so the driver we need is pymysql so we just need to find Init under the project name file, write in it:

import pymysqlpymysql.install_as_MySQLdb()

Finally, the table can be created in the specified database with the two Database Migration command:

python manage.py makemigrationspython manage.py migrate

Note 2: Make sure that the name of the app we created is written in Installed_apps in the configuration file

INSTALLED_APPS = [    ‘django.contrib.admin‘,    ‘django.contrib.auth‘,    ‘django.contrib.contenttypes‘,    ‘django.contrib.sessions‘,    ‘django.contrib.messages‘,    ‘django.contrib.staticfiles‘,    "book"]

Note 3: If the error is as follows:

django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.3 or newer is required; you have 0.7.11.None

Mysqlclient currently only supports to python3.4, so if you are using a later version of Python, you need to modify the following:

By finding the path C:\Programs\Python\Python36-32\Lib\site-packages\Django-2.0-py3.6.egg\django\db\backends\mysql the file in this path

if version < (1, 3, 3):     raise ImproperlyConfigured("mysqlclient 1.3.3 or newer is required; you have %s" % Database.__version__)

Comment out on the OK.
Note 4: If you want to print SQL in the ORM conversion process, you need to configure the following in Settings:

LOGGING = {    ‘version‘: 1,    ‘disable_existing_loggers‘: False,    ‘handlers‘: {        ‘console‘:{            ‘level‘:‘DEBUG‘,            ‘class‘:‘logging.StreamHandler‘,        },    },    ‘loggers‘: {        ‘django.db.backends‘: {            ‘handlers‘: [‘console‘],            ‘propagate‘: True,            ‘level‘:‘DEBUG‘,        },    }}

First, add the table record

Mode 1

# create方法的返回值book_obj就是插入book表中的python葵花宝典这本书籍纪录对象  book_obj=Book.objects.create(title="python葵花宝典",state=True,price=100,publish="苹果出版社",pub_date="2012-12-12")

Mode 2

book_obj=Book(title="python葵花宝典",state=True,price=100,publish="苹果出版社",pub_date="2012-12-12")book_obj.save()

Second, the enquiry form record

Query API

<1> all():                  查询所有结果<2> filter(**kwargs):       它包含了与所给筛选条件相匹配的对象<3> get(**kwargs):          返回与所给筛选条件相匹配的对象，返回结果有且只有一个，                            如果符合筛选条件的对象超过一个或者没有都会抛出错误。<4> exclude(**kwargs):      它包含了与所给筛选条件不匹配的对象<5> order_by(*field):       对查询结果排序<6> reverse():              对查询结果反向排序<8> count():                返回数据库中匹配查询(QuerySet)的对象数量。<9> first():                返回第一条记录<10> last():                返回最后一条记录<11> exists():              如果QuerySet包含数据，就返回True，否则返回False<12> values(*field):        返回一个ValueQuerySet——一个特殊的QuerySet，运行后得到的并不是一系列                            model的实例化对象，而是一个可迭代的字典序列<13> values_list(*field):   它与values()非常相似，它返回的是一个元组序列，values返回的是一个字典序列<14> distinct():            从返回结果中剔除重复纪录

Fuzzy query based on double underline

Book.objects.filter(price__in=[100,200,300])Book.objects.filter(price__gt=100)Book.objects.filter(price__lt=100)Book.objects.filter(price__range=[100,200])Book.objects.filter(title__contains="python")Book.objects.filter(title__icontains="python")Book.objects.filter(title__startswith="py")Book.objects.filter(pub_date__year=2012)

Third, delete the table record

The deletion method is delete (). It runs immediately when the object is deleted without returning any values. For example:

model_obj.delete()

You can also delete multiple objects at once. Each QuerySet has a delete () method that deletes all the objects in QuerySet at once.
For example, the following code removes pub_date as the Entry object for 2005:

    Entry.objects.filter(pub_date__year=2005).delete()

When Django deletes an object, it mimics the behavior of the SQL constraint on DELETE CASCADE, in other words, deleting an object also deletes the foreign key object associated with it. For example:

b = Blog.objects.get(pk=1)# This will delete the Blog and all of its Entry objects.b.delete()

Note that the delete () method is a method on QuerySet, but does not apply to the Manager itself. This is a protection mechanism to avoid accidentally invoking the Entry.objects.delete () method, which causes all records to be mistakenly deleted. If you confirm that you want to delete all the objects, then you must explicitly call:

Entry.objects.all().delete()

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)

Iv. Modification of table records

Book.objects.filter(title__startswith="py").update(price=120)

In addition, the update () method is valid for any result set (QuerySet), which means that you can update multiple records at the same time the update () method returns an integer value that represents the number of record bars affected.

V. Chapter Assignments

1 Library Management System implementation function: Book single table additions and deletions 2 query operation exercise

1 查询老男孩出版社出版过的价格大于200的书籍2 查询2017年8月出版的所有以py开头的书籍名称3 查询价格为50,100或者150的所有书籍名称及其出版社名称4 查询价格在100到200之间的所有书籍名称及其价格5 查询所有人民出版社出版的书籍的价格（从高到低排序，去重）

9.17 model-level ORM single-table operation