(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
