关系型数据库最强大之处在于“关系”,表和表之间是有关联的,表之间的关系有一对一,一对多,多对多。
1.1一对一
学生和档案之间属于一对一,学生表和档案表就是一对一关系,学生表是主表,档案表是从表。从表中有一个外键和学生表关联,并且要求外键取值唯一,对应关键字为:OneToOneField
#创建模型
class Student(models.Model):
sno = models.CharField(max_length=6,primary_key=True)
sname = models.CharField(max_length=100,null=False)
ssex = models.CharField(max_length=2,default='男',null=True)
sage = models.IntegerField(null=True)
sclass = models.CharField(max_length=10,null=True)
def __str__(self):
return "no:{},name:{}".format(self.sno,self.sname)
class Meta:
db_table = 'student'
class Archives(models.Model):
idcard = models.CharField(max_length=18, unique=True)
address = models.CharField(max_length=200,null=True)
# on_delete=models.CASCADE 级联删除,删除学生会连同档案一块删除
student = models.OneToOneField(Student, on_delete=models.CASCADE)
def __str__(self):
return "{},{}".format(self.idcard,self.address)
class Meta:
db_table = 'archives'
增加数据
def addstudent(request):
student = Student()
student.sno = '180502'
student.sname = '王二狗'
student.sage = 22
student.save()
return HttpResponse("增加了一个学生")
def addarchives(request):
stu = Student.objects.get(pk='180503')
arc = Archives()
arc.idcard = '130098384893838953'
arc.student = stu #学生对象必须已经保存到数据库,否认错误
arc.save()
return HttpResponse("增加档案")
删除数据
def deletestudent(request):
student = Student.objects.get(pk='180503')
student.delete()
return HttpResponse("删除学生")
正向查询
def findstudent(request):
# 获取学生信息
student = Student.objects.first()
print(student)
# 通过学生对象获取档案信息
archive = student.archives
print(archive)
return HttpResponse(student)
反向查询
def findarchives(request):
#获取档案记录
archive = Archives.objects.first()
#通过档案获取关联学生信息
student = archive.student
return HttpResponse(student)
跨关系查询
def lookup(request):
#根据档案查学生
# student = Student.objects.get(archives__pk=1)
student = Student.objects.get(archives__idcard='13009488384383838')
#根据学生查档案
archive = Archives.objects.get(student__sno='180501')
return HttpResponse(archive)
on_delete
- CASECADE,默认级联删除数据
- PROTECT 保护模式,当从表中存在级联记录的时候,删除主表记录会抛出保护异常,从表中不存在级联数据的时候,是允许删除的
- SET——xxx
- NULL 字段本身允许为空
- DEFAULT 字段本身有默认值
- DO-NOTHING 什么都不做
1.2一对多
一个出版社可以出版多本书,一本书只能被一个出版社出版。出版社和图书表属于一对多,一对多一般将主表中的主键并到从表中做外键。在模型中用ForeignKey表示多对一
class Publisher(models.Model):
pname = models.CharField(max_length=100,null=True)
def __str__(self):
return self.pname
class Meta:
db_table = 'publisher'
class Book(models.Model):
bname = models.CharField(max_length=200,null=True)
#多对一模型通过ForeignKey表示多对一
#如果publisher定义在book之后,第一个参数应该用字符串'Publisher'
publisher = models.ForeignKey(Publisher,on_delete=models.CASCADE,null=True)
def __str__(self):
return self.bname
class Meta:
db_table = 'book'
正向查询
def findpublisher(req):
pub = Publisher.objects.first()
print(pub)
# pub = Publisher()
book = pub.book_set.all()
print(book)
return HttpResponse("查询出版社")
反向查询
def findbook(req):
book = Book.objects.first()
return HttpResponse(book.publisher.pname)
跨关系查询
def loopup(req):
# 根据图书获取出版社
pub = Publisher.objects.get(book__bname='花样年华927937')
print(pub)
# 根据出版社获取图书
books = Book.objects.filter(publisher__pname='科技出版社5829')
print(books)
return HttpResponse("跨关系查询")
1.3多对多
一个买家可以购买多件商品,一件商品可以被多个买家购买,买家和商品之间构成多对多关系,多对多关系必然会生成一张中间表:买家-商品表,记录商品和买家的关系,该表包含商品表主键和买家表的主键
from django.db import models
# Create your models here.
class Buyer(models.Model):
bname = models.CharField(max_length=30)
level = models.IntegerField(default=1)
class Goods(models.Model):
gname = models.CharField(max_length=100)
price = models.FloatField()
buyer = models.ManyToManyField(Buyer)
def __str__(self):
return self.gname +" "+ str(self.price)
购买商品
def sellgoods(req):
goods = Goods.objects.get(pk=randint(1,Goods.objects.count()))
goods.buyer.add(Buyer.objects.get(pk=randint(1,Buyer.objects.count())))
goods.save()
return HttpResponse("剁手成功")
正向查询
def findgoods_by_buyer(req):
buyer = Buyer.objects.get(pk=13)
res = buyer.goods_set.all()
print(res)
return HttpResponse("由买家查询商品")
反向查询
def findbuyer_by_goods(request):
goods = Goods.objects.last()
buyer = goods.buyer.all()
print(buyer)
return HttpResponse("由商品查询买家")
2.模型继承
django中的数据库模块提供了一个非常不错的功能,就是支持models的面向对象,可以在models中添加Meta,指定是否抽象,然后进行继承
class Animal(models.Model):
xxx
class Meta:
abstract = True/False
class Dog(Animal):
xxx
默认模型就是允许继承的,但是默认的继承处理方式不是很合理:
-
默认在父类中定义的字段会存在父类的表中,子类的数据通用部分会存在父表中,子类特有数据会在子表中,子类通过外键进行级联
-
默认方式比较垃圾,效率比较低
开发中,需要将父类抽象化,在元信息中使用abstract=True
-
抽象化的父类不会再数据库生成表了
-
子类会将父类中的通用数据,复制到子表中
3 原生sql
from django.db import connection
# with语句相当与cursor= connection.cursor() 和 cursor.close(),简化了语
with connection.cursor() as cursor:
cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
row = cursor.fetchone()
# 返回列表套字典
with connection.cursor() as cursor:
cursor.execute("select * from publisher")
columns = [col[0] for col in cursor.description]
res = [dict(zip(columns, row)) for row in cursor.fetchall()]
print(res)