How to redefine tables with the same name in SQLAlchemy using Classical Mapping

Question

I am using SQLAlchemy classical mapping to define a table with the same name but different columns depending on the db, I have mapped the class as it's explained on docs, but I am getting errors every single time I try to redefine the class for another database. For instance:

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

The error I get:

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

How is this happening if I am using completely different instances of MetaData and engines? Is there a way to achieve this?

Answer 1

Using the default_cols variable was actually the problem, seems like this kind of setup doesn't work unless the columns are defined individually on each function:

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)

Otherwise it will raise the Exception:

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