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

user_3876830 0
Viewed 868

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?

flask-sqlalchemy sqlalchemy sql-server
2 Answers

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'

user_38768300

I couldn't reproduce your error. To get the code to work I had to swap the order of the Mapper arguments and add primary keys to the tables definitions. More significantly perhaps, I had to set one of the mappers as non-primary after getting this error:

sqlalchemy.exc.ArgumentError: Class '<class '__main__.MyTable'>' already has a primary
mapper defined. Use non_primary=True to create a non primary Mapper.  clear_mappers() 
will remove *all* current mappers from all classes.

from sqlalchemy import Table, MetaData, String, Column, create_engine, Integer
from sqlalchemy.orm import mapper


class MyTable(object):
    def __init__(self, *args, **kwargs):
        [setattr(self, k, v) for k, v in kwargs.items()]
        
        
def myfunc1():
    engine = create_engine("mysql+pymysql:///test")
    metadata = MetaData() 
    mytable = Table(
        "mytable111",
        metadata,
        Column("id", Integer, primary_key=True),
        Column("column1", String(20)),
        Column("column2", String(20)),
    )
    mapper(MyTable, mytable)
    metadata.create_all(bind=engine)
        
        
def myfunc2():
    engine = create_engine("postgresql+psycopg2:///test")
    metadata = MetaData() 
    mytable = Table(
        "mytable111",
        metadata,
        Column("id", Integer, primary_key=True),
        Column("column1", String(20)),
        Column("column2", String(20)),
        Column("column3", String(20)),
    )
    mapper(MyTable, mytable, non_primary=True)
    metadata.create_all(bind=engine)


myfunc1()
myfunc2()

Using Python3.8, SQLAlchemy 1.3.10.

user_53209060
Related