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
<[email protected]> 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.
--
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/CAFHwexewJhgRY6e_hQgFO48P-ac0HpfuwMgkjyx%3D8GPCa3LmXQ%40mail.gmail.com.
