Removing relations with Many To One

[ThereMichael]

I have this relation:

    # Users
    user_table = Table('user', self.metadata,
        Column('id', Integer, primary_key=True),
        Column('place_id', Integer),
        mysql_engine='InnoDB'
    )
   
    # Places
    places_table = Table('places', self.metadata,
      Column('id', Integer, primary_key=True),     
      mysql_engine='InnoDB'
    )
   
    mapper(User, user_table,properties={
        'user' : relation(Place,
          primaryjoin=(user_table.c.place_id==places_table.c.id),
          foreign_keys=[places_table.c.id]),
      }
    )         
         
         
    mapper(Place, places_table, properties={
      'id':places_table.c.id,
      }
    )

A Place can be associated with multiple users.

I fetch a user as so:

    query = session.query(User).options(
      eagerload('place')).\
      filter_by(id=id)
     
    user  = query.one()

If there's a place associated with the user I end up with : user.place[], where user.place[0] is the first place, etc.

Now, I want to remove the relation between this User and this Place, without deleting the place. So, I tried:

user.place_id = None

That fails with "AssertionError: Dependency rule tried to blank-out primary key column 'places.id' on instance '<Place at 0x1116df6d0>'"

So, then I tried:

del user.place[0]

That fails also. I've also tried the various incarnations of passive_deletes=True and False (fails).


The only solution I've come up with is this:

session.expunge(user.place[0])
user.place_id = None

That works, but it doesn't feel right. Is there a better way? Thanks!

[Michael Bayer]

On May 23, 2012, at 12:10 AM, ThereMichael wrote:

I have this relation:

    # Users
    user_table = Table('user', self.metadata,
        Column('id', Integer, primary_key=True),
        Column('place_id', Integer),
        mysql_engine='InnoDB'
    )
   
    # Places
    places_table = Table('places', self.metadata,
      Column('id', Integer, primary_key=True),     
      mysql_engine='InnoDB'
    )
   
    mapper(User, user_table,properties={
        'user' : relation(Place,
          primaryjoin=(user_table.c.place_id==places_table.c.id),
          foreign_keys=[places_table.c.id]),
      }
    )         
         
         
    mapper(Place, places_table, properties={
      'id':places_table.c.id,
      }
    )

A Place can be associated with multiple users.

That would make user.place_id the foreign key here.  Put a ForeignKey() on the "user.place_id" Column pointing to "places.id" and lose the foreign_keys directive in the relation().    Because you have the primary key set as the FK at the moment, detaching a user/place means the "foreign" column becomes null - which if you've told it "places.id", means it's nulling out the primary key.

I fetch a user as so:

    query = session.query(User).options(
      eagerload('place')).\
      filter_by(id=id)
     
    user  = query.one()

If there's a place associated with the user I end up with : user.place[], where user.place[0] is the first place, etc.

Now, I want to remove the relation between this User and this Place, without deleting the place. So, I tried:

user.place_id = None

That fails with "AssertionError: Dependency rule tried to blank-out primary key column 'places.id' on instance '<Place at 0x1116df6d0>'"

So, then I tried:

del user.place[0]

That fails also. I've also tried the various incarnations of passive_deletes=True and False (fails).


The only solution I've come up with is this:

session.expunge(user.place[0])
user.place_id = None

That works, but it doesn't feel right. Is there a better way? Thanks!

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/PDhdyOGXWuoJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Wilson]

Worked perfectly. Thanks!