python数据库中计算_python – 更新sqlalchemy中具有计算列的表

我正在使用SQLalchemy在遗留的MSSQL数据库中工作,我有一个声明性映射.

该数据库有几个具有计算列的表.我可以读得很好,但(当然)写入计算列不起作用.但是,当我创建并尝试保存ORM对象时,SQLAlchemy仍尝试在这些列中保存“无”值,从而导致错误.

我在网上发现了一些应该通过使用SQLAlchemy的hybrid_property装饰器使特定列“只读”的示例,但即使在实现之后,也会出现同样的错误(“tlog_real_timehh”列无法修改,因为它是计算列或者是UNION操作符的结果.)仍然出现.

代码如下 – 映射：

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

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

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上没有任何敏感数据).

问题是,为什么SQLAlchemy仍然尝试为tlog_real_timehh,tlog_real_timemm,tlog_real_timess和tlog_fin_booking插入值,我该如何防止这种情况？

感谢您给我的任何指示.

埃里克

解决方法:

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")

标签：python,orm,sqlalchemy

来源： https://codeday.me/bug/20190629/1327454.html