操作sqlalchemy对象的最佳方法

Question

我正在使用sqlalchemy，希望通过一个表中的一组行循环添加两个计算行并将它们插入到第二个表中。我可以将row对象转换为字典并使用它：

def object_as_dict(obj):
    return {c.key: getattr(obj, c.key)
            for c in inspect(obj).mapper.column_attrs}

for row in session.query(source).limit(50):

    d = object_as_dict(row)
    del d['id'] # DON'T FORGET TO REMOVE ID FROM FIRST TABLE FROM DICT
    item = Filtered_Item()
    for k in d.keys():
        item[k] = d.get(k)

    item['calc1']=None
    item['calc2'] = None

但是我想知道是否有更直接的方法来处理sqlalchemy行对象？你知道吗

Answer 1

如果要在不序列化的情况下执行此操作，可以尝试以下操作：

for row in session.query(source).limit(50):
  session.add(models.MyOtherModel(val=foo(row.val),val2=bar(row.bal2))

我个人认为序列化/反序列化数据没有任何问题。我每天用类似的方法写2500万篇文章：

def serialze_sqlalchemy_data(model, items):
    headers = sqlalchemy_headers(model)

    for record in items:
        row = {str(k): str(v) for k, v in
           zip(headers, [getattr(record, header.lower(), str(header.lower())) for header in headers])}
        yield row

def sqlalchemy_headers(model):
    try:
        _headers = [col.name for col in model.__mapper__.columns]
    except AttributeError:
        _headers = [col for col in model.__dict__.keys()
                    if isinstance(model.__dict__[col], InstrumentedAttribute)]
    return _headers

你可以这样做：

def foo(row):
  row['val'] = 'taco'
  row['val2'] = 42
  return row

mymodel = models.MyModel
rows = session.query(mymodel)
rowdata = serialize_sqlalchemy_data(mymodel, rows)

table2 = models.MyModel2
session.bulk_insert_mappings(tables2, map(foo, rowdata))
session.commit()
session.close()