Is __table_args__ the only reason why you are creating separate modules for the different databases? You can specify parameters for different database dialects in __table_args__, and the ones that don't match the current engine will be ignored. For example:
######## import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class SomeTable(Base): __tablename__ = "sometable" __table_args__ = { "mysql_default_charset": "utf8", "mysql_engine": "InnoDB", "sqlite_autoincrement": True, } id = sa.Column(sa.Integer(), primary_key=True) name = sa.Column(sa.Text()) engine = sa.create_engine("sqlite:///", echo=True) Base.metadata.create_all(engine) ######## Simon On Fri, Aug 28, 2020 at 10:35 AM Nicolas Lykke Iversen <nlyk...@gmail.com> wrote: > > Hi all, > > I need to create identical models (mapped classes) for several database > backends, e.g. MySQL and MSSQL, that take different __table_args__. > > Thus, I've opted for created one base for each database backend defining the > __table_args__ (base.py), while using common mixins for defining the columns > (mixin.py). The bases and mixins are then combined in mssql.py and mysql.py > to create the models. > > The problem is that I don't know how to create a table-level composite > foreign-key constraint (ForeignKeyConstraint) by reading the following > documentation: > > https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-relationships > > Indeed, it can create column-level foreign-keys (ForeignKey), but defining > the ForeignKeyConstraint on any of the below classes yield errors, e.g.: > > class Project(): > id = Column(Integer, primary_key=True) > scan_id = Column(Integer, nullable=False) > ... > > class Project(Base, mixin.Project): > ForeignKeyConstraint(['project.scan_id'], ['stash_scan.id']) > > sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between > parent/child tables on relationship Scan.projects - there are no foreign keys > linking these tables. Ensure that referencing columns are associated with a > ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression. > > Is it not possible to use ForeignKeyConstraint with the base/mixin design I'm > using? > > SQLAlchemy Version: 1.3.17. > > base.py: > class SqlBase(): > @declared_attr > def __tablename__(cls): > return f'stash_{cls.__name__.lower()}' > > def __repr__(self): > return f'<{self.__class__.__name__}(id=\'{self.id}\')>' > > class MySqlBase(SqlBase): > __table_args__ = {'mysql_default_charset': 'utf8', > 'mysql_collate': 'utf8_bin'} > > class MsSqlBase(SqlBase): > __table_args__ = {} > > mixin.py: > class Project(): > id = Column(Integer, primary_key=True) > key = Column(Text, nullable=False) > name = Column(Text, nullable=False) > href = Column(Text, nullable=False) > > @declared_attr > def scan_id(cls): > return Column(Integer, ForeignKey('stash_scan.id', onupdate='CASCADE', > ondelete='CASCADE'), nullable=False) > > @declared_attr > def scan(cls): > return relationship('Scan', back_populates='projects') > > mssql.py: > Base = declarative_base(cls=db.MsSqlBase) > > class Scan(Base, mixin.Scan): > pass > > class Project(Base, mixin.Project): > pass > > mysql.py: > Base = declarative_base(cls=db.MySqlBase) > > class Scan(Base, mixin.Scan): > pass > > class Project(Base, mixin.Project): > pass > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/39315b84-f595-47af-adc4-2b4afa508c67n%40googlegroups.com. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexewJhgRY6e_hQgFO48P-ac0HpfuwMgkjyx%3D8GPCa3LmXQ%40mail.gmail.com.