Django ORM

# Django uses SQLite3 by default.
# In settings.py, find the database configuration:
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

# To switch to MySQL, configure as follows:
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'django_base',
        'USER': 'root',
        'PASSWORD': 'your_password',
        'HOST': '118.25.65.95',
        'PORT': '3306',
        'CHARSET': 'UTF8'
    }
}
# After configuring, you must also declare that Django should use pymysql instead
# of the default mysqldb module (which has compatibility issues).
# Add the following to the project's __init__.py or any app's __init__.py:
import pymysql
pymysql.install_as_MySQLdb()

# Configuring a separate database per app
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'bms',          # Database to connect to (must exist beforehand)
        'USER': 'root',         # Database username
        'PASSWORD': '',         # Database password
        'HOST': '127.0.0.1',   # Host, defaults to localhost
        'PORT': 3306            # Port, defaults to 3306
    },
    'app01': {  # Each app can have its own database; it does not have to be MySQL
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'bms',
        'USER': 'root',
        'PASSWORD': '',
        'HOST': '127.0.0.1',
        'PORT': 3306
    }
}

# Purpose:
#   Lets developers with no SQL knowledge operate databases easily
#   through Python object-oriented code.
# Drawbacks:
#   High level of encapsulation; generated SQL can sometimes be inefficient.
#   You may need to write raw SQL for performance-critical queries.

# Comparison with MySQL:
#   Class         <->  Table
#   Object        <->  Row (record)
#   Object field  <->  Value of a column in that row

# How to create a table:
# 1. Define a class in models.py
class User(models.Model):
    id = models.AutoField(primary_key=True)
    username = models.CharField(max_length=32)
    password = models.IntegerField()

# 2. Sync to MySQL
#   python3 manage.py makemigrations
#       Records operations to the migrations folder (converts ORM to SQL).
#   python3 manage.py migrate
#       Applies changes to the actual database.
# Note: Whenever you modify database-related code in models.py,
# you must re-run both commands.

# __init__.py
import pymysql
pymysql.install_as_MySQLdb()

# settings.py
'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'django',
    'USER': 'root',
    'PASSWORD': 'your_password',
    'HOST': '106.14.213.94',
    'PORT': '3306',
    'CHARSET': 'UTF-8'
}

# models.py
from django.db import models
class User(models.Model):
    uid = models.AutoField(primary_key=True, verbose_name='Primary Key')
    username = models.CharField(max_length=32, verbose_name='Name')
    password = models.IntegerField(verbose_name='Password')

# Field type mapping (ORM field -> SQL type):
'AutoField': 'integer AUTO_INCREMENT',
'BigAutoField': 'bigint AUTO_INCREMENT',
'BinaryField': 'longblob',
'BooleanField': 'bool',
'CharField': 'varchar(%(max_length)s)',
'CommaSeparatedIntegerField': 'varchar(%(max_length)s)',
'DateField': 'date',
'DateTimeField': 'datetime',
'DecimalField': 'numeric(%(max_digits)s, %(decimal_places)s)',
'DurationField': 'bigint',
'FileField': 'varchar(%(max_length)s)',
'FilePathField': 'varchar(%(max_length)s)',
'FloatField': 'double precision',
'IntegerField': 'integer',
'BigIntegerField': 'bigint',
'IPAddressField': 'char(15)',
'GenericIPAddressField': 'char(39)',
'NullBooleanField': 'bool',
'OneToOneField': 'integer',
'PositiveIntegerField': 'integer UNSIGNED',
'PositiveSmallIntegerField': 'smallint UNSIGNED',
'SlugField': 'varchar(%(max_length)s)',
'SmallIntegerField': 'smallint',
'TextField': 'longtext',
'TimeField': 'time',
'UUIDField': 'char(32)',

# 1. Auto-increment fields:
id = models.AutoField(primary_key=True)  # Added automatically; no need to define explicitly
id = models.BigAutoField()  # Supports very large numbers (e.g., billions)

# 2. Binary field:
Binary = models.BinaryField()  # Used for binary data in special scenarios

# 3. Boolean fields:
Boolean = models.BooleanField()       # Cannot be null
NullBoolean = models.NullBooleanField()  # Can be null

