Re: [sqlalchemy] Re: Relationship to child with 2 foreginKey from same Parent column

[Mike Bayer]

is there a stack trace (just the beginning and then a bit of the
repeating part, not the whole thing), there's no recursion inherent in
the SQLAlchemy part of this so something must be up with your model.
an MCVE (see the link below) is always the best way to show what's
happening.

On Thu, Aug 23, 2018 at 4:57 PM, Alireza Ayin Mehr
 wrote:
> Well, I tried "or_()" before
> primaryjoin="or_(User.id == Conversation.user1, User.id ==
> Conversation.user2)"
> It returns RecursionError :(
>
> On Thursday, August 23, 2018 at 9:00:58 PM UTC+4:30, Jonathan Vanasco wrote:
>>
>> I believe something like this should work.
>>
>>
>> conversations = sqlalchemy.orm.relationship("Conversation",
>>
>> primaryjoin="""or_(User.id==Conversation.user_id_1,
>>
>> User.id==Conversation.user_id_2,
>>)""",
>>
>> order_by="Conversation.id.desc()",
>> )
>>
>>
>> i also think you'll need to make an explicit relationship for a user1 and
>> user2 relationship instead of using back_populates.  i could be wrong.
>> personally i would make separate relationships though, because having an
>> undordered list for them makes little sense.
>>
> --
> 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.

-- 
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.

Re: [sqlalchemy] Setting many to many collection with secondary relationship when having only pk to one of the models.

[Mike Bayer]

On Thu, Aug 23, 2018 at 3:07 PM, Alex Rothberg  wrote:
> I didn't mean to confuse this question by showing both formats of the
> many-to-many relationship (using secondary and not); I am aware that using
> both can lead to problems / inconsistencies.
>
> I just wanted to show that I had both options available at my disposal. Is
> there anyway to use the secondary relationship (rather than the association
> one) with some combination of cascading options to get what I want where I
> can create and use a Geography knowing only its pk without the ORM trying to
> then save it to the DB?


So to get the session.merge(x, load=False) pattern with a transient
object, there is a more special purpose method that is not going to
cascade or anything like that, but if you have just the identity you
want and are watching carefully what you are doing, it is the
make_transient_to_detached() function, example below.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr

Base = declarative_base()

fund_geo = Table(
'fund_geo', Base.metadata,
Column('geo_id', ForeignKey('geography.id'), primary_key=True),
Column('fund_id', ForeignKey('fund.id'), primary_key=True)
)


class Geography(Base):
__tablename__ = 'geography'
id = Column(Integer, primary_key=True)


class Fund(Base):
__tablename__ = 'fund'

id = Column(Integer, primary_key=True)

geographies = relationship(
Geography,
backref="fund",
secondary=fund_geo
)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

g1 = Geography()
s.add(g1)
s.commit()
s.close()

g1 = Geography(id=1)
make_transient_to_detached(g1)
s.add(g1)

f1 = Fund(geographies=[g1])
s.add(f1)
s.commit()







>
> On Thursday, August 23, 2018 at 1:02:40 PM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Aug 22, 2018 at 5:41 PM, Alex Rothberg  wrote:
>> > I am using an association model / table to represent a many to many
>> > relationship:
>> >
>> > class Geography(db.Model):
>> >
>> > id =
>> > ...
>> >
>> > class Fund(db.Model):
>> > id =
>> > ...
>> > geography_associations = db.relationship(
>> > lambda: FundGeographyAssociation,
>> > back_populates="fund",
>> > cascade='save-update, merge, delete, delete-orphan'
>> > )
>> >
>> > geographies = db.relationship(
>> > Geography,
>> > backref="fund",
>> > secondary=lambda: FundGeographyAssociation.__table__,
>> > )
>> >
>> > class FundGeographyAssociation(db.Model):
>> > fund_id = db.Column(
>> > UUID, db.ForeignKey(Fund.id), primary_key=True,
>> > )
>> > geography_id = db.Column(
>> > UUID, db.ForeignKey(Geography.id), primary_key=True,
>> > )
>> >
>> > fund = db.relationship(Fund,
>> > back_populates='geography_associations')
>> >
>> >
>> > and then am attempting to update the list of geographies for a Fund
>> > using:
>> >fund.geographies = []
>> >
>> >
>> > my issue is what to put in ??? when I only have the pk of the geography
>> > model.
>>
>> it is not a recommended pattern to re-purpose a mapped association
>> class as a "secondary" elsewhere.  The ORM does not know that
>> Fund.geography_associations and Fund.geographies refer to the same
>> table and mutations to each of these independently will conflict (see
>>
>> http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object)
>> .   The usual pattern is to use an association proxy for
>> Fund.geographies (see
>>
>> http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects).
>>
>> If you want to add a row having only the id of Geography, the most
>> straightforward approach is to append the association object directly:
>>
>> fund.geography_associations = [FundGeoAssoc(geo_id=1)]
>>
>>
>> >
>> > this works: Geography.query.get(id) however this does not:
>> > Geography(id=id)
>> > as the latter tries to create a new Geography object leading to
>> > conflicts.
>> > The former seems "silly" as it requires an extra query to db to load the
>> > object even though all i need is the geography id to create the
>> > association
>> > object. I tried variation of session.merge with load=False however that
>> > doesn't work as the object is transient.
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email 

[sqlalchemy] Re: Relationship to child with 2 foreginKey from same Parent column

[Alireza Ayin Mehr]

Well, I tried "or_()" before
primaryjoin="or_(User.id == Conversation.user1, User.id == 
Conversation.user2)"
It returns RecursionError :(

On Thursday, August 23, 2018 at 9:00:58 PM UTC+4:30, Jonathan Vanasco wrote:
>
> I believe something like this should work. 
>
>
> conversations = sqlalchemy.orm.relationship("Conversation",
> primaryjoin=
> """or_(User.id==Conversation.user_id_1,
>   
>  User.id==Conversation.user_id_2,
>)""",
> order_by=
> "Conversation.id.desc()",
> )
>
>
> i also think you'll need to make an explicit relationship for a user1 and 
> user2 relationship instead of using back_populates.  i could be wrong. 
> personally i would make separate relationships though, because having an 
> undordered list for them makes little sense. 
>
>

-- 
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.

Re: [sqlalchemy] Setting many to many collection with secondary relationship when having only pk to one of the models.

[Alex Rothberg]

I didn't mean to confuse this question by showing both formats of the 
many-to-many relationship (using secondary and not); I am aware that using 
both can lead to problems / inconsistencies.

I just wanted to show that I had both options available at my disposal. Is 
there anyway to use the secondary relationship (rather than the association 
one) with some combination of cascading options to get what I want where I 
can create and use a Geography knowing only its pk without the ORM trying 
to then save it to the DB?

On Thursday, August 23, 2018 at 1:02:40 PM UTC-4, Mike Bayer wrote:
>
> On Wed, Aug 22, 2018 at 5:41 PM, Alex Rothberg  > wrote: 
> > I am using an association model / table to represent a many to many 
> > relationship: 
> > 
> > class Geography(db.Model): 
> > 
> > id = 
> > ... 
> > 
> > class Fund(db.Model): 
> > id = 
> > ... 
> > geography_associations = db.relationship( 
> > lambda: FundGeographyAssociation, 
> > back_populates="fund", 
> > cascade='save-update, merge, delete, delete-orphan' 
> > ) 
> > 
> > geographies = db.relationship( 
> > Geography, 
> > backref="fund", 
> > secondary=lambda: FundGeographyAssociation.__table__, 
> > ) 
> > 
> > class FundGeographyAssociation(db.Model): 
> > fund_id = db.Column( 
> > UUID, db.ForeignKey(Fund.id), primary_key=True, 
> > ) 
> > geography_id = db.Column( 
> > UUID, db.ForeignKey(Geography.id), primary_key=True, 
> > ) 
> > 
> > fund = db.relationship(Fund, 
> back_populates='geography_associations') 
> > 
> > 
> > and then am attempting to update the list of geographies for a Fund 
> using: 
> >fund.geographies = [] 
> > 
> > 
> > my issue is what to put in ??? when I only have the pk of the geography 
> > model. 
>
> it is not a recommended pattern to re-purpose a mapped association 
> class as a "secondary" elsewhere.  The ORM does not know that 
> Fund.geography_associations and Fund.geographies refer to the same 
> table and mutations to each of these independently will conflict (see 
>
> http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object)
>  
>
> .   The usual pattern is to use an association proxy for 
> Fund.geographies (see 
>
> http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects).
>  
>
>
> If you want to add a row having only the id of Geography, the most 
> straightforward approach is to append the association object directly: 
>
> fund.geography_associations = [FundGeoAssoc(geo_id=1)] 
>
>
> > 
> > this works: Geography.query.get(id) however this does not: 
> Geography(id=id) 
> > as the latter tries to create a new Geography object leading to 
> conflicts. 
> > The former seems "silly" as it requires an extra query to db to load the 
> > object even though all i need is the geography id to create the 
> association 
> > object. I tried variation of session.merge with load=False however that 
> > doesn't work as the object is transient. 
> > 
> > -- 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@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.

Re: [sqlalchemy] Setting many to many collection with secondary relationship when having only pk to one of the models.

[Mike Bayer]

On Wed, Aug 22, 2018 at 5:41 PM, Alex Rothberg  wrote:
> I am using an association model / table to represent a many to many
> relationship:
>
> class Geography(db.Model):
>
> id =
> ...
>
> class Fund(db.Model):
> id =
> ...
> geography_associations = db.relationship(
> lambda: FundGeographyAssociation,
> back_populates="fund",
> cascade='save-update, merge, delete, delete-orphan'
> )
>
> geographies = db.relationship(
> Geography,
> backref="fund",
> secondary=lambda: FundGeographyAssociation.__table__,
> )
>
> class FundGeographyAssociation(db.Model):
> fund_id = db.Column(
> UUID, db.ForeignKey(Fund.id), primary_key=True,
> )
> geography_id = db.Column(
> UUID, db.ForeignKey(Geography.id), primary_key=True,
> )
>
> fund = db.relationship(Fund, back_populates='geography_associations')
>
>
> and then am attempting to update the list of geographies for a Fund using:
>fund.geographies = []
>
>
> my issue is what to put in ??? when I only have the pk of the geography
> model.

it is not a recommended pattern to re-purpose a mapped association
class as a "secondary" elsewhere.  The ORM does not know that
Fund.geography_associations and Fund.geographies refer to the same
table and mutations to each of these independently will conflict (see
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object)
.   The usual pattern is to use an association proxy for
Fund.geographies (see
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects).

If you want to add a row having only the id of Geography, the most
straightforward approach is to append the association object directly:

fund.geography_associations = [FundGeoAssoc(geo_id=1)]


>
> this works: Geography.query.get(id) however this does not: Geography(id=id)
> as the latter tries to create a new Geography object leading to conflicts.
> The former seems "silly" as it requires an extra query to db to load the
> object even though all i need is the geography id to create the association
> object. I tried variation of session.merge with load=False however that
> doesn't work as the object is transient.
>
> --
> 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.

-- 
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.

Re: [sqlalchemy] Question about ShardedQuery Gevent parallelization

[Carson Ip]

Hi Mike,

Thanks for your detailed reply and your work on sqlalchemy. When I was 
researching about the issue, I have already read your blog post about 
asyncio.
It was very insightful.

Let me briefly describe my setup.
Benchmark setup:
2 MySQL database nodes, total 90 shards (databases).


On Thursday, August 23, 2018 at 12:04:16 AM UTC+8, Mike Bayer wrote:
>
> On Wed, Aug 22, 2018 at 6:03 AM, Carson Ip  > wrote: 
> > This is my first post here. 
> > 
> > Software / Library versions: (tho unrelated) 
> > sqlalchemy version: 1.0.19 
> > db: MySQL 5.6 
> > db driver: mysqlclient 1.3.7 
> > 
> > Background: 
> > I have 6 databases with a total of hundreds of shards. I realize when I 
> add 
> > more database hosts (and shards), my ShardedSession which queries all 
> the 
> > shards becomes slower. I looked into ShardedQuery and understand that 
> things 
> > run in serial. I am trying to add some gevent magic to parallelize the 
> > queries. 
>
> First step.  Did you do Python profiling that shows that waiting for 
> the databases to return results is where you actually have problems? 
> Here's a recipe for code profiling: 
> http://docs.sqlalchemy.org/en/latest/faq/performance.html#code-profiling. 
>I see that you claim a 20-30% speedup in some cases however you 
> will likely get the same or better speedup parallelizing with threads, 
> and you will not have the danger of CPU-bound greenlets running which 
> in the example you've given, you are definitely at risk of having. 
> Specifically with MySQL if one of your connections is making a fresh 
> MySQL connection to the database and another greenlet takes over 
> running that often-CPU-expensive instances() call, the MySQL server 
> will reject your connection for taking too long to respond in the 
> authentication phase and you'll get an error (which at my job took me 
> weeks to figure out).   However, that issue is if you are actually 
> using non-blocking IO.  Since you are using the native mysqlclient 
> driver, I believe gevent's monkeypatch of that runs it in a 
> thread-pool anyway, the real non-blocking version is when you use 
> pymysql instead.  So it's unlikely you are actually using any 
> non-blocking IO anyway, you're just using threads with a lot of gevent 
> complexity around it. 
>
>

