Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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.
On Thu, Sep 3, 2020, at 10:15 AM, Simon King wrote:
> 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
>
> On Thu, Sep 3, 2020 at 2:00 PM Nicolas Lykke Iversen
> wrote:
> >
> > 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 :
> >>
> >> 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
> >> 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 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
> >> >>
> >> >> 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
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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
On Thu, Sep 3, 2020 at 2:00 PM Nicolas Lykke Iversen wrote:
>
> 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 :
>>
>> 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
>> 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 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
>> >>
>> >> 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,
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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 :
> 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
> 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 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
> >>
> >> 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
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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 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 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
>>
>> 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,
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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 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
>
> 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
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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
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 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/CAFHwexewJhgRY6e_hQgFO48P-ac0HpfuwMgkjyx%3D8GPCa3LmXQ%40mail.gmail.com.
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
__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 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 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/020f3e4a-91e4-416c-90e1-382ea9ac3462%40www.fastmail.com.
[sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
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.
