How Do I Change The Schema For Both A Table And A Foreign Key?

I have the following simplified database access layer and two tables: class DataAccessLayer(): def __init__(self): conn_string = 'mysql+mysqlconnector://root:root@local

Solution 1:

You might wanna decorate your subclassed Base declarative model with the @declared_attr decorator.

Try this--

In a base class for your models, say __init__.py...

from sqlalchemy.ext.declarative import declarative_base, declared_attr


SCHEMA_MAIN = 'belgarath'# figure out how you want to retrieve this
SCHEMA_TEST = 'belgarath_test'class_Base(object):

    @declared_attrdef__table_args__(cls):
        return {'schema': SCHEMA_MAIN}

...
Base = declarative_base(cls=_Base)
Base.metadata.schema = SCHEMA_MAIN

Now that you have a Base that subclasses _Base with the main schema already defined, all your other models will subclass Base and do the following:

from . import Base, declared_attr, SCHEMA_TEST

classTestModel(Base):
    
    @declared_attrdef__table_args__(cls):
        return {'schema': SCHEMA_TEST}

Changing a schema for a foreign key could look like this:

classTournamentsATP(Base):
    __tablename__ = "tours_atp"
    __table_args__ = {"schema": "oncourt", "extend_existing": True}

    ID_T = Column(Integer, primary_key=True)
    NAME_T = Column(String(255))

    match_id = Column('match_id', Integer, ForeignKey(f'{__table_args__.get("schema")}.matches_atp.id'))

Where match_id is a foreign key to matches_atp.id by using the __table_args[schema] element defined at the class level via @declared_attr.

Solution 2:

Assuming your goal is to:

1. have dev/test/prod schemas on a single mysql host
2. allow your ORM classes to be flexible enough to be used in three different environments without modification

Then John has you most of the way to one type of solution. You could use @declared_attr to dynamically generate __table_args__ as he has suggested.

You could also consider using something like flask-sqlalchemy that comes with a built-in solution for this:

import os

    DB_ENV = os.getenv(DB_ENV)

    
    SQLALCHEMY_BINDS = {
        'belgarath': 'mysql+mysqlconnector://root:root@localhost/belgarath{}'.format(DB_ENV),
        'oncourt': 'mysql+mysqlconnector://root:root@localhost/oncourt{}'.format(DB_ENV)
    }
    
    classMatchesATP(Base):
        __bind_key__ = "belgarath"
        ID_M = Column(Integer, primary_key=True)
        ID_T_M = Column(Integer, ForeignKey("oncourt.tours_atp.ID_T"))
    
    
    classTournamentsATP(Base):
        __bind_key__ = "oncourt"
        ID_T = Column(Integer, primary_key=True)
        NAME_T = Column(String(255))

Basically this method allows you to create a link to a schema (a bind key), and that schema is defined at run-time via the connection string. More information at the flask-sqlalchemy link.