I can send an example later, but first I'll describe the general scenario which may be causing the issue. I dug through the codebase and removed any code that I thought could be potentially causing some weird object aliasing problems. To provide some insight, I'm building an adhoc query tool that allows a user to iteratively refine a query, so the expected workflow is as follows:
- Build initial query by selecting the columns desired. - Serialize it, assign a unique ID and save it to the database - Fetch from the database, unserialize it, and apply a filter in the form of a where condition, e.g. BIOPSY.SITE_DISTANCE > 5.0 - Serialize the new query with the applied filter and assign it a new, unique ID, then save to the database - Repeat Each time, I'm passing in the the same Engine, Session, and Metadata instance to sqlalchemy.ext.serializer.Deserializer. This could be a detail of the implementation of SA, and as a result tangential, but after digging through the internals of each unserialized Select, I can see that the internal Table objects referenced in the From clause have changed via the builtin id() function. Thanks for your help. - Mike On Thursday, November 14, 2013 10:36:36 AM UTC-5, Michael Bayer wrote: > > > On Nov 14, 2013, at 10:18 AM, Michael Woods <mikes...@gmail.com<javascript:>> > wrote: > > Hi SA community > > This issue has been bugging me for a while. Hopefully someone has some > input, since this issue is really impeding my work at this point. > > Using the following code > > query_object = select([id_column] + columns, > and_(*where_clauses), > from_parts, > use_labels=True, > correlate=False, > distinct=True) > > I am able to produce the valid query: > > SELECT DISTINCT accard."ESOPH_REGISTRY"."PTID" AS > "accard_ESOPH_REGISTRY_PTID", > accard."ESOPH_REGISTRY_VW"."GENDER" AS > "accard_ESOPH_REGISTRY_VW_GENDER", > accard."ESOPH_REGISTRY_VW"."LAST_NAME" AS > "accard_ESOPH_REGISTRY_VW_LAST_NAME", > accard."ESOPH_REGISTRY_VW"."FIRST_NAME" AS > "accard_ESOPH_REGISTRY_VW_FIRST_NAME", > accard."ESOPH_REGISTRY"."RACE" AS > "accard_ESOPH_REGISTRY_RACE", > accard."ESOPH_BIOPSY"."SPECIMEN_NO" AS > "accard_ESOPH_BIOPSY_SPECIMEN_NO", > accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" AS > "accard_ESOPH_BIOPSY_SITE_DISTANCE_CM", > accard."ESOPH_BIOPSY"."PATHOLOGY_CODE" AS > "accard_ESOPH_BIOPSY_PATHOLOGY_CODE" > FROM accard."ESOPH_REGISTRY", > accard."ESOPH_REGISTRY_VW" > LEFT OUTER JOIN accard."MASTER_REGISTRY" ON > accard."ESOPH_REGISTRY_VW"."UUID" = accard."MASTER_REGISTRY"."UUID", > accard."ESOPH_BIOPSY" > LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON > accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" = > accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID" > WHERE accard."ESOPH_ENDOSCOPY"."PTID" = accard."ESOPH_REGISTRY"."PTID" > AND accard."MASTER_REGISTRY"."UUID" = accard."ESOPH_REGISTRY"."UUID" > AND accard."ESOPH_REGISTRY"."PTID" = accard."ESOPH_REGISTRY_VW"."PTID" > AND upper(accard."ESOPH_ENDOSCOPY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_BIOPSY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_REGISTRY"."DISPLAY") = 'Y' > AND upper(accard."MASTER_REGISTRY"."DISPLAY") = 'Y' LIMIT 250 > > All is good and right with the world. > > However, once I add a new where clause, an invalid query is produced, and > the source of my frustration begins. > > For example, the following statement: > > query_object.append_whereclause(esoph_biopsy.c['site_distance_cm'] > 5.0) > > produces the query: > > SELECT DISTINCT accard."ESOPH_REGISTRY"."PTID" AS > "accard_ESOPH_REGISTRY_PTID", > accard."ESOPH_REGISTRY_VW"."GENDER" AS > "accard_ESOPH_REGISTRY_VW_GENDER", > accard."ESOPH_REGISTRY_VW"."LAST_NAME" AS > "accard_ESOPH_REGISTRY_VW_LAST_NAME", > accard."ESOPH_REGISTRY_VW"."FIRST_NAME" AS > "accard_ESOPH_REGISTRY_VW_FIRST_NAME", > accard."ESOPH_REGISTRY"."RACE" AS > "accard_ESOPH_REGISTRY_RACE", > accard."ESOPH_BIOPSY"."SPECIMEN_NO" AS > "accard_ESOPH_BIOPSY_SPECIMEN_NO", > accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" AS > "accard_ESOPH_BIOPSY_SITE_DISTANCE_CM", > accard."ESOPH_BIOPSY"."PATHOLOGY_CODE" AS > "accard_ESOPH_BIOPSY_PATHOLOGY_CODE" > FROM accard."ESOPH_REGISTRY", > accard."ESOPH_BIOPSY", > accard."ESOPH_REGISTRY_VW" > LEFT OUTER JOIN accard."MASTER_REGISTRY" ON > accard."ESOPH_REGISTRY_VW"."UUID" = accard."MASTER_REGISTRY"."UUID", > accard."ESOPH_BIOPSY" > LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON > accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" = > accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID" > WHERE accard."ESOPH_ENDOSCOPY"."PTID" = accard."ESOPH_REGISTRY"."PTID" > AND accard."MASTER_REGISTRY"."UUID" = accard."ESOPH_REGISTRY"."UUID" > AND accard."ESOPH_REGISTRY"."PTID" = accard."ESOPH_REGISTRY_VW"."PTID" > AND upper(accard."ESOPH_ENDOSCOPY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_BIOPSY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_REGISTRY"."DISPLAY") = 'Y' > AND upper(accard."MASTER_REGISTRY"."DISPLAY") = 'Y' > AND accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" > 5.0 LIMIT 250 > > When run, it produces the error <OperationalError> ambiguous column > name: accard.ESOPH_BIOPSY.SPECIMEN_NO, which is understandable, considering > the table ESOPH_BIOPSY appears twice in the FROM clause--once as a > standalone table, and once in a join: > > accard."ESOPH_BIOPSY" LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON > accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" = > accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID" > > I'm not doing anything strange in the construction of the Select object > via select(). The value of from_parts contains 5 items: the 2 standalone > tables (accard."ESOPH_REGISTRY" and accard."ESOPH_REGISTRY_VW"), and the 2 > joins. > > This is confusing behavior, as it contradicts the SQLAlchemy documentation > for select(), mainly the from_obj argument: > > > - *from_obj* – A list of > ClauseElement<http://docs.sqlalchemy.org/en/rel_0_8/core/sqlelement.html#sqlalchemy.sql.expression.ClauseElement> > objects > which will be added to the FROM clause of the resulting statement. > Note that “from” objects are automatically located within the columns and > whereclause ClauseElements. Use this parameter to explicitly specify > “from” > objects which are not automatically locatable. This could include > Table<http://docs.sqlalchemy.org/en/rel_0_8/core/metadata.html#sqlalchemy.schema.Table> > objects > that aren’t otherwise present, or > Join<http://docs.sqlalchemy.org/en/rel_0_8/core/selectable.html?highlight=join#sqlalchemy.sql.expression.Join> > objects > whose presence will supercede that of the > Table<http://docs.sqlalchemy.org/en/rel_0_8/core/metadata.html#sqlalchemy.schema.Table> > objects > already located in the other clauses. > > > My understanding of the above is that if I add a column to the WHERE > clause that references a table T, first all present FROM clause objects > will be checked for T first. If T appears in a join, T will not be added to > the FROM clause objects list. Likewise, if T already appears explictly in > the FROM clause it will not be added either. > > Am I missing something? > > > you need to send along a real example that illustrates this effect, it > indicates that a copy of the Table, or an alias of some kind, is doubling > up on the FROM list. this has to do with how the query was constructed in > the first place and without full specifics there’s no way to guess the > origins of this behavior. Also it’s probably a good idea to use > select().where(), producing a new select() object, rather than > append_whereclause() - the latter can lead to confusion if you re-inject > pieces of the existing select() into itself. > > > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
