djangoORM语句

1.这段语句配置写在settings里可以把数据库语句显示在前端

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

　2.数据库外键处理

反向查询:related_name='表名'
表关系:
db_constraint=False:断开表关系
on_delete=models.CASCADE 级联关系,可以做级联删除
on_delete=models.SET_NULL,null=True设置为空
on_delete=models.SET_DEFAULT,default=0 设置成默认值为0
on_delete=models.DO_NOTHING不处理该字段
注:多对多关系不需要明确on-delete
           

3.表查询练习

from django.db import models
# 书本表
class Book(models.Model):
    title=models.CharField(max_length=32)
    price=models.CharField(max_length=32)
    publish_date=models.DateField(auto_now_add=True)
    publish=models.ForeignKey(to="Publish")
    authors=models.ManyToManyField(to='Author')

    def __str__(self):
          return self.title  # 必须返回一个字符串类型 否则直接报错

#出版社表
class Publish(models.Model):
    name=models.CharField(max_length=32)
    addr=models.CharField(max_length=32)
    email=models.EmailField()
    def __str__(self):
        return self.name

#作者表
class Author(models.Model):
    name=models.CharField(max_length=32)
    age=models.CharField(max_length=32)
    author_dateil=models.OneToOneField(to='AuthorDetail')
    def __str__(self):
        return self.name

# 作者信息表
class AuthorDetail(models.Model):
    phone=models.CharField(max_length=32)
    addr=models.CharField(max_length=32)
    def __str__(self):
        return self.phone

　　

import os

