Oh, it’s the serializer, that makes sense. An example would help and this is likely to be some bug that can be fixed (since a Table of a particular name should not change its id(), it comes back from the same MetaData collection based on name), but overall I would strongly recommend not using the serializer extension, and in fact I’d love to just remove it from SQLAlchemy - the query structures have proven to be too complicated for many versions of Python pickle, in particular the “pure python” version used by Pypy. We’ve had to disable a lot of tests of this system as many versions of pickle simply can’t handle the structure.
Storing SQL queries in the database for execution later, whether as plain SQL or as SQLAlchemy constructs IMO is not a best practice. Python pickles are a poor choice for database storage as they are language-dependent and insecure; SQL queries themselves are hardwired to a particular schema, and storing them in the database means you can no longer upgrade your database schema without rewriting all those queries somehow. If it were me, and “search criteria” needed to be stored, I’d create some simple criteria system that can be persisted and later turned back into a SQL query (or any kind of query) on the application side. On Nov 20, 2013, at 12:15 AM, Michael Woods <mikeswo...@gmail.com> wrote: > 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 > instanceto 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> 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 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 objects that aren’t >> otherwise present, or Join objects whose presence will supercede that of the >> 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 tosqlalchemy+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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
