Hello again,
Another strange thing I noticed today. Here is a part of my tables /
mappers :
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))
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('group_id', Integer, ForeignKey('invasive_groups.id')),
Column('subgroup_id', Integer, ForeignKey('invasive_subgroups.id')))
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=True)
}
)
session = create_session()
query_invasive = session.Query(Invasive)
cond = and_(query_invasive.join_to('habitats'))
order_by = Habitat.c.habitat
##
When I do the following, everything works fine :
invasive_objects = query_invasive.select(cond, order_by=order_by)
The generated query looks like :
SELECT invasives.nat_dispersal_capacity AS
invasives_nat_dispersal__1c9f, invasives.intr_accidental AS
invasives_intr_accidental, invasives.scientific_name AS
invasives_scientific_name, invasives.impact_disease_transmission AS
invasives_impact_disease_608, invasives.intr_petornamental AS
invasives_intr_petornamental, invasives.first_obs_belgium AS
invasives_first_obs_belgium, invasives.impact_competition AS
invasives_impact_competition, invasives.geographic_range AS
invasives_geographic_range, invasives.intr_culture AS
invasives_intr_culture, invasives.impact_health AS
invasives_impact_health, invasives.first_obs_flanders AS
invasives_first_obs_flanders, invasives.id AS invasives_id,
invasives.trend AS invasives_trend, invasives.black_lists_eppo AS
invasives_black_lists_eppo, habitats_47b7.id AS habitats_47b7_id,
habitats_47b7.habitat AS habitats_47b7_habitat, invasives.subgroup_id AS
invasives_subgroup_id, invasives.status AS invasives_status,
invasives.impact_economic AS invasives_impact_economic, invasives.added
AS invasives_added, invasives.black_lists_sebi AS
invasives_black_lists_sebi, invasives.nat_dense_populations AS
invasives_nat_dense_popu_f4af, invasives.impact_global AS
invasives_impact_global, invasives.nat_natural_habitats AS
invasives_nat_natural_ha_29c1, invasives.impact_hybridisation AS
invasives_impact_hybridi_79e2, invasives.intr_fur AS invasives_intr_fur,
invasives.black_lists_belg AS invasives_black_lists_belg,
invasives.intr_hunting AS invasives_intr_hunting,
invasives.impact_ecosystem_disruption AS invasives_impact_ecosyst_dc3d,
invasives.modified AS invasives_modified, invasives.intr_canals AS
invasives_intr_canals, invasives.intr_fishing AS invasives_intr_fishing,
invasives.first_obs_wallonia AS invasives_first_obs_wallonia,
invasives.intr_deliberated AS invasives_intr_deliberated,
invasives.impact_predation AS invasives_impact_predation,
invasives.group_id AS invasives_group_id, invasives.nat_reproduction AS
invasives_nat_reproduction
FROM invasive_habitats, habitats, invasives LEFT OUTER JOIN
invasive_habitats AS invasive_habita_d386 ON invasives.id =
invasive_habita_d386.invasive_id LEFT OUTER JOIN habitats AS
habitats_47b7 ON habitats_47b7.id = invasive_habita_d386.habitat_id
WHERE (invasives.id = invasive_habitats.invasive_id AND
habitats.id = invasive_habitats.habitat_id) AND invasives.status = 1
ORDER BY habitats_47b7.habitat, invasive_habita_d386.invasive_id
However, if I do :
invasive_objects = query_invasive.select(cond, order_by=order_by,
limit=limit, offset=offset)
I got an error :
2006-06-19 16:55:06 [22183] LOG: statement: SELECT
invasives.nat_dispersal_capacity AS invasives_nat_dispersal__3468,
invasives.intr_accidental AS invasives_intr_accidental,
invasives.scientific_name AS invasives_scientific_name,
invasives.impact_disease_transmission AS invasives_impact_disease_b32f,
invasives.intr_petornamental AS invasives_intr_petornamental,
invasives.first_obs_belgium AS invasives_first_obs_belgium,
invasives.impact_competition AS invasives_impact_competition,
invasives.geographic_range AS invasives_geographic_range,
invasives.intr_culture AS invasives_intr_culture,
invasives.impact_health AS invasives_impact_health,
invasives.first_obs_flanders AS invasives_first_obs_flanders,
invasives.id AS invasives_id, invasives.trend AS invasives_trend,
invasives.black_lists_eppo AS invasives_black_lists_eppo,
habitats_6ae0.id AS habitats_6ae0_id, habitats_6ae0.habitat AS
habitats_6ae0_habitat, invasives.subgroup_id AS invasives_subgroup_id,
invasives.status AS invasives_status, invasives.impact_economic AS
invasives_impact_economic, invasives.added AS invasives_added,
invasives.black_lists_sebi AS invasives_black_lists_sebi,
invasives.nat_dense_populations AS invasives_nat_dense_popu_f47b,
invasives.impact_global AS invasives_impact_global,
invasives.nat_natural_habitats AS invasives_nat_natural_ha_299,
invasives.impact_hybridisation AS invasives_impact_hybridi_46f0,
invasives.intr_fur AS invasives_intr_fur, invasives.black_lists_belg AS
invasives_black_lists_belg, invasives.intr_hunting AS
invasives_intr_hunting, invasives.impact_ecosystem_disruption AS
invasives_impact_ecosyst_2cfe, invasives.modified AS invasives_modified,
invasives.intr_canals AS invasives_intr_canals, invasives.intr_fishing
AS invasives_intr_fishing, invasives.first_obs_wallonia AS
invasives_first_obs_wallonia, invasives.intr_deliberated AS
invasives_intr_deliberated, invasives.impact_predation AS
invasives_impact_predation, invasives.group_id AS invasives_group_id,
invasives.nat_reproduction AS invasives_nat_reproduction
FROM (SELECT invasives.id AS invasives_id
FROM invasives, invasive_habitats, habitats
WHERE (invasives.id = invasive_habitats.invasive_id AND
habitats.id = invasive_habitats.habitat_id) AND invasives.status = 1
ORDER BY habitats_6ae0.habitat DESC
LIMIT 200 OFFSET 0) AS rowcount, invasives LEFT OUTER JOIN
invasive_habitats AS invasive_habita_f18f ON invasives.id =
invasive_habita_f18f.invasive_id LEFT OUTER JOIN habitats AS
habitats_6ae0 ON habitats_6ae0.id = invasive_habita_f18f.habitat_id
WHERE rowcount.invasives_id = invasives.id ORDER BY
habitats_6ae0.habitat DESC, invasive_habita_f18f.invasive_id
2006-06-19 16:55:06 [22183] ERROR: relation "habitats_6ae0" does not exist
Does it look like a bug or I missed something about the limit/offset
clues ?
Thanks !
Julien
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
