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 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/CAFHwexd-DqhqskcP78vZsKPxS2JWGyBX7z1xoYiScsOTyivyrg%40mail.gmail.com.
