django orm 一对多多对一

django orm 一对多多对一

---

1. django orm实战

1.1. 模型类

models.py

  
from django.db import models  

# Create your models here.  


class TableTest(models.Model):  
    book_name = models.CharField(max_length=100)  

    class Meta:  
        db_table = "testtable"  
        verbose_name = '测试表'  
        verbose_name_plural = '测试表s'  
        ordering = ['book_name']  

class Book(models.Model):  
    nid = models.AutoField(primary_key=True)  
    title = models.CharField(max_length=32, default=None)  
    create_time = models.DateTimeField()  
    price = models.DecimalField(decimal_places=2, max_digits=8, default=None)  
    publish_id = models.ForeignKey(to="Publish", on_delete=models.CASCADE, null=True, blank=True)  
    authors = models.ManyToManyField(to='Author')  

    def __str__(self):  
        return str(self.nid) + ':' + self.title  

class Publish(models.Model):  
    nid = models.AutoField(primary_key=True)  
    name = models.CharField(max_length=32)  
    email = models.CharField(max_length=32)  

class Author(models.Model):  
    name = models.CharField(max_length=32)  
    age = models.IntegerField()  
    email = models.CharField(max_length=32)  
    ad = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)  

class AuthorDetail(models.Model):  
    address = models.CharField(max_length=32)  
    telephone = models.CharField(max_length=11)  

1.2. 一对一：实质也是使用外键

  
    # 模拟数据插入  
    for i in range(10):  
        name = fak.name()  
        age = random.randint(10,80)  
        email = fak.ascii_email()  
        addr = fak.address()  
        telephone = fak.phone_number()  
        print(name, age, email, telephone)  
        # continue  
        res = AuthorDetail.objects.create(address=addr, telephone=telephone)  
        Author.objects.create(name=name,  
                              age=age,  
                              email=email,  
                              # ad=res,  
                              ad_id=res.pk  
                              )  

注意：如果参数名为ad，值需要是对象；如果参数名为ad_id，值为int即可
级联操作体现在orm层，数据库层只有外键约束。

1.3. 多对多

  
# 模拟数据插入  
    pub_list = [i[0] for i in Publish.objects.values_list('nid')]  
    author_list = [i[0] for i in Author.objects.values_list('id')]  
    # print(pub_list, author_list)  

    for i in range(100):  
        book_name = fak.word()  
        create_time = fak.date_time()  
        price = random.uniform(10,1000)  
        publish_id = random.choice(pub_list)  
        author = random.sample(author_list, random.randint(1, len(author_list)))  
        print(publish_id, author)  
        book = Book.objects.create(  
            title=book_name,  
            create_time=create_time,  
            price=price,  
            publish_id_id=publish_id,  
        )  
        book.authors.set(author)  

它实质是生成dbtest_author_book表，表中三个字段，id,author_id,bok_id，后两者均为外键；

  
# 插入  
x = Book.objects.create(title='book_1', create_time=datetime.datetime.now(), price=34, publish_id_id=21)  
# 无法直接对manytomany参数赋值  
x.authors.add(19)  
x.authors.add(199) # 199超限了，数据库中查看外键约束有效，奇怪的是没有异常  
x.authors.add(1,2,3,4) # 合法数据添加成功，不合法数据被忽略  
x.authors.remove(2)  
x.authors.clear()  
x.authors.set(1)  
x.authors.set([1,2,3,4]) # 删除后重添加  

1.4. 查询

  
# 一对多查询  
    # 正查  
    book = Book.objects.get(nid='1')  
    # 取到关联的对象  
    publish = book.publish_id  
    print(publish.name)  

    # 反查  
    publish = Publish.objects.get(nid='1')  
    books = publish.book_set.all()  

    # 多对多查询  
    book = Book.objects.get(nid='1')  
    authors = book.authors.all()  

    author = Author.objects.get(id='1')  
    book = author.book_set.all()  

    # 一对一  
    author = Author.objects.get(id='1')  
    ad = author.ad  
    author_detail = AuthorDetail.objects.get(id='1')  
    author = author_detail.author  

基于对象的跨表查询（基于子查询）

  
# 一对多查询  
    # 正查  
    book = Book.objects.get(nid='1')  
    # 取到关联的对象  
    publish = book.publish_id  
    print(publish.name)  

    # 反查  
    publish = Publish.objects.get(nid='1')  
    books = publish.book_set.all()  

    # 多对多查询  
    book = Book.objects.get(nid='1')  
    authors = book.authors.all()  

    author = Author.objects.get(id='1')  
    book = author.book_set.all()  

    # 一对一  
    author = Author.objects.get(id='1')  
    ad = author.ad  
    author_detail = AuthorDetail.objects.get(id='1')  
    author = author_detail.author  