# 4. Integer fields:
PositiveSmallInteger = models.PositiveSmallIntegerField()  # 5 bytes, positive only
SmallInteger = models.SmallIntegerField()                  # 6 bytes, positive or negative
PositiveInteger = models.PositiveIntegerField()            # 10 bytes, positive only
Integer = models.IntegerField()                            # 11 bytes
BigInteger = models.BigIntegerField()                      # 20 bytes

# 5. String types:
Char = models.CharField()            # maps to varchar; max_length is required
Char = models.CharField(max_length=14, verbose_name='Username')
Text = models.TextField()            # maps to longtext; no length limit

# 6. Time types:
Date = models.DateField()            # Year, month, day
DateTime = models.DateTimeField()    # Year, month, day, hour, minute, second
Duration = models.DurationField()    # Stored as int; implemented via Python timedelta

# 7. Floating-point types:
Float = models.FloatField()
Decimal = models.DecimalField()      # e.g., 11.22, 16.34; requires max_digits and decimal_places

# 8. Other types:
Email = models.EmailField()
Image = models.ImageField()
File = models.FileField()
FilePath = models.FilePathField()
URL = models.URLField()
UUID = models.UUIDField()
GenericIPAddress = models.GenericIPAddressField()  # IPv4 or IPv6

# 9. Relational fields:

# OneToOneField: one-to-one
#   Can be placed on either side; recommended on the more frequently queried table.
#   to="AuthorDetail"   related table
#   to_field="nid"      which field to relate to
#   on_delete=models.CASCADE  cascade delete

# ForeignKey: many-to-one
#   Place the foreign key on the "many" side.
#   to          related table
#   to_field    related field
#   related_name  name for reverse lookup, replaces default 'tablename_set'
#   related_query_name  prefix for reverse query
#   on_delete   behavior when the related object is deleted

# ManyToManyField: many-to-many
#   Three ways to create:

#   Method 1: Manually create the intermediate table
class Book(models.Model):
    title = models.CharField(max_length=32, verbose_name="Title")

class Author(models.Model):
    name = models.CharField(max_length=32, verbose_name="Author Name")

class Author2Book(models.Model):
    author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")

    class Meta:
        unique_together = ("author", "book")

#   Method 2: Let ORM auto-create the intermediate table via ManyToManyField
class Book(models.Model):
    title = models.CharField(max_length=32, verbose_name="Title")

class Author(models.Model):
    name = models.CharField(max_length=32, verbose_name="Author Name")
    books = models.ManyToManyField(to="Book", related_name="authors")
    # The auto-generated third table cannot have extra fields

#   Method 3: Use ManyToManyField with a manually defined intermediate model
class Book(models.Model):
    title = models.CharField(max_length=32, verbose_name="Title")

class Author(models.Model):
    name = models.CharField(max_length=32, verbose_name="Author Name")
    books = models.ManyToManyField(
        to="Book",
        through="Author2Book",
        through_fields=("author", "book")
        # through_fields takes a 2-tuple ('field1', 'field2'):
        # field1 is the FK on this model (author), field2 is the FK to the target (book)
    )

class Author2Book(models.Model):
    author = models.ForeignKey(to="Author")
    book = models.ForeignKey(to="Book")
    # Extra fields can be added here
    class Meta:
        unique_together = ("author", "book")

# Method 3 lets you still use many-to-many interface (all, add, clear, etc.)
# Method 1 prevents you from using ORM's set/add/remove/clear helpers.

# Field parameters

# 1. Parameters available on all fields:
#   db_column=" "         rename the column in the database
#   primary_key=True      set as primary key (default False)
#   verbose_name=" "      human-readable label
#   unique=True           each value in this column must be unique
#   null=True             allow NULL in the database
#   blank=True            allow empty value in form validation
#   db_index=True         create a database index
#   help_text=" "         help text shown in forms
#   editable=False        field is not editable (default True)
#   default               default value (can be a callable)
#   choices               iterable of 2-tuples for select input
#   auto_now_add=True     set to current time when the record is created
#   auto_now=True         update to current time every time the record is saved

# 2. Parameters specific to certain fields:
#   CharField(max_length=100)
#   DateField(unique_for_date=True)
#   DateField(auto_now=True)
#   DateField(auto_now_add=True)
#   DecimalField(max_digits=4, decimal_places=2)

