Manipulating mysql databases in django

DATABASES = {
    'default': {
        'ENGINE': '',
        'NAME': 'python',
        'USER': 'root',
        'PASSWORD': 'zy199909237412',
        'HOST': '127.0.0.1',
        'POST': '3306',
    }
}

import pymysql
pymysql.install_as_MySQLdb()

from  import models # 1. import models module

# 2. Define a class to inherit from (create a table of project name (application name)_class name)
class Book():

# 3. Define the class attributes title and price (corresponding to the creation of field names, field types and field options)
title = ('Title of the book', max_length=50, default='')
price = ('Price', max_digits=7, decimal_places=2)

class model class name（):
           field name = models.Field type（Field Options）


Not specifying a primary key field inside a class，djangowill help you automatically create aidprimary key！

Add fields directly inside the corresponding class、Field types and field options

Note that any fields added need to be specifieddefault = '' ornull = True，Add the followinginfofield：

info = (max_length=32,default='',null=True)

from app01 import models  # 1. Import the model class you created.

# Query method, syntax is :models.class name. (), filter can query more than one parameter, the default is and join, equal to the SQL syntax of the where method! If you don't pass any parameter, it means check all

res = (username=username)
# The method has a return value, the return is a queryset object, the object can be seen as a list set of dictionary form, the list inside the set of data objects, the form of: [data object 1, data object 2]
# queryset list also supports indexing, slicing operations, but does not support negative indexing, can be used as a list set of dictionaries in the form of a for loop

user_obj = res[0]  # Get the specific data object, but the official does not recommend the use of the index way to get the specific data object, it is recommended () method to get the first data object in the list!

print(user_obj.username)  # pass the buck，assume (office).method of the property to get the specific value

The # all method queries the User table for all data, returning a list of queryset objects.
user_queryset = ()

# create add data method with syntax :models.classname. ()
res = (username=username,password=password)
# This method also has a return value, which is the current data object
print(,) # You can view the value of a property by tapping the property's method.

obj = (username=username,password=password)
() # Save data to database

# Find out what objects the id is for and then do a batch update. filter can look up all, or specific
(id=edit_id).update(username=username,password=password)

obj = (id=edit_id).first() # Get the object to be modified
 = username # Modify the data by reassigning values to the attributes of the object
 = password
() # Remember to bite and save after the final assignment

# First filter to find out which objects need to be deleted, then .delete() method
(id=delete_id).delete() # Here is the queryset object that filter found out all deleted, there are a few deleted a few.
# Here.idIt can be rewritten aspk，used uppkThere would be no need to know that the primary key of the table isidOr something.！

res = (id=delete_id).first()
()  # single deletion

# () Query all data

# () Query with filter, get a list of queryset objects

# () takes the data object directly, but the condition does not exist and reports an error.

# () take the first element of queryset
# res = ().first()
# print(res) # Get the first data object in the queryset object list.

# ()
# res = ().last()
# print(res) # Same as above, got the best one

# () You can specify the data field to fetch select name,age from ...
# res = ('name','age')
# print(res) # The result is: list set dictionary <QuerySet [{'name': 'jason', 'age': 18}, {'name': 'egonPPP', 'age': 84}]>

# 7.values_list()
# res = .values_list('name','age')
# print(res) # The result is: a list set of tuples, <QuerySet [('jason', 18), ('egonPPP', 84)]>

# 
# print() # View internally wrapped sql statements
# The above way of viewing sql statements can only be used for queryset objects.
# Only queryset objects can click on query to see the internal sql statement

# () De-weighting
# res = ('name','age').distinct()
# print(res)
"""
The de-emphasis must be on the exact same data.
If it has a primary key, then it's definitely not the same, and you mustn't ignore the primary key in future queries.
"""

# 10.order_by()
# res = .order_by('age') # ascending by default
# res = .order_by('-age') # descending order
# print(res)

