Django model layer - Single table operations

Introduction to ORM

This is the second day of my participation in Gwen Challenge

Object Relational Mapping (ORM) pattern is a technique to solve the mismatch between object-oriented and Relational databases. Simply put, ORM automatically persists an object in a program to a relational database using metadata that describes the mapping between the object and the database. ORM acts as a bridge between the business logic layer and the database layer.Copy the code

Correspondence:

Class – > table
Object –> Data row (record)
Properties –> fields

Using ORM

SQL: Configure database connection in Settings

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3'.'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),}}Copy the code

Write the class in models.py under app:

class User(models.Model) :  # inheritance models. The Model
    username = models.CharField(max_length=32)  # varchar(32)
    password = models.CharField(max_length=32)  # varchar(32)
    A db. Sqlite3 is generated in the templates file
Copy the code

Then click Database on the far right
Double-click the file db.sqlite3!

– Click Settings!

The first time to download the plug-in, direct download -> download after!

Database migration command (enter following command in terminal)

python manage.py makemigrations  # Test models under all apps. What changes have been made to the py file to make the change record into a migration file
python manage.py migrate  Database migration synchronizes records of changes to the database
Copy the code

Refresh after!

Then double click app01_user to add data and click DB where to save!

Add this to settings.py and you can see the translated SQL statement on the console

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

The ORM testing

 from app01 import models
    res = models.User.objects.all(a)Select * from table_name
    for i in res:
        ''' User object abc 123 
      
        User object cba 123 
       
         '''
       
      
        print(i,i.username,i.password,type(i.username))
        
    ret = models.User.objects.get(username="abc",password="123") Get a User object
    # ret1 = models.user.objects.get (password="123")
        
    ret = models.User.objects.filter(password="123") Return a list, or an empty list if not retrieved
    print(ret[0].username,ret[1].username,ret) #abc cba <QuerySet [<User: User object>, <User: User object>]>
Copy the code

Mysql database:

Create a mysql database
```
create database login;
Copy the code
```
The configuration Settings. Py