When I profile my code with a ShardedSession which would query 
all 90 shards, 15% of the time is gevent's wait function, and 12% 
is mysql's rollback function (due to close). Others are insignificant 
(~1%). This confirms my assumption of "most of the time is waiting
for the sequential queries across shards".

When I parallelize them (by modifying _execute_and_instances), 
such a bottleneck goes away. Of course now I realize this is NOT
the way to go after reading your detailed explanation.

I was naive and assumed that parallelizing things will reduce the wait.
Thanks to your response, I am now making progress. I am trying to 
not mess with the internals and do the parallelization at a higher level.


Let me show you my benchmarks:

It is a very simple "SELECT id FROM table WHERE field = target".

Global (cross shard ShardedSession): 0.195 (seconds)
Global with baked query: 0.189
Parallel (1 session per shard, shard-parallelized): 0.170
Parallel with baked query: 0.160
Chunked with baked (1 session per shard but parallelized per mysql node): 
0.129
Raw Parallel (Using mysqldb directly, shard-parallelized): 0.102
Raw Serial (Mysqldb, sequential): 0.173

Observations:

1. The query is only run once therefore global with baked yielded
insignificant gain since the the query is generated and used once
only. For the same reason, parallel with baked query works better
than parallel alone since the baked query is reused.

2. As you have suggested, the chunked has the best result with 
sqlalchemy. My guess is it did not bombard MySQL with the queries
and did not push Python too hard at a time.

