Table of Contents
1.ORM
1.1 table structure
2.1 Connection database configuration
1. Connect to the default sqlite3
2. Connect to mysql
3. Connect to oracle
4. Connect to postgresql
5. Database connection pool
6. Connect multiple databases
3. 1 Database advancement (separation of reading and writing, sub-tables and sub-databases)
1. Separation of reading and writing
2. Sub-table and sub-database (multiple apps)
3. Sub-library (single app) (the command is not supported by default, you need to use the router)
4.1 Common fields and parameters
1. Field:
2. Parameters:
3. Create table instance
5. Data operations
1. Increase (return an object)
2. Delete (return the number of affected columns)
3. Change (return the number of affected columns)
4. Check (return query_set object)
6. The data type returned by the query (obj, dict, toupl)
7. Magical double __ lines. Double __ lines (obj, dict, toupl) will be used when querying conditions across fields in tables.
8. Understand the concept of objects
2. Cookies
3. session
1. The session is saved in the file
2. Save in database
3. Save in cache
4. Session instance:
4. ORM offline script
1.admin
2.client
1.ORM
orm, relational object mapping, essential translation.
1, 1 table structure
Implementation: create tables, modify tables, delete tables.
1. Writing classes
from django.db import models
class UserInfo(models.Model):
name = models.CharField(max_length=16)
age = models.IntegerField()
2. Register app
INSTALLED_APPS = [
# 'django.contrib.admin',
# 'django.contrib.auth',
# 'django.contrib.contenttypes',
# 'django.contrib.sessions',
# 'django.contrib.messages',
'django.contrib.staticfiles',
'apps.app01.apps.App01Config',
'apps.app02.apps.App02Config',
]
3. Command, django generates a configuration file for database operations => migrations based on the classes in models
python manage.py makemigrations
4. Command, read the migrations directory in the registered app and convert the configuration file -> into: generate table, modify table SQL -> connect to the database to run.
python manage.py migrate
2.1 Connection database configuration
1. Connect by default sqlite3
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': BASE_DIR / 'db.sqlite3',
}
}
2. Connect to mysql
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'xxxxxxxx', # Database name
'USER': 'root',
'PASSWORD': 'root123',
'HOST': '127.0.0.1', # ip
'PORT': 3306,
}
}
Install third-party components:
pip install pymysql
Important: Replacement of database syntax
Project root directory/project name directory/__init__.py importpymysql pymysql.install_as_MySQLdb()
3. Connect to oracle
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.oracle',
'NAME': "xxxx", # library name
"USER": "xxxxx", # Username
"PASSWORD": "xxxxx", # Password
"HOST": "127.0.0.1", # ip
"PORT": 1521, # Port
}
}
# Requires pip install cx-Oracle
4. Connect to postgresql
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'mydatabase',
'USER': 'mydatabaseuser',
'PASSWORD': 'mypassword',
'HOST': '127.0.0.1',
'PORT': 5432,
}
}
# Requires pip install psycopg2
5. Database connection pool
DATABASES = {
"default": {
'ENGINE': 'dj_db_conn_pool.backends.mysql',
'NAME': 'day04', # Database name
'USER': 'root',
'PASSWORD': 'root123',
'HOST': '127.0.0.1', # ip
'PORT': 3306,
'POOL_OPTIONS': {
'POOL_SIZE': 10, # minimum
'MAX_OVERFLOW': 10, # On the basis of the minimum, 10 can be added, that is, the maximum is 20.
'RECYCLE': 24 * 60 * 60, # How long the connection can be reused. If it exceeds, it will be re-created. -1 means permanent.
'TIMEOUT':30, # The maximum waiting time for no connections in the pool.
}
}
}
#Install components: pip install django-db-connection-pool
6. Connect to multiple databases
You need to specify the database to connect to. The default connection operation is default. I added another database bak
DATABASES = {
"default": {
'ENGINE': 'dj_db_conn_pool.backends.mysql',
'NAME': 'day05db', # Database name
'USER': 'root',
'PASSWORD': 'root123',
'HOST': '127.0.0.1', # ip
'PORT': 3306,
'POOL_OPTIONS': {
'POOL_SIZE': 10, # minimum
'MAX_OVERFLOW': 10, # On the basis of the minimum, 10 can be added, that is, the maximum is 20.
'RECYCLE': 24 * 60 * 60, # How long the connection can be reused. If it exceeds, it will be re-created. -1 means permanent.
'TIMEOUT': 30, # The maximum waiting time for no connections in the pool.
}
},
"bak": {
'ENGINE': 'dj_db_conn_pool.backends.mysql',
'NAME': 'day05bak', # Database name
'USER': 'root',
'PASSWORD': 'root123',
'HOST': '127.0.0.1', # ip
'PORT': 3306,
'POOL_OPTIONS': {
'POOL_SIZE': 10, # minimum
'MAX_OVERFLOW': 10, # On the basis of the minimum, 10 can be added, that is, the maximum is 20.
'RECYCLE': 24 * 60 * 60, # How long the connection can be reused. If it exceeds, it will be re-created. -1 means permanent.
'TIMEOUT': 30, # The maximum waiting time for no connections in the pool.
}
},
}
3.1 Database advancement (read-write separation, separate tables and databases)
1. Separation of reading and writing
Two databases, one responsible for reading and one responsible for writing
1. Generate database tables
python manage.py makemigrations # Find the class reading in models.py in all registered apps -> migrations configuration python manage.py migrate python manage.py migrate --database=default (default) python manage.py migrate --database=bak
2. When developing later (the default database is operated by default, using can specify the database to be operated)
models.UserInfo.objects.using("default").create(title="Wu Peiqi")
models.UserInfo.objects.using("bak").all()
3. Write the router class to simplify [during subsequent development]
1. Now configure the configuration file for DATABASE_ROUTERS
DATABASE_ROUTERS = ["utils.router.DemoRouter"]
class DemoRouter(object):
def db_for_read(...):
return "bak"
def db_for_write(...):
return "default".
#Write in defaut library, read in bak
2. Sub-table and sub-database (multiple apps)
100 tables, 50 tables-A database [app02]; 50 tables-B database [app02]
1. app01/models
#app01/models
from django.db import models
class UserInfo(models.Model):
title = models.CharField(verbose_name="title", max_length=32)
2. app02/models
from django.db import models
class Role(models.Model):
title = models.CharField(verbose_name="title", max_length=32)
3. Execute the command
python manage.py makemigrations python manage.py migrate app01 --database=default python manage.py migrate app02 --database=bak
4. Now configure the configuration file for DATABASE_ROUTERS
DATABASE_ROUTERS = ["utils.router.DemoRouter"]
5. Write route class
class DemoRouter(object):
def db_for_read(self, model, **hints):
if model._meta.app_label = "app01"
return "default"
if model._meta.app_label = "app02"
return "bak"
def db_for_write(self, model, **hints):
if model._meta.app_label = "app01"
return "default"
if model._meta.app_label = "app02"
return "bak"
#Put the table structure of app01 in the default database and the table structure of app02 in the bak database
3. Sub-library (single app) (the command is not supported by default, you need to use a router)
1. Write route class
class DemoRouter(object):
def db_for_read(self, model, **hints):
if model._meta.model_name in ["role", 'depart']:
return "bak"
if model._meta.model_name in ["userinfo"]:
return "default"
def db_for_write(self, model, **hints):
if model._meta.model_name in ["role", 'depart']:
return "bak"
if model._meta.model_name in ["userinfo"]:
return "default"
def allow_migrate(self, db, app_label, model_name=None, **hints):
# python manage.py migrate app01 --database=default
# python manage.py migrate app01 --database=bak
# True ->Generate to database
# False -> Do not generate
if db == 'bak':
if model_name in ["role", 'depart']:
return True
else:
return False
if db == 'default':
if model_name in ['userinfo']:
return True
else:
return False
2. Command
python manage.py migrate python manage.py migrate --database=default (default) python manage.py migrate --database=bak
4.1 Common fields and parameters
1. Field:
CharField SmallIntegerField IntegerField BigIntegerField DateField DateTimeField BooleanField -> In fact, the database does not support true and false, it is created based on SmallIntegerField. 0 1 DecimalField -> Exact decimal
2. Parameter:
name = models.CharField(verbose_name="name", max_length=16)
name = models.CharField(verbose_name="name", max_length=16, default="hahaha")
# Frequent query, fast speed (MySQL, https://www.bilibili.com/video/BV15R4y1b7y9)
name = models.CharField(verbose_name="name", max_length=16, default="hahaha", null=True, blank=True, db_index=True)
email = models.CharField(verbose_name="name", max_length=16, default="hahaha", null=True, blank=True, unique=True)
# When stored in the database, it can only be: sh, bj (Shanghai and Beijing are generally used to display Chinese on the page)
code = models.CharField(verbose_name="name", max_length=16, choices=(("sh", "Shanghai"), ("bj", "Beijing")), default="sh")
count = models.IntegerField(verbose_name="quantity", default=1, null=True, blank=True, unique=True)
code = models.IntegerField(verbose_name="gender",choices=((1, "male"), (2, "female")),default=1)
Time date:
register_date = models.DateField(verbose_name="Registration Time", auto_now=True)
Amount: decimal:
amount = models.DecimalField(verbose_name="balance", max_digits=10, decimal_places=2)
3. Create table instance
from django.db import models
class UserInfo(models.Model):
name = models.CharField(verbose_name="name", max_length=16, db_index=True)
age = models.PositiveIntegerField(verbose_name="age")
email = models.CharField(verbose_name="Email", max_length=128, unique=True)
amount = models.DecimalField(verbose_name="balance", max_digits=10, decimal_places=2, default=0)
register_date = models.DateField(verbose_name="Registration Time", auto_now=True)
class Goods(models.Model):
title = models.CharField(verbose_name="title", max_length=32)
# detail = models.CharField(verbose_name="Details", max_length=255)
detail = models.TextField(verbose_name="Details")
price = models.PositiveIntegerField(verbose_name="price")
count = models.PositiveBigIntegerField(verbose_name="inventory", default=0)
5. Data operation
1. Increase (return an object)
class Role(models.Model):
title = models.CharField(verbose_name="title", max_length=32)
obj1 = models.Role.objects.create(title="Administrator", od=1)
obj2 = models.Role.objects.create(**{"title": "Administrator", "od": 1})
# memory -> save
obj = models.Role(title="Customer", od=1)
obj.od = 100
obj.save()
obj = models.Role(**{"title": "Administrator", "od": 1})
obj.od = 100
obj.save()
#Create data
2. Delete (return the number of affected columns)
# models.Role.objects.all().delete() models.Role.objects.filter(title="Administrator").delete()
3. Change (return the number of affected columns)
models.Role.objects.all().update(od=99)
models.Role.objects.filter(id=7).update(od=99, title="Administrator")
models.Role.objects.filter(id=7).update(**{"od": 99, "title": "administrator"})
4. Check (return query_set object)
# QuerySet = [obj, obj]
v1 = models.Role.objects.all()
for obj in v1:
print(obj, obj.id, obj.title, obj.od)
# QuerySet = []
# v2 = models.Role.objects.filter(od=99, id=99)
v2 = models.Role.objects.filter(**{"od": 99, "id": 99})
for obj in v2:
print(obj, obj.id, obj.title, obj.od)
v3 = models.Role.objects.filter(id=99)
print(v3.query)
v3 = models.Role.objects.filter(id__gt=2)#Greater than
print(v3.query)
v3 = models.Role.objects.filter(id__gte=2)#Greater than or equal to
print(v3.query)
v3 = models.Role.objects.filter(id__lt=2)#less than
print(v3.query)
v3 = models.Role.objects.filter(id__in=[11, 22, 33])#in
print(v3.query)
v3 = models.Role.objects.filter(title__contains="household")#contains
print(v3.query)
v3 = models.Role.objects.filter(title__startswith="household")#starts
print(v3.query)
v3 = models.Role.objects.filter(title__isnull=True)# is null
print(v3.query)
Condition not equal
v3 = models.Role.objects.filter(id=99)print(v3.query) # not equal to v3 = models.Role.objects.exclude(id=99).filter(od=88)print(v3.query)
Return the first object
v6 = models.Role.objects.filter(id__gt=0).first() # print(v6) # Object
Whether the object exists
v7 = models.Role.objects.filter(id__gt=10).exists() print(v7) # True/False
Sort
# asc
v8 = models.Role.objects.filter(id__gt=0).order_by("id")
# id desc od asc
v9 = models.Role.objects.filter(id__gt=0).order_by("-id", 'od')
6. Data type returned by query (obj, dict, toupl)
# queryset=[obj,obj], the value can be clicked,
v3 = models.Role.objects.filter(id=99)
# queryset=[{'id': 6, 'title': 'customer'}, {'id': 7, 'title': 'customer'}], take value dict method
v4 = models.Role.objects.filter(id__gt=0).values("id", 'title')
# QuerySet = [(6, 'Customer'), (7, 'Customer')]
v5 = models.Role.objects.filter(id__gt=0).values_list("id", 'title')
print(v5[0])
7. Magical double __ lines. Query conditions across tables Double __ lines (obj, dict, toupl) will be used for conditions
1. When there is only obj, when operating double underscores across tables, you need to select_related(“depart”) first, so that the performance will be faster and multiple queries will be avoided
2. For dict and toupl cross-table operations, double underscores are connected to the table first and then the data is returned, and the query is performed once
3. Double underline in cross-table operation. ForeignKey is used as the condition for joining tables in the forward direction, and the table name is used as the condition for joining tables in the reverse direction
8. Understand the concept of objects
For query data, you can use points to get the data after cross-table. When querying obj across tables, in order to avoid multiple queries, you can first select_related() join table operation
2.cookie
Return the set credentials to the browser for the first time (corresponding header),
def login(request):
res= HttpResponse("return")
res.set_cookie("Set cookie credentials")
return res
The second request header will carry cookie credentials and send data
def home(request):
print(request.COOKIES)
return HttpResponse("HOME")
You can check the cookie configuration
3.session
1. The session is saved in the file
MIDDLEWARE = [
'django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
# 'django.contrib.auth.middleware.AuthenticationMiddleware',
# 'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
]
# session
SESSION_ENGINE = 'django.contrib.sessions.backends.file'
SESSION_FILE_PATH = 'xxxx'
SESSION_COOKIE_NAME = "sid" # The key when the Session cookie is saved on the browser, that is: sessionid = random string
SESSION_COOKIE_PATH = "/" # The path where Session cookies are saved
SESSION_COOKIE_DOMAIN = None # Domain name saved by Session cookie
SESSION_COOKIE_SECURE = False # Whether HTTPS transmits cookies
SESSION_COOKIE_HTTPONLY = True # Whether the Session cookie only supports http transmission
SESSION_COOKIE_AGE = 1209600 # Session cookie expiration date (2 weeks)
SESSION_EXPIRE_AT_BROWSER_CLOSE = False # Whether to close the browser to cause the Session to expire
SESSION_SAVE_EVERY_REQUEST = True # Whether to save the Session for each request, the default is to save it after modification
2. Save in database
INSTALLED_APPS = [
# 'django.contrib.admin',
# 'django.contrib.auth',
# 'django.contrib.contenttypes',
'django.contrib.sessions',
# 'django.contrib.messages',
'django.contrib.staticfiles',
"app01.apps.App01Config",
]
MIDDLEWARE = [
'django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
# 'django.contrib.auth.middleware.AuthenticationMiddleware',
# 'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
]
# session
SESSION_ENGINE = 'django.contrib.sessions.backends.db'
SESSION_COOKIE_NAME = "sid" # The key when the Session cookie is saved on the browser, that is: sessionid = random string
SESSION_COOKIE_PATH = "/" # The path where Session cookies are saved
SESSION_COOKIE_DOMAIN = None # Domain name saved by Session cookie
SESSION_COOKIE_SECURE = False # Whether HTTPS transmits cookies
SESSION_COOKIE_HTTPONLY = True # Whether the Session cookie only supports http transmission
SESSION_COOKIE_AGE = 1209600 # Session cookie expiration date (2 weeks)
SESSION_EXPIRE_AT_BROWSER_CLOSE = False # Whether to close the browser to cause the Session to expire
SESSION_SAVE_EVERY_REQUEST = True # Whether to save the Session for each request, the default is to save it after modification
3. Save in cache
1. Install and connect the redis package
pip install django-redis
2. Configuration file configuration redis
CACHES = {
"default": {
"BACKEND": "django_redis.cache.RedisCache",
"LOCATION": "redis://127.0.0.1:6379",
"OPTIONS": {
"CLIENT_CLASS": "django_redis.client.DefaultClient",
"CONNECTION_POOL_KWARGS": {"max_connections": 100}
# "PASSWORD": "Password",
}
}
}
INSTALLED_APPS = [
# 'django.contrib.admin',
# 'django.contrib.auth',
# 'django.contrib.contenttypes',
# 'django.contrib.sessions',
# 'django.contrib.messages',
'django.contrib.staticfiles',
"app01.apps.App01Config",
]
MIDDLEWARE = [
'django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
# 'django.contrib.auth.middleware.AuthenticationMiddleware',
# 'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware',
]
# session
SESSION_ENGINE = 'django.contrib.sessions.backends.cache'
SESSION_CACHE_ALIAS = 'default'
SESSION_COOKIE_NAME = "sid" # The key when the Session cookie is saved on the browser, that is: sessionid = random string
SESSION_COOKIE_PATH = "/" # The path where Session cookies are saved
SESSION_COOKIE_DOMAIN = None # Domain name saved by Session cookie
SESSION_COOKIE_SECURE = False # Whether HTTPS transmits cookies
SESSION_COOKIE_HTTPONLY = True # Whether the Session cookie only supports http transmission
SESSION_COOKIE_AGE = 1209600 # Session cookie expiration date (2 weeks)
SESSION_EXPIRE_AT_BROWSER_CLOSE = False # Whether to close the browser to cause the Session to expire
SESSION_SAVE_EVERY_REQUEST = True # Whether to save the Session for each request, the default is to save it after modification
3. Manually operate redis
from django_redis import get_redis_connection
conn = get_redis_connection("default")
conn.set("xx","123123")
conn.get("xx")
4. Session instance:
def login(request):
# Set the value in the session + write the credentials in the cookie
request.session['user_info'] = "Wu Peiqi"
return HttpResponse("Login")
def home(request):
user_info = request.session.get("user_info")
print(user_info)
return HttpResponse("HOME")
4. ORM offline script
1, admin
# Start django
import os
importsys
import django
base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(base_dir)
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'day06.settings')
django.setup() # Fake django to start
from web import models
from utils.encrypt import md5
models.Administrator.objects.create(username='wp', password=md5("wp"), mobile="1888888889")
2.client
# Start django
import os
importsys
import django
base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(base_dir)
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'day06.settings')
django.setup() # Fake django to start
from web import models
from utils.encrypt import md5
#Create level
# level_object = models.Level.objects.create(title="VIP", percent=90)
# models.Customer.objects.create(
# username='xinchen',
# password=md5("xinchen"),
# mobile='1999999998',
# level_id=1,
# creator_id=1
# )
for i in range(303, 602):
models.Customer.objects.create(
username='xinchen-{}'.format(i),
password=md5("xinchen-{}".format(i)),
mobile='1999999998',
level_id=4,
creator_id=1
)