Python下ORM介绍及常见操作
对象关系映射(英语:Object Relational Mapping,简称ORM,或O/RM,或O/R mapping),是一种程序设计技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换。
ORM操作(对象关系映射)
Http请求:
url -> 视图(模板+数据)
ORM利用pymysql第三方工具连接数据库
默认:
SQLlite
MySQL:
mysql -> MySQLDB(修改django默认连接mySQL方式)
步骤:
1. 创建数据库 orm
2. 在主目录下的settings.py修改DATABASES项
DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': '', 'USER': 'root', 'PASSWORD': '', 'HOST': 'localhost', 'PORT': 3306, } }
3. 在主目录下的__init__.py增加代码
import pymysql pymysql.install_as_MySQLdb()
ORM操作数据
首先在models中新建两张表做为示例,如下
from django.db import models # 新建部门和用户信息两张表,其中各自表的id会自动生成 # UserInfo表中的dp_id外键关联Department表 class Department(models.Model): title = models.CharField(max_length=32) class UserInfo(models.Model): username = models.CharField(max_length=32) age = models.IntegerField(,max_length=32) dp_id = models.ForeignKey(to='Department')
增删改查
from app01 import models #新增 # models.Department.objects.create(title='财务部') # models.Department.objects.create(title='人事部') # models.UserInfo.objects.create(username='曹操',age=18,dp_id=3) #删除 # models.Department.objects.filter(id=1).delete() #查询 # alist = models.Department.objects.all() # alist = models.Department.objects.filter(id__gt=2) // 大于2 # alist = models.Department.objects.filter(id__lt=2) // 小于2 更多查用查询条件见下方 # for i in alist: //所有查询都是如此输出 # print(i.id,i.title) #更新 models.UserInfo.objects.filter(nid='1').update(username="曹操曹")
ORM常用查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
__gt 大于 __lt 小于 __gte 大于等于 __lte 小于等于 __in[ ,] 存在于一个list范围内 __startwith 以…开头 __istartwith 以…开头(忽略大小写) __endwith 以…开头 __iendwith 以…开头(忽略大小写) __exact 精确等于like"aaa" __iexact 精确等于 忽略大小写 like"aaa" __contains 包含(区分大小写) __icontains 包含(不区分大小写) __range 在…范围内 __exclude 除…之外
外键获取数据(跨表)
通过外键字段跨表操作obj.dp.title,如果ug表有外键关联其他表,还可加 . 继续获取
下面代码输出 每个用户的姓名和对应的部门id、部门名
# UserInfo,dp是FK字段 - 正向操作 obj = models.UserInfo.objects.all() for obj in alist: print(obj.username,obj.age,obj.dp_id,obj.dp.title)
注意:反向操作中的obj.userinfo_set.all()或者obj.userinfo_set.filter()得到的是Queryset类型数据,需要循环从而输出想要的结果
#Department,表名小写_set.all() - 反向操作 obj = models.Department.objects.all().first() print(obj.id,obj.title) for row in obj.userinfo_set.all(): //或obj.userinfo_set.filter(条件) print(row.username,row.age)
values和values_list
被关联表的跨表反向操作
示例为values,实则values_list也可以用同样的方法实现
如果加的是主表的小写,输出则为主表的id,指定字段用双下划线
v = models.Department.objects.values('id','title','userinfo','小写的表名/小写的表名__主表字段')
v = models.Department.objects.values('id','title','userinfo') # < QuerySet[ # {'id': 2, 'title': '财务部', 'userinfo': 2}, # {'id': 3, 'title': '人事部', 'userinfo': 1}, # {'id': 4, 'title': '技术部', 'userinfo': None}, # {'id': 5, 'title': 'xx部', 'userinfo': None}] > v1 = models.Department.objects.values('id','title','userinfo__username') # < QuerySet[ # {'id': 3, 'title': '人事部', 'userinfo__username': '曹操曹'}, # {'id': 2, 'title': '财务部', 'userinfo__username': 'hh0'}, # {'id': 4, 'title': '技术部', 'userinfo__username': None}, # {'id': 5, 'title': 'xx部', 'userinfo__username': None}] >
PS:前面的所有数据都会显示,如果没有对应的数据,默认显示None
排序与分组
排序orderby
v = models.UserInfo.objects.all().orderby('-id','name')
PS: ‘-’为倒序,也可多条件排序
分组annotate
from django.db.models import Count,Sum,Max,Min v = models.UserInfo.objects.values('username').annotate(xx=Count('nid')) print(v.query) #SELECT `nid`, `username`, `password`, `age`,`ug_id`, #COUNT(`app01_userinfo`.`nid`) AS`xx` #FROM userinfo GROUPBY `nid`
PS:分组前后还可进行filter筛选
F和Q
F,更新时用于获取原来的值
from django.db.models import F,Q models.UserInfo.objects.all().update(age=F("age")+1)
Q,用于构造复杂查询条件
# 应用一: models.UserInfo.objects.filter(Q(id__gt=1)) models.UserInfo.objects.filter(Q(id=8) | Q(id=2)) models.UserInfo.objects.filter(Q(id=8) & Q(id=2)) # 应用二: q1 = Q() q1.connector = 'OR' q1.children.append(('id__gt', 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)) q3 = Q() q3.connector = 'AND' q3.children.append(('id', 1)) q3.children.append(('id', 2)) q2.add(q3,'OR') con = Q() con.add(q1, 'AND') con.add(q2, 'AND') #相当于(id=1 or id=10 or id=9 or(id=1 and id=2)) and (c1=1 or c1=10 or c1=9) models.UserInfo.objects.filter(con)
extra
extra, 额外查询条件以及相关表,排序
models.UserInfo.objects.filter(id__gt=1) models.UserInfo.objects.all() # id name age ut_id models.UserInfo.objects.extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None) # a. 映射 # select # select_params=None # select 此处 from 表 # b. 条件 # where=None # params=None, # select * from 表 where 此处 # c. 表 # tables # select * from 表,此处 # d. 排序 # order_by=None # select * from 表 order by 此处 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 """
result = models.UserInfo.objects.filter(id__gt=1).extra( where=['app01_userinfo.id < %s'], params=[100,], tables=['app01_usertype'], order_by=['-app01_userinfo.id'], select={'uid':1,'sw':"select count(1) from app01_userinfo"} ) print(result.query) # SELECT (1) AS "uid", (select count(1) from app01_userinfo) AS "sw", "app01_userinfo"."id", "app01_userinfo"."name", "app01_userinfo"."age", "app01_userinfo"."ut_id" FROM "app01_userinfo" , "app01_usertype" WHERE ("app01_userinfo"."id" > 1 AND (app01_userinfo.id < 100)) ORDER BY ("app01_userinfo".id) DESC
原生SQL语句
from django.db import connection, connections cursor = connection.cursor() # connection=default数据 cursor = connections['db2'].cursor() cursor.execute("""SELECT * from auth_user where id = %s""", [1]) row = cursor.fetchone() row = cursor.fetchall()
result
result = models.UserInfo.objects.raw('select * from userinfo') [obj(UserInfo),obj,] result = models.UserInfo.objects.raw('select id,1 as name,2 as age,4 as ut_id from usertype') [obj(UserInfo),obj,] v1 = models.UserInfo.objects.raw('SELECT id,title FROM app01_usertype',translations=name_map)
ORM多对多操作
1. 自定义类方法创建关系表
2. django自带ManyToManyFiled(),此方法只生成3个字段-自表id和另外两表id
3. 两种混合使用
- 但是此处的manytomany只可使用查询和清空,类方法不受影响
obj.m.all()
obj.m.clear()
自定义第三张表
class Teacher(models.Model): name = models.CharField(max_length=32) class Class(models.Model): title = models.CharField(max_length=32) class Mas(models.Model): n = models.ForeignKey('Teacher', on_delete=models.CASCADE) t = models.ForeignKey('Class', on_delete=models.CASCADE)
# 1
obj = models.Teacher.objects.filter(name='丁冰冰').first()
mas_list = obj.mas_set.all()
for row in mas_list:
print(row.t.title)# 2
mas_list = models.Mas.objects.filter(n__name='丁冰冰')
for row in mas_list:
print(row.t.title)# 3
mas_list = models.Mas.objects.filter(n__name='丁冰冰').values('t__title')
for row in mas_list:
print(row['t__title'])# 4
mas_list = models.Mas.objects.filter(n__name='丁冰冰').select_related('t')
for obj in mas_list:
print(obj.t.title)
使用ManyToManyFiled创建第三张表
class Teacher(models.Model): name = models.CharField(max_length=32) m = models.ManyToManyField('Class') class Class(models.Model): title = models.CharField(max_length=32) obj = models.Teacher.objects.filter(name='丁冰冰').first() # 增删add、remove可用单数、多数、列表 obj.m.add(4) obj.m.add(1,3) obj.m.add(*[2,]) #remove同add操作 obj.m.set([1,3,]) # set为重置 Q = obj.m.all() #[Q为Class的对象] print(Q) cla_list = obj.m.all() cla_list = obj.m.filter(title='5班') obj.m.clear() # 清除所有有关’丁冰冰‘的关系 # 反向获取数据同 一对多(小写表名_set) obj = models.Class.objects.filter(title='5班').first() v = obj.teacher_set.all()