On Apr 24, 2014, at 5:20 PM, SAY <[email protected]> wrote: > After upgrading SQLAlchemy from 0.7.8 to 0.9.4, we got the following > exception:
While we no longer need to make the major API changes that were common back in our early days of version 0.2, 0.3, 0.4, we still need to make repairs to systems which impact upon lesser used usage patterns. These changes continue to decrease and we are targeting that by 1.0 (next year) there will be little to no backwards-incompatible changes to public APIs on major releases going forward. Since 0.6 or so, users are unaffected by the majority of backwards-incompatible changes as they now target patterns that are mostly little-known and involved in an inconsistency or are the product of bugs being fixed. Most of the upgrade issues we get are instead regressions, which we fix immediately so that users can upgrade smoothly. But we still make sure a very detailed and comprehensive "what's changing?" guide is published months before beta releases, so that any pre-migration code adjustments can be made (we always provide for forwards compatibility unless it is completely impossible). Users definitely should be reading these guides when upgrading versions. These guides for 0.8 and 0.9 are at http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_08.html and http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html. In this case you are hitting upon an API feature that to my knowledge almost nobody uses consciously and in the common case would instead confuse users when it becomes apparent. The pattern is: session.query(A).select_from(union_all(q1, q2)) where the expectation is that the query will replace all mentions of "A" with a new selectable derived from the union. This is a little-known usage pattern and was causing confusion with select_from's more typical use which is just to set the FROM clause. The correct call which is supported from 0.8 on forward is select_entity_from(). Full background on this change is at: http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#query-select-from-no-longer-applies-the-clause-to-corresponding-entities > > Traceback (most recent call last): > File "/opt/2.0/flx/pylons/flx/flx/controllers/assignment.py", line 1824, in > getGroupAssignments > pageSize=pageSize) > File "/opt/2.0/flx/pylons/flx/flx/model/api.py", line 13638, in > _getAssignmentsByCreatorID > page = p.Page(query, pageNum, pageSize) > File "/opt/2.0/flx/pylons/flx/flx/model/page.py", line 53, in __init__ > Subset.__init__(self, query, offset, self.size) > File "/opt/2.0/flx/pylons/flx/flx/model/page.py", line 9, in __init__ > self.results = query.offset(offset).limit(limit).all() > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line > 2292, in all > return list(self) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line > 2404, in __iter__ > return self._execute_and_instances(context) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line > 2419, in _execute_and_instances > result = conn.execute(querycontext.statement, self._params) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", > line 720, in execute > return meth(self, multiparams, params) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/sql/elements.py", > line 317, in _execute_on_connection > return connection._execute_clauseelement(self, multiparams, params) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", > line 817, in _execute_clauseelement > compiled_sql, distilled_params > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", > line 947, in _execute_context > context) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", > line 1108, in _handle_dbapi_exception > exc_info > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/util/compat.py", > line 185, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/base.py", > line 940, in _execute_context > context) > File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/engine/default.py", > line 435, in do_execute > cursor.execute(statement, parameters) > File > "/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg/MySQLdb/cursors.py", > line 174, in execute > self.errorhandler(self, exc, value) > File > "/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg/MySQLdb/connections.py", > line 36, in defaulterrorhandler > raise errorclass, errorvalue > InternalError: (InternalError) (126, "Incorrect key file for table > '/tmp/#sql_52d_2.MYI'; try to repair it") 'SELECT > substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, `Artifacts`.id > AS `Artifacts_id`, `Artifacts`.`artifactTypeID` AS > `Artifacts_artifactTypeID`, `Artifacts`.`encodedID` AS `Artifacts_encodedID`, > `Artifacts`.name AS `Artifacts_name`, `Artifacts`.description AS > `Artifacts_description`, `Artifacts`.handle AS `Artifacts_handle`, > `Artifacts`.`creatorID` AS `Artifacts_creatorID`, `Artifacts`.`ancestorID` AS > `Artifacts_ancestorID`, `Artifacts`.`licenseID` AS `Artifacts_licenseID`, > `Artifacts`.`creationTime` AS `Artifacts_creationTime`, > `Artifacts`.`updateTime` AS `Artifacts_updateTime`, `Licenses_1`.id AS > `Licenses_1_id`, `Licenses_1`.name AS `Licenses_1_name`, > `Licenses_1`.description AS `Licenses_1_description`, `ArtifactTypes_1`.id AS > `ArtifactTypes_1_id`, `ArtifactTypes_1`.name AS `ArtifactTypes_1_name`, > `ArtifactTypes_1`.`extensionType` AS `ArtifactTypes_1_extensionType`, > `ArtifactTypes_1`.description AS `ArtifactTypes_1_description`, > `ArtifactTypes_1`.modality AS `ArtifactTypes_1_modality` \nFROM `Artifacts`, > (SELECT anon_2.`sortableName` AS `sortableName`, anon_2.id AS id, > anon_2.`artifactTypeID` AS `artifactTypeID`, anon_2.`encodedID` AS > `encodedID`, anon_2.name AS name, anon_2.description AS description, > anon_2.handle AS handle, anon_2.`creatorID` AS `creatorID`, > anon_2.`ancestorID` AS `ancestorID`, anon_2.`licenseID` AS `licenseID`, > anon_2.`creationTime` AS `creationTime`, anon_2.`updateTime` AS `updateTime` > \nFROM (SELECT substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, > `Artifacts`.id AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`, > `Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name, > `Artifacts`.description AS description, `Artifacts`.handle AS handle, > `Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS > `ancestorID`, `Artifacts`.`licenseID` AS `licenseID`, > `Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS > `updateTime` \nFROM `Artifacts` \nWHERE `Artifacts`.`creatorID` = %s AND > `Artifacts`.`artifactTypeID` = %s AND `Artifacts`.id NOT IN (%s, %s) ORDER BY > `Artifacts`.id DESC) AS anon_2 UNION ALL SELECT anon_3.`sortableName` AS > `sortableName`, anon_3.id AS id, anon_3.`artifactTypeID` AS `artifactTypeID`, > anon_3.`encodedID` AS `encodedID`, anon_3.name AS name, anon_3.description AS > description, anon_3.handle AS handle, anon_3.`creatorID` AS `creatorID`, > anon_3.`ancestorID` AS `ancestorID`, anon_3.`licenseID` AS `licenseID`, > anon_3.`creationTime` AS `creationTime`, anon_3.`updateTime` AS `updateTime` > \nFROM (SELECT substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, > `Artifacts`.id AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`, > `Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name, > `Artifacts`.description AS description, `Artifacts`.handle AS handle, > `Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS > `ancestorID`, `Artifacts`.`licenseID` AS `licenseID`, > `Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS > `updateTime` \nFROM `Artifacts` INNER JOIN `Assignments` ON > `Assignments`.`assignmentID` = `Artifacts`.id AND `Assignments`.`groupID` = > %s \nWHERE `Artifacts`.`creatorID` = %s AND `Artifacts`.`artifactTypeID` = %s > ORDER BY isnull(`Assignments`.due), `Assignments`.due ASC) AS anon_3) AS > anon_1 LEFT OUTER JOIN `Licenses` AS `Licenses_1` ON `Licenses_1`.id = > anon_1.`licenseID` LEFT OUTER JOIN `ArtifactTypes` AS `ArtifactTypes_1` ON > `ArtifactTypes_1`.id = anon_1.`artifactTypeID` ORDER BY > `Artifacts`.`artifactTypeID` DESC \n LIMIT %s, %s' ('-::of::-', 1, > '-::of::-', 1, 12L, 55, 1444153L, 1444204L, '-::of::-', 1, '3021', 12L, 56, > 0, 10) > > So we downgraded it to 0.8.6 and it worked again. The query generated was > different from that from 0.9.4: > > SELECT anon_1.`sortableName` AS `anon_1_sortableName`, anon_1.id AS > anon_1_id, anon_1.`artifactTypeID` AS `anon_1_artifactTypeID`, > anon_1.`encodedID` AS `anon_1_encodedID`, anon_1.name AS anon_1_name, > anon_1.description AS anon_1_description, anon_1.handle AS anon_1_handle, > anon_1.`creatorID` AS `anon_1_creatorID`, anon_1.`ancestorID` AS > `anon_1_ancestorID`, anon_1.`licenseID` AS `anon_1_licenseID`, > anon_1.`creationTime` AS `anon_1_creationTime`, anon_1.`updateTime` AS > `anon_1_updateTime`, `Licenses_1`.id AS `Licenses_1_id`, `Licenses_1`.name AS > `Licenses_1_name`, `Licenses_1`.description AS `Licenses_1_description`, > `ArtifactTypes_1`.id AS `ArtifactTypes_1_id`, `ArtifactTypes_1`.name AS > `ArtifactTypes_1_name`, `ArtifactTypes_1`.`extensionType` AS > `ArtifactTypes_1_extensionType`, `ArtifactTypes_1`.description AS > `ArtifactTypes_1_description`, `ArtifactTypes_1`.modality AS > `ArtifactTypes_1_modality` FROM (SELECT anon_2.`sortableName` AS > `sortableName`, anon_2.id AS id, anon_2.`artifactTypeID` AS `artifactTypeID`, > anon_2.`encodedID` AS `encodedID`, anon_2.name AS name, anon_2.description AS > description, anon_2.handle AS handle, anon_2.`creatorID` AS `creatorID`, > anon_2.`ancestorID` AS `ancestorID`, anon_2.`licenseID` AS `licenseID`, > anon_2.`creationTime` AS `creationTime`, anon_2.`updateTime` AS `updateTime` > FROM (SELECT substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, > `Artifacts`.id AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`, > `Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name, > `Artifacts`.description AS description, `Artifacts`.handle AS handle, > `Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS > `ancestorID`, `Artifacts`.`licenseID` AS `licenseID`, > `Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS > `updateTime` FROM `Artifacts` WHERE `Artifacts`.`creatorID` = %s AND > `Artifacts`.`artifactTypeID` = %s AND `Artifacts`.id NOT IN (%s, %s) ORDER BY > `Artifacts`.id DESC) AS anon_2 UNION ALL SELECT anon_3.`sortableName` AS > `sortableName`, anon_3.id AS id, anon_3.`artifactTypeID` AS `artifactTypeID`, > anon_3.`encodedID` AS `encodedID`, anon_3.name AS name, anon_3.description AS > description, anon_3.handle AS handle, anon_3.`creatorID` AS `creatorID`, > anon_3.`ancestorID` AS `ancestorID`, anon_3.`licenseID` AS `licenseID`, > anon_3.`creationTime` AS `creationTime`, anon_3.`updateTime` AS `updateTime` > FROM (SELECT substring_index(`Artifacts`.name, %s, %s) AS `sortableName`, > `Artifacts`.id AS id, `Artifacts`.`artifactTypeID` AS `artifactTypeID`, > `Artifacts`.`encodedID` AS `encodedID`, `Artifacts`.name AS name, > `Artifacts`.description AS description, `Artifacts`.handle AS handle, > `Artifacts`.`creatorID` AS `creatorID`, `Artifacts`.`ancestorID` AS > `ancestorID`, `Artifacts`.`licenseID` AS `licenseID`, > `Artifacts`.`creationTime` AS `creationTime`, `Artifacts`.`updateTime` AS > `updateTime` FROM `Artifacts` INNER JOIN `Assignments` ON > `Assignments`.`assignmentID` = `Artifacts`.id AND `Assignments`.`groupID` = > %s WHERE `Artifacts`.`creatorID` = %s AND `Artifacts`.`artifactTypeID` = %s > ORDER BY isnull(`Assignments`.due), `Assignments`.due ASC) AS anon_3) AS > anon_1 LEFT OUTER JOIN `Licenses` AS `Licenses_1` ON `Licenses_1`.id = > anon_1.`licenseID` LEFT OUTER JOIN `ArtifactTypes` AS `ArtifactTypes_1` ON > `ArtifactTypes_1`.id = anon_1.`artifactTypeID` ORDER BY > anon_1.`artifactTypeID` DESC LIMIT %s, %s ('-::of::-', 1, 12L, 55, 1444153L, > 1444204L, '-::of::-', 1, '3021', 12L, 56, 0, 10) > > Here is the python code for that query: > > def _getAssignmentsByCreatorID(session, creatorID, typeIDs, artifactTypeDict, > groupID=None, sort=None, pageNum=1, pageSize=10): > stID = artifactTypeDict['study-track'] > asID = artifactTypeDict['assignment'] > if stID in typeIDs and asID in typeIDs: > # > # Get the study tracks for this creator. > # > query = session.query(model.Artifact.id) > query = query.filter_by(creatorID=creatorID) > query = query.filter_by(artifactTypeID=stID) > idList = query.all() > ids = [] > for id in idList: > ids.append(id[0]) > > > # > # Find out the study tracks that are already assigned. > # > query = session.query(model.ArtifactAndChildren.childID) > query = query.filter_by(artifactTypeID=asID) > if groupID: > query = query.join(model.Assignment, > and_(model.Assignment.assignmentID == > model.ArtifactAndChildren.id, > model.Assignment.groupID == groupID)) > query = query.filter(model.ArtifactAndChildren.childID.in_(ids)) > idList = query.all() > ids = [] > for id in idList: > ids.append(id[0]) > # > # Get the qualified artifacts. > # > stQuery = session.query(model.Artifact) > stQuery = stQuery.filter_by(creatorID=creatorID) > stQuery = stQuery.filter_by(artifactTypeID=stID) > if stID in typeIDs and asID in typeIDs: > # > # Skip the assigned study tracks. > # > stQuery = stQuery.filter(not_(model.Artifact.id.in_(ids))) > stQuery = stQuery.order_by(model.Artifact.id.desc()) > > if asID not in typeIDs: > asQuery = None > else: > asQuery = session.query(model.Artifact) > asQuery = asQuery.filter_by(creatorID=creatorID) > asQuery = asQuery.filter_by(artifactTypeID=asID) > if not groupID: > asQuery = asQuery.join(model.Assignment, > model.Assignment.assignmentID == > model.Artifact.id) > else: > asQuery = asQuery.join(model.Assignment, > and_(model.Assignment.assignmentID == > model.Artifact.id, > model.Assignment.groupID == groupID)) > # > > > # Sorting order for assignment specific. > # > sortedByDueDate = False > if sort: > asgn = model.Assignment > col, order = sort.split(',') > if col == 'due': > oby = asgn.due > if order == 'asc': > asQuery = asQuery.order_by(func.isnull(oby), asc(oby)) > else: > asQuery = asQuery.order_by(func.isnull(oby), desc(oby)) > sortedByDueDate = True > sort = None > > if not sortedByDueDate: > asQuery = asQuery.order_by(func.isnull(model.Assignment.due), > model.Assignment.due) > > if not asQuery: > query = stQuery > else: > # > # SQLAlchemy does not put parentheses on queries between union > # so the order by clause won't work unless we use subquery. > # > stQuery = stQuery.subquery() > asQuery = asQuery.subquery() > query = session.query(model.Artifact) > query = query.select_from(union_all(stQuery.select(), > asQuery.select())) > # > # Sorting order. > # > if not sort: > query = query.order_by(model.Artifact.artifactTypeID.desc()) > else: > art = model.Artifact > col, order = sort.split(',') > oby = None > if col == 'name': > oby = art.name > elif col == 'assign': > oby = art.artifactTypeID > if oby: > if order == 'asc': > query = query.order_by(asc(oby)) > else: > query = query.order_by(desc(oby)) > > page = p.Page(query, pageNum, pageSize) > return page > > Thanks, > Stephen > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