if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "books.settings")
    import django

    django.setup()
    from app import models

    print(models.Publish.objects.all())
    publish_obj = models.Publish.objects.filter(pk=2).first()
    models.Book.objects.create(title='无敌天下', price=589, publish=publish_obj)
    models.Publish.objects.create(name='天堂出版社', addr='天京', email='11528654@qq.com')

    obj = models.Publish.objects.filter(id=5).first()
    print(obj.name)
    # 传数字
    obj = models.Publish.objects.filter(pk=5).first()
    # 传对象
    models.Book.objects.filter(pk=2).update(publish=obj)

    book_obj = models.Book.objects.filter(pk=5).first()
    # # book_obj.authors.add(1)
    # author_onj=models.Author.objects.get(pk=2)
    # 传对象
    # book_obj.authors.add(author_onj)
    author_list = models.Author.objects.all()
    # 传列表
    book_obj.authors.add(*author_list)
    # 删除中间关系表
    book_obj = models.Book.objects.filter(pk=5).first()
    book_obj.authors.remove(2)
    # 跨表查询都可以根据对象进行查询,删除,修改

    book_obj = models.Book.objects.filter(pk=4).first()
    author_obj = models.Author.objects.all()
    book_obj.authors.add(*author_obj)
    book_obj = models.Book.objects.filter(pk=6).first()
    book_obj.authors.set([2, ])

    # 查询天堂出版社出版的书
    publish_obj = models.Publish.objects.filter(name='天堂出版社').first()
    print(publish_obj.book_set.all())

    # 查询jason出版的书
    author_obj = models.Author.objects.filter(name='jason').first()
    print(author_obj.book_set.all())
    # 查地址
    print(author_obj.author_dateil.phone)
    # 查询地址
    print(author_obj.author_dateil.addr)
 
    #查询手机号是110的作者姓名
    # 反向查询表名小写
    author_obj = models.AuthorDetail.objects.filter(phone='110').first()
    print(author_obj.author.name)

    # 查询书本出版社
     book_obj = models.Book.objects.filter(title='静瓶梅').values('publish__name')
     print(book_obj)
    # 查询书本的作者
     book_obj = models.Book.objects.filter(title='银瓶梅').values('authors__name', 'title')
    print(book_obj)

   # 查询作者的手机号
    res = models.Author.objects.filter(name='jason').values('author_dateil__phone')
    print(res)
 
   # 查询书本作者的手机号
   res = models.Book.objects.filter(title='遮天').values('title', 'authors__name', 'authors__author_dateil__phone')
   print(res)

   # 查询出版社的所有图书的名字和价格
   res = models.Publish.objects.filter(name='天堂出版社').values('book__title', 'book__price')
   print(res)
   res1 = models.Book.objects.filter(publish__name='北方出版社').values('title', 'price')
   print(res1)
   # 查询所有的书本的作者
   resf = models.Author.objects.values('book__title', 'name')
   print(resf)

 3.列表的增删改查

  from app01 import models
    # obj=models.Author.objects.all()
    # print(obj)
    # obj=models.Book.objects.values_list('publish__name').filter(authors__name='协警')
    # print(obj)
    # SELECT `app01_publish`.`name` FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) INNER JOIN `app01_book_authors` ON (`app01_book`.`id` = `app01_book_authors`.`book_id`) INNER JOIN `app01_author` ON (`app01_book_authors`.`author_id` = `app01_author`.`id`) WHERE `app01_author`.`name` = '协警' LIMIT 21;

    # obj = models.Author.objects.all().order_by("id")
    # print(obj)
    # SELECT `app01_author`.`id`, `app01_author`.`name`, `app01_author`.`age` FROM `app01_author` ORDER BY `app01_author`.`id` ASC LIMIT 21;

    # 多对多关联删除语句,先删除主表,后删除中间表
    # obj = models.Author.objects.filter(pk=1)
    # obj.delete()

    #先删除中间表,在删除主表
    # (0.000) DELETE FROM `app01_book_authors` WHERE `app01_book_authors`.`author_id` IN (1); args=(1,)
    # (0.000) DELETE FROM `app01_author` WHERE `app01_author`.`id` IN (1); args=(1,)

    # 一对多添加
    # get返回的是对象本身<class 'app01.models.Publish'>
    # onj=models.Publish.objects.get(id=2)
    # print(onj)
    # # 新增对象返回来的是对象<class 'app01.models.Book'>本身,不是querset对象
    # bok=models.Book.objects.create(publish=onj,title='tan',pub_date='2020-02-29',price=220)
    # print(type(bok))

    # INSERT INTO `app01_book` (`title`, `price`, `pub_date`, `publish_id`) VALUES ('吸星大法', 220, '2020-02-29', 1);

    # 多对多添加
    # au=models.Author.objects.filter(id__in=[1,2])
    # print(au)
    # 多对多时主表必须是对象本身<class 'app01.models.Book'>,而不是querset对象
    # mb=models.Book.objects.get(id=1)
    # print(mb)
    # mb.authors.add(*au)

    # 直接插入
    # (0.000) INSERT INTO `app01_book` (`title`, `price`, `pub_date`, `publish_id`) VALUES ('tan', 220, '2020-02-29', 2);


    #一对多删除,先删主表,后删子表
    # models.Publish.objects.filter(id=2).delete()
    #先删除中间表,在删关联表
    # DELETE FROM `app01_book_authors` WHERE `app01_book_authors`.`book_id` IN (2, 4, 6, 7);
    # DELETE FROM `app01_book` WHERE `app01_book`.`id` IN (2, 4, 6, 7);
    # DELETE FROM `app01_publish` WHERE `app01_publish`.`id` IN (2);

    # # 带first()的查询不是querset对象,是对象本身<class 'app01.models.Book'>
    # bok=models.Book.objects.filter(id=1).first()
    # print(bok)
    # # 不加first()是querset对象
    # pu=models.Publish.objects.filter(id=bok.publish_id)
    # print(pu)

    #第一种改法
    # models.Book.objects.filter(title='金刚经').update(title='至尊秘籍')
    # UPDATE `app01_book` SET `title` = '金刚经' WHERE `app01_book`.`title` = '天产教';
    
    # 第二种改法
    # bok=models.Book.objects.get(id=1)
    # bok.price=500
    # bok.save()

     # UPDATE `app01_book` SET `title` = '如来神掌', `price` = 500, `pub_date` = '2020-02-29', `publish_id` = 1 WHERE `app01_book`.`id` = 1;