# 3. Parameters for relational fields:
#   on_delete behavior options:
#   CASCADE      delete related objects when the referenced object is deleted (Django default)
#   PROTECT      raise ProtectedError to prevent deletion
#   SET_NULL     set the FK to null (requires null=True)
#   SET_DEFAULT  set the FK to its default value
#   DO_NOTHING   do nothing
#   SET()        set to a given value or callable result

# 4. Self-referential field:
#   Use 'self' as the first argument, or the model class name.

# (1) null
# If True, Django stores NULL in the database. Default is False.

# (1) blank
# If True, the field is allowed to be empty in form validation. Default is False.
# Note: null is database-level; blank is form-validation-level.

# (2) default
# Default value for the field. Can be a value or a callable.
# If the field is not nullable and you add it later, a default is required.

# (3) primary_key
# If True, this field is the model's primary key.
# Django automatically adds an id IntegerField if none is specified.

# (4) unique
# If True, this field's value must be unique across the table.

# (5) choices
# An iterable of 2-tuples providing choices for a select widget.

# (6) db_index
# If True, a database index is created for this field.

# DateTimeField, DateField, and TimeField all support:
# (7) auto_now_add
#   Sets the field to the current time when the record is first created.
# (8) auto_now
#   Updates the field to the current time every time the record is saved.

# (9) choices example
sex = models.IntegerField(choices=((1, 'Male'), (2, 'Female')))
# The database stores 1 or 2.
# Use model_instance.get_fieldname_display() to get the human-readable value.

# auto_now_add and auto_now example
class t1(models.Model):
    name = models.CharField(max_length=12, default='John')
    sex = models.IntegerField(choices=((1, 'Male'), (2, 'Female')))
    d1 = models.DateTimeField(auto_now_add=True, null=True)  # auto-set on create
    d2 = models.DateTimeField(auto_now=True, null=True)      # auto-update on save

# auto_now only works with .save(); it does NOT trigger on .update()
# For update-time tracking, manually set the current time and update.

redirect() can take a URL directly or an alias (name). If the alias requires
extra parameters, use reverse() to resolve it.

# Adding a field:
#   Simply add it in models.py

# Modifying a field:
#   Modify the code, then run the two migration commands.

# Deleting a field:
#   Comment out the field, then run the two migration commands.
#   Note: the data in that column will also be deleted.
#   Always review your code carefully before running migrations.

# When adding a field without a default value, Django raises an error:
# "You are trying to add a non-nullable field 'hobby' to user without a default..."
# Three solutions:
# 1. Provide a one-off default in the terminal prompt.
# 2. Allow the field to be null:
info = models.CharField(max_length=32, verbose_name='Bio', null=True)
# 3. Specify a default value:
hobby = models.CharField(max_length=32, verbose_name='Hobby', default='study')

# Method 1:
def index(request):
    obj = models.Book(
        bid='1',
        title='Romance of the Three Kingdoms',
        price=9.99,
        pub_date=datetime.datetime.now(),
        publish='Honglangman Press',
    )
    obj.save()

# Method 2:
models.Book.objects.create(
    bid='2',
    title='Dream of the Red Chamber',
    price=19.99,
    pub_date=datetime.datetime.now(),
    publish='Xinhua Press',
)

# Bulk insert:
obj_list = []
for i in range(3, 10):
    obj = models.Book(
        bid=i,
        title='Journey to the West ' + str(i),
        price=99.99,
        pub_date=datetime.datetime.now(),
        publish='Sunset Press',
    )
    obj_list.append(obj)
models.Book.objects.bulk_create(obj_list)

# Method 1: Bulk update — only modifies the specified fields
models.Book.objects.filter(bid=4).update(
    title='Water Margin',
    price=59.99
)

# Method 2: Update via object
obj = models.Book.objects.get(bid=6)
obj.price = 18
obj.save()

# Method 3: Full update — rewrites all fields regardless of whether they changed
edit_obj.username = username
edit_obj.password = password
edit_obj.save()

# Method 1: Bulk delete
models.Book.objects.filter(title='Romance of the Three Kingdoms').delete()

# Method 2: Delete a single object
models.Book.objects.get(id=3).delete()
# Both QuerySet objects and model instances can call .delete()

# In production, data is often not physically deleted.
# Instead, a status/flag field is used to mark records as deleted.

# <1> all(): returns all results as a QuerySet
# <2> filter(**kwargs): returns QuerySet matching conditions; multiple conditions are AND-ed
#   Book.objects.filter(title='linux', price=100)

