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;