SQLAlchemy + Flask managing several databases and migration

Question

I need to write an application using SQLAlchemy + Flask AppBuilder. Currently I use 2 databases and it works well(with SQLALCHEMY_BINDS in config, separate metadata and bind_key parameter).

When I am trying to add tables with the same names to the different databases the error occured:

'sqlalchemy.exc.InvalidRequestError: Table 'name_1' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.'

How can I manage the problem with 2 databases? Will be there any problems with Alembic using 2 databases simultaneously?

score 0 · Answer 1 · answered Aug 24 '23 at 20:43

Can you add your code? Hard to tell what is causing it without seeing what initiates it and what the Model looks like. But you are probably defining the same table in both databases.

When you define a table in SQLAlchemy, you associate it with a MetaData object. If you define the same table in two different MetaData objects, you'll get this error message when you try to create the tables.

You can define the table in one MetaData object and then use it in both databases. Like this:

class MyTable(Model):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    other_table_id = Column(Integer, ForeignKey('other_table.id'))
    other_table = relationship('OtherTable')

# Define the table in one MetaData object
metadata = MetaData()
metadata.reflect(bind=engine1)
Table('other_table', metadata, autoload=True, autoload_with=engine1)

# Use the table in both databases
class MyTable1(MyTable):
    __bind_key__ = 'db1'

class MyTable2(MyTable):
    __bind_key__ = 'db2'

Again, hard to tell without seeing the code that is causing the problem.

SQLAlchemy + Flask managing several databases and migration

1 Answers1