# () is reversed if the data has already been sorted order_by()
# res = ()
# res1 = .order_by('age').reverse()
# print(res,res1)

# () Count the number of current data
# res = ()
# print(res)

# () Excluded
# res = (name='jason')
# print(res)

# () # Basically unused because the data itself comes with a boolean value Returns a boolean value
# res = (pk=10).exists() # reads if primary key 10 exists, returns a boolean value
# print(res)

__exact:equivalence query 
__contains:Contains the specified value--distinguishing capitals from lower case letters   a2=(name____contains=‘n') Query for names that containn(used form a nominal expression)
__icontains:Contains the specified value--ignore capitals
__startwith:in order toxxxcommencement
__endwith:in order toxxxwind up
__gt:greater than the specified value，for example：a2=(id__gt=3)
__gte:greater than or equal to
__it:less than
__ite：less than等于
__in:Find out if the data is within the specified range a2=(id__in=[1,3,5])
__range:Queries whether the data is within the specified interval range a2=(id__range=[1,5]) search outidexist1-5(used form a nominal expression)收尾都要
a2=（register_time__month='1'）:search out月份是1月(used form a nominal expression)数据
a2=（register_time__year='2022'）:search out年份exist2022(used form a nominal expression)数据

"""
Book and publisher: one-to-many relationship, foreign keys are built on the side of the many
Book and author: many-to-many relationship, foreign keys are built on either side, but it is recommended to build them on the side with high query frequency.
Author and author details: one-to-one relationship, the foreign key is built on any side, but it is recommended to build on the side with high query frequency
"""
class Book():
    title = (verbose_name='Title of the book',max_length=32)
    price = (max_digits=8,decimal_places=2)
    create_time = (auto_now_add=True)
    # A one-to-many foreign key is built on one side of the many, to='is the name of the class for which the foreign key needs to be built publish'
    # Note: In django 2 and 3, you need to specify the cascading delete parameter on_delete=.
    publish = (to='Publish',on_delete=)
    # Many-to-many foreign keys are built on the side of high query frequency, many-to-many in the sql statement you need to manually build a third table yourself, but in django, django encounters will automatically help you create a third table!
    author = (to='Author')

class Publish():
    name = (max_length=32)
    addr = (max_length=64)
    email = ()

class Author():
    name = (max_length=32)
    age = ()
    # One-to-one foreign keys are built on the side with high query frequency and need to be specified on_delete
    author_detail = (to='AuthorDetail',on_delete=)

class AuthorDetail():
    phone = ()
    addr = (max_length=64)

from app01 import models
# 1. Increase
# Method 1: Write the actual field name of the foreign key inside the book table, and then specify the associated publish id.
(title='Alive',price=33,publish_id=1)
# Method 2: Virtual fields, just pass in a specific publish data object.
publish_obj = (pk=2).first()
# Note that here you need to .first() a bit, because do not point to get the queryset object, point to get the queryset in the specific data objects
(title='I',price=555,publish=publish_obj)

# 2. Delete
(pk=2).delete() # Delete all book ids=2 as well

# 3.
# Method 1: Pass the name and value of the foreign key field to be modified in update.
(pk=2).update(publish_id=2) # Change the associated publish_id of the book with id 2 to 2
# Buddhist teaching2：updateInside, pass in the dummy fieldpublish=associatedpublishdata object，同增的Buddhist teaching2

# 1. Increase
# A book corresponds to multiple authors, first check the object of this book, and then associate the author id with the book via the object. Many-to-many field name.add() method associated with author ids
# add() method can be inside the French number 1,2,3, indicating the primary key value of the associated author; you can also put a specific author data object!
book_obj = (pk=1).first()
book_obj.(1,2)
author_obj = (pk=1).first()
book_obj.(author_obj)

# 2. Delete
# Delete this side of the book associated with author ids 1 and 2, and like the add method also supports putting specific author data objects inside!
book_obj.(1,2)

# 3.
# set method modifies the author ids associated with the book to 1 and 3, the set method supports putting specific author data objects inside just like the add method!
# Note: you must put an iterable object inside the set method, such as a list!
book_obj.([1,3])

# 4. Clear the current book-author correspondence
book_obj.()

 from app01 import models

    # 1. Query the name of the publisher whose book's primary key is 1 ---- Forward One-to-Many Query
    book_obj = (pk=1).first() # First get the book object with primary key 1
    res = book_obj.publish # Forward query by field, there is a publish field inside the Book table, return a publisher object associated with primary key 1
    print(res)
    print() # object. The methods query specific field attributes of the
    print()

    # 2. query book with primary key 1 author ---- forward many-to-many query
    book_obj1 = (pk=1).first()
    res = book_obj1.() # Not.all() returns a
    print(res) # .all() returns <QuerySet [<Author: Author object (1)>, <Author: Author object (2)>]>, book id of 1 corresponds to two authors
    print(().name) # Get author-specific information by .first().name

    # 3. Query author Wang at ---- Positive one-on-one querying
    author_obj = (name='King').first()
    res = author_obj.author_detail
    print(res) # Returned is AuthorDetail object (1), which is an author detail object
    print() # . Field to find out details about the corresponding author
    """
    summarize：When querying in the forward direction，When your results may be multiple，Then you need to add.all(),If it's an object that gets the data directly
    """

    # 4. query publisher is East Wind Press book ---- reverse one-to-many query
    publish_obj = (name='East Wind').first()
    res = publish_obj.book_set.all()
    print(res)

    # 5. query for books written by author King ---- reverse many-to-many queries
    author_obj = (name='King').first()
    res = author_obj.book_set.all()
    print(res)

    # 6. Search for authors whose phone number is 122324233
    author_detail_obj = (phone=122324233).first()
    res = author_detail_obj.author
    print()
    """
    summarize：For the reverse query，If the result is more than one，Then you need to add_set.all();If it's one-on-one, it's one.，wouldn't have to！
    """

 from app01 import models

    # 1. query king this author's age and cell phone number - positive one-to-one query
    # First get the king of this object, then .values, inside the need to query the field name, forward then directly 'field name'; reverse then 'table name lowercase __ field name'
    res = (name='King').values('age','author_detail__phone')
    print(res) # What you get is a queryset object (a dictionary nested in a list)
    dict = ()  # The .first() method fetches this dictionary object
    print(dict['age']) # Dictionary way to get the specific value needed
    # Reverse queries
    res = (author__name='King').values('phone','author__age')
    print(res)

    # 2. Query the name of the publisher and the price of the book for which the book's primary key is 1 - a forward one-to-many query
    res = (pk=1).values('price','publish__name')
    print(res)
    # Reverse queries
    res = (book__id=1).values('name','book__price')
    print(res)

    # 3. Query the author's name and book title for books with primary key 1
    res = (pk=1).values('title','author__name')
    print(res)
    # Reverse queries
    res = (book__id=1).values('name','book__title')
    print(res)
	
    # 4. The ultimate trick: querying the phone number of the author of a book with a primary key of 1!!!! --- across the book, author, author_detail table query
    res =(pk=1).values('author__author_detail__phone')
    print(res)
    # Reverse queries
    res = (book__id=1).values('author_detail__phone')
    print(res)

from  import models

# Create your models here.
class MyBook():
    # 1. String field type CharField, must pass the field options are max_length=specify the maximum number of characters, verbose_name='' specify the field in the django backend management in the description of the name
    name = (max_length=32,verbose_name='Name')
	
    # 2. Numeric field type IntegerField
    age = ()
	
    # 3. DateTimeField field type DateTimeField
    register_time = (auto_now_add=True)
	
    # 4. Date field types
    register_time = (auto_now_add=True)
	
    # For both field types, there are two key parameters
    # auto_now: each time the data is manipulated, this field will automatically update the current time to the current time
    # auto_now_add: the current time is automatically recorded when the data is created, and remains unchanged as long as it is not considered to be modified in the future
	
    # 5. Mailbox field
    email = ()
	
    # 6. Large numeric field types
    phone = ()
	
    # 7. Decimal fields, there are two field options max_digits = 8, that is, even the decimal a total of 8 bits; decimal_places = 2, that is, the decimal part of 2 bits.
    price = (max_digits=8,decimal_places=2)
	
    # 8. Boolean field, the input parameter is False\True, in the database corresponds to 0\1
    boolean = (False)
	
    # 9. Text field type, no word limit, large text
    text = ()
	
    # 10. file field type, upload_to='' parameter: pass a file object to the field, it will automatically save the file in the /data directory, how to pass the path of the file to the database
    file = (upload_to='/data')
	
    Customized fields omitted

 # Pilot five aggregation functions
    """
    Tip: Any module related to the database is basically in there!
    If not, it's probably in there.
    """
    from  import Sum,Avg,Min,Max,Count
    # 1. find the average and total sum of prices and the maximum value inside the table of the book... On its own you need to use the .aggregate method
    res = (Avg('price'),Max('price'),Sum('price'))
    print(res)

# 1. Count the number of authors per book
    res = (author_num=Count('author')).values('author_num')
    print(res)
    """
    clarification：
    1.The keywords for grouping queries areannotate
    What's at the back?，It's a grouping by what?
    3.author_numIt's your own customized field，Used to store the number of copies of each book.
    innerauthorRefers to the number of statistical authors
    ('author_num')is the number of authors taken out of the count
    """

    # 2. Count the prices of the cheapest books sold by each publisher
    res = (book_price=Min('book__price')).values('name','book_price')
    print(res)
    print('=========')
    # 3. Counting books with more than one author
    # First grouped by book to find the number of authors corresponding to the book; then filter out the number of authors greater than 1 filter
    res = (author_num=Count('author')).filter(author_num__gt=1).values('title','author_num')
    print(res)

    # 4. Look up the total price of books produced by each author
    res = (book_price=Sum('book__price')).values('name','book_price')
    print(res)

    """
    So how do you group by fields?？
    ('price').annotate()  
    # If annotate is preceded by values, it will not be grouped by book but by values
    """

# F queries
    # 1. Search for books with more copies sold than in stock
    # F queries
    """
    Helps you to directly get the data corresponding to a field in a table
    """
    from  import F # Importing f-modules
    # res = (maichu__gt=F('kucun')) #f in brackets is the corresponding field name
    # print(res)


    # 2. Raise the price of all books by 500 bucks.
    # (price=F('price') + 500)


    # 3. Add the word pop to the end of all the books' names
    """
    When manipulating data of character type F cannot directly do string concatenation
    """
    from  import Concat
    from  import Value  # Pilot into Concat and Value modules
    (title=Concat(F('title'), Value('Explosive')))
    # (title=F('title') + 'Explosive')  # All names will be blank

# Q Query
    # 1. Search for books with a sell count greater than 100 or a price less than 600
    # res = (maichu__gt=100,price__lt=600)
    """Multiple parameters in filter brackets are AND relationships and do not yield results"""
    from  import Q # Import q module for or and not functions
    # res = (Q(maichu__gt=100),Q(price__lt=600)) # Q wrapped comma-separated or and relationship
    # res = (Q(maichu__gt=100)|Q(price__lt=600)) # | or relationship
    # res = (~Q(maichu__gt=100)|Q(price__lt=600)) # ~ not relation
    # print(res)  # <QuerySet []>

    # Advanced usage of Q to turn the left side of a query condition into a string as well
    q = Q()
     = 'or'
    (('maichu__gt',100))
    (('price__lt',600))
    res = (q)  # Default or and relationship
    print(res)