Ok it works like this, thanks !
Michael Bayer wrote:
> the "clear()" method on the "keywords" list is blowing away all the
> stored history (that method, as well as the HistoryArraySet its
> attached to, is going away in the next release). I just added a unit
> test to make sure what youre trying to do works. do it like this:
>
> invasive_objects.habitat = []
>
>
>
> On Jun 14, 2006, at 8:35 AM, Julien Cigar wrote:
>
>> (sorry if this message appears twice ...)
>>
>> Hi,
>>
>> I'm using SQLAlchemy 2.2 in a mod_python application, with a "home made"
>> handler.
>> Here are my tables / mappers definitions:
>>
>> ###########################
>>
>> # application/models/__init__.py
>>
>> from datetime import datetime
>>
>> from sqlalchemy import *
>>
>> from application.models.invasive import Invasive, InvasiveName,
>> InvasiveGroup, InvasiveSubGroup
>> from application.models.language import Language
>> from application.models.habitat import Habitat
>> from application.models.origin import Origin
>> from application.models.user import User, UserPermission
>>
>> from application import configuration
>>
>> db_engine = create_engine(configuration.main.DB_DSN, encoding='utf-8')
>>
>> meta = BoundMetaData(db_engine)
>>
>> languages = Table('languages', meta,
>> Column('id', Integer, primary_key=True),
>> Column('iso_code', String(2)),
>> Column('language', String(100)))
>> users = Table('users', meta,
>> Column('id', Integer, primary_key=True),
>> Column('login', String(50)),
>> Column('password', String(32)),
>> Column('first_name', String(100)),
>> Column('name', String(100)),
>> Column('email', String(100)))
>>
>> user_permissions = Table('user_permissions', meta,
>> Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
>> Column('perm_invasive', String(50)),
>> Column('perm_users', String(50)))
>>
>> invasives = Table('invasives', meta,
>> Column('id', Integer, primary_key=True),
>> Column('added', DateTime, default=datetime.now()),
>> Column('modified', DateTime, default=datetime.now(),
>> onupdate=datetime.now()),
>> Column('scientific_name', String(200)),
>> Column('geographic_range', String(3)),
>> Column('trend', String(30)),
>> Column('nat_reproduction', Boolean),
>> Column('nat_dense_populations', Boolean),
>> Column('nat_dispersal_capacity', Boolean),
>> Column('nat_natural_habitats', Boolean),
>> Column('impact_global', Boolean),
>> Column('impact_competition', Boolean),
>> Column('impact_predation', Boolean),
>> Column('impact_hybridisation', Boolean),
>> Column('impact_disease_transmission', Boolean),
>> Column('impact_ecosystem_disruption', Boolean),
>> Column('impact_health', Boolean),
>> Column('impact_economic', Boolean),
>> Column('first_obs_belgium', Smallinteger),
>> Column('first_obs_flanders', Smallinteger),
>> Column('first_obs_wallonia', Smallinteger),
>> Column('intr_fishing', Boolean),
>> Column('intr_hunting', Boolean),
>> Column('intr_fur', Boolean),
>> Column('intr_culture', Boolean),
>> Column('intr_petornamental', Boolean),
>> Column('intr_canals', Boolean),
>> Column('intr_accidental', Boolean),
>> Column('intr_deliberated', Boolean),
>> Column('black_lists_belg', String(3)),
>> Column('black_lists_sebi', Boolean),
>> Column('black_lists_eppo', Boolean),
>> Column('status', Integer),
>> Column('group_id', Integer, ForeignKey('invasive_groups.id')),
>> Column('subgroup_id', Integer, ForeignKey('invasive_subgroups.id')))
>>
>> invasive_groups = Table('invasive_groups', meta,
>> Column('id', Integer, primary_key=True),
>> Column('group_name', String(100)))
>>
>> invasive_subgroups = Table('invasive_subgroups', meta,
>> Column('id', Integer, primary_key=True),
>> Column('subgroup_name', String(100)))
>>
>> invasive_names = Table('invasive_names', meta,
>> Column('name', String(200)),
>> Column('language_id', Integer, ForeignKey('languages.id'),
>> primary_key=True),
>> Column('invasive_id', Integer, ForeignKey('invasives.id'),
>> primary_key=True))
>> invasive_habitats = Table('invasive_habitats', meta,
>> Column('invasive_id', Integer, ForeignKey('invasives.id'),
>> primary_key=True),
>> Column('habitat_id', Integer, ForeignKey('habitats.id'),
>> primary_key=True))
>>
>> invasive_origins = Table('invasive_origins', meta,
>> Column('invasive_id', Integer, ForeignKey('invasives.id'),
>> primary_key=True),
>> Column('origin_id', Integer, ForeignKey('origins.id'),
>> primary_key=True))
>>
>> habitats = Table('habitats', meta,
>> Column('id', Integer, primary_key=True),
>> Column('habitat', String(100)))
>>
>> origins = Table('origins', meta,
>> Column('id', Integer, primary_key=True),
>> Column('origin', String(100)))
>>
>> mapper(Origin, origins)
>>
>> mapper(Habitat, habitats)
>>
>> mapper(InvasiveName, invasive_names)
>>
>> mapper(Invasive, invasives, properties = {
>> 'names' : relation(InvasiveName, backref='invasive', cascade='all,
>> delete-orphan'),
>> 'habitats' : relation(Habitat, secondary=invasive_habitats,
>> lazy=False),
>> 'origins' : relation(Origin, secondary=invasive_origins, lazy=False)
>> }
>> )
>>
>> mapper(InvasiveGroup, invasive_groups, properties = {
>> 'invasives' : relation(Invasive, backref='group', lazy=False)
>> }
>> )
>>
>> mapper(InvasiveSubGroup, invasive_subgroups, properties = {
>> 'invasives' : relation(Invasive, backref='subgroup', lazy=False)
>> }
>> )
>>
>> mapper(User, users)
>>
>> mapper(Language, languages, properties = {
>> 'invasive_names' : relation(InvasiveName, backref='language',
>> cascade='all, delete-orphan')
>> }
>> )
>>
>> mapper(UserPermission, user_permissions, properties = {
>> 'user' : relation(User,
>> backref=backref('permission', uselist=False), cascade='all,
>> delete-orphan', lazy=False)
>> }
>> )
>>
>> ###########################
>>
>> As you can see, I have a table "invasives" which is mapped to the
>> Invasive object. This table has a "many-to-many" relation with the
>> "habitats" table through the "invasive_habitats" table. So an invasive
>> can have multiple habitats. When I load an Invasive object from the
>> database with a session.query(Invasive).get(<pk>) I have their habitats
>> through the "habitats" properties ('habitats' : relation(Habitat,
>> secondary=invasive_habitats, lazy=False)), so this is OK and works fine.
>> The problem I have is when I want to update the habitats of an Invasive.
>> The values comes from an html <form> in a mod_python Session object
>> ("params" in the following). So I do something like :
>>
>> # application/controllers/invasive.py
>>
>> invasive_object = session.query(Invasive).get(168)
>>
>> => The result is send to the HTML form for update
>> => The user submit the modification
>>
>> Then I did the following :
>>
>> for habitat in self.params.getlist('invasive_habitats'):
>> invasive_object.habitats.append(query_habitat.get(habitat))
>>
>> which fails because SQLAlchemy does not update or delete the rows in the
>> "invasive_habitats" table. In place it try to insert a new record which
>> fail of course :
>>
>> INSERT INTO invasive_habitats (invasive_id, habitat_id) VALUES (168, 1)
>> 'INSERT INTO invasive_habitats (invasive_id, habitat_id) VALUES
>> (%(invasive_id)s, %(habitat_id)s)' [{'invasive_id': 168, 'habitat_id':
>> 1}, {'invasive_id': 168, 'habitat_id': 2}]
>>
>> I tried a clear() method like:
>>
>> invasive_object.habitats.clear()
>> for habitat in self.params.getlist('invasive_habitats'):
>> invasive_object.habitats.append(query_habitat.get(habitat))
>>
>> but it doesn't work ...
>> What did I wrong ?
>>
>> In advance, thanks !
>>
>> Julien
>>
>> --Julien Cigar
>> Belgian Biodiversity Platform
>> http://www.biodiversity.be
>> Université Libre de Bruxelles
>> Campus de la Plaine CP 257
>> Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
>> Boulevard du Triomphe, entrée ULB 2
>> B-1050 Bruxelles
>> office: [EMAIL PROTECTED]
>> home: [EMAIL PROTECTED]
>>
>>
>>
>> _______________________________________________
>> Sqlalchemy-users mailing list
>> Sqlalchemy-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
>
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