# <3> get(**kwargs): returns a single matching object (not a QuerySet)
#   Raises an error if zero or more than one object is found.
#   Book.objects.get(id=1)

# <4> exclude(**kwargs): returns objects NOT matching conditions
#   Book.objects.exclude(id=6)

# <5> order_by(*field): sorts the QuerySet
#   models.Book.objects.all().order_by('price', 'id')
#   Prefix with '-' for descending: order_by('-price')

# <6> reverse(): reverses the order of a sorted QuerySet

# <7> count(): returns the number of matching objects

# <8> first(): returns the first record as a model object

# <9> last(): returns the last record as a model object

# <10> exists(): returns True if the QuerySet contains any data
#   all_books = models.Book.objects.all().exists()

# <11> values(*field): returns a QuerySet of dicts instead of model instances
# <12> values_list(*field): similar to values() but returns tuples
# <13> distinct(): removes duplicate rows from values()/values_list() results

# Double-underscore lookups
# res = models.User.objects.filter(age__gt=35)    # age > 35
# res = models.User.objects.filter(age__lt=35)    # age < 35
# res = models.User.objects.filter(age__gte=32)   # age >= 32
# res = models.User.objects.filter(age__lte=32)   # age <= 32

# age in [18, 32, 40]
# res = models.User.objects.filter(age__in=[18, 32, 40])

# age between 18 and 40 (inclusive)
# res = models.User.objects.filter(age__range=[18, 40])

# name contains 's' (case-sensitive)
# res = models.User.objects.filter(name__contains='s')

# name contains 'p' (case-insensitive)
# res = models.User.objects.filter(name__icontains='p')

# starts with / ends with
# res = models.User.objects.filter(name__startswith='j')
# res1 = models.User.objects.filter(name__endswith='j')

# filter by date parts
# res = models.User.objects.filter(register_time__month='1')
# res = models.User.objects.filter(register_time__year='2020')

# Table relationships:
#   One-to-many
#   Many-to-many
#   One-to-one
#   No relationship
#   Use "swap perspective" to determine the relationship.

# Book <-> Publisher: one-to-many; FK goes on the "many" side (Book)
# Book <-> Author: many-to-many; requires a third table
# Author <-> AuthorDetail: one-to-one

# In Django 1.X, FKs cascade by default.
# Many-to-many has several creation methods; see the field section above.

# (See the data CRUD sections above for examples.)

from django.db import models

# Author table (frequently accessed fields)
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    # One-to-one with AuthorDetail; can be on either side
    ad = models.OneToOneField(to="AuthorDetail", to_field='id', on_delete=models.CASCADE)
    # foreign key + unique

# Author detail table (less frequently accessed fields)
class AuthorDetail(models.Model):
    birthday = models.DateField()
    telephone = models.CharField(max_length=11)
    addr = models.CharField(max_length=64)

# Publisher table
class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)

# Book table
class Book(models.Model):
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    publish = models.ForeignKey(to="Publish")  # cascade delete by default, relates to PK
    authors = models.ManyToManyField(to='Author')  # auto-creates third table

models.AuthorDetail.objects.create(
    birthday='2018-01-01',
    telephone='13800000000',
    addr='Beijing'
)
ad_obj = models.AuthorDetail.objects.get(id=1)
models.Author.objects.create(
    name='Minghao',
    age=38,
    ad_id=2,
)
ad_obj = models.AuthorDetail.objects.get(id=4)
obj = models.Author(
    name='Yanghao',
    age=47,
    ad=ad_obj,
)
obj.save()

# One-to-many FK operations

# Create
# models.Book.objects.create(title='Analects', price=899.23, publish_id=1)
# models.Book.objects.create(title='Strange Tales', price=444.23, publish_id=2)
# models.Book.objects.create(title='Tao Te Ching', price=333.66, publish_id=1)

# Using the virtual field (object reference):
# publish_obj = models.Publish.objects.filter(pk=2).first()
# models.Book.objects.create(title='Dream of the Red Chamber', price=666.23, publish=publish_obj)

# Delete (cascade delete)
# models.Publish.objects.filter(pk=1).delete()

# Update
# models.Book.objects.filter(pk=1).update(publish_id=2)
# publish_obj = models.Publish.objects.filter(pk=1).first()
# models.Book.objects.filter(pk=1).update(publish=publish_obj)

