How can I use a composite foreign-key constraint with a "mixin" class using declarative?

[Nicolas Lykke Iversen]

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

[Mike Bayer]

__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.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/39315b84-f595-47af-adc4-2b4afa508c67n%40googlegroups.com.

[Simon King]

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

[Nicolas Lykke Iversen]

Thank you, Simon.

Yes, __table_args__ is the only reason I’m creating separate modules.

Where do you see that arguments that don’t match the database dialect of the engine get ignored? I looked at the source code for answering this question myself, but couldn't find an answer to that question.

Will constructs like table-level foreign keys also just get ignored for databases that don’t support them?

In what scenarios would it make sense to use multiple modules, like I do?

If possible, please provide a link to relevant part of the documentation, I’m eager to learn more.

Best wishes

Nicolas

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/MYGuVZ51G60/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexewJhgRY6e_hQgFO48P-ac0HpfuwMgkjyx%3D8GPCa3LmXQ%40mail.gmail.com.

[Simon King]

To be honest, I looked for documentation before I wrote my reply to
you, and couldn't find anything. I just know that I often use MySQL in
production but sqlite for tests, and sqlite never complained about the
mysql-specific options in the table args.

dialect-specific options end up in table.dialect_options:

https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options

But different dialects seem to handle them in different ways. For
example, here's how mysql consumes table-level options:

https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L1871

whereas sqlite does this:

https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/sqlite/base.py#L1120

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJBhrxVPzv70Egr%2BfqbQcqAz3B%3DHOQHjQJy984B-MiBkDg%40mail.gmail.com.

[Nicolas Lykke Iversen]

Thanks Simon,

Just to be clear: 

dialect-specific options end up in table.dialect_options:
https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
But different dialects seem to handle them in different ways.

Does this mean that it's not safe in general to mix dialect-specific options in __table_args__, since it's arbitrary how they are handled? Indeed, in your case it works (MySQL and SQLite), but that might not be the case for other dialects like MSSQL?

Best regards

Nicolas

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexd-DqhqskcP78vZsKPxS2JWGyBX7z1xoYiScsOTyivyrg%40mail.gmail.com.

[Simon King]

You'd have to wait for a response from Mike to be certain, but it
seems overwhelmingly likely to me that dialect-specific options will
always be ignored by other dialects. One of the strengths of
SQLAlchemy is that it makes it easier to write code that works against
multiple database dialects, so I can't see that changing.

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJAExsNA%2BcuoFZMdQi7CYfo2CLb9rxGPnymUgBGbjtSCKA%40mail.gmail.com.

[Mike Bayer]

dialect specific options are prefixed with the dialect name,e.g. "mysql_engine", so that they only take place for the dialect currently interacting with the schema object.   they are ignored by any dialect that does not have that name.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcVeJR1R4aWKiAQLYfBHczkZiM9wAqxJnGkotjLF-jhvg%40mail.gmail.com.