使用sqlalchemy中的计算列更新表 [英] update tables with computed columns in sqlalchemy

问题描述

我正在使用SQLalchemy在旧版MSSQL数据库中进行工作，对此我具有声明性映射.
该数据库有几个具有计算列的表.我可以很好地阅读它们，但是(当然)写入计算列是行不通的.但是，当我创建并尝试保存ORM对象时，SQLAlchemy仍尝试在这些列中保存无"值，从而导致错误.

I'm working in a legacy MSSQL database using SQLalchemy, for which I have a declarative mapping.
This database has several tables that have computed columns. I can read them fine, but (of course) writing to a computed column doesn't work. However, when I create and try to save an ORM object, SQLAlchemy still tries to save 'None' values in these columns, resulting in an error.

我在网上找到了一些示例，这些示例应该使用SQLAlchemy的hybrid_property装饰器将特定列设置为只读"，但是即使实现了该错误，仍然会出现相同的错误(The column "tlog_real_timehh" cannot be modified because it is either a computed column or is the result of a UNION operator.).

I have found some examples online that should make specific columns "read-only" by using SQLAlchemy's hybrid_property decorator, but even after implementing that, the same error (The column "tlog_real_timehh" cannot be modified because it is either a computed column or is the result of a UNION operator.) still comes up.

代码如下-映射:

class transactionlog(Base):
    __tablename__ = 'transactionlog'
    tlog_id = Column(VARCHAR(length=36), primary_key=True, nullable=False)
    tlog_ppl_id = Column(VARCHAR(length=36), ForeignKey('people.ppl_id'))
    tlog_evtt_id = Column(VARCHAR(length=5))
    tlog_testrun = Column(BIT())
    tlog_Data = Column(NVARCHAR(length=300))
    tlog_price = Column(DECIMAL(precision=18, scale=2))
    tlog_comment = Column(NVARCHAR(length=1000))
    _tlog_real_timehh = Column('tlog_real_timehh', INTEGER())
    _tlog_real_timemm = Column('tlog_real_timemm', INTEGER())
    _tlog_real_timess = Column('tlog_real_timess', INTEGER())
    _tlog_fin_booking = Column('tlog_fin_booking', BIT())

    @hybrid_property
    def tlog_real_timehh(self):
        return self._tlog_real_timehh

    @tlog_real_timehh.setter
    def tlog_real_timehh(self, tlog_real_timehh):
        self._tlog_real_timehh = tlog_real_timehh

    @hybrid_property
    def tlog_real_timemm(self):
        return self._tlog_real_timemm

    @tlog_real_timemm.setter
    def tlog_real_timemm(self, tlog_real_timemm):
        self._tlog_real_timemm = tlog_real_timemm

    @hybrid_property
    def tlog_real_timess(self):
        return self._tlog_real_timess

    @tlog_real_timess.setter
    def tlog_real_timess(self, tlog_real_timess):
        self._tlog_real_timess = tlog_real_timess

    @hybrid_property
    def tlog_fin_booking(self):
        return self._tlog_fin_booking

    @tlog_fin_booking.setter
    def tlog_fin_booking(self, tlog_fin_booking):
        self._tlog_fin_booking = tlog_fin_booking

以及应该添加新记录的代码:

and the code that should add a new record:

    rem = Transactionlog()
    rem.tlog_testrun = 0
    rem.tlog_evtt_id = 'rem'
    rem.tlog_Data = None
    rem.tlog_comment = 'reminder'
    rem.tlog_price = 0
    db.session.add(rem)
    db.session.flush()

我希望hybrid_property代码将计算的字段设为只读，但是似乎SQLAlchemy仍会尝试根据映射代码将其填充到INSERT语句中. (我在查看SQL语句时可以看到这一点.我无法发布该SQL语句，因为我在某种程度上将对象缩写为在StackOverflow上没有任何敏感数据.)

I would expect the hybrid_property code to make the computed fields read-only, but it seems that SQLAlchemy still tries to fill them in the INSERT statement, based on the mapping code. (I can see this when I look at the SQL statement. I cannot post the SQL statement, because I abbreviated the object somewhat to not have any sensitive data on StackOverflow).

问题是，为什么SQLAlchemy仍然尝试为tlog_real_timehh，tlog_real_timemm，tlog_real_timess和tlog_fin_booking插入值，并且如何防止这种情况发生?

The question is, why does SQLAlchemy still try to insert values for tlog_real_timehh, tlog_real_timemm, tlog_real_timess and tlog_fin_booking, and how can I prevent this?

感谢您可以给我任何指导.
埃里克

Thanks for any pointers you can give me.
Erik

推荐答案

标签 FetchedValue :

Label server-generated columns with a FetchedValue:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, autoincrement=False, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))
    fullname = Column(String(100), FetchedValue())

e = create_engine("mssql+pyodbc://scott:tiger@ms_2005", echo=True)
Base.metadata.drop_all(e)

e.execute("""
    CREATE TABLE a (
            id INTEGER PRIMARY KEY,
            firstname VARCHAR(50),
            lastname VARCHAR(50)
        )
""")
e.execute("ALTER TABLE a ADD fullname AS firstname + ' ' + lastname")

sess = Session(e)

sess.add_all([
    A(id=1, firstname='ed', lastname='jones'),
    A(id=2, firstname='wendy', lastname='smith'),
    A(id=3, firstname='jack', lastname='bean')
])
sess.commit()

assert [
    fname for fname, in
    sess.query(A.fullname).order_by(A.id)
] == ['ed jones', 'wendy smith', 'jack bean']


e.execute("DROP TABLE a")

这篇关于使用sqlalchemy中的计算列更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！