Database = {'default': {'ENGINE': 'django.db.backends.mysql', 'database' = {' database ': 'django.db.backends.mysql', 'NAME': 'login', # database 'HOST': '127.0.0.1 ', 'PORT': 3306, 'USER': 'root', 'PASSWORD': 'mysql', } }Copy the code

Connect to the mysql database using the Pymysql module

It is customary to write to ‘init.py’ in the folder with the same name as the project
```
import pymysqlpymysql.install_as_MySQLdb()
Copy the code
```

Write the class to models.py under app

class User(models.Model): Model username = models.charfield (max_length=32) # varchar(32) password = models.CharField(max_length=32) # varchar(32)Copy the code

Execute the database migration command
```
Python Manage.py Makemigbar # Detect models in All apps. Python manage.py migrate # Synchronize changes to the databaseCopy the code
```
After we have generated the file, click on the right Datebases and select —– in the upper left corner. Select Data Source and select MYSQL to set —-!

Finally, test the Connection in test Connection

Server returns invalid timezone. Go to ‘Advanced’ TAB and set ‘serverTimezone’ property manually.

Just type two commands: show variables like’%time_zone’;

set global time_zone = '+8:00';
Copy the code

Or:

In Advanced –> serverTimezone set oh Hongkong

Second, the Model

Fields commonly used by Model

AutoField Primary_key =TrueBecomes the primary key. IntegerField plastic10A -2147483648 ~ 2147483647. CharField string varchar max_length (length) mandatory parameter DateField date type in the format of yyyy-mm-dd (year-month-day) DatetimeField DatetimeField, Format for MM - DD YYYY - HH: MM [: ss [. Uuuuuu]] (TZ) (on - - - - - seconds)#auto_now: Changes to the current date and time each time.
#auto_now_add: Automatically add the current date and time when a new object is created.
#auto_now and auto_now_add and default parameters are mutually exclusive and cannot be set at the same time.BooleanFiled Type TextField Large text FloatField DecimalField10Hexadecimal decimal#max_digits, total decimal length
# decimal_Places, decimal length
# more parameters: https://www.cnblogs.com/maple-shaw/articles/9323320.html
Copy the code

The instance

class Person(models.Model) :    
    pid = models.AutoField(primary_key=True)  # Set primary key, if not set, it will be added automatically
    name = models.CharField(max_length=32) # varchar(32)    
    age = models.IntegerField() # plastic
    birth = models.DateTimeField(auto_now_add=True) The current time is automatically added when a new object is created
    birth = models.DateTimeField(auto_now=True) Change the time when modifying objects
Copy the code

Custom field

Custom field

class MyCharField(models.Field) :    Custom field class for type CHAR    
    def __init__(self, max_length, *args, **kwargs) :        
        self.max_length = max_length        
        super(MyCharField, self).__init__(max_length=max_length, *args, **kwargs)    
        def db_type(self, connection) :        
                        Restrict the field type of the generated database table to char and length to the value specified by max_length.       
            return 'char(%s)' % self.max_length  #char(11)
Copy the code

Enter the database migration command

Python manage.py makemigrations# because you are adding fields, the following image will appear:Copy the code

Add a default parameter to the previous field, or add a default value.

class Person(models.Model) :    # add custom fields
    cname = MyCharField(max_length=11) #char(11)
Copy the code

Parameter of model field

When adding or deleting fields, remember to migrate the database

null=TrueThis field can be blank= in the databaseTrueAllow user input to be null (TrueDb_column Specifies the column name (alias) of a database column. Defalut Specifies the default value. Db_index Specifies the index unique=TrueCreate a new database and unique=TrueSQL > select * from choices; SQL > select * from choices; SQL > select * from choices;True."Male"), (False."Female"))) the drop-down selection box more detailed data: https://www.cnblogs.com/maple-shaw/articles/9323320.html
Copy the code

Ex. :

class Person(models.Model) :    
    pid = models.AutoField(primary_key=True)  # Set primary key, if not set, it will be added automatically
    name = models.CharField(verbose_name="Name",db_column='nick',max_length=32, blank=True,unique=True) # varchar(32)    
    age = models.IntegerField(null=True,default=18) # plastic
    birth = models.DateTimeField(auto_now_add=True) The current time is automatically added when a new object is created
    birth = models.DateTimeField(auto_now=True) Change the time when modifying objects
    sex = models.BooleanField(choices=((True."Male"), (False."Female"))) Add a custom field
    cname = MyCharField(max_length=11)  #char(11)
Copy the code

Parameters of the model table

Modify table (to migrate the database, don’t forget)

class Person(models.Model) :    
    pid = models.AutoField(primary_key=True)  # Set primary key, if not set, it will be added automatically
    name = models.CharField(verbose_name="Name",db_column='nick',max_length=32, blank=True,unique=True) # varchar(32)    
    age = models.IntegerField(null=True,default=18) # plastic
    birth = models.DateTimeField(auto_now_add=True) # birth = models.datetimeField (auto_now=True) # Change the time when modifying an object
    sex = models.BooleanField(choices=((True."Male"), (False."Female"))) Add a custom field
    cname = MyCharField(max_length=11)  #char(11)    
    
    
    
    
    class Meta:        # create table name from Database default app name + underscore + class name
        db_table = "Person"                The name of the table displayed in # admin
        verbose_name = 'Personal Information'        # verbose_name + s admin
        verbose_name_plural = 'All User information'        Mysql > alter table unique=True
        index_together = [            ("name"."age"),  Mysql > alter table unique=True; alter table unique=True
        unique_together = (("name"."age"),# should be two existing fields
Copy the code

ORM single table add delete change check

increase

save()
create()
bulk_create()
update_or_create()

Create a record 1
student_obj = models.Student(        name='Lao wang',        age=19,    )    
student_obj.save() # # brush to list created record way 2 new_obj = models. Student. Objects. The create (name = 'xiao li', Age =15) # Student object -- Models object print(new_obj.name)
print(new_obj.age)Create objs = []
for i in range(20):        obj = models.Student(            name='xiangxi%s' % i,            age=10 + i,        )        objs.append(obj)    models.Student.objects.bulk_create(objs)# 4 update_or_create creation method () have updated without creating, models, Student. Objects. Update_or_create (name = 'Lao wang 2, defaults = {' age: 48,})
Copy the code

delete

delete()

# delete delete queryset and model can be called models. Student. Objects. The get (id = 2). The delete () # Object model call the delete method models. Student. Objects. The filter (name = 'small'). The delete () models. Student. Objects. All () the delete () # delete all
Copy the code

change

update()

# update the update () method the model object cannot call update method Only QuerySet invocation models. Student. Objects. The filter (name = 'Lao wang'). The update (age = 29)
Copy the code

check

.all() queries all and returns a collection of QuerySets (similar to a list)
.filter() returns an empty set, and returns a QuerySet
.get() returns a Model object with one and only result (none or more results fail)

# a simple query all_obj = models. Student. Objects. All ()
Print (all_obj)
# 
      
       , 
       
        ]>
       
      
# query. Filter () returns an empty QuerySet. No error objs = models. Student. Objects. The filter (id = 2) objs = models. Student. Objects. The filter (name = 'Lao wang') print (objs)
# conditions query the get () returns the model object, and the get method and must have a result obj = models. Student. Objects. The get (id = 1) print (obj)
# Lao wang obj = models. Student. Objects. Get correct (name = 'small') # 1, get the query results more than one () returned more than one Student, it returned 2! Obj = models. Student. Objects. The get (name = 'significantly')
# Student matching query does not exist. Print (obj)
Copy the code

Query interface

<1> all(): query all results with querySet <2> filter(**kwargs): This contains objects that match the filter given, and the result is also a QuerySet of type Book.objects.filter(title='linux',price=100) Select * from 'and'; select * from 'or'; Direct write here is not the or models. Student. Objects. The filter (id = 7, name = 'xiangxi0'), update (name = 'little sister, age = 18,)
<3> get(**kwargs): returns an object that matches the filter criteria, not a querySet type, but a row record object, with one and only one result. If there is more than one or none of the filter criteria, an error will be thrown. Catch exceptionstry. Book.objects.get(id=1)  
<4> exclude(**kwargs): exclude(**kwargs): exclude(**kwargs): exclude(**kwargs): exclude(**kwargs): exclude(**kwargs)id=6), returningidIs not equal to6Objects, or a querySet based call, book.objects.all().exclude(id=6) <5> order_by(*field): data of type querySet to sort the results of the queryidThe return value is of the querySet type models.book.objects.all().order_by('price'.'id') Order_by ('-price') order_by('price','id') order_by('price','id') order_by('price','id') Ascending by ID
<6> reverse(): call with data of type querySet, sort query results in reverse, return value of type querySet <7> count(): called with data of type querySet and returns the number of objects in the database that match the query. <8> first(): called with data of type querySet, returning the first record book.objects.all(to)0] = Book.objects.all().first(), returns all model objects, not querySet <9> last(): called with data of type querySet, returning the last record <10> exists(): data of type querySet, returned if the querySet contains dataTrueOtherwise returnFalseEmpty QuerySet data also has a Boolean valueTrueandFalseIf there is a lot of data in the database, you need to query all the data. This is very inefficient. Use count or exits examples. all_books = models.Book.objects.all().exists() SELECT (1) AS 'a' FROM 'app01_book' LIMIT 1 (SELECT (1) FROM 'app01_book' LIMIT 1
<11> values(*field): ValueQuerySet -- a special QuerySet that returns, instead of a series of model instantiations, an iterable sequence of dictionaries. As long as the querySet type returned, You can proceed to chain calls to other lookup methods of type QuerySet, as well as other methods. <12> values_list(*field): this is very similar to values() in that it returns a tuple sequence and values returns a dictionary sequence <13> distinct(): values and values_list get querySet data to exclude duplicate entries from the returned resultCopy the code

There are 13 things you must know

import osos.environ.setdefault("DJANGO_SETTINGS_MODULE"."about_orm.settings")
import djangodjango.setup()from app01 import models# all query all data QuerySet object list
ret = models.Person.objects.all(a)Select * from __repr__# get; select * from __repr__# get;
ret = models.Person.objects.get(pk=1)  __str__# filter is called directly to retrieve the list of data objects that meet the criteria
ret = models.Person.objects.filter(pk=1)# order_by order_order by order_order by order_order
ret = models.Person.objects.all().order_by("-age"."pid")# reverse Reverses the list of sorted objects
ret = models.Person.objects.all().order_by("pid").reverse()QuerySet [{" PID ":5,"name":111}] query [{"pid":5,"name":111}
ret = models.Person.objects.all().values("pid"."name")QuerySet [('111', 5), ('1111', 3)]
ret = models.Person.objects.all().values_list("name"."pid")# remove distinct (age like)
ret = models.Person.objects.values("age").distinct()# count count
ret = models.Person.objects.all().count()# first gets the first element
ret = models.Person.objects.all().first()# last gets the last element
ret = models.Person.objects.all().last()# exists Determines whether a result exists. True,False
ret = models.Person.objects.filter(pk=2).exists()
print(ret)Allfilterexclude takes the anti-order_byReverseValues [{},{}]values_list [(),()]distinct Return object getFirstLast Return number count Return Boolean value exists"""
Copy the code

Fuzzy query based on double underline

Among the in
Gt greater than or equal to
Lt less than or equal to
The range interval
Contains/icontains
Startswidth beginning
Year date (be careful when looking for dates, change USE_TZ to FALSE in settings.py)

Book.objects.filter(price__in=[100.200.300]) The value of #price is equal to any of these three objects
Book.objects.filter(price__gt=100)  Price__gte =100; price>100 is not supported
Book.objects.filter(price__lt=100)Book.objects.filter(price__range=[100.200])  SQL > select * from 'between and'
Book.objects.filter(title__contains="python")  The #title value contains python's
Book.objects.filter(title__icontains="python") # case insensitive
Book.objects.filter(title__startswith="py") # begins with what, istartswith case insensitive
Book.objects.filter(pub_date__year=2012)# date query
# all_books = models. Book. Objects. The filter (pub_date__year = 2012) # to find all the books in 2012
# all_books = models. Book. Objects. The filter (pub_date__year__gt = 2012) # to find greater than all the books in 2012
all_books = models.Book.objects.filter(pub_date__year=2019,pub_date__month=2)Mysql database time zone is different from djangos time zone. All you need to do is change USE_TZ = True to False in the Settings file in Django to check the result. Later, this value will be set to False, and it is the mysql database that causes this problem. Other databases do not.
Copy the code

Double underline of a single table

ret = models.Person.objects.filter(pid__lt=5)  Less than pid = less than pid 5
ret = models.Person.objects.filter(pid__gt=5)  Less than pid < 5
ret = models.Person.objects.filter(pid__lte=5)  Pid = less than or equal to pid 5
ret = models.Person.objects.filter(pid__gte=5)  Less than pid equal = less than pid equal = 5
ret = models.Person.objects.filter(pid__range=[1.6])  # values between 1 and 6 include 1 and 6
ret = models.Person.objects.filter(pid__in=[1.3.11])  Pid = 1,3,11
ret = models.Person.objects.filter(name__contains="Xiao Li")  # indicates whether the name contains "Li Xiaoxiao"
ret = models.Person.objects.filter(name__icontains="a")  # indicates whether the name contains "a/ a "plus I regardless of case
ret = models.Person.objects.filter(name__startswith="Li")  # indicates that the name begins with "lee"
ret = models.Person.objects.filter(name__istartswith="a")  # Indicates that the name starts with "A/a" and case is ignored
ret = models.Person.objects.filter(name__endswith="Small")  # indicates that name ends in "small"
ret = models.Person.objects.filter(name__iendswith="a")  # indicates that the name ends in "A/a" and case is ignored
ret = models.Person.objects.filter(birth__year="2020")  # is the year of birth in 2020
ret = models.Person.objects.filter(birth__month="2")  # indicates the # of month 2 in birth
ret = models.Person.objects.filter(birth__day="21")  # stands for date 21 in birth
ret = models.Person.objects.filter(birth__contains="- 10 -")  # represents the month of 10
ret = models.Person.objects.filter(name__isnull=False)  The name field is not empty
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Django model layer — Single table operations

Introduction to ORM

Using ORM

The ORM testing

Mysql database:

Second, the Model

Fields commonly used by Model

Custom field

Parameter of model field

Parameters of the model table

ORM single table add delete change check

increase

delete

change

check

Query interface

There are 13 things you must know

Fuzzy query based on double underline

Double underline of a single table

Django model layer — Single table operations

Introduction to ORM

Using ORM

The ORM testing

Mysql database:

Second, the Model

Fields commonly used by Model

Custom field

Parameter of model field

Parameters of the model table

ORM single table add delete change check

increase

delete

change

check

Query interface

There are 13 things you must know

Fuzzy query based on double underline

Double underline of a single table

Related Posts

Go language – Select at first glance

Real-time GC – tricolor labeling algorithm for Go language

Java describes data structures and algorithms