数据库配置

1. 创建数据库

2. 配置 settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'XXX',
        'USER': 'XXX',
        'PASSWORD': 'xxx',
        'HOST': 'localhost',
        'PORT': 3306,
    }
}

3. 修改 __init__.py，更改 mysqldb 为 pymysql

import pymysql
pymysql.install_as_MySQLdb()

操作表

创建表、修改表、删除表

>python manage.py makemigrations
>python manage.py migrate

操作行

增加

models.UserGroup.objects.create(title='销售部')
models.UserInfo.objects.create(username='root', password='123', usergroup_id=1)

删除

usergroups = models.UserGroup.objects.filter(id=1).delete()

改变

usergroups = models.UserGroup.objects.filter(id=1).update(title='公关部')

from django.db.models import F
models.UserInfo.objects.all().update(age=F('age')+1)
# update userinfo set age = age + 1;

查找

一般查找

userlist = models.UserGroup.objects.all()#.first()
for row in userlist:
    print(row.id, row.title)

userlist = models.UserGroup.objects.filter(id=1, title='aaa') # and
userlist = models.UserGroup.objects.exclude(id=1) # !=
userlist = models.UserGroup.objects.filter(id__gt=1) # >
userlist = models.UserGroup.objects.filter(id__lt=1) # <

进阶查找

# 连表查找
# usertype是userinfo的fk字段
obj = models.UserInfo.objects.all().first()
print(obj.name, obj.age, obj.usertype_id, obj.usertype.title) # (正向)连表查找

obj = models.UserType.objects.all().first()
print(obj.id, obj.title, obj.userinfo_set.all()) # (反向)
print(obj.id, obj.title, obj.userinfo_set.filter(name='xx')) # 过滤

# 获取的类型
models.UserInfo.objects.all() # 返回UserInfo类型的QuerySet数组
models.UserInfo.objects.all().values('id', 'name') # 返回字典类型的QuerySet数组
models.UserInfo.objects.all().values_list('id', 'name') # 返回元组类型的QuerySet数组

# 排序 Order By
models.UserInfo.objects.all().order_by('id') # 升序
models.UserInfo.objects.all().order_by('-id') # 降序
models.UserInfo.objects.all().order_by('id', 'name') # 多列排序

# 分组 Group By ... Having ...
from django.db.models import Count, Sum, Max, Min, Avg
v = models.UserInfo.objects.filter(id__gt=2).values('usertype_id').annotate(xxxx=Count('id')).filter(xxxx__gt=2) # 注意前后filter表示的含义不同
print(v.query) # 查看生成的SQL语句
# select usertype_id, count(id) as xxxx from userinfo where id > 2 group by usertype_id having count(id) > 2;

# in; between ... and ...; gt lt gte lte ...;
models.UserInfo.objects.filter(id__in=[1,2,3]) # in
models.UserInfo.objects.filter(id__range=[1,3]) # between...and...
models.UserInfo.objects.filter(id__gt=1) # >
models.UserInfo.objects.filter(id__lt=1) # <
models.UserInfo.objects.filter(id__gte=1) # >=
models.UserInfo.objects.filter(id__lte=1) # <=
# not ...
models.UserInfo.objects.exclude(id__in=[1,2,3]) # in
models.UserInfo.objects.exclude(id__range=[1,3]) # between...and...
models.UserInfo.objects.exclude(id__gt=1) # >
models.UserInfo.objects.exclude(id__lt=1) # <
models.UserInfo.objects.exclude(id__gte=1) # >=
models.UserInfo.objects.exclude(id__lte=1) # <=

# like %
models.UserInfo.objects.filter(name__startswith='a') # like '%a'
models.UserInfo.objects.filter(name__endswith='a') # like 'a%'
models.UserInfo.objects.filter(name__contains='a') # like '%a%'
# not like %
models.UserInfo.objects.exclude(name__startswith='a') # not like '%a'
models.UserInfo.objects.exclude(name__endswith='a') # not like 'a%'
models.UserInfo.objects.exclude(name__contains='a') # not like '%a%'

# and
condition = {'id': 1, 'name': 'root'}
models.UserInfo.objects.filter(**condition) # 不推荐

# and or混合，对象方式 (不推荐)
from django.db.models import Q
models.UserInfo.objects.filter(Q(id=1) | Q(id=2)) # or
models.UserInfo.objects.filter(Q(id=1) & Q(id=2)) # and

# and or混合，方法方式 (推荐)
q1 = Q()
q1.connector = 'OR'
q1.children.append(('id', 1))
q1.children.append(('id', 10))
q1.children.append(('id', 9))

q2 = Q()
q2.connector = 'OR'
q2.children.append(('c1', 1))
q2.children.append(('c1', 10))
q2.children.append(('c1', 9))

con = Q()
con.add(q1, 'AND')
con.add(q2, 'AND')
models.UserInfo.objects.filter(con)

# select id, name, (select ...) as n from xb;
models.UserInfo.objects.all().extra(
    select={
        'n': 'select ... where id > %s and id < %s',
        'm': 'select ... where id = %s and id = %s',
    },
    select_params=[1, 3, 4, 5])

# where (select ... )
models.UserInfo.objects.all().extra(
    where=["id=%s or id=2", "name=%s"], # and
    params=[1, 'alex'])

# select * from app01_userinfo, app01_usertype;
models.UserInfo.objects.all().extra(
    tables=['app01_usertype'],
    where=['app01_usertype.id = app01_userinfo.user']
)

分页

obj = models.UserInfo.objects.all()[0:10]