[sqlalchemy] foreign key relations
Hi,
I have 3 tables and I'm concerned about possible issues with foreign key
relations and how they might impact my code and its behavior. Maybe there's a
circular reference issue or other concern I should know about with the tables
below?
A player and a song are both created separately and associated with a client.
class Client(Base):
__tablename__ = 'client'
id = Column(Integer, primary_key=True)
class Player(Base):
__tablename__ = 'player'
id = Column(Integer, primary_key=True)
clientid = Column(Integer, ForeignKey('client.id'), nullable=False,
index=True)
client = relationship('Client', lazy='select')
class Song(Base):
__tablename__ = 'song'
id = Column(Integer, primary_key=True)
clientid = Column(Integer, ForeignKey('client.id'), nullable=False,
index=True)
client = relationship('Client', lazy='select')
name = Column(Unicode(50), nullable=False, index=True)
I now need to modify the above tables/relations so I can assign one song to
each player.
To accomplish this, would it be ok to add the following to Player?
songid = Column(Integer, ForeignKey('song.id'))
song = relationship('Song', lazy='select')
Or maybe I should add an association table for Song and Player?
As a follow-up question, what should I do if need to be able to assign more
than one song to a player?
Thanks
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] foreign key relations
On Sun, 3 Aug 2014, nathan wrote:
A player and a song are both created separately and associated with a
client.
class Client(Base):
__tablename__ = 'client'
id = Column(Integer, primary_key=True)
class Player(Base):
__tablename__ = 'player'
id = Column(Integer, primary_key=True)
clientid = Column(Integer, ForeignKey('client.id'), nullable=False,
index=True)
client = relationship('Client', lazy='select')
class Song(Base):
__tablename__ = 'song'
id = Column(Integer, primary_key=True)
clientid = Column(Integer, ForeignKey('client.id'), nullable=False,
index=True)
client = relationship('Client', lazy='select')
name = Column(Unicode(50), nullable=False, index=True)
I now need to modify the above tables/relations so I can assign one song to
each player.
To accomplish this, would it be ok to add the following to Player?
songid = Column(Integer, ForeignKey('song.id'))
song = relationship('Song', lazy='select')
Or maybe I should add an association table for Song and Player?
Nathan,
If I correctly understand, the relationship between client and song is 1
(client) to many (songs), and the relationship between client and player is
also 1 (client) to many (players). But, you also have the relationship of 1
(song) to 1 (player). Yes?
This looks similar to my schema that has the class Agency with a 1:Many
relationship with the class Agency_Units, and Agency_Units has a 1:Many
relationship with class Agency_Contacts. Stripped down this is what I have;
I think you can use the same thinking on your schema:
class Agencies(Base):
__tablename__ = 'agencies'
org_name = Column(Unicode(48), Sequence('org_name_seq'), primary_key =
True)
child1 = relationship('Agencies_Units', backref = 'user', cascade =
'all, delete, delete-orphan')
class Agency_Units(Base):
__tablename__ = 'agency_units'
unit_name = Column(Unicode(48), Sequence('unit_name_seq'), primary_key =
True)
ForeignKey('agencies.org_name'))
children = relationship('Agency_Contacts', backref = 'agency_units',
cascade = 'all, delete, delete-orphan')
class Agency_Contacts(Base):
__tablename__ = 'agency_contacts'
id = Column(Integer, Sequence('agency_contact_seq'), primary_key=True)
unit_name = Column(Unicode(48), ForeignKey('agency_units.unit_name'))
The relationship section of the manual (2.1.10, I believe) will help you
determine the specific relationships of your clients, players, and songs
tables.
HTH,
Rich
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] foreign key relations
Hi Rich,
Yes, you have it exactly right. There's currently 1 song to 1 player, but I'm
thinking of going ahead and experimenting with assigning more than 1 song to a
player.
I'm reading through the manual section again on relationships. :)
Thanks for sharing your schema configuration. I really appreciate your time.
On Aug 3, 2014, at 5:14 PM, Rich Shepard rshep...@appl-ecosys.com wrote:
On Sun, 3 Aug 2014, nathan wrote:
A player and a song are both created separately and associated with a
client.
class Client(Base):
__tablename__ = 'client'
id = Column(Integer, primary_key=True)
class Player(Base):
__tablename__ = 'player'
id = Column(Integer, primary_key=True)
clientid = Column(Integer, ForeignKey('client.id'), nullable=False,
index=True)
client = relationship('Client', lazy='select')
class Song(Base):
__tablename__ = 'song'
id = Column(Integer, primary_key=True)
clientid = Column(Integer, ForeignKey('client.id'), nullable=False,
index=True)
client = relationship('Client', lazy='select')
name = Column(Unicode(50), nullable=False, index=True)
I now need to modify the above tables/relations so I can assign one song to
each player.
To accomplish this, would it be ok to add the following to Player?
songid = Column(Integer, ForeignKey('song.id'))
song = relationship('Song', lazy='select')
Or maybe I should add an association table for Song and Player?
Nathan,
If I correctly understand, the relationship between client and song is 1
(client) to many (songs), and the relationship between client and player is
also 1 (client) to many (players). But, you also have the relationship of 1
(song) to 1 (player). Yes?
This looks similar to my schema that has the class Agency with a 1:Many
relationship with the class Agency_Units, and Agency_Units has a 1:Many
relationship with class Agency_Contacts. Stripped down this is what I have;
I think you can use the same thinking on your schema:
class Agencies(Base):
__tablename__ = 'agencies'
org_name = Column(Unicode(48), Sequence('org_name_seq'), primary_key =
True)
child1 = relationship('Agencies_Units', backref = 'user', cascade =
'all, delete, delete-orphan')
class Agency_Units(Base):
__tablename__ = 'agency_units'
unit_name = Column(Unicode(48), Sequence('unit_name_seq'), primary_key =
True)
ForeignKey('agencies.org_name'))
children = relationship('Agency_Contacts', backref = 'agency_units',
cascade = 'all, delete, delete-orphan')
class Agency_Contacts(Base):
__tablename__ = 'agency_contacts'
id = Column(Integer, Sequence('agency_contact_seq'), primary_key=True)
unit_name = Column(Unicode(48), ForeignKey('agency_units.unit_name'))
The relationship section of the manual (2.1.10, I believe) will help you
determine the specific relationships of your clients, players, and songs
tables.
HTH,
Rich
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] foreign key relations
On Sun, 3 Aug 2014, nathan wrote: Yes, you have it exactly right. There's currently 1 song to 1 player, but I'm thinking of going ahead and experimenting with assigning more than 1 song to a player. Nathan, It does not matter if you know the relationship(s) you want you can define them properly. Remember that it's possible for a table to be a child of a parent and the parent to a child table. The 1:1 relationship is fairly straight-forward. With the 1:Many and Many:1 it's a bit more complex, but the backref() helps once that's understood. The tricky part is making sure you understand which table is the 1 and which table is the Many. It's easy to get them backwards. Regards, Rich -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