基于双下划线的跨表查询（基于join实现的）
KEY：正向查询按字段，反向查询按表明小写

  
# linux这本书的出版社名字  
    # 正向  
    name = Book.objects.filter(title='linux').values('publish_id__name')  
    # 反向  
    name = Publish.objects.filter(book__title='linux').values('name')  

    # 查询第一个出版社对用的所有书  
    # 正向  
    book = Publish.objects.filter(nid='1').values('book__title')  
    # 反向  
    book = Book.objects.filter(publish_id_id='1').values('title')  

    # 查询Linux这本书所有作者的手机号  
    telephone = Book.objects.filter(title='linux').values('authors__ad__telephone')  
    telephone = Author.objects.filter(book__title='linux').values('ad__telephone')  

    # 查询id=1的出版社的作者的书籍和作者的名字  
    title_name = Publish.objects.filter(nid='1').values('book__authors__name', 'book__title')  
    title_name = Book.objects.filter(publish_id_id=1).values('title', 'authors__name')  

    # 查询作者手机号的开头为110的书籍和出版社名称  
    title_name = Author.objects.filter(ad__address__startswith='北京').values('book__title', 'book__publish_id__name')  

聚合和分组

  
from django.db.models import Avg, Max, Sum, Min, Count  
    avg = Book.objects.all().aggregate(price=Avg('price'))  

    # 单表分组查询  
    # 查询每个出版社id以及对应大的书籍个数  
    count = Book.objects.values('publish_id').annotate(count=Count(1))  

    # values中的值为分类的参数  
    '''  
        select Count(1) from Book GROUP by publish_id  
    '''  
    # 跨表分组查询  
    # 查看每一个出版社名称以及对应的书籍个数  
    count = Book.objects.values('publish_id__name').annotate(count=Count(1))  
    count = Publish.objects.values('name').annotate(c=Count('book__nid'))  
    # 查找作者大于一个的书籍对应的作者数  
    query = Book.objects.annotate(c=Count('authors')).filter(c__gt=1).values('c', 'title')  

2. 问题

2.1. 问题1

直接使用django 的models插值发现报错

django.core.exceptions.ImproperlyConfigured: Requested setting INSTALLED_APPS, but settings are not configured. You must either define the environment variable DJANGO_SETTINGS_MODULE or call settings.configure() before accessing settings.

原因：没有添加环境
解决办法：将路径添加到环境变量中，成功解决这个问题

3. 代码

操作代码

  
import datetime  
import random  

def db_test():  
    # 环境配置  
    import django  
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'dj_site.settings')  
    django.setup()  

    from dbtest.models import Publish,Book,Author,AuthorDetail,TableTest  
    # 数据操作  
    from faker import Faker  
    fak = Faker('zh-CN')  
    """  
    book_list = []  
    for i in range(100):  
        book = Book(  
            title='book_%s' % i,  
            price=i * i,  
            create_time=datetime.datetime.now())  
        book_list.append(book)  
    Book.objects.bulk_create(book_list)  
    """  
    """  
    for i in range(30):  
        pub = Publish(  
            name=fak.company()[:32],  
            email=fak.company_email()[:32]  
        )  
        pub.save()  
        print(pub)  
    """  
    """  
    # 一对一插值  
    for i in range(10):  
        name = fak.name()  
        age = random.randint(10,80)  
        email = fak.ascii_email()  
        addr = fak.address()  
        telephone = fak.phone_number()  
        print(name, age, email, telephone)  
        # continue  
        res = AuthorDetail.objects.create(address=addr, telephone=telephone)  
        Author.objects.create(name=name,  
                              age=age,  
                              email=email,  
                              # ad=res,  
                              ad_id=res.pk  
                              )  
    """  
    pub_list = [i[0] for i in Publish.objects.values_list('nid')]  
    author_list = [i[0] for i in Author.objects.values_list('id')]  
    # print(pub_list, author_list)  

    for i in range(100):  
        book_name = fak.word()  
        create_time = fak.date_time()  
        price = random.uniform(10,1000)  
        publish_id = random.choice(pub_list)  
        author = random.sample(author_list, random.randint(1, len(author_list)))  
        print(publish_id, author)  
        book = Book.objects.create(  
            title=book_name,  
            create_time=create_time,  
            price=price,  
            publish_id_id=publish_id,  
        )  
        book.authors.set(author)  


db_test()