> On 17 Mar 2016, at 19:11, Alex Hall <[email protected]> wrote:
>
> Hello all,
> It seems like I can't go a day without running into some kind of wall.
> This one is a conceptual one regarding foreign keys. I have to somehow
> get the same FK column in table A pointing to IDs in tables B and C.
>
> At one person's suggestion, I'm making classes for my tables, even
> though I'm using automap. This is to let me stop doing a ton of joins,
> making querying much easier... I hope! I'm defining all the foreign
> keys between my tables manually. For instance:
>
> class item(base):
> __tablename__ = "item"
> itm_id = Column(Integer, primary_key=True)
> vendornum = Column(String, ForeignKey(VENDR.PVVNNO))
>
> class vendorTable(base):
> __tablename__ = "VENDR"
> PVVNNO = Column(String, primary_key=True)
>
> If I've understood correctly, I'll now be able to say
> item.vendornum.vendor_full_name
> to get the vendor's full name for any item.
>
> Here's the problem. Items have attachments, and attached text,
> respectively held in attach and attach_text tables. Binding them to
> items is a table called assignment. Assignment is pretty
> straightforward, with an itm_id and an attachment id (att_id). The
> trouble is that this att_id occurs in both attach and attach_text. I
> can make att_id a foreign key to one table or the other, but I'm not
> sure how to make it go to both tables.
>
> class assignmentTable(base):
> __tablename__ = "assignment"
> itm_id = Column(Integer, ForeignKey(item.itm_id))
> #the following column has to point to attach_text.att_id AS WELL
> att_id = Column(Integer, ForeignKey(attachment.att_id))
> seq_num = Column(Integer)
> asn_primary = Column(Integer, nullable=True)
>
> class attachmentTable(base):
> __tablename__ = "attachment"
> att_id = Column(Integer, primary_key=True)
>
> class attachmentTextTable(base):
> __tablename__ = "attach_text"
> att_id = Column(Integer, primary_key=True)
This isn’t possible - a foreign key can only point at one other column. If rows
in attachment and attach_text are always supposed to be in a 1-to-1
relationship, you could consider one of them to be the “master” record, and
make any other occurrences point to that. So for example, attachment.att_id
could be the master id, and attach_text.att_id could be a foreign key pointing
at attachment.att_id (perhaps with a unique constraint on it). The assignment
table suggests that this is a many-to-many relationship (an attachment can
belong to many items, and an item can have many attachments), so the
relationships might be set up like this:
item.assignments (list of assignmentTable objects)
assignment.item (item object)
assignment.attachment (attachmentTable object)
attachmentTable.text (attachmentTextTable object)
Here’s a working example:
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Item(Base):
__tablename__ = 'item'
itm_id = sa.Column(sa.Integer, primary_key=True)
vendornum = sa.Column(sa.ForeignKey('vendr.pvvnno'))
vendor = saorm.relationship('Vendor')
assignments = saorm.relationship('Assignment', back_populates='item')
class Vendor(Base):
__tablename__ = 'vendr'
pvvnno = sa.Column(sa.String(16), primary_key=True)
vendor_full_name = sa.Column(sa.Text())
class Assignment(Base):
__tablename__ = 'assignment'
asn_primary = sa.Column(sa.Integer, primary_key=True)
itm_id = sa.Column(sa.ForeignKey(Item.itm_id), nullable=False)
att_id = sa.Column(sa.ForeignKey('attachment.att_id'), nullable=False)
seq_num = sa.Column(sa.Integer)
item = saorm.relationship('Item', back_populates='assignments')
attachment = saorm.relationship('Attachment', back_populates='assignment')
class Attachment(Base):
__tablename__ = 'attachment'
att_id = sa.Column(sa.Integer, primary_key=True)
att_data = sa.Column(sa.Text())
assignment = saorm.relationship('Assignment', back_populates='attachment')
text = saorm.relationship('AttachmentText',
back_populates='attachment',
uselist=False)
class AttachmentText(Base):
__tablename__ = 'attachmenttext'
att_id = sa.Column(sa.ForeignKey('attachment.att_id'), primary_key=True)
att_text = sa.Column(sa.Text())
attachment = saorm.relationship('Attachment', back_populates='text')
if __name__ == '__main__':
import sys
engine = sa.create_engine('sqlite://', echo='debug')
Base.metadata.create_all(bind=engine)
Session = saorm.sessionmaker(bind=engine)
session = Session()
item = Item(vendor=Vendor(pvvnno='Alex',
vendor_full_name='Alex the Vendor'))
for i in range(5):
attachment = Attachment(att_data=str(i),
text=AttachmentText(att_text='text for %s' % i))
item.assignments.append(Assignment(attachment=attachment))
session.add(item)
session.commit()
del item
del attachment
session = Session()
q = session.query(Item)
if 'eager' in sys.argv:
q = q.options(
saorm.joinedload('vendor'),
(saorm
.joinedload('assignments')
.joinedload('attachment')
.joinedload('text')),
)
item = q.first()
print item
print item.vendor
for assignment in item.assignments:
print assignment.attachment.att_data
print assignment.attachment.text.at_text
Hope that helps,
Simon
--
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.
