django【orm操作】

一、ORM表结构

class Publisher(models.Model):
    name = models.CharField(max_length=30, verbose_name="名称")
    address = models.CharField("地址", max_length=50)
    city = models.CharField('城市',max_length=60)
    state_province = models.CharField(max_length=30)
    country = models.CharField(max_length=50)
    website = models.URLField()
 
    class Meta:
        verbose_name = '出版商'
        verbose_name_plural = verbose_name
 
    def __str__(self):
        return self.name
 
class Author(models.Model):
    name = models.CharField(max_length=30)
    def __str__(self):
        return self.name
 
class AuthorDetail(models.Model):
    sex = models.BooleanField(max_length=1, choices=((0, '男'),(1, '女'),))
    email = models.EmailField()
    address = models.CharField(max_length=50)
    birthday = models.DateField()
    author = models.OneToOneField(Author)
 
class Book(models.Model):
    title = models.CharField(max_length=100)
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher)
    publication_date = models.DateField()
    price=models.DecimalField(max_digits=5,decimal_places=2,default=10)
    def __str__(self):
        return self.title

　 

二、ORM增加数据

from app01.models import *

一、单表增加数据
    #create方式一:   Author.objects.create(name='Alvin')
    #create方式二:   Author.objects.create(**{"name":"alex"})
    #save方式一:     author=Author(name="alvin")
                            author.save()
    #save方式二:     author=Author()
                            author.name="alvin"
                            author.save()

二、1对多增加数据 
    #方式一：
    #Book.objects.create(title='vb',publisher_id=1, publication_date="2016-7-7",price=198)
    #方式二：
    #publisher_obj = Publisher.objects.get(id=3)
    #Book.objects.create(title='vb',publisher=publisher_obj, publication_date="2016-7-7",price=198)
    备注：将 publisher_id=2 改为  publisher=publisher_obj

三、多对多增加数据
    1.#为id=1这本书增加2个作者。将数据增加到第三张表中
       # 正向增加
    author1=Author.objects.get(id=1)
    author2=Author.objects.filter(name='alvin')[0]
    book=Book.objects.filter(id=1)[0]
    book.authors.add(author1,author2)
   
    2.#反向增加
       #将多本书增加到一个作者
    book=models.Book.objects.filter(id__gt=1)
    authors=models.Author.objects.filter(id=1)[0]
    authors.book_set.add(*book)
    authors.book_set.remove(*book)
 
    3.如果第三张表是通过models.ManyToManyField()自动创建的,那么绑定关系只有上面一种方式
    #如果第三张表是自己创建的:
     class Book2Author(models.Model):
            author=models.ForeignKey("Author")
            Book=  models.ForeignKey("Book")
    #那么就还有一种方式:
            author_obj=models.Author.objects.filter(id=2)[0]
            book_obj  =models.Book.objects.filter(id=3)[0]

       s=models.Book2Author.objects.create(author_id=1,Book_id=2)
            s.save()
            s=models.Book2Author(author=author_obj,Book_id=1)
            s.save()

 

三、ORM删除数据

1.删除BOOK表id=1的数据
    Book.objects.filter(id=1).delete()

2.删除一个出版社. 注意与出版相关联的书也会被删除 
　　Publisher.objects.filter(id=3).delete()

3.删除id=6这本书和作者id=1的关联(删除第三张关系表数据)
    book = Book.objects.filter(id=6)[0]   # 找到这本书的对象
    author = Author.objects.filter(id=1)[0] # 找到这个作者的对象

    author.book_set.remove(book)  # 反向删除
    book.authors.remove(author)    # 正向删除

 

四、ORM更新数据

1.对象更新(所有字段更新，性能低)
    ret = Publisher.objects.get(id=2)
    ret.name = "复旦大学"
    ret.save()

2.级联方法更新
    Publisher.objects.filter(id=1).update(city="北京市")

 

五、ORM查询数据

1.了不起的双下划线(__)之单表条件查询
# models.Tb1.objects.filter(id__lt=10, id__gt=1)   # 获取id大于1 且 小于10的值
#
# models.Tb1.objects.filter(id__in=[11, 22, 33])   # 获取id等于11、22、33的数据
# models.Tb1.objects.exclude(id__in=[11, 22, 33])  # not in
#
# models.Tb1.objects.filter(name__contains="ven")
# models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感
#
# models.Tb1.objects.filter(id__range=[1, 2])   # 范围bettwen and
#
# startswith，istartswith, endswith, iendswith,

2.了不起的双下划线(__)之多表条件关联查询
# 正向查找(条件)

# ret3=models.Book.objects.filter(title='Python').values('id')
# print(ret3)#[{'id': 1}]
#正向查找(条件)之一对多      ret4=models.Book.objects.filter(title='Python').values('publisher__city')
      print(ret4)  #[{'publisher__city': '北京'}]

#正向查找(条件)之多对多
      ret5=models.Book.objects.filter(title='Python').values('author__name')
      print(ret5)
      ret6=models.Book.objects.filter(author__name="alex").values('title')
      print(ret6)

      #注意
      #正向查找的publisher__city或者author__name中的publisher,author是book表中绑定的字段
      #一对多和多对多在这里用法没区别

# 反向查找(条件)

    #反向查找之一对多:
    ret8=models.Publisher.objects.filter(book__title='Python').values('name')
    print(ret8)#[{'name': '人大出版社'}]  注意,book__title中的book就是Publisher的关联表名

    ret9=models.Publisher.objects.filter(book__title='Python').values('book__authors')
    print(ret9)#[{'book__authors': 1}, {'book__authors': 2}]

    #反向查找之多对多:
    ret10=models.Author.objects.filter(book__title='Python').values('name')
    print(ret10)#[{'name': 'alex'}, {'name': 'alvin'}]

    #注意
    #正向查找的book__title中的book是表名Book
    #一对多和多对多在这里用法没区别