# Adding authors to a book
book_obj = models.Book.objects.filter(pk=1).first()
# book_obj.authors is like accessing the third relationship table

# book_obj.authors.add(1)        # bind author with pk=1 to this book
# book_obj.authors.add(2, 3)     # accepts multiple integers or objects
# book_obj.authors.add(author_obj)

"""
add() inserts rows into the third table.
Accepts integers (PKs) or model objects; supports multiple values.
"""

# Delete
# book_obj.authors.remove(2)
# book_obj.authors.remove(1, 3)
# book_obj.authors.remove(author_obj, author_obj1)
"""
remove() deletes rows from the third table.
Accepts integers or model objects; supports multiple values.
"""

# Update
# book_obj.authors.set([1, 2])   # must pass an iterable
# book_obj.authors.set([author_obj, author_obj1])
"""
set() replaces all existing relationships.
Must receive an iterable of integers or model objects.
"""

# Clear all author relationships for this book
book_obj.authors.clear()
"""
clear() takes no arguments and removes all entries for this object in the third table.
"""

def ab_pl(request):
    book_queryset = models.Book.objects.all()

    # Bulk insert example:
    # book_list = []
    # for i in range(100000):
    #     book_obj = models.Book(title='Book No.%s' % i)
    #     book_list.append(book_obj)
    # models.Book.objects.bulk_create(book_list)
    """
    Use bulk_create() when inserting large amounts of data —
    it significantly reduces the total time compared to individual creates.
    """
    return render(request, 'ab_pl.html', locals())

# One-to-one and one-to-many behave like single-table delete (cascade delete)
models.Author.objects.get(id=1).delete()
models.AuthorDetail.objects.get(id=2).delete()
models.Book.objects.get(id=1).delete()

"""
When you only want to test a single Django .py file, you can write
a test script instead of going through the full request/response cycle.

The script can go in the app's tests.py or any standalone .py file.
"""
# Set up the test environment — copy the first 4 lines from manage.py
import os
import sys

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day64.settings")
    import django
    django.setup()
    # Test individual Django modules below this line

# Method 1: Only works on QuerySet objects
res = models.User.objects.values_list('name', 'age')
print(res.query)  # prints the underlying SQL

# Method 2: Log all SQL statements via settings.py
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level': 'DEBUG',
        },
    }
}

# Forward vs. reverse query concept:
#   If the FK field is on your model, querying the related model is "forward".
#   If the FK field is NOT on your model, querying is "reverse".

#   book ---FK on Book (forward)---> publish
#   publish ---FK on Book (reverse)---> book

#   The same logic applies to one-to-one and many-to-many.

"""
Forward query: use the field name
Reverse query: use the lowercase table name
    + _set  (for one-to-many and many-to-many)
"""
db_constraint = False

# Sub-query style (object-based cross-table queries)

# 1. Get the publisher of book with pk=1
# book_obj = models.Book.objects.filter(pk=1).first()
# res = book_obj.publish       # forward query
# print(res.name)

# 2. Get authors of book with pk=2
# book_obj = models.Book.objects.filter(pk=2).first()
# res = book_obj.authors.all()  # forward query; use .all() when result can be multiple

# 3. Get phone number of author 'jason'
# author_obj = models.Author.objects.filter(name='jason').first()
# res = author_obj.author_detail  # forward query
# print(res.phone)

"""
Write ORM queries incrementally, just like writing SQL step by step.

When to add .all() in forward queries:
    If the result can be multiple objects, add .all().
    If the result is a single object, access it directly:
        book_obj.publish
        book_obj.authors.all()
        author_obj.author_detail
"""

# 4. Get all books from publisher 'Oriental Press'
# publish_obj = models.Publish.objects.filter(name='Oriental Press').first()
# res = publish_obj.book_set.all()   # reverse query

# 5. Get all books written by author 'jason'
# author_obj = models.Author.objects.filter(name='jason').first()
# res = author_obj.book_set.all()    # reverse query

# 6. Get author name by phone number 110
# author_detail_obj = models.AuthorDetail.objects.filter(phone=110).first()
# res = author_detail_obj.author    # one-to-one reverse: no _set needed
# print(res.name)

"""
Reverse queries:
    If the result can be multiple, use _set.all()
    If the result is a single object, access directly (no _set)
"""

