Re: [sqlalchemy] ORM AmbiguousForeignKeysErro
Thank you, so that go into each subclass that would have the problem.
8/28/20 2:37 PM, Mike Bayer wrote:
> the argument you're looking for is inherit_condition:
>
> https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
>
>
> class Foo(...):
> __mapper_args__ = {
> "inherit_condition": node_id == Node.node_id
> }
>
--
Richard Damon
--
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/a529b33e-4675-e94f-e937-c39a651a6e0a%40Damon-Family.org.
Re: [sqlalchemy] ORM AmbiguousForeignKeysErro
the argument you're looking for is inherit_condition:
https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherit_condition
class Foo(...):
__mapper_args__ = {
"inherit_condition": node_id == Node.node_id
}
On Fri, Aug 28, 2020, at 1:45 PM, Richard Damon wrote:
> Following code and error I am getting:
>
>
> class Base:
> """Base Class for SQLAlchemy ORM Classes"""
> @declared_attr
> def __tablename__(cls):
> """Default the Table Name to the Class Name"""
> return cls.__name__
>
>
> Base = declarative_base(cls=Base)
>
>
> class Node(Base):
> """Class represents the base of the User Data types."""
>
> node_id = Column(Integer, primary_key=True)
> type_name = Column(String(255), nullable=False) # todo should come
> from type_id
>
> __mapper_args__ = {
> 'polymorphic_identity': 'Node',
> 'polymorphic_on': type_name,
> }
>
>
> class Property(Node):
> """Class represents a Node providing information about another Node"""
> node_id = Column(Integer, ForeignKey('Node.node_id'), primary_key=True)
> ref_id = Column(Integer, ForeignKey('Node.node_id'))
>
> __mapper_args__ = {
> 'polymorphic_identity': 'Property',
> }
>
> Error:
>
> sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
> 'Node' and 'Property'; tables have more than one foreign key constraint
> relationship between them. Please specify the 'onclause' of this join
> explicitly.
>
>
> Other classes driving from node don't have this issue, but then they
> don't have that second foreign key back to node. Can't figure out how to
> sepecify the onclause, since I don't explicitly give the join., and my
> searching-foo isn't finding anything on this. I suspect somewhere,
> likely in the __mapper_args__ I need to specify the field to join on,
> but can't find it.
>
>
> On a somewhat related note, for purposes of DRY, since all of the
> subclasses have the same node_id declaration, and basically the same
> __mapper__ is there a way I can add this to Node to push this into the
> subclasses? Would I use a @declared_attr, which would need to check if
> the type was Node since it is different?
>
> (Background, long time programmer, but somewhat new to python, looking
> to learn how with reasons, not just rote recipes to follow)
>
> --
> Richard Damon
>
> --
> 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/bd6dcf30-f6ee-308d-284f-14aeef4505ae%40Damon-Family.org.
>
--
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/de75f770-73f0-42ac-84c6-7caeef218ab5%40www.fastmail.com.
[sqlalchemy] ORM AmbiguousForeignKeysErro
Following code and error I am getting:
class Base:
"""Base Class for SQLAlchemy ORM Classes"""
@declared_attr
def __tablename__(cls):
"""Default the Table Name to the Class Name"""
return cls.__name__
Base = declarative_base(cls=Base)
class Node(Base):
"""Class represents the base of the User Data types."""
node_id = Column(Integer, primary_key=True)
type_name = Column(String(255), nullable=False) # todo should come
from type_id
__mapper_args__ = {
'polymorphic_identity': 'Node',
'polymorphic_on': type_name,
}
class Property(Node):
"""Class represents a Node providing information about another Node"""
node_id = Column(Integer, ForeignKey('Node.node_id'), primary_key=True)
ref_id = Column(Integer, ForeignKey('Node.node_id'))
__mapper_args__ = {
'polymorphic_identity': 'Property',
}
Error:
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'Node' and 'Property'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Other classes driving from node don't have this issue, but then they
don't have that second foreign key back to node. Can't figure out how to
sepecify the onclause, since I don't explicitly give the join., and my
searching-foo isn't finding anything on this. I suspect somewhere,
likely in the __mapper_args__ I need to specify the field to join on,
but can't find it.
On a somewhat related note, for purposes of DRY, since all of the
subclasses have the same node_id declaration, and basically the same
__mapper__ is there a way I can add this to Node to push this into the
subclasses? Would I use a @declared_attr, which would need to check if
the type was Node since it is different?
(Background, long time programmer, but somewhat new to python, looking
to learn how with reasons, not just rote recipes to follow)
--
Richard Damon
--
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/bd6dcf30-f6ee-308d-284f-14aeef4505ae%40Damon-Family.org.
[sqlalchemy] Re: Update multiple rows in SQLite Databse
I believe your error is tied to this section of code: > for item in ingredDict: > ingredient_item = Ingredients(ingredientKey=item['ingredientKey'], > > ingredientDescription=item['ingredientDescription'], > ingredientRecipeKey=recipeKey, > > ingredientQuantity=item['ingredientQuantity']) > Ingredients_item_object_list.append(ingredient_item) It looks like you are iterating through this dict, creating new ingredients, and adding them to the recipe/database. This is fine for CREATE, but is raising an integrity error on UPDATE because the ingredients already exist and you are creating a new entry on every iteration. A lazy way to address this would be something like: remove all the existing ingredients, flush, then run this loop. A common way to handle this is the bit of Mike's suggestion which you missed: calculate the differences between the set of old and new items to determine which ingredients need to be added or removed (or updated, as that seems possible now). Within the Unit of Work, as mike said, you need to delete and add (and also update it would seem). -- 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/36add23c-b1c5-4c6a-a494-d9d71addc1a8o%40googlegroups.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.
