django图书管理系统-外键字段的增删改查
1、创建app02, 并在settings.py里面设置
2、在app02/models.py里面，创建模型
from django.db import models # Create your models here. class Book(models.Model): name = models.CharField(max_length=32) price = models.DecimalField(max_digits=8, decimal_places=2) publish_date = models.DateTimeField( auto_now_add= True) #添加表关联关系 publish = models.ForeignKey( to='Publish', on_delete=models.CASCADE ) authors = models.ManyToManyField( to='Author') class Publish( models.Model): name = models.CharField(max_length=32) addr = models.CharField(max_length=64) email = models.EmailField() #内部实现也是charField，定义为EmailField是为了表单校验 class Author(models.Model): name = models.CharField(max_length=32) age = models.IntegerField() #添加表关联关系 authordetail = models.OneToOneField( to='AuthorDetail', on_delete= models.CASCADE) #切表，是因为不常用的数据单独放一张表， 对经常用的数据也单独放一张表，提高查询的效率 class AuthorDetail(models.Model): addr = models.CharField(max_length=32) phone = models.IntegerField()
2、执行makemigrations和migrate, 同步数据库； 然后在对应的表格中添加数据；
给publish出版社表添加数据
给作者详情表添加数据：
给作者表添加数据
4、创建项目下创建一个test.py文件，并把manage.py里第一句话复制过来； 然后，可以在test.py里面直接调用模型来操作；
5、对Books表外键字段，执行添加数据
import os import sys if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'untitled1.settings') import django django.setup() from app02 import models #书籍表的增删改查 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 publish_obj = models.Publish.objects.filter( pk=3 ).first() models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj )
6、对Books表外键字段，执行修改操作
import os import sys if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #数据表数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 publish_obj = models.Publish.objects.filter( pk = 3).first() models.Book.objects.filter(name="三体").update(publish = publish_obj)
7、对Books表多对多字段的增加单条数据
import os import sys from django.db import connection if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表，外键字段，数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #书籍表，外键字段，数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 # publish_obj = models.Publish.objects.filter( pk = 3).first() # models.Book.objects.filter(name="三体").update(publish = publish_obj) #书籍表，多对多字段的增删改查 book_obj = models.Book.objects.filter(name="三体").first() book_obj.authors.add(1) print(connection.queries)
执行后，打印的源sql语句为：
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},
{'sql': 'SELECT VERSION()', 'time': '0.001'},
{'sql': "SELECT `app02_book`.`id`, `app02_book`.`name`, `app02_book`.`price`, `app02_book`.`publish_date`, `app02_book`.`publish_id` FROM `app02_book` WHERE `app02_book`.`name` = '三体' ORDER BY `app02_book`.`id` ASC LIMIT 1", 'time': '0.000'},
{'sql': 'SELECT `app02_book_authors`.`author_id` FROM `app02_book_authors` WHERE (`app02_book_authors`.`book_id` = 1 AND `app02_book_authors`.`author_id` IN (1))', 'time': '0.001'},
{'sql': 'INSERT INTO `app02_book_authors` (`book_id`, `author_id`) VALUES (1, 1)', 'time': '0.001'}]
比较重要的一句，样式美化后：
SELECT `app02_book`.`id`, `app02_book`.`name`, `app02_book`.`price`, `app02_book`.`publish_date`, `app02_book`.`publish_id FROM ` app02_book ` WHERE ` app02_book `.` NAME ` = '三体' ORDER BY ` app02_book `.` id ` ASC LIMIT 1
然后，要注意的地方，插入数据之前，要先检查是否有重复数据，django自动做了重复数据检查的查询操作，所以不会报错；
如果是手动在数据库中插入重复数据会报错，例如，手动再次执行重复插入的操作：
8、对Books表多对多字段的增加多条数据
import os import sys from django.db import connection if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表，外键字段，数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #书籍表，外键字段，数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 # publish_obj = models.Publish.objects.filter( pk = 3).first() # models.Book.objects.filter(name="三体").update(publish = publish_obj) #书籍表，多对多字段的增加单条数据 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1) # print(connection.queries) # 书籍表，多对多字段的增加多条数据 book_obj = models.Book.objects.filter(name="三体").first() book_obj.authors.add(1, 2) print(connection.queries)
打印源sql:
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.001'},
{'sql': 'SELECT VERSION()', 'time': '0.000'},
{'sql': "SELECT `app02_book`.`id`, `app02_book`.`name`, `app02_book`.`price`, `app02_book`.`publish_date`, `app02_book`.`publish_id` FROM `app02_book` WHERE `app02_book`.`name` = '三体' ORDER BY `app02_book`.`id` ASC LIMIT 1", 'time': '0.001'},
{'sql': 'SELECT `app02_book_authors`.`author_id` FROM `app02_book_authors` WHERE (`app02_book_authors`.`book_id` = 1 AND `app02_book_authors`.`author_id` IN (1, 2))', 'time': '0.002'},
{'sql': 'INSERT INTO `app02_book_authors` (`book_id`, `author_id`) VALUES (1, 2)', 'time': '0.001'}]
9、Book表多对多字段，关联表添加多条数据，方法2
import os import sys from django.db import connection if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表，外键字段，数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #书籍表，外键字段，数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 # publish_obj = models.Publish.objects.filter( pk = 3).first() # models.Book.objects.filter(name="三体").update(publish = publish_obj) #书籍表，多对多字段的增加单条数据 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法1 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1, 2) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法2 # author_obj1 = models.Author.objects.filter(id=1).first() # author_obj2 = models.Author.objects.filter(id=2).first() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( author_obj1, author_obj2 ) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法3 author_queryset = models.Author.objects.all() book_obj = models.Book.objects.filter(name="三体").first() book_obj.authors.add( *author_queryset) print(connection.queries)
10、book表多对多字段，关联表删除数据remove方法
import os import sys from django.db import connection if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表，外键字段，数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #书籍表，外键字段，数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 # publish_obj = models.Publish.objects.filter( pk = 3).first() # models.Book.objects.filter(name="三体").update(publish = publish_obj) #书籍表，多对多字段的增加单条数据 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法1 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1, 2) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法2 # author_obj1 = models.Author.objects.filter(id=1).first() # author_obj2 = models.Author.objects.filter(id=2).first() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( author_obj1, author_obj2 ) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法3 # author_queryset = models.Author.objects.all() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( *author_queryset) # print(connection.queries) #删除书籍对象与作者的绑定关系 book_obj = models.Book.objects.filter(name="三体").first() book_obj.authors.remove(1,2)
11、 book表多对多字段，关联表删除数据clear方法
clear会删除所有绑定关系，不需要传入参数
import os import sys from django.db import connection if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表，外键字段，数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #书籍表，外键字段，数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 # publish_obj = models.Publish.objects.filter( pk = 3).first() # models.Book.objects.filter(name="三体").update(publish = publish_obj) #书籍表，多对多字段的增加单条数据 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法1 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1, 2) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法2 # author_obj1 = models.Author.objects.filter(id=1).first() # author_obj2 = models.Author.objects.filter(id=2).first() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( author_obj1, author_obj2 ) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法3 # author_queryset = models.Author.objects.all() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( *author_queryset) # print(connection.queries) #删除书籍对象与作者的绑定关系 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.remove(1,2) # 清空书籍对象与作者的所有绑定关系 book_obj = models.Book.objects.filter(name="三体").first() book_obj.authors.clear()
12、 book表多对多字段，关联表修改关联数据
import os import sys from django.db import connection if __name__ == "__main__": os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'kindeditor_pro.settings') import django django.setup() from app02 import models #书籍表，外键字段，数据的增加 #添加数据，方法1 # models.Book.objects.create( name = "白蛇",price= 66.66, publish_id = 1 ) #添加数据，方法2 # publish_obj = models.Publish.objects.filter( pk=3 ).first() # models.Book.objects.create( name="三体", price = 68.89, publish = publish_obj ) #书籍表，外键字段，数据的修改 #修改数据，方法1 # models.Book.objects.filter(name="三体").update(publish_id = 2) #修改数据，方法2 # publish_obj = models.Publish.objects.filter( pk = 3).first() # models.Book.objects.filter(name="三体").update(publish = publish_obj) #书籍表，多对多字段的增加单条数据 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法1 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add(1, 2) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法2 # author_obj1 = models.Author.objects.filter(id=1).first() # author_obj2 = models.Author.objects.filter(id=2).first() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( author_obj1, author_obj2 ) # print(connection.queries) # 书籍表，多对多字段的增加多条数据, 方法3 # author_queryset = models.Author.objects.all() # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.add( *author_queryset) # print(connection.queries) #删除书籍对象与作者的绑定关系 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.remove(1,2) # 清空书籍对象与作者的所有绑定关系 # book_obj = models.Book.objects.filter(name="三体").first() # book_obj.authors.clear() #修改书籍对象与作者的所有绑定关系 book_obj = models.Book.objects.filter(name="三体").first() book_obj.authors.set([2,]) #参数必须是可迭代对象； 执行的时候是先清空后添加；
执行后就只剩一条
