防止Django在MSSQL中更新身份列 [英] Prevent Django from updating identity column in MSSQL

问题描述

我正在使用MSSQL中的遗留数据库。我们有一张表，它有两列导致我问题的列：

  class Emp（models.Model）：
 empid = models.IntegerField（_（Unique ID），unique = True，db_column = u'EMPID'）
 ssn = models.CharField（_（Social security number），max_length = 10，primary_key =真，db_column = u'SSN'）＃字段名称小写。 
  

因此，该表将ssn列作为主键，并生成SQL更新代码的相关部分由django是这样的：

 更新[EMP] SET [EMPID] = 399，
 ...... ... 
 WHERE [EMP]。[SSN] = 2509882579 
  

问题是EMP.EMPID是MSSQL中的一个身份字段，因此，只要我尝试保存对现有员工的更改，pyodbc将抛出此错误：

 编程错误：（'42000'，[42000] [Microsoft] [SQL Native Client] [SQL Server] C 
注释更新身份列'EMPID'（8102）（SQLExecDirectW）; [42000] [Microsof 
t] [SQL Native Client] [SQL Server]声明无法准备（8180））
  

将EMP.EMPID作为标识对于任何程序来说并不重要，因此通过创建一个临时列和复制，删除，重命名来删除它似乎是一件合乎逻辑的事情。这样就可以将老客户转移到Django中，所以我的问题是，当我在这个表上进行更新时，有没有办法防止Django生成'[EMPID] = XXX'片段？

编辑

我已经修补了我的模型：

  def save（self，* args，** kwargs）：
 if self.empid：
 self._meta.local_fields = [f for f in self._meta .local_fields如果f.name！='empid'] 
  

这个工作，利用Django的方式在django / db / models / base.py（525）中填充它的sql-sentence。如果任何人有更好的方法或者可以解释为什么这是不好的做法，我很乐意听到它！

解决方案

是老的，Sindri找到一个可行的解决方案，但是我想提供一个解决方案，我已经在生产中使用了几年，不需要在 _meta

我必须编写一个与现有业务数据库集成的Web应用程序，其中包含许多计算字段。这些字段，通常计算记录的状态，几乎每个对象访问整个应用程序，Django必须能够与他们一起使用。

这些类型的字段可以与模型管理器一起工作，该模型管理器使用 extra（select = ...）向查询添加必填字段。

ComputedFieldsManager 代码段： https： //gist.github.com/manfre/8284698

  class Emp（models.Model）：
 ssn = models.CharField（_（Social security number），max_length = 10，primary_key = True，db_column = u'SSN'）＃字段名称小写。 
 
对象= ComputedFieldsManager（calculate_fields = ['empid']）
 
 
＃将empid添加到模型实例
 Emp.objects。 all（）[0] .empid 
 
＃您还可以在计算字段上搜索
 Emp.objects.all（）。computed_field_in（'empid'，[1234]）
  

I'm working with a legacy DB in MSSQL. We have a table that has two columns that are causing me problems:

class Emp(models.Model):  
    empid = models.IntegerField(_("Unique ID"), unique=True, db_column=u'EMPID')  
    ssn = models.CharField(_("Social security number"), max_length=10, primary_key=True, db_column=u'SSN') # Field name made lowercase.  

So the table has the ssn column as primary key and the relevant part of the SQL-update code generated by django is this:

UPDATE [EMP] SET [EMPID] = 399, 
......... 
WHERE [EMP].[SSN] = 2509882579 

The problem is that EMP.EMPID is an identity field in MSSQL and thus pyodbc throws this error whenever I try to save changes to an existing employee:

ProgrammingError: ('42000', "[42000] [Microsoft][SQL Native Client][SQL Server]C
annot update identity column 'EMPID'. (8102) (SQLExecDirectW); [42000] [Microsof
t][SQL Native Client][SQL Server]Statement(s) could not be prepared. (8180)")

Having the EMP.EMPID as identity is not crucial to anything the program, so dropping it by creating a temporary column and copying, deleting, renaming seems like the logical thing to do. This creates one extra step in transferring old customers into Django, so my question is, is there any way to prevent Django from generating the '[EMPID] = XXX' snippet whenever I'm doing an update on this table?

EDIT
I've patched my model up like this:

def save(self, *args, **kwargs):
    if self.empid:
        self._meta.local_fields = [f for f in self._meta.local_fields if f.name != 'empid']

This works, taking advantage of the way Django populates it's sql-sentence in django/db/models/base.py (525). If anyone has a better way or can explain why this is bad practice I'd be happy to hear it!

解决方案

This question is old and Sindri found a workable solution, but I wanted to provide a solution that I've been using in production for a few years that doesn't require mucking around in _meta.

I had to write a web application that integrated with an existing business database containing many computed fields. These fields, usually computing the status of the record, are used with almost every object access across the entire application and Django had to be able to work with them.

These types of fields are workable with a model manager that adds the required fields on to the query with an extra(select=...).

ComputedFieldsManager code snippet: https://gist.github.com/manfre/8284698

class Emp(models.Model):
    ssn = models.CharField(_("Social security number"), max_length=10, primary_key=True, db_column=u'SSN') # Field name made lowercase.

    objects = ComputedFieldsManager(computed_fields=['empid'])


# the empid is added on to the model instance
Emp.objects.all()[0].empid

# you can also search on the computed field
Emp.objects.all().computed_field_in('empid', [1234])

这篇关于防止Django在MSSQL中更新身份列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！