Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'property'

I'm attempting to query Version where I wish to get objects back where their max_version_number attribute equals the version_number:

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

This results in an error:

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>}]

What am I doing wrong?

Here's the Version model.

This is my actual query which works without issues:

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

...however, that returns all versions and I wish to get only the ones of the highest version number returned from the query, which is why I'm attempting to filter on that as well.

like image 981
fredrik Avatar asked Jun 17 '16 10:06

fredrik


2 Answers

The problem is that you're passing a property descriptor object (Version.max_version_number) to compare, which psycopg2 has no idea about how to adapt. Instead you should use an SQL expression one way or the other. A common approach is to use hybrid attributes.

To get the rows with maximum versions grouped by tasks in project project_id you can use DISTINCT ON combined with ORDER BY in 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()

This will return just 1 Version per Task though, even if multiple instances of Version shared the same Version.version_number, which on the other hand would seem strange.

Another option is to use a subquery as a lookup of sorts and join to that:

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()
like image 72
Ilja Everilä Avatar answered Nov 03 '22 20:11

Ilja Everilä


In my case, I added a non-string variable inside the insert statement was

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

will return this error

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'type'

to solve it make sure you submit a valid data type

It should be

str(sys.exc_info ()) # string value
new_error = error_board(sys_message=sys_message)
like image 26
Mahmoud Magdy Avatar answered Nov 03 '22 20:11

Mahmoud Magdy