On Wed, Aug 22, 2018 at 11:34 AM, Alexios Damigos <[email protected]> wrote: > 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,
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. or figure out how to declare the > relationship in the sensor table in order to point to the correct > batmon/radmon or deported entry. 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 ? 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 > > 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 <[email protected]> >> 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 N2 N3 >> > >> > Table C (device2) >> > id comp1 comp2 >> > 6 N4 N5 >> > >> > Table D (device3) >> > id comp1 comp2 >> > 1 N6 N7 >> > >> > 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 -> <N2 br2 used> >> > component1.deviceInstalled -> <2 N2 N3> (from table B) >> > component2 -> <N4 br3 used> >> > 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, nullable=True) >> fet2 = Column(String(20), unique=True, nullable=True) >> pin = Column(String(20), unique=True, nullable=True) >> >> >> configure_mappers() >> >> >> >> >> > >> > 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 [email protected]. >> > To post to this group, send email to [email protected]. >> > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
