django-modles操作（ORM）
配置数据库
1.在Mysql中创建数据库
2.在settings中配置DATABASE
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME':'s4day70db',
'USER': 'root',
'PASSWORD': '',
'HOST': 'localhost',
'PORT': 3306,
}
}
3.在与project同名的init下修改django中默认连接数据库的方式
import pymysql
pymysql.install_as_MySQLdb()
4.在settings.py中注册app01
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'app01',
]
5.运行命令创建数据表
python manage.py makemigrations
python manage.py migrate
orm基本操作
# 增
#
# models.Tb1.objects.create(c1='xx', c2='oo') 增加一条数据，可以接受字典类型数据 **kwargs
# obj = models.Tb1(c1='xx', c2='oo')
# obj.save()
# 查
#
# models.Tb1.objects.get(id=123) # 获取单条数据，不存在则报错（不建议）
# models.Tb1.objects.all() # 获取全部
# models.Tb1.objects.filter(name='seven') # 获取指定条件的数据
# models.Tb1.objects.exclude(name='seven') # 获取指定条件的数据
# 删
#
# models.Tb1.objects.filter(name='seven').delete() # 删除指定条件的数据
# 改
# models.Tb1.objects.filter(name='seven').update(gender='0') # 将指定条件的数据更新，均支持 **kwargs
# obj = models.Tb1.objects.get(id=1)
# obj.c1 = '111'
# obj.save() # 修改单条数据
基本操作
进阶操作
# order by
models.Tb1.objects.filter(name='seven').order_by('id') # asc
models.Tb1.objects.filter(name='seven').order_by('-id') # desc
# 批量插入
objs = [
models.UserInfo(name="批量插入1", age=23, ut_id=1),
models.UserInfo(name="批量插入2", age=23, ut_id=1),
models.UserInfo(name="批量插入3", age=23, ut_id=1),
models.UserInfo(name="批量插入4", age=23, ut_id=1),
models.UserInfo(name="批量插入5", age=23, ut_id=1),
]
models.UserInfo.objects.bulk_create(objs, 10)
# aggregate()聚合函数
from django.db.models import Max, Avg, Count, Min, Sum
result = models.UserInfo.objects.aggregate(k=Count("ut_id", distinct=True), n=Count("id", distinct=True))
print(result) # {'k': 3, 'n': 21}
annotate()分组
# 用于实现聚合group by查询
from django.db.models import Count, Avg, Max, Min, Sum
def test(request):
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id'))
# SELECT u_id, COUNT(ui) AS `uid` FROM UserInfo GROUP BY u_id
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id')).filter(uid__gt=1)
# SELECT u_id, COUNT(ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
v = models.UserInfo.objects.values('u_id').annotate(uid=Count('u_id',distinct=True)).filter(uid__gt=1)
# SELECT u_id, COUNT( DISTINCT ui_id) AS `uid` FROM UserInfo GROUP BY u_id having count(u_id) > 1
return HttpResponse("....")
正则匹配
# regex正则匹配，iregex 不区分大小写
Entry.objects.get(title__regex=r'^(An?|The) +')
Entry.objects.get(title__iregex=r'^(an?|the) +')
F和Q
from django.db.models import F ,Q
def test(request):
# F,更新时取原来值
# 原有age字段都加5
models.UserInfo.objects.all().update(age=F("age")+5)
# Q:用于构造复杂查询条件
q1 = Q()
q1.connector = "OR"
q1.children.append(("id__gt", 2)) # id > 2
q2 = Q()
q2.connector= "OR"
q2.children.append(("age", 23)) # age=23
conn = Q()
conn.add(q1, "AND")
conn.add(q2, "AND") # conn此时相当于 (id > 2) and ( age = 23 or age = 24)
obj = models.UserInfo.objects.filter(conn)
print(obj)
return HttpResponse("...")
extra()
models.UserInfo.objects.extra(
select={'newid':'select count(1) from app01_usertype where id>%s'},
select_params=[1,],
where = ['age>%s'],
params=[18,],
order_by=['-age'],
tables=['app01_usertype']
)
"""
select
app01_userinfo.id,
(select count(1) from app01_usertype where id>1) as newid
from app01_userinfo,app01_usertype
where
app01_userinfo.age > 18
order by
app01_userinfo.age desc
"""
连表性能相关
# select_related: 查询主动做连表
q = models.UserInfo.objects.all().select_related('ut','gp')
# select * from userinfo
# select * from userinfo inner join usertype on ...
for row in q:
print(row.name,row.ut.title)
# prefetch_related: 不做连表，做多次查询
q = models.UserInfo.objects.all().prefetch_related('ut')
# select * from userinfo;
# Django内部：ut_id = [2,4]
# select * from usertype where id in [2,4]
for row in q:
print(row.id,row.ut.title)
models执行原生sql
def test(request):
# models执行原生SQL
from django.db import connections,connection
cursor = connection.cursor() # = connections["default"].cursor()
cursor.execute("select * from app01_userinfo where id = %s",[1, ])
result = cursor.fetchall()
print(result)
return HttpResponse(".....")
时间操作date()和datetimes()
def dates(self, field_name, kind, order='ASC'):
# 根据时间进行某一部分进行去重查找并截取指定内容
# kind只能是："year"（年）, "month"（年-月）, "day"（年-月-日）
# order只能是："ASC" "DESC"
# 并获取转换后的时间
- year : 年-01-01
- month: 年-月-01
- day : 年-月-日
models.DatePlus.objects.dates('ctime','day','DESC')
def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
# 根据时间进行某一部分进行去重查找并截取指定内容，将时间转换为指定时区时间
# kind只能是 "year", "month", "day", "hour", "minute", "second"
# order只能是："ASC" "DESC"
# tzinfo时区对象
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.UTC)
models.DDD.objects.datetimes('ctime','hour',tzinfo=pytz.timezone('Asia/Shanghai'))
"""
pip3 install pytz
import pytz
pytz.all_timezones
pytz.timezone(‘Asia/Shanghai’)
"""
多对多操作
# manytomanyfied创建第三张表
m = models.ManyToManyField("classes")
obj.m.add(*[1,2])
obj.m.remove(*[1,2])
obj.m.set([1,2,3]) # 会覆盖第三张表原有数据
obj.m.clear()
obj.m.filter(name="alex")
obj.m.all() # 查询所有
# 自定义第三张表
# 1. 和方少伟有关系的姑娘
# obj = models.Boy.objects.filter(name='方少伟').first()
# love_list = obj.love_set.all()
# for row in love_list:
# print(row.g.nick)
#
#
# love_list = models.Love.objects.filter(b__name='方少伟')
# for row in love_list:
# print(row.g.nick)
#
# love_list = models.Love.objects.filter(b__name='方少伟').values('g__nick')
# for item in love_list:
# print(item['g__nick'])
#
# love_list = models.Love.objects.filter(b__name='方少伟').select_related('g')
# for obj in love_list:
# print(obj.g.nick)
自关联
ManyToManyField自关联
// 建表
class UserInfo(models.Model):
nickname = models.CharField(max_length=32)
username = models.CharField(max_length=32)
password = models.CharField(max_length=32)
gender_list = (
(1,"男"),
(2,"女"),
)
gender = models.IntegerField(choices=gender_list)
# 多对多
m = models.ManyToManyField("UserInfo")
// django会自动帮我们创建第二张表，两个字段分别是,from_userinfo_id和to_userinfo_id，通过userinfo.m正向操作的时候，选中的是from_userinfo_id，反向操作时选中的是to_userinfo_id
def test2(request):
# 正向操作
obj = models.UserInfo.objects.filter(nickname="少伟").first()
obj1 = obj.m.all()
# <QuerySet [<UserInfo: 高圆圆>, <UserInfo: 赵丽颖>, <UserInfo: 刘涛>]>
# 反向操作
obj = models.UserInfo.objects.filter(nickname=" 高圆圆").first()
obj1 = obj.userinfo_set.clear()
print(obj1)
# <QuerySet [<UserInfo: 少伟>, <UserInfo: 陈军>]>
return HttpResponse("...from test 2")
ForeignKey自关联
FK自关联：
class Comment(models.Model):
"""
评论表
"""
news_id = models.IntegerField() # 新闻ID
content = models.CharField(max_length=32) # 评论内容
user = models.CharField(max_length=32) # 评论者
reply = models.ForeignKey('Comment',null=True,blank=True,related_name='xxxx')
"""
id 新闻ID content user reply_id
1 1 别比比 root null
2 1 就比比 root null
3 1 瞎比比 shaowei null
4 2 写的正好 root null
5 1 拉倒吧 由清滨 2
6 1 拉倒吧1 xxxxx 2
7 1 拉倒吧2 xxxxx 5
"""
新闻1
别比比
就比比
- 拉倒吧
- 拉倒吧2
- 拉倒吧1
瞎比比
新闻2：
写的正好
"""
多表自关联
class UserInfo(models.Model):
nickname = models.CharField(max_length=32)
username = models.CharField(max_length=32)
password = models.CharField(max_length=32)
gender_list = (
(1,"男"),
(2,"女"),
)
gender = models.IntegerField(choices=gender_list)
class U2U(models.Model):
b = models.ForeignKey("UserInfo", related_name="girls")
g = models.ForeignKey("UserInfo", related_name="boys")
# g = models.ForeignKey("UserInfo", related_query_name="boys")
# 若使用related_query_name，反向查找时需要加上_set(如obj.boys_set)
def test(request):
obj = models.UserInfo.objects.filter(id=1).first()
obj1 = obj.girls.all() # 此时是U2U2对象
obj2 = obj.boys
for row in obj1:
print(row.g) # 高圆圆 赵丽 颖刘涛
