sqlalchemy.exc.ProgrammingError：(心理学2.psycopg2.ProgrammingError)不能适应“属性”类型

Question

我正在尝试查询Version，希望将对象返回到它们的max_version_number属性等于version_number的位置

latest_versions = \
    dbSession.query(Version).filter(Version.max_version_number == Version.number_version)

这将导致一个错误：

Traceback (most recent call last):
...
...
    filter(Version.max_version_number == Version.version_number).\
  File "c:\python27\lib\site-packages\sqlalchemy\orm\query.py", line 2588, in all
    return list(self)
  File "c:\python27\lib\site-packages\sqlalchemy\orm\query.py", line 2736, in __iter__
    return self._execute_and_instances(context)
  File "c:\python27\lib\site-packages\sqlalchemy\orm\query.py", line 2751, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "c:\python27\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context
    context)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context
    context)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'property' [SQL: 'SELECT "Versions".id AS "Versions_id", "Links".id AS "Links_id", "Entities".id AS "Entities_id", "SimpleEntities".stalker_version AS "SimpleEntities_stalker_version", "SimpleEntities".id AS "SimpleEntities_id", "SimpleEntities".entity_type AS "SimpleEntities_entity_type", "SimpleEntities".name AS "SimpleEntities_name", "SimpleEntities".description AS "SimpleEntities_description", "SimpleEntities".created_by_id AS "SimpleEntities_created_by_id", "SimpleEntities".updated_by_id AS "SimpleEntities_updated_by_id", "SimpleEntities".date_created AS "SimpleEntities_date_created", "SimpleEntities".date_updated AS "SimpleEntities_date_updated", "SimpleEntities".type_id AS "SimpleEntities_type_id", "SimpleEntities".generic_text AS "SimpleEntities_generic_text", "SimpleEntities".thumbnail_id AS "SimpleEntities_thumbnail_id", "SimpleEntities".html_style AS "SimpleEntities_html_style", "SimpleEntities".html_class AS "SimpleEntities_html_class", "Links".original_filename AS "Links_original_filename", "Links".full_path AS "Links_full_path", "Versions".task_id AS "Versions_task_id", "Versions".take_name AS "Versions_take_name", "Versions".version_number AS "Versions_version_number", "Versions".is_published AS "Versions_is_published", "Versions".created_with AS "Versions_created_with", "Versions".parent_id AS "Versions_parent_id" \nFROM "SimpleEntities" JOIN "Entities" ON "SimpleEntities".id = "Entities".id JOIN "Links" ON "Entities".id = "Links".id JOIN "Versions" ON "Links".id = "Versions".id \nWHERE "Versions".version_number = %(version_number_1)s'] [parameters: {'version_number_1': <property object at 0x00000000060EF6D8>}]

我做错了什么？

下面是 Version模型。

这是我的实际查询，没有问题：

proj_id = 5
versions = Version.query.join(Task).filter(Task.project_id == proj_id).all()

...however，它返回所有版本，我希望只获得查询返回的最高版本号的版本，这也是我尝试对其进行筛选的原因。

Answer 1

问题是您要传递一个property描述符对象(Version.max_version_number)来进行比较，psycopg2不知道如何进行调整。相反，您应该以某种方式使用SQL表达式。一种常见的方法是使用混合属性。

要获得按项目project_id中的任务分组的最大版本的行，可以在Postgresql中使用与订单结合使用：

versions = Version.query\
    .join(Task)\
    .distinct(Version.task_id)\
    .filter(Task.project_id == proj_id)\
    .order_by(Version.task_id, Version.version_number.desc())\
    .all()

不过，即使多个Version实例共享相同的Version.version_number，每个Task也只返回一个Version.version_number，这在另一方面看起来很奇怪。

另一个选项是使用子查询作为排序查找，并加入到其中：

from sqlalchemy import tuple_

max_per_task = dbSession.query(Version.task_id,
                               func.max(Version.version_number).label('max'))\
    .join(Task)\
    .filter(Task.project_id == proj_id)\
    .group_by(Version.task_id)\
    .subquery()

versions = Version.query\
    .join(max_per_task,
          tuple_(max_per_task.c.task_id, max_per_task.c.max) ==
          tuple_(Version.task_id, Version.version_number))\
    .all()

Answer 2

在我的例子中，我在insert语句中添加了一个非字符串变量

sys_message = sys.exc_info() # none string value
new_error = error_board(sys_message=sys_message)

将返回此错误。

sqlalchemy.exc.ProgrammingError：(心理学2.psycopg2.ProgrammingError)不能适应类型' type‘

若要解决此问题，请确保提交了有效的数据类型。

它应该是

str(sys.exc_info ()) # string value
new_error = error_board(sys_message=sys_message)