# JOIN style (double-underscore cross-table queries)

# 1. Get jason's phone number and name
# res = models.Author.objects.filter(name='jason').values('author_detail__phone', 'name')
# Reverse:
# res = models.AuthorDetail.objects.filter(author__name='jason').values('phone', 'author__name')

# 2. Get the publisher name and book title for book pk=1
# res = models.Book.objects.filter(pk=1).values('title', 'publish__name')
# Reverse:
# res = models.Publish.objects.filter(book__id=1).values('name', 'book__title')

# 3. Get author names for book pk=1
# res = models.Book.objects.filter(pk=1).values('authors__name')
# Reverse:
# res = models.Author.objects.filter(book__id=1).values('name')

# Get phone number of author(s) of book pk=1 (crosses 3 tables)
# res = models.Book.objects.filter(pk=1).values('authors__author_detail__phone')

"""
Once you understand forward/reverse and double-underscore syntax,
you can traverse any number of tables without restriction.
"""

# Aggregate queries use aggregate()
"""
Aggregate queries are typically used together with grouping.
Most database-related functions are in django.db.models;
if not found there, check django.db.
"""
from app01 import models
from django.db.models import Max, Min, Sum, Count, Avg

# Average price of all books:
# res = models.Book.objects.aggregate(Avg('price'))

# Multiple aggregations at once:
res = models.Book.objects.aggregate(Max('price'), Min('price'), Sum('price'), Count('pk'), Avg('price'))
print(res)

# Group queries use annotate()
"""
MySQL grouping behavior:
    After grouping, only the grouping key is directly accessible.
    Strict mode: ONLY_FULL_GROUP_BY
"""
from django.db.models import Max, Min, Sum, Count, Avg

# 1. Count authors per book
# res = models.Book.objects.annotate(author_num=Count('authors')).values('title', 'author_num')
"""
author_num is a custom alias for the aggregated count.
"""

# 2. Cheapest book price per publisher
# res = models.Publish.objects.annotate(min_price=Min('book__price')).values('name', 'min_price')

# 3. Books with more than one author
# res = models.Book.objects.annotate(author_num=Count('authors')).filter(author_num__gt=1).values('title', 'author_num')

# 4. Total book price per author
# res = models.Author.objects.annotate(sum_price=Sum('book__price')).values('name', 'sum_price')

"""
To group by a specific field:
    models.Book.objects.values('price').annotate()

If grouping queries throw errors, you may need to disable strict SQL mode.
"""

# F queries
"""
F() lets you reference the value of another column in the same table.
"""
from django.db.models import F

# 1. Books where sales > stock
# res = models.Book.objects.filter(maichu__gt=F('kucun'))

# 2. Increase all book prices by 500
# models.Book.objects.update(price=F('price') + 500)

# 3. Append "Hot" to all book titles
"""
F() cannot directly concatenate strings; use Concat + Value instead.
"""
from django.db.models.functions import Concat
from django.db.models import Value
models.Book.objects.update(title=Concat(F('title'), Value('Hot')))
# models.Book.objects.update(title=F('title') + 'Hot')  # This sets all titles to blank

# Q queries
"""
filter() with multiple keyword arguments uses AND.
Q() allows OR and NOT logic.
"""
from django.db.models import Q

# 1. Books with sales > 100 OR price < 600
# res = models.Book.objects.filter(Q(maichu__gt=100) | Q(price__lt=600))  # | = OR
# res = models.Book.objects.filter(~Q(maichu__gt=100) | Q(price__lt=600)) # ~ = NOT

# Advanced Q usage: build conditions programmatically
q = Q()
q.connector = 'or'
q.children.append(('maichu__gt', 100))
q.children.append(('price__lt', 600))
res = models.Book.objects.filter(q)
print(res)

"""
Transactions — ACID properties:
    Atomicity: indivisible smallest unit of work
    Consistency: always moves from one valid state to another
    Isolation: transactions do not interfere with each other
    Durability: committed transactions persist permanently

    rollback: undo uncommitted changes
    commit:   persist changes
"""
# Basic transaction usage in Django:
from django.db import transaction
try:
    with transaction.atomic():
        # sql1
        # sql2
        ...
        # All ORM operations inside the with block belong to the same transaction
except Exception as e:
    print(e)
print('Other operations continue here')