__table_args__ don't merge automatically right now for mixins so you would need to use a __table_args__ function with @declared_attr and merge the constraints manually. see https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#combining-table-mapper-arguments-from-multiple-mixins for background + example.
On Fri, Aug 28, 2020, at 5:35 AM, Nicolas Lykke Iversen 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/39315b84-f595-47af-adc4-2b4afa508c67n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/39315b84-f595-47af-adc4-2b4afa508c67n%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/020f3e4a-91e4-416c-90e1-382ea9ac3462%40www.fastmail.com.
