Dialect-specific table and column names in sqlalchemy while reusing an existing ORM mapping

Question

I use sqlalchemy's ORM library to interface with one of our application's databases. On one host, the database backend of an application instance is PostgreSQL, while on another, we use MySQL for historical reasons.

The schemas (and relationships) are identical on both backends, but the table and column names in PostgreSQL are lowercase, while on MySQL they are uppercase. Is there any way I can adapt my schema in sqlalchemy such that I don't need to duplicate code?

The following is an example of my schema. Hint, the application is a Confluence wiki ;)

from sqlalchemy import (
    BigInteger,
    Column,
    DateTime,
    String,
)
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Space(Base):
    __tablename__ = 'spaces'

    spaceid = Column(BigInteger, primary_key=True)
    spacename = Column(String(255))
    spacekey = Column(String(255), nullable=False, unique=True)
    lowerspacekey = Column(String(255), nullable=False, index=True)
    spacedescid = Column(BigInteger, index=True)
    homepage = Column(BigInteger, index=True)
    creator = Column(String(255), index=True)
    creationdate = Column(DateTime, index=True)
    lastmodifier = Column(String(255), index=True)
    lastmoddate = Column(DateTime)
    spacetype = Column(String(255))
    spacestatus = Column(String(255), index=True)

Can you auto load the tables? Maybe this is a solution stackoverflow.com/questions/9383837/… — rfkortekaas

E Y E Y · Accepted Answer · 2021-02-10T12:23:06

Thanks to @rfkortekaas' suggestion, I managed to solve the issue by automapping the schema and subsequently overriding the naming scheme for table and column names.

The documentation of sqlalchemy has two very useful pages:

For posterity's sake:

from sqlalchemy import event
from sqlalchemy.ext.automap import automap_base
from .utilities import to_pascal_case, to_snake_case


AutomapBase = automap_base()


@event.listens_for(AutomapBase.metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
    column_info['key'] = to_snake_case(column_info['name'])


def classname_for_table(base, table_name, table):
    return to_pascal_case(table_name)


if __name__ == '__main__':
    dbcs = 'postgresql://username:[email protected]:3306/database'
    automap_engine = create_engine(dbcs, future=True, echo=verbose > 2)
    AutomapBase.prepare(
        autoload_with=automap_engine,
        classname_for_table=classname_for_table,
    )

    # Access the automapped classes with AutomapBase.classes.<class_name>

Dialect-specific table and column names in sqlalchemy while reusing an existing ORM mapping

1 Answers