On 05/15/2017 09:27 PM, Никита Крокош wrote:
So, how should i do if I need many encumbrances for every cadastral
object (flat.encumrances, building.encumbrances, ...etc).
If I get it correctly, I need something like:
class iCadastralObject(Base):
__abstract__ =True
def __init__(self, cadastral_region, cadastral_district, cadastral_block,
cadastral_object):
self.cadastral_region = cadastral_region
self.cadastral_district = cadastral_district
self.cadastral_block = cadastral_block
self.cadastral_object = cadastral_object
# this is a combined PK
cadastral_region = Column(Integer, primary_key=True, index=True)
cadastral_district = Column(Integer, primary_key=True, index=True)
cadastral_block = Column(Integer, primary_key=True, index=True)
cadastral_object = Column(Integer, primary_key=True, index=True)
@declared_attr
def encumbrances(self):
return relationship("iEncumbrance")
class Building(iCadastralObject):
__tablename__ ='building'
class Flat(iCadastralObject):
__tablename__ ='flat'
class Construction(iCadastralObject):
__tablename__ ='construction'
class iEncumbrance(Base):
__tablename__ ='iencumbrance'
id = Column(Integer, primary_key=True, index=True)
def __init__(self, cadastral_object):
self.parent_cadastral_region = cadastral_object.cadastral_region
self.parent_cadastral_district = cadastral_object.cadastral_district
self.parent_cadastral_block = cadastral_object.cadastral_block
self.parent_cadastral_object = cadastral_object.cadastral_object
# FK fields
parent_cadastral_region = Column(Integer, nullable=False)
parent_cadastral_district = Column(Integer, nullable=False)
parent_cadastral_block = Column(Integer, nullable=False)
parent_cadastral_object = Column(Integer, nullable=False)
What is the code I need to make Flat.encumbrances, etc work. Should I
write custom query in a iCadastralObject class? Something like
encumbrances = session.query(iEncumbrance).__filter__...?
I guess the example is pretty straightforward, can you supply me with
some code I should use.
And if I have for example 10 child classes, in this cade i will have a
lot of boilerplate code right?
OK so lets look at the tables. you have these three (and more) tables
"building" "flat" "construction". all of them can have a collection of
"encumbrances". However the tables don't otherwise share anything (e.g.
a common table they all join on or something). We call this the
"polymorphic association" or more recently the "generic association"
pattern. In frameworks like Django, they do this using a thing they
call "GenericForeignKey". I've written a lot about this, and in
SQLAlchemy we have an example suite illustrating a variety of recipes
for implementing this - see
http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.generic_associations.
My favorite pattern here is "table per association":
http://docs.sqlalchemy.org/en/latest/_modules/examples/generic_associations/table_per_association.html
. This would let you keep all the encumbrances in one big table, but
makes querying from each of your "building" etc. tables very simple.
People are afraid of this pattern because it creates a lot of tables.
But we aren't dealing with those manually, the maintenance of tables is
automated by SQLAlchemy (and potentially Alembic if you're using that too).
вторник, 16 мая 2017 г., 9:41:02 UTC+10 пользователь Mike Bayer написал:
when you use concrete inheritance, you now have three tables: building,
flat, construction. If you'd like these to each have a
relationship to
iencumbrance, that's three separate foreign key constraints. Given the
four-column primary key, you'd need to have twelve columns total on
iencumbrance and three constraints. You cannot have a relationship
from the "abstract" class alone, this class does not actually
correspond
to any table.
Overall, if you do not need to emit a query of this form:
objects = session.query(iCastradalObject).filter(...).all()
then you should not use concrete inheritance. you should map
building /
flat / construction alone. if you'd like them to all have common
features, use iCastradalObject as you are and add the __abstract__
flag,
don't actually use AbstractConcreteBase. Concrete inheritance is
almost never used.
On 05/15/2017 07:03 PM, Никита Крокош wrote:
> This is a duplicate from:
>
http://stackoverflow.com/questions/43972912/abstract-table-concrete-inheritance-could-not-determine-join-condition-between
<http://stackoverflow.com/questions/43972912/abstract-table-concrete-inheritance-could-not-determine-join-condition-between>
> I've got following example code:
>
> models.py
> |
> classCadastralObject(Base):
> __tablename__ ='cadastral_object'
>
def__init__(self,cadastral_region,cadastral_district,cadastral_block,cadastral_object):
> self.cadastral_region =cadastral_region
> self.cadastral_district =cadastral_district
> self.cadastral_block =cadastral_block
> self.cadastral_object =cadastral_object
>
> # this is a combined PK
> cadastral_region
=Column(Integer,primary_key=True,index=True)
> cadastral_district
=Column(Integer,primary_key=True,index=True)
> cadastral_block
=Column(Integer,primary_key=True,index=True)
> cadastral_object
=Column(Integer,primary_key=True,index=True)
>
> encumbrances =relationship("Encumbrance")
>
> classEncumbrance(Base):
> __tablename__ ='encumbrance'
> id =Column(Integer,primary_key=True,index=True)
> def__init__(self,cadastral_object):
> self.parent_cadastral_region =cadastral_object.cadastral_region
> self.parent_cadastral_district =cadastral_object.cadastral_district
> self.parent_cadastral_block =cadastral_object.cadastral_block
> self.parent_cadastral_object =cadastral_object.cadastral_object
>
> # FK fields
> parent_cadastral_region =Column(Integer,nullable=False)
> parent_cadastral_district =Column(Integer,nullable=False)
> parent_cadastral_block =Column(Integer,nullable=False)
> parent_cadastral_object =Column(Integer,nullable=False)
>
> parent_object =relationship(CadastralObject)
> __table_args__ =(ForeignKeyConstraint(
> [
> parent_cadastral_region,
> parent_cadastral_district,
> parent_cadastral_block,
> parent_cadastral_object],
> [
> CadastralObject.cadastral_region,
> CadastralObject.cadastral_district,
> CadastralObject.cadastral_block,
> CadastralObject.cadastral_object]),
> {}
> )
> |
>
> this code works as intended:
>
> main.py
>
> |
> c =CadastralObject(1,2,3,4)
> session.add(c)
> e =Encumbrance(c)
> session.add(e)
> session.commit()
> print(c.encumbrances)
> print(e.parent_object)
> |
>
>
> results:
>
> |
> [<app.models.Encumbranceobjectat 0x000001C9B820BCC0>]
> <app.models.CadastralObjectobjectat 0x000001C9B820BB00>
> |
>
>
> however, when I'm trying convert my code to Concrete Inheritance:
>
> imodels.py
>
> |
> classiCadastralObject(AbstractConcreteBase,Base):
>
>
def__init__(self,cadastral_region,cadastral_district,cadastral_block,cadastral_object):
> self.cadastral_region =cadastral_region
> self.cadastral_district =cadastral_district
> self.cadastral_block =cadastral_block
> self.cadastral_object =cadastral_object
>
> # this is a combined PK
> cadastral_region
=Column(Integer,primary_key=True,index=True)
> cadastral_district
=Column(Integer,primary_key=True,index=True)
> cadastral_block
=Column(Integer,primary_key=True,index=True)
> cadastral_object
=Column(Integer,primary_key=True,index=True)
>
> @declared_attr
> defencumbrances(self):
> returnrelationship("iEncumbrance")
>
>
>
> classBuilding(iCadastralObject):
> __tablename__ ='building'
>
> __mapper_args__ ={
> 'polymorphic_identity':'building',
> 'concrete':True
> }
>
> @declared_attr
> defencumbrances(self):
> returnrelationship("iEncumbrance")
>
>
> classFlat(iCadastralObject):
> __tablename__ ='flat'
>
> __mapper_args__ ={
> 'polymorphic_identity':'flat',
> 'concrete':True
> }
>
> @declared_attr
> defencumbrances(self):
> returnrelationship("iEncumbrance")
>
>
> classConstruction(iCadastralObject):
> __tablename__ ='construction'
>
> __mapper_args__ ={
> 'polymorphic_identity':'construction',
> 'concrete':True
> }
>
>
> classiEncumbrance(Base):
> __tablename__ ='iencumbrance'
>
> id =Column(Integer,primary_key=True,index=True)
>
> def__init__(self,cadastral_object):
> self.parent_cadastral_region =cadastral_object.cadastral_region
> self.parent_cadastral_district =cadastral_object.cadastral_district
> self.parent_cadastral_block =cadastral_object.cadastral_block
> self.parent_cadastral_object =cadastral_object.cadastral_object
>
> # FK fields
> parent_cadastral_region =Column(Integer,nullable=False)
> parent_cadastral_district =Column(Integer,nullable=False)
> parent_cadastral_block =Column(Integer,nullable=False)
> parent_cadastral_object =Column(Integer,nullable=False)
>
> parent_object =relationship(iCadastralObject)
>
> __table_args__ =(ForeignKeyConstraint(
> [
> parent_cadastral_region,
> parent_cadastral_district,
> parent_cadastral_block,
> parent_cadastral_object],
> [
> iCadastralObject.cadastral_region,
> iCadastralObject.cadastral_district,
> iCadastralObject.cadastral_block,
> iCadastralObject.cadastral_object]),
> {}
> )
> |
>
>
>
>
> I'm getting an error on "from app.imodels import Building"
>
> |
> sqlalchemy.exc.NoForeignKeysError:Couldnotdetermine join
condition
> between parent/child tables on relationship
> iCadastralObject.encumbrances -there are noforeign keys linking
these
> tables.Ensurethat referencing columns are associated witha
> ForeignKeyorForeignKeyConstraint,orspecify a
'primaryjoin'expression.
> |
>
>
>
>
>
>
> --
> 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
<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 <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
