在django项目中使用遗留数据库中的复合主键 [英] Working with composite primary key in django project with legacy database
问题描述
manage.py inspectdb 命令得到的模型看起来像这样。 class MyTable（models.Model）：
field1_id = models.IntegerField（db_column ='field1id'，primary_key = True）
is_favorite = models.BooleanField（db_column ='isfavorite'，default = False， null = False）
is_admin = models.BooleanField（db_column ='isadmin'，default = False，null = False）
role = models.IntegerField（default = USER_GUEST，null = False）
field2 = models.BooleanField（null = False，default = True）
field3 = models.BooleanField（null = False，default = True）
is_active = models.BooleanField（db_column ='isactive'，null = False，default = True）
user = models.ForeignKey（
CustomUser，models.DO_NOTHING，db_column ='userid'，primary_key = True）
class Meta ：
managed = False
db_table ='mytable'
unique_together =（（'user'，'field1_id'），）
我可以正常获取数据。但是，当我想在某些模型实例上运行 save（）命令时，会出现此问题。查询django执行不正确。例如：
>>>来自web_services.apps.my_app.models import MyTable
>>> g = MyTable.objects.get（field1_id = 12）
>>> g.is_active = True
>>> g.save（）
>>> connection.queries [-1]
{'time'：'0.000'，'sql'：'UPDATE`mytable` SET`isfavorite` = 0，`isadmin` = 1，`role` = 3，`field2 `= 1，`field3` = 1，`isactive` = 1 WHERE`mytable`.`field1id` = 12'}
但我需要：
{'time'：'0.000'，'sql'：'UPDATE` mytable` SET`isfavorite` = 0，`isadmin` = 1，`role` = 3，`field2` = 1，`field3` = 1，`isactive` = 1 WHERE`mytable`.`field1id` = 12'AND `mytable`.`userid` = 1'}
由于django不支持复合主键，将是解决这个问题的最佳解决方案？注意，它是旧数据库表，它没有 AutoField 。
编辑：添加传统表结构
+ ------------ + ------- ----- + ------ + ----- + --------- + ------- +
| Field |类型|空|关键|默认|额外|
+ ------------ + ------------ + ------ + ----- + ------ --- + ------- +
| userid | int（11）| NO | PRI | NULL | |
| field1id | int（11）| NO | PRI | NULL | |
| isfavorite | int（11）| NO | | 0 | |
| isadmin | int（11）| NO | | 0 | |
|角色| int（11）|是| | NULL | |
| field2 | tinyint（1）| NO | | 1 | |
| field3 | tinyint（1）| NO | | 1 | |
|不活跃| tinyint（4）| NO | | 1 | |
+ ------------ + ------------ + ------ + ----- + ------ --- + ------- +
不幸的是，django做了还没有复合主键（复合主键也称为多列主键键）
有一个第三方库，不出意料的名字是 django-compositekey ，可以创建具有多列主键的模型。但是，该项目现在维护并且与最新版本的django不兼容。
最好的选择是在您的表中添加一个新列，这是一个AutoField，使其成为新的主要关键。现在构成主键的字段可以标记为 unique_together 。虽然这可能不是理想的几个查询。对于大多数人来说，这是很好的。
如果您使用当前的表格结构更新您的问题（如您的sql控制台所示），我将更新我的答案，以显示该模型将看起来像。
更新
有很多不同的方式可以删除旧的复合主键，并将其替换为一个新的自动增量主键。这是最简单的，它只涉及SQL
CREATE TABLE newtable LIKE mytable;
ALTER TABLE newtable DROP PRIMARY KEY;
ALTER TABLE newtable ADD`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
RENAME TABLE mytable to mytable_old;
RENAME TABLE newtable to mytable;
INSERT INTO mytable（userid，field1id，其余字段）
SELECT * FROM mytable_old;
然后编辑您的模型，并从这些字段中删除primary_key = True标志。
脚注：
例如sqlite等某些数据库不支持 LIKE 子句在 CREATE TABLE 中。在这些情况下，您将必须查找原始表的create table语句，并在编辑表名后复制粘贴。根据您的表格结构，您似乎正在使用mysql，并支持 LIKE 子句。
I have a legacy database, where some table contains composite primary key. The model I get by running manage.py inspectdb command looks like this.
class MyTable(models.Model):
field1_id = models.IntegerField(db_column='field1id', primary_key=True)
is_favorite = models.BooleanField(db_column='isfavorite', default=False, null=False)
is_admin = models.BooleanField(db_column='isadmin', default=False, null=False)
role = models.IntegerField(default=USER_GUEST, null=False)
field2 = models.BooleanField(null=False, default=True)
field3 = models.BooleanField(null=False, default=True)
is_active = models.BooleanField(db_column='isactive', null=False, default=True)
user = models.ForeignKey(
CustomUser, models.DO_NOTHING, db_column='userid', primary_key=True)
class Meta:
managed = False
db_table = 'mytable'
unique_together = (('user', 'field1_id'),)
I can fetch data normally. However, the problem arises when I want to run save() command on some model instance. The query django executes is not correct one. For example:
>>> from web_services.apps.my_app.models import MyTable
>>> g = MyTable.objects.get(field1_id=12)
>>> g.is_active = True
>>> g.save()
>>> connection.queries[-1]
{'time': '0.000', 'sql': 'UPDATE `mytable` SET `isfavorite` = 0, `isadmin` = 1, `role` = 3, `field2` = 1, `field3` = 1, `isactive` = 1 WHERE `mytable`.`field1id` = 12'}
But I need:
{'time': '0.000', 'sql': 'UPDATE `mytable` SET `isfavorite` = 0, `isadmin` = 1, `role` = 3, `field2` = 1, `field3` = 1, `isactive` = 1 WHERE `mytable`.`field1id` = 12' AND `mytable`.`userid` = 1'}
Since django does not support composite primary key, what would be the best solution to overcome that problem? Note, it's legacy database table and it didn't have AutoField.
EDIT: adds legacy table structure
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| userid | int(11) | NO | PRI | NULL | |
| field1id | int(11) | NO | PRI | NULL | |
| isfavorite | int(11) | NO | | 0 | |
| isadmin | int(11) | NO | | 0 | |
| role | int(11) | YES | | NULL | |
| field2 | tinyint(1) | NO | | 1 | |
| field3 | tinyint(1) | NO | | 1 | |
| isactive | tinyint(4) | NO | | 1 | |
+------------+------------+------+-----+---------+-------+
Unfortunately django does not yet have a composite primary key (composite primary keys are also called multi column primary keys)
There is a third party library, unsurprisingly name django-compositekey that makes it possible to create a model that has a multi column primary key. However that project is now maintained and not compatible with the latest versions of django.
Your best bet, is to add a new column into your table which is a an AutoField and make it the new primary key. The fields that make up the primary key at present can then be marked as unique_together. While this may not be ideally for a few queries. It's good enough for most.
If you update your question with the current table structure (as shown in your sql console) I will update my answer to show what the model will look like.
Update There are lots of different ways in which you can drop the old composite primary key and replace it with a new auto increment primary key. Here is the easiest, it involves just SQL
CREATE TABLE newtable LIKE mytable;
ALTER TABLE newtable DROP PRIMARY KEY;
ALTER TABLE newtable ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
RENAME TABLE mytable to mytable_old;
RENAME TABLE newtable to mytable;
INSERT INTO mytable(userid,field1id, rest of the fields)
SELECT * FROM mytable_old;
Then edit your model and remove the primary_key=True flag from those fields.
Footnote:
Some databases like sqlite for example does not support the LIKE clause in a CREATE TABLE. In these cases, you will have to look up the create table statement for the original table, and copy paste after editing the table name. Judging by your table structure you seem to be using mysql and it supports the LIKE clause.
这篇关于在django项目中使用遗留数据库中的复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
