Hey there!
I was testing my service room.list and found this error on the logs:
2018-11-15 12:08:43,649 - WARNING - 136:DummyThread-65 - zato.server.service:502 - Traceback (most recent call last):
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 477, in update_handle
self._invoke(service, channel)
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 399, in _invoke
service.handle()
File "/opt/zato/env/qs-1/server2/work/hot-deploy/current/room.py", line 266, in handle
query = query.add_columns(Cols[c])
File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
return self._data[key]
KeyError: u'accommodates'
2018-11-15 12:08:43,651 DEBG 'zato-server2' stdout output:
2018-11-15 12:08:43,650 - ERROR - 136:DummyThread-65 - zato.server.connection.http_soap.channel:324 - Caught an exception, cid:`b302d6ac64bdbe4708a2cdb2`, status_code:`500`, _format_exc:`Traceback (most recent call last):
File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 268, in dispatch
payload, worker_store, self.simple_io_config, post_data, path_info, soap_action)
File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 502, in handle
params_priority=channel_item.params_pri)
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 477, in update_handle
self._invoke(service, channel)
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 399, in _invoke
service.handle()
File "/opt/zato/env/qs-1/server2/work/hot-deploy/current/room.py", line 266, in handle
query = query.add_columns(Cols[c])
File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
return self._data[key]
KeyError: u'accommodates'
So the first thing that came into my mind was this thread about hybrid properties and queries.
By checking the modifications made on the code, I realised that, by the time of the abovementioned discussion, I was querying like this:
session.query(Room).filter(and_(Room.id == id_, Room.deleted.is_(None))).one_or_none()
Whereas now, in the room.list service, I am querying by adding all columns manually (due to the fields projection feature, in which one is allowed to select which fields to be returned by using the fields query string parameter:
fields_allowed = ('id', 'floor_no', 'room_no', 'name', 'sgl_beds',
'dbl_beds', 'supplement', 'code', 'accommodates',
'number', 'deleted')
query = session.query(func.count().over().label('count'))
if not columns:
columns = self.fields_allowed
for c in columns:
query = query.add_columns(Room.__table__.columns)
output_optional in the SimpleIO class is as follows:
output_optional = ('id', 'floor_no', 'room_no', 'sgl_beds', 'dbl_beds',
'supplement', 'code', 'name', 'accommodates', 'number', 'count')
So the first thing that came into my mind was that I needed to add expressions to the Room model class. Unfortunately, that didn’t turn out very well (partially, and probably, due to my lack of hability or capacity):
from sqlalchemy import func
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.dialects import postgresql
@hybrid_property
def accommodates(self):
return self.sgl_beds + self.dbl_beds * 2
@accommodates.expression
def accommodates(cls):
return (cls.sgl_beds + cls.dbl_beds * 2)
@hybrid_property
def number(self):
return '%d%02d' % (self.floor_no, self.room_no)
@number.expression
def number(cls):
return (func.cast(cls.floor_no, String) +
func.lpad(func.cast(cls.room_no, postgresql.TEXT), 2, '0'))
The error was still the same:
2018-11-16 19:15:25,165 - WARNING - 137:DummyThread-24 - zato.server.service:502 - Traceback (most recent call last):
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 477, in update_handle
self._invoke(service, channel)
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 399, in _invoke
service.handle()
File "/opt/zato/env/qs-1/server1/work/hot-deploy/current/room.py", line 278, in handle
query = query.add_columns(Cols[c])
File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
return self._data[key]
KeyError: u'number'
2018-11-16 19:15:25,167 DEBG 'zato-server1' stdout output:
2018-11-16 19:15:25,166 - ERROR - 137:DummyThread-24 - zato.server.connection.http_soap.channel:324 - Caught an exception, cid:`b5fe826016cc20e4e370c65d`, status_code:`500`, _format_exc:`Traceback (most recent call last):
File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 268, in dispatch
payload, worker_store, self.simple_io_config, post_data, path_info, soap_action)
File "/opt/zato/3.0/code/zato-server/src/zato/server/connection/http_soap/channel.py", line 502, in handle
params_priority=channel_item.params_pri)
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 477, in update_handle
self._invoke(service, channel)
File "/opt/zato/3.0/code/zato-server/src/zato/server/service/__init__.py", line 399, in _invoke
service.handle()
File "/opt/zato/env/qs-1/server1/work/hot-deploy/current/room.py", line 278, in handle
query = query.add_columns(Cols[c])
File "/opt/zato/3.0/code/local/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
return self._data[key]
KeyError: u'number'
So the next thing I did was to remove number and accomodates from the tuple fields_allowed:
fields_allowed = ('id', 'floor_no', 'room_no', 'name', 'sgl_beds',
'dbl_beds', 'supplement', 'code', 'deleted')
And executing the call (using curl) to http://127.0.0.1:11223/genesisng/rooms/list was now not failing, but the hybrid properties number and accommodates were not there:
$ curl -v -g "http://127.0.0.1:11223/genesisng/rooms/list"; echo ""
* Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to 127.0.0.1 (127.0.0.1) port 11223 (#0)
> GET /genesisng/rooms/list HTTP/1.1
> Host: 127.0.0.1:11223
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Server: Zato
< Date: Fri, 16 Nov 2018 19:19:41 GMT
< Connection: close
< Transfer-Encoding: chunked
< Content-Type: application/json
< X-Zato-CID: 769adfe37f8c8da88bef6de2
< Cache-Control: no-cache
<
{"response": [{"count": 6, "code": "pink", "sgl_beds": 2, "room_no": 1, "floor_no": 1, "supplement": 20.0, "dbl_beds": 0, "id": 1, "name": "Normal bedroom with two single beds"}, {"count": 6, "code": "black", "sgl_beds": 2, "room_no": 2, "floor_no": 1, "supplement": 40.0, "dbl_beds": 1, "id": 2, "name": "Large bedroom with two single and one double beds"}, {"count": 6, "code": "white", "sgl_beds": 3, "room_no": 3, "floor_no": 1, "supplement": 50.0, "dbl_beds": 1, "id": 3, "name": "Very large bedroom with three single and one double beds"}, {"count": 6, "code": "purple", "sgl_beds": 4, "room_no": 4, "floor_no": 1, "supplement": 40.0, "dbl_beds": 0, "id": 4, "name": "Very large bedroom with four single beds"}, {"count": 6, "code": "blue", "sgl_beds": 3, "room_no": 5, "floor_no": 1, "supplement": 30.0, "dbl_beds": 0, "id": 5, "name": "Large bedroom with three single beds"}, {"count": 6, "code": "brown", "sgl_beds": 0, "room_no": 6, "floor_no": 1, "supplement": 20.0, "dbl_beds": 1, "id": 6, "name": "Normal bedroo* Closing connection 0
m with one double bed"}]}
So the next thing I did was to modify the code so that it looked like the room.get service, that is:
- Use session.query(Room).
- Add no other attributes (no count, no number and no accommodates).
query = session.query(Room)
And it worked fine (as expected, as room.get does work). Number and accommodates are back in the list of results.
Then I did one more test and added just count, like this:
query = session.query(Room, func.count().over().label('count'))
And I got no error, but only count is present in the result:
$ curl -v -g "http://127.0.0.1:11223/genesisng/rooms/list"; echo ""
* Trying 127.0.0.1...
* TCP_NODELAY set
* Connected to 127.0.0.1 (127.0.0.1) port 11223 (#0)
> GET /genesisng/rooms/list HTTP/1.1
> Host: 127.0.0.1:11223
> User-Agent: curl/7.58.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Server: Zato
< Date: Fri, 16 Nov 2018 19:26:27 GMT
< Connection: close
< Transfer-Encoding: chunked
< Content-Type: application/json
< X-Zato-CID: 699dbf7b6132fb07119c676c
< Cache-Control: no-cache
<
* Closing connection 0
{"response": [{"count": 6}, {"count": 6}, {"count": 6}, {"count": 6}, {"count": 6}, {"count": 6}]}
I checked the queries being executed and they are all the same each and everytime results are returned (despite the fact that some attributes are missing from the returned JSON). When there is an error on the logs, no SQL query appears on the database logs:
2018-11-16 20:26:27.696 CET [22732] genesisng@genesisng LOG: execute pg8000_statement_137_12: SELECT room.id AS room_id, room.floor_no AS room_floor_no, room.room_no AS room_room_no, room.name AS room_name, room.sgl_beds AS room_sgl_beds, roo
m.dbl_beds AS room_dbl_beds, room.supplement AS room_supplement, room.code AS room_code, room.deleted AS room_deleted, count(*) OVER () AS count
FROM room ORDER BY room.id ASC
LIMIT $1 OFFSET $2
2018-11-16 20:26:27.696 CET [22732] genesisng@genesisng DETAIL: parameters: $1 = '20', $2 = '0'
So, the conclussion is that hybrid properties are only returned if the queried fields are exactly the ones in the model class.
At the moment I do not need to filter by number or accommodates, so I can/could take them out of the fields_allowed tuple and hope there is a fix as there was one for the hybrid properties to be returned as part of the JSON before.
Generally speaking, though, it would be a nice feature to have and, from the SQLAlchemy documentation, I take it that I’d require the abovementioned hybrid property expressions on the model class to work.
Worst case scenario, I would have to forget about hybrid properties and just have them as table columns being filled in by triggers or some other mechanism SQLAlchemy may provide.
So, ideally, from less to more:
- Fix (again) to allow hybrid properties to be returned as part of the JSON when other attributes, such as count, is being added to the query.
- Allow the possiblity to execute queries that use hybrid attributes, i.e. by having hybrid property expressions on the model class
Thanks in advance.
Addendum
Here you are the service room.list as it’s supposed to be running:
class List(Service):
"""Service class to get a list of all rooms in the system."""
"""Channel /genesisng/rooms/list."""
model = Room
criteria_allowed = ('id', 'number')
direction_allowed = ('asc', 'desc')
filters_allowed = ('id', 'floor_no', 'sgl_beds', 'dbl_beds', 'code')
comparisons_allowed = ('lt', 'lte', 'eq', 'ne', 'gte', 'gt')
operators_allowed = ('and', 'or')
fields_allowed = ('id', 'floor_no', 'room_no', 'name', 'sgl_beds',
'dbl_beds', 'supplement', 'code', 'deleted',
'number', 'accommodates')
search_allowed = ()
class SimpleIO:
input_optional = (List('page'), List('size'), List('sort'),
List('filters'), List('fields'), List('operator'),
List('search'))
output_optional = ('id', 'floor_no', 'room_no', 'sgl_beds', 'dbl_beds',
'supplement', 'code', 'name', 'accommodates',
'number', 'count')
skip_empty_keys = True
output_repeated = True
def handle(self):
conn = self.user_config.genesisng.database.connection
default_page_size = int(
self.user_config.genesisng.pagination.default_page_size)
max_page_size = int(
self.user_config.genesisng.pagination.max_page_size)
Cols = self.model.__table__.columns
# TODO: Have these default values in user config?
default_criteria = 'id'
default_direction = 'asc'
default_operator = 'and'
# Page number
try:
page = int(self.request.input.page[0])
except (ValueError, KeyError, IndexError):
page = 1
# Page size
try:
size = int(self.request.input.size[0])
except (ValueError, KeyError, IndexError):
size = default_page_size
# Order by
try:
criteria, direction = self.request.input.sort[0].lower().split('|')
except (ValueError, KeyError, IndexError, AttributeError):
criteria = default_criteria
direction = default_direction
# Filters
try:
filters = self.request.input.filters
operator = self.request.input.operator[0]
except (ValueError, KeyError, IndexError):
filters = []
operator = default_operator
# Fields projection
try:
fields = self.request.input.fields
except (ValueError, KeyError):
fields = []
# Search
try:
search = self.request.input.search[0]
except (ValueError, KeyError, IndexError):
search = None
# Check and adjust parameter values
# Handle pagination
page = 1 if page < 1 else page
size = default_page_size if size < 1 else size
size = default_page_size if size > max_page_size else size
# Handle sorting
if criteria not in self.criteria_allowed:
criteria = default_criteria
if direction not in self.direction_allowed:
direction = default_direction
# Handle filtering
conditions = []
for filter_ in filters:
field, comparison, value = filter_.split('|')
if field in self.filters_allowed and \
comparison in self.comparisons_allowed:
conditions.append((field, comparison, value))
if operator not in self.operators_allowed:
operator = default_operator
# Handle fields projection
columns = []
for f in fields:
if f in self.fields_allowed:
columns.append(f)
# Handle search
if not self.search_allowed:
search = None
# Compose query
with closing(self.outgoing.sql.get(conn).session()) as session:
query = session.query(func.count().over().label('count'))
# Add columns
if not columns:
columns = self.fields_allowed
for c in columns:
query = query.add_columns(Cols[c])
# Prepare filters
if conditions:
clauses = []
for c in conditions:
f, o, v = c
if o == 'lt':
clauses.append(Cols[f] < v)
elif o == 'lte':
clauses.append(Cols[f] <= v)
elif o == 'eq':
clauses.append(Cols[f] == v)
elif o == 'ne':
clauses.append(Cols[f] != v)
elif o == 'gte':
clauses.append(Cols[f] >= v)
elif o == 'gt':
clauses.append(Cols[f] > v)
if operator == 'or':
query = query.filter(or_(*clauses))
else:
query = query.filter(and_(*clauses))
# Search
if search:
clauses = []
for s in self.search_allowed:
clauses.append(Cols[s].ilike(search))
query = query.filter(or_(*clauses))
# Order by
if direction == 'asc':
query = query.order_by(Cols[criteria].asc())
else:
query = query.order_by(Cols[criteria].desc())
# Calculate limit and offset
limit = size
offset = size * (page - 1)
query = query.offset(offset)
query = query.limit(limit)
# Execute query
result = query.all()
# Return result
if result:
self.response.payload[:] = result
self.response.status_code = OK
self.response.headers['Cache-Control'] = 'no-cache'
else:
self.response.status_code = NO_CONTENT
self.response.headers['Cache-Control'] = 'no-cache'
And here is the model class, with the two added attempts at hybrid propertiy expressions:
# coding: utf8
from __future__ import absolute_import, division
from __future__ import print_function, unicode_literals
from .base import Base
from sqlalchemy import Column, Integer, Float, String, DateTime, func
from sqlalchemy import UniqueConstraint, CheckConstraint
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.dialects import postgresql
import hashids
def generate_code(context):
# TODO: Add a salt value?
# hashids = Hashids(salt='this is my salt 1')
# hashid = hashids.encode(value)
return hashids.encode(context.current_parameters.get('floor_no'),
context.current_parameters.get('room_no'),
context.current_parameters.get('sgl_beds'),
context.current_parameters.get('dbl_beds'))
class Room(Base):
__tablename__ = 'room'
__rels__ = []
__table_args__ = (
# Combination of floor number and room number must be unique.
UniqueConstraint('floor_no', 'room_no', name='room_floor_no_room_no'),
# The sum of beds must be a positive integer.
CheckConstraint('sgl_beds + dbl_beds > 0'),
)
# SQLAlchemy automatically creates the table column using the SERIAL type
# which triggers the creation of a sequence automatically.
id = Column(Integer, primary_key=True)
floor_no = Column(Integer, nullable=False)
room_no = Column(Integer, nullable=False)
name = Column(String(100), index=True)
sgl_beds = Column(Integer, nullable=False, default=0, index=True)
dbl_beds = Column(Integer, nullable=False, default=0, index=True)
supplement = Column(Float, nullable=False, default=0)
code = Column(
String(32),
nullable=False,
default=generate_code,
unique=True,
comment='Unique code used to link to images')
deleted = Column(DateTime, default=None)
def __repr__(self):
return "<Room(id='%s', number='%s', accommodates='%s')>" % (
self.id, self.number, self.accommodates)
@hybrid_property
def accommodates(self):
return self.sgl_beds + self.dbl_beds * 2
@accommodates.expression
def accommodates(cls):
return (cls.sgl_beds + cls.dbl_beds * 2)
@hybrid_property
def number(self):
return '%d%02d' % (self.floor_no, self.room_no)
@number.expression
def number(cls):
return (func.cast(cls.floor_no, String) +
func.lpad(func.cast(cls.room_no, postgresql.TEXT), 2, '0'))