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.
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()
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With