sql-server - 如何使用经典映射在 SQLAlchemy 中重新定义同名表

Question

我正在使用 SQLAlchemy classical mapping要根据数据库定义具有相同名称但不同列的表，我已经按照文档中的说明映射了该类，但是每次尝试为另一个数据库重新定义该类时，我都会遇到错误。例如:

from sqlalchemy import (Table, MetaData, String, Column)
from sqlalchemy.orm import mapper


class MyTable(object):
    def __init__(self, *args, **kwargs):
        [setattr(self, k, v) for k, v in kwargs.items()]


default_cols = (
    Column('column1', String(20), primary_key=True),
    Column('column2', String(20))
)


def myfunc1():
    engine = create_engine('connection_to_database1')
    session = sessionmaker(bind=engine)()
    metadata = MetaData()
    mytable = Table('mytable', metadata, *default_cols)
    mapper(MyTable, mytable)
    metadata.create_all(bind=engine)

def myfunc2():
    engine = create_engine('connection_to_database2')
    session = sessionmaker(bind=engine)()
    metadata = MetaData()
    columns =  list(default_cols) + [Column('column3', String(20))]
    mytable = Table('mytable', metadata, *columns)
    mapper(MyTable, mytable)
    metadata.create_all(bind=engine)


myfunc1()
myfunc2()

我得到的错误:

Column object 'column1' already assigned to Table 'mytable'

如果我使用完全不同的 MetaData 和引擎实例，这是如何发生的？有没有办法实现这一目标？

Answer 1

使用 default_cols 变量实际上是问题所在，除非在每个函数上单独定义列，否则这种设置似乎不起作用:

def myfunc1():
    engine = create_engine('connection_to_database1')
    session = sessionmaker(bind=engine)()
    metadata = MetaData()
    mytable = Table('mytable', metadata, 
        Column('column1', String(20), primary_key=True),
        Column('column2', String(20))
    )
    mapper(MyTable, mytable)
    metadata.create_all(bind=engine)

def myfunc2():
    engine = create_engine('connection_to_database2')
    session = sessionmaker(bind=engine)()
    metadata = MetaData()
    columns =  [
        Column('column1', String(20), primary_key=True),
        Column('column2', String(20),
        Column('column3', String(20))
    ]
    mytable = Table('mytable', metadata, *columns)
    mapper(MyTable, mytable)
    metadata.create_all(bind=engine)

否则它会引发异常: Column object 'column1' already assigned to Table 'mytable'