3. The mysqldb benchmarks exist for baseline purposes.

Off topic:
In the application, I also added a cache to cache the shard_id of
the requested object. The numbers do look much better, but when 
there is a cache miss, the cross-shard query is still necessary.

 

> As far as non-blocking IO (which is the thing you are hoping to take 
> advantage of when using gevent), I have been writing for years now, 
> non-blocking IO does not help with concurrent relational database 
> access in the general, CRUD-style case compared to using threads. 
> Python is way more CPU bound than you'd like, and your non-blocking 
> sockets will simply be waiting for your CPU to get around to attending 
> to them.  See my post at 
> http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/ 
> with benchmarks, and in particular look at the "runsnakerun" images 
> that show just how much work Python's interpreter does around a 
> relatively small bit of actually waiting for the database. 
>
> Whether you use thread or gevent, running the results into a single 
> session 

Re: [sqlalchemy] Multiple join paths and relationships that span across multiple tables

[Alexios Damigos]

On Wednesday, August 22, 2018 at 6:13:36 PM UTC+2, Mike Bayer wrote:
>
>
> that does not create any issue by itself, relationship() knows to only 
> look at the tables that are involved in the linkage.   Feel free to 
> illustrate a code example that shows a failure and I can show you why. 
>

You were right, adding a second foreign key sensor.id to the Batmon table 
indeed works. I must have done something wrong previously when I tried it, 
thanks.
 

