python - SQLAlchemy 连接表并在每一行中插入新字段

Question

所以我是 SQLAlchemy 和 Python 的新手，但对 MySQL 非常熟悉。我需要从两个不同的表 beam_data 和 beam_unbracedlengths 从 MySQL 数据库中提取数据以计算新值 lb，然后插入那个 lb 返回到 beam_data 作为附加字段。我已经继续使用 ORM 功能在 python 中构建我的对象，并具有正确的最终值 lb。我需要做的就是将其写回我的数据库。当我 session.commit() 时，我在数据库中没有得到任何肯定的结果。请帮忙。代码和原理图，如下:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import *
from sqlalchemy.dialects import mysql
import fnmatch
import math

engine = create_engine('mysql+mysqldb://user:pass@url:port/database')
connection = engine.connect()

# create a Session
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()
###################################################################
class beam(Base):
    __tablename__ = 'beam_data'
    BeamName = Column(String(50), primary_key=True)
    lb = Column(Integer)

    def __repr__(self):
        return "<beam(BeamName='%s', lb='%s')>" \
               % (self.BeamName, self.lb)

class bar(Base):
    __tablename__ = 'beam_unbracedlengths'
    BeamName = Column(String(50), primary_key=True)
    N1 = Column(String(50))
    N2 = Column(String(50))
    X = Column(mysql.DECIMAL(7, 3))
    Y = Column(mysql.DECIMAL(7, 3))
    Z = Column(mysql.DECIMAL(7, 3))
    X1 = Column(mysql.DECIMAL(7, 3))
    Y1 = Column(mysql.DECIMAL(7, 3))
    Z1 = Column(mysql.DECIMAL(7, 3))
    N1_grp = Column(String(100))
    N2_grp = Column(String(100))
    ElType = Column(String(3))
    # lb = Column(Integer)

    def __repr__(self):
        return "<bar(BeamName='%s', X=%s, Y=%s, Z=%s, X1=%s, Y1=%s, Z1=%s, " \
               "N1='%s', N2='%s, N1_grp='%s', N2_grp='%s, ElType='%s, lb='%s')>" \
               % (self.BeamName, self.X, self.Y, self.Z, self.X1, self.Y1, self.Z1,
                  self.N1, self.N2, self.N1_grp, self.N2_grp, self.ElType, self.lb)
###################################################################################

# give me all columns with bottom node braced
baseColumns = session.query(bar) \
    .filter(((bar.Z1 > bar.Z) & bar.N1_grp.like('%brc_strong%'))
            | ((bar.Z1 < bar.Z) & bar.N2_grp.like('%brc_strong%'))) \
    .filter(bar.ElType == 'COL') \
    .join(beam, bar.BeamName == beam.BeamName)
baseColumns = baseColumns.all()

for column in baseColumns:
    # initial length of column
    lb = math.sqrt((column.X1 - column.X)**2 + (column.Y1 - column.Y)**2 + (column.Z1 - column.Z)**2)
    topNodeGrp = ''
    columns = []

    # if the top node is found to be braced, break
    while not fnmatch.fnmatch(topNodeGrp, '*brc_strong*'):

        # keep track of all BeamNames composing our column
        columns.append(column)

        # store the current column name
        prevName = column.BeamName

        # which node is on top? and is it braced?
        if column.Z1 > column.Z:
            topNode = column.N2
            topNodeGrp = column.N2_grp
        else:
            topNode = column.N1
            topNodeGrp = column.N1_grp
        if fnmatch.fnmatch(topNodeGrp, '*brc_strong*'):
            break

        # if it isn't braced at the top, then find the column above it
        column = session.query(bar) \
            .filter(bar.N1.like(topNode) | bar.N2.like(topNode)) \
            .filter(bar.BeamName != prevName) \
            .filter(bar.ElType == 'COL') \
            .join(beam, bar.BeamName == beam.BeamName).first()

        # add the length from the column above to the previous column
        lb = lb + math.sqrt((column.X1 - column.X)**2 + (column.Y1 - column.Y)**2 + (column.Z1 - column.Z)**2)

    # write unbraced length value to all segments of column
    for segment in columns:
        segment.lb = lb
        session.add(segment)

session.commit()

Answer 1

所以我可以在代码中的最后一个 for 循环的底部附加两行:

# write unbraced length value to all segments of column
for segment in columns:
    segment.lb = lb
    session.add(segment)
    upd = update(beam_data).where(beam_data.c.BeamName == segment.BeamName).values(lb=lb)
    connection.execute(upd)

它确实有效，但这太费力了。我认为有一种更智能的方法可以将整个列写入表中，而不是更新单个值。