Many to Many and Unique Object question

[dan]

I've got a many to many relationship with the association table. The issue I am running into is that a given pesticide entry may have a pest or pests already in the pest table. I don't want to add the pest since it will be a duplicate, however I still want the association of that pest to the pesticide done.

I've looked at the Unique Object wiki, however I am not sure that solves my issue. Is there a way that SQLAlchemy can handle this, or do I need to manually build the entries in the PestToPesticide association table?

Thanks!

Dan

PestToPesticide = Table('pest_to_pesticide', Base.metadata,
                          Column('pesticide_id', Integer, ForeignKey('pesticide.row_id')),
                          Column('pest_id', Integer, ForeignKey('pest.row_id'))
                          )
class Pesticide(Base):
  __tablename__ = 'pesticide'
  row_id                 = Column(Integer,primary_key=True)                    
  name                   =  Column(String(64), unique=True)
 
  pestList                  =relationship("Pest", secondary=PestToPesticide, backref="pesticide")

class Pest(object):
  row_id                 = Column(Integer,primary_key=True)                    
  name                   = Column(String(), unique=True)

[Michael Bayer]

pesttopesticide is maintained by relationship() as the "secondary" table, but the expectation is that rows in this table will be unique.  if you are appending duplicate entries to "pestList" or "pesticide" you may want to use collection_class=set to maintain uniqueness in the collection.

--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[dan]

Using the collection_class=set is only going to work if I try to add the same pest multiple times into pestList isn't it?
My situation is more along the lines that the pest table already has the pest, and the pesticide I want to add also will have that pest, so I want the entries in the pest_to_pesticide created. What currently happens is an IntegrityError is thrown complaining that there is a duplicate pest entry.

[Michael Bayer]

On Apr 24, 2013, at 1:38 PM, dan <d...@inlet.geol.sc.edu> wrote:

Using the collection_class=set is only going to work if I try to add the same pest multiple times into pestList isn't it?
My situation is more along the lines that the pest table already has the pest, and the pesticide I want to add also will have that pest, so I want the entries in the pest_to_pesticide created. What currently happens is an IntegrityError is thrown complaining that there is a duplicate pest entry.

well the issue of the pest_to_pesticide table and the unique entries in Pest are two separate things.   since this is many to many, sure any number of Pests can be associated with any number of Pesticides, so to achieve this you of course need to work with an existing Pest entry and associate it as needed.   Typically, if you know what Pest you want to work with, you look it up in the database, if its not there, you create it.  The UniqueObject recipe is one way to make this more "invisible" but it isn't a requirement.    Just to get things to work you might want to keep it simple and just do a "get_or_create_pest()" function, before turning it into something more transparent.

[dan]

Cool, that's what I wanted to know. I didn't want to miss some function that did this for me if it was there.

Thanks alot for the help!

Dan