> Can you provide a pseudocode example illustrating what this 
> relationship looks like?   you want a single Sensor.thing that returns 
> any of batmon/radmon/deported?   can you share the actual structure of 
> the tables or is that part of what you are trying to decide upon ? 
>

I have included the structure of the tables and pseudocode for the 
relationship on my first email, and the structure of the tables is set to 
what I describe.
 

> in the simplest terms this looks like you could just have 
> Radmon/Batmon/Deported be joined-table subclasses of Device, then link 
> Sensor->Device, and you're done.   but im not sure what these concepts 
> mean.Also check out generic foreign key examples: 
>
> http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.generic_associations
>  
> 
>  
>

How can I achieve that when Radmon/Batmon/Deported are separate tables?
It would be as if the sensor.thing (component1.deviceInstalled in my 
original email) is looking for any instance of sensor.id on batmon.fet1 and 
batmon.fet2 and radmon.fet etc

Maybe if I created a union table with all the id's, fet1's and fet2's of 
all three tables and looked there?
For example if I query this:

SELECT id,fet1 FROM batmon
UNION
SELECT id,fet2 FROM batmon
UNION
SELECT id,fet1 FROM radmon
UNION
SELECT id,fet2 FROM radmon
UNION
SELECT id,fet1 FROM deported
UNION
SELECT id,fet2 FROM deported;

