项目环境:Python 3.8+Django 1.11+MySQL5.7+Win 10
Model创建,(只要针对一对一跨表查询)
Model源代码,
from django.db import models
# 创建用户信息表
class UserOwner(models.Model): # 用户表
id =
models.AutoField(primary_key=True) # 自增id
name =
models.CharField(max_length=20)
sex = models.CharField(max_length=10)
def __str__(self):
return self.id
class Meta:
db_table = "test_userowner" # 创建数据库的表名
# 新建一对一关用户信息表拓展表,添加完成后执行数据库迁移同步操作
class ExtendUser(models.Model):
signature = models.CharField(max_length=255, verbose_name='用户签名', help_text="自建签名")
nickname = models.CharField(max_length=255, verbose_name='昵称', help_text="自建昵称")
user = models.OneToOneField(to='UserOwner')
def __str__(self):
return self.id
class Meta:
db_table = "test_extenduser" # 创建数据库的表名
生成的数据库表信息,
CREATE TABLE `test_userowner` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`sex` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `test_extenduser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`signature` varchar(255) NOT NULL,
`nickname` varchar(255) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`),
CONSTRAINT `test_extenduser_user_id_28802b1b_fk_test_userowner_id` FOREIGN KEY (`user_id`) REFERENCES `test_userowner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
对应数据库模型
插入数据:
INSERT INTO `test_userowner` VALUES ('1', '何权', '男');
INSERT INTO `test_userowner` VALUES ('2', '王毅', '女');
INSERT INTO `test_userowner` VALUES ('3', '卢勇', '男');
INSERT INTO `test_extenduser` VALUES ('1', '爱就一个字', '小泉泉', '1');
INSERT INTO `test_extenduser` VALUES ('2', '舍得', '依依', '2');
跨表查询
SELECT * from test_extenduser ex WHERE ex.user_id =(SELECT id FROM test_userowner u WHERE u.name = "何权")
QuerySet查询
正向(子表查父表)跟反向(父表需指定具体子表关键字)的区别
父表:自己用户表,userower
子表:扩展用户表,extenduser
开始QuerySet操作,
D:\softs\djangoapps\devops>python manage.py shell
In [1]: from twoapp.models import UserOwner, ExtendUser
In [2]: extendusers = ExtendUser.objects.all()
In [4]: for extend in extendusers:
...: print(extend.nickname,extend.signature,extend.user.name)
...:
以上是正向查询,下面进行反向查询。
反向必须跟filter结合使用
In [7]: userowner = UserOwner.objects.filter(name="何权")
In [8]: for userown in userowner:
...: print(userown.name, userown.extenduser.nickname)
...:
正向查询:通过扩展信息,找到对应用户
反向查询,通过用户找到其扩展信息
创建记录
In [15]: user_new = UserOwner(name="newnew")
In [16]: user_new.save()
In [17]: user_ext = ExtendUser(nickname="新测试狗狗", user=user_new)
In [18]: user_ext.save()
In [21]: print(ExtendUser.objects.filter(nickname="新测试狗狗").values("user__name"))
<QuerySet [{'user__name': 'newnew'}]>
如上,两种创建方式均可实现创建记录。
删除记录
In [32]: ExtendUser.objects.filter(nickname="testone测试测试").delete()
Out[32]: (1, {'twoapp.ExtendUser': 1})