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 Den tor. 3. sep. 2020 kl. 11.00 skrev Simon King <si...@simonking.org.uk>: > 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 > > On Thu, Sep 3, 2020 at 5:38 AM Nicolas Lykke Iversen <nlyk...@gmail.com> > wrote: > > > > 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 > > > > On Tue, 1 Sep 2020 at 10.49, Simon King <si...@simonking.org.uk> wrote: > >> > >> 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 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+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 > . > >> > > -- > > 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/CAE%2B5CJBhrxVPzv70Egr%2BfqbQcqAz3B%3DHOQHjQJy984B-MiBkDg%40mail.gmail.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 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAFHwexd-DqhqskcP78vZsKPxS2JWGyBX7z1xoYiScsOTyivyrg%40mail.gmail.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/CAE%2B5CJAExsNA%2BcuoFZMdQi7CYfo2CLb9rxGPnymUgBGbjtSCKA%40mail.gmail.com.