I get a table with all the fets (either 1 or 2) installed and the id of the 
device that they are installed in.
If I somehow create a relationship on *sensor* that looks in that table for 
the id and grabs the device with that id, I would have achieved what I'm 
after.

Thank you,
Alex
 

>
> > 
> > On Wednesday, August 22, 2018 at 5:22:07 PM UTC+2, Mike Bayer wrote: 
> >> 
> >> On Wed, Aug 22, 2018 at 5:50 AM, Alexios Damigos  
> >> wrote: 
> >> > Hello everyone, 
> >> > 
> >> > I have been trying to create a model for my database, with no luck so 
> >> > far. 
> >> > I am using SQLAlchemy 1.2.10 together with PyMySQL 0.9.2 to connect 
> to a 
> >> > MariaDB database. 
> >> > 
> >> > A description of the database model: 
> >> > 
> >> > Table A (components) 
> >> > id brand status 
> >> > N1 br3   free 
> >> > N2 br2   used 
> >> > N3 br2   used 
> >> > N4 br3   used 
> >> > N5 br2   used 
> >> > N6 br3   used 
> >> > N7 br2   used 
> >> > 
> >> > 
> >> > Table B (device1) 
> >> > id comp1 comp2 
> >> > 2   N2N3 
> >> > 
> >> > Table C (device2) 
> >> > id comp1 comp2 
> >> > 6   N4N5 
> >> > 
> >> > Table D (device3) 
> >> > id comp1 comp2 
> >> > 1   N6N7 
> >> > 
> >> > So there are two foreign keys pointing to the same table (A) and 
> column 
> >> > for 
> >> > all the other three tables, and every item on table A can only be 
> >> > assigned 
> >> > to a singe component column of a single table B, C or D. 
> >> > 
> >> > Ideally what I would like to achieve is something like this: 
> >> > 
> >> > component1 ->  
> >> > component1.deviceInstalled -> <2 N2 N3> (from table B) 
> >> > component2 ->  
> >> > component2.deviceInstalled -> <6 N4 N5> (from table C) 
> >> > 
> >> > I tried following the guidelines in Multiple Join Paths, still got 
> >> > ambiguous 
> >> > foreign keys error. 
> >> > As far as the relationship deviceInstalled, I have not managed to 
> find 
> >> > somewhere how to implement that, since it has to span across three 
> >> > tables. 
> >> > 
> >> > 
> >> > My working but incomplete code at the moment, with all the failed 
> >> > attempts 
> >> > removed. 
> >> 
> >> Looking at the code, I dont know what: 
> >> 
> >> ForeignKey('') 
> >> 
> >> means, I guess that means you don't know which table to refer towards? 
> >> 
> >> if batmon/radmon/deported are B, C, and D, and "Sensor" is not part of 
> >> the problem, just make them FK's to"Device"? Here's that 
> >> 
> >> 
> >> from sqlalchemy import * 
> >> from sqlalchemy.orm import * 
> >> from sqlalchemy.ext.declarative import declarative_base 
> >> from sqlalchemy.ext.declarative import declared_attr 
> >> 
> >> Model = declarative_base() 
> >> 
> >> 
> >> class Device(Model): 
> >> __tablename__ = 'device' 
> >> 
> >> id = Column(Integer, primary_key=True) 
> >> type = Column(String(30), unique=False, nullable=False) 
> >> variant = Column(String(30), unique=False, nullable=True) 
> >>