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
>  
> <http://www.google.com/url?q=http%3A%2F%2Fdocs.sqlalchemy.org%2Fen%2Flatest%2Form%2Fexamples.html%23module-examples.generic_associations&sa=D&sntz=1&usg=AFQjCNEB4ZrA3jYzuvDpML5N1kDY4F5hjg>
>  
>

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 
> >&g

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

[Alexios Damigos]

Thank you for the reply.
The lack of foreign keys must be some bug of pastecode.xyz, because if you 
see it on the browser or on "raw", they are indeed set just like your 
suggestion.

The issue is that I cannot manage to add a second ForeignKey('sensor.id') to 
the tables batmon/radmon/deported, or figure out how to declare the 
relationship in the sensor table in order to point to the correct 
batmon/radmon or deported entry.

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) 
> serialNo = Column(String(30), unique=True, nullable=False) 
>
> batmonConf = relationship('Batmon', backref='device', 
> uselist=False, lazy=True) 
> radmonConf = relationship('Radmon', backref='device', 
> uselist=False, lazy=True) 
> deportedConf = relationship('Deported', backref='device', 
> uselist=False, lazy=True) 
>
>
> class Batmon(Model): 
> __tablename__ = 'batmon' 
>
> id = Column(Integer, ForeignKey('device.id'), primary_key=True) 
> voltage = Column(Integer, unique=False, nullable=False) 
> fet1 = Column(String(20), ForeignKey(''), unique=True) 
> fet2 = Column(String(20), unique=True, nullable=True) 
> pin = Column(String(20), unique=True, nullable=True) 
> mem1 = Column(String(20), unique=True, nullable=True) 
> mem2 = Column(String(20), unique=True, nullable=True) 
>
>
> class Radmon(Model): 
> __tablename__ = 'radmon' 
>
> id = Column(Integer, ForeignKey('device.id'), primary_key=True) 
> fet1 = Column(String(20), unique=True, nullable=True) 
> fet2 = Column(String(20), unique=True, nullable=True) 
> pin = Column(String(20), unique=True, nullable=True) 
> mem1 = Column(String(20), unique=True, nullable=True) 
> mem2 = Column(String(20), unique=True, nullable=True) 
>
>
> class Deported(Model): 
> __tablename__ = 'deported' 
>
> id = Column(Integer, ForeignKey('device.id'), primary_key=True) 
> fet1 = Column(String(20), unique=True,

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

[Alexios Damigos]

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.

Any help would be very much appreciated, as I have been trying for a couple of 
days with no luck!
Thank you

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