[sqlalchemy] Re: Using custom function expression throws 'TypeError: an integer is required' in orm query
Yes, the __eq__() and __hash__() functions are overridden to compare the primary keys (e.g. self.id==other.id, or hash(self.id)). The orm query works as expected once I remove them from the Entity class definition. On Jul 1, 4:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: does your Entity class have some overridden __eq__(), __cmp__(), __hash__() on it ? I think there might be an issue here but I need a lot more specifics. On Jul 1, 2011, at 6:34 AM, Adrian wrote: I just tested it and session.execute(query.statement) returns the proper resultset. The 'similarity' functions returns REAL. --- In [13]: query.all() --- TypeError Traceback (most recent call last) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679 @_generative(_no_clauseelement_condition) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1916 1917 if filter: - 1918 rows = filter(rows) 1919 1920 if context.refresh_state and self._only_load_props \ /usr/local/lib/python2.7/dist-packages/sqlalchemy/util/ _collections.pyc in unique_list(seq, hashfunc) 594 if not hashfunc: 595 return [x for x in seq -- 596 if x not in seen 597 and not seen.__setitem__(x, True)] 598 else: TypeError: an integer is required # the last value in the row is the similarity value In [14]: session.execute(query).fetchall() Out[14]: [(10581, u'STI', u'STI', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [4-methyl-3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4ccc(cc4)C[NH +]5CC[NH+](CC5)C', 495.619, 37, 29, 8, 0, 0.275862, 2, 2, 0, 41, 8, 5, 4, 0, 0, 88.68, 2.588, 0.241379, True, False, False, False, False, False, False, False, False, False, 1.0), (8099, u'MPZ', u'MPZ', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'C[NH+]1CC[NH+](CC1)Cc2ccc(cc2)C(=O)Nc3(c3)Nc4nccc(n4)c5cccnc5', 481.592, 36, 28, 8, 0, 0.285714, 2, 2, 0, 40, 8, 5, 4, 0, 0, 88.68, 2.292, 0.214286, True, False, False, False, False, False, False, False, False, False, 0.811594202898551), (9593, u'PRC', u'PRC', None, u'N-[4-methyl-3-[[4-(3- pyridyl)pyrimidin-2-yl]amino]phenyl]pyridine-3-carboxamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4cccnc4', 382.418, 29, 22, 7, 0, 0.318182, 0, 0, 0, 32, 6, 4, 4, 0, 0, 92.69, 2.143, 0.0454545, True, False, False, False, False, False, False, False, False, False, 0.691176470588235), (5653, u'G6G', u'G6G', None, u'N-[3-[[3-[4-(4-methoxyanilino)-1,3,5- triazin-2-yl]-2-pyridyl]amino]-4-methyl-phenyl]-4-[(4- methylpiperazin-1-yl)methyl]benzamide', None, None, u'Cc1ccc(cc1Nc2c(cccn2)c3ncnc(n3)Nc4ccc(cc4)OC)NC(=O)c5ccc(cc5)C[NH +]6CC[NH+](CC6)C', 617.743, 46, 35, 11, 0, 0.314286, 2, 2, 0, 51, 11, 6, 5, 0, 0, 122.83, 3.668, 0.228571, True, False, False, False, False, False, False, False, False, False, 0.619047619047619), (1153, u'406', u'406', None, u'4-[[(1R,3R)-3- (dimethylamino)pyrrolidin-1-yl]methyl]-N-[4-methyl-3-[(4-pyrimidin-5- ylpyrimidin-2-yl)amino]phenyl]-3-(trifluoromethyl)benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cncnc3)NC(=O)c4ccc(c(c4)C(F)(F)F)C[N@@H +]5CC[C@@H](C5)[NH+](C)C', 578.631, 42, 30, 12, 3, 0.4, 2, 2, 0, 46, 10, 5, 4, 0, 0, 101.57, 3.187, 0.3, True, False, False, False, False, False, False, False, False, False, 0.526881720430108), (8552, u'NIL', u'NIL', None, u'4-methyl-N-[3-(4-methylimidazol-1- yl)-5-(trifluoromethyl)phenyl]-3-[[4-(3-pyridyl)pyrimidin-2- yl]amino]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)C(=O)Nc4cc(cc(c4)n5cc(nc5)C)C(F)(F)F', 529.516, 39, 28, 11, 3, 0.392857, 0, 0, 0, 43, 8, 5, 5, 0, 0, 97.62, 3.771, 0.107143, True, False, False, False, False, False, False, False, False, False, 0.50561797752809)] On Jun 30, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 30, 2011, at 9:23 AM, Adrian wrote: SQAlchemy 0.7.1 / pyscopg 2.2.1 / PostgreSQL 9.0 --- I have a weird problem with orm queries that contain custom functions, in this case from postgres contrib modules. When I do a query like this session.query(Entity, func.similarity(Entity.string, 'querystring')).all() # postgres pg_trgm extension I will get the error below. However, when I specify one or all the columns of the Entity individually it works. It also works if the function is in the .order_by() clause. Any ideas where the problem could come from? this seems like it has to do
[sqlalchemy] Re: Using custom function expression throws 'TypeError: an integer is required' in orm query
I found the problem now - the __hash__() function I had did not return an integer, it returned a tuple of the composite primary key. I changed it now and it works, thanks for your help! On Jul 4, 8:50 am, Adrian adr...@schreyer.me wrote: Yes, the __eq__() and __hash__() functions are overridden to compare the primary keys (e.g. self.id==other.id, or hash(self.id)). The orm query works as expected once I remove them from the Entity class definition. On Jul 1, 4:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: does your Entity class have some overridden __eq__(), __cmp__(), __hash__() on it ? I think there might be an issue here but I need a lot more specifics. On Jul 1, 2011, at 6:34 AM, Adrian wrote: I just tested it and session.execute(query.statement) returns the proper resultset. The 'similarity' functions returns REAL. --- In [13]: query.all() --- TypeError Traceback (most recent call last) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679 @_generative(_no_clauseelement_condition) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1916 1917 if filter: - 1918 rows = filter(rows) 1919 1920 if context.refresh_state and self._only_load_props \ /usr/local/lib/python2.7/dist-packages/sqlalchemy/util/ _collections.pyc in unique_list(seq, hashfunc) 594 if not hashfunc: 595 return [x for x in seq -- 596 if x not in seen 597 and not seen.__setitem__(x, True)] 598 else: TypeError: an integer is required # the last value in the row is the similarity value In [14]: session.execute(query).fetchall() Out[14]: [(10581, u'STI', u'STI', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [4-methyl-3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4ccc(cc4)C[NH +]5CC[NH+](CC5)C', 495.619, 37, 29, 8, 0, 0.275862, 2, 2, 0, 41, 8, 5, 4, 0, 0, 88.68, 2.588, 0.241379, True, False, False, False, False, False, False, False, False, False, 1.0), (8099, u'MPZ', u'MPZ', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'C[NH+]1CC[NH+](CC1)Cc2ccc(cc2)C(=O)Nc3(c3)Nc4nccc(n4)c5cccnc5', 481.592, 36, 28, 8, 0, 0.285714, 2, 2, 0, 40, 8, 5, 4, 0, 0, 88.68, 2.292, 0.214286, True, False, False, False, False, False, False, False, False, False, 0.811594202898551), (9593, u'PRC', u'PRC', None, u'N-[4-methyl-3-[[4-(3- pyridyl)pyrimidin-2-yl]amino]phenyl]pyridine-3-carboxamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4cccnc4', 382.418, 29, 22, 7, 0, 0.318182, 0, 0, 0, 32, 6, 4, 4, 0, 0, 92.69, 2.143, 0.0454545, True, False, False, False, False, False, False, False, False, False, 0.691176470588235), (5653, u'G6G', u'G6G', None, u'N-[3-[[3-[4-(4-methoxyanilino)-1,3,5- triazin-2-yl]-2-pyridyl]amino]-4-methyl-phenyl]-4-[(4- methylpiperazin-1-yl)methyl]benzamide', None, None, u'Cc1ccc(cc1Nc2c(cccn2)c3ncnc(n3)Nc4ccc(cc4)OC)NC(=O)c5ccc(cc5)C[NH +]6CC[NH+](CC6)C', 617.743, 46, 35, 11, 0, 0.314286, 2, 2, 0, 51, 11, 6, 5, 0, 0, 122.83, 3.668, 0.228571, True, False, False, False, False, False, False, False, False, False, 0.619047619047619), (1153, u'406', u'406', None, u'4-[[(1R,3R)-3- (dimethylamino)pyrrolidin-1-yl]methyl]-N-[4-methyl-3-[(4-pyrimidin-5- ylpyrimidin-2-yl)amino]phenyl]-3-(trifluoromethyl)benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cncnc3)NC(=O)c4ccc(c(c4)C(F)(F)F)C[N@@H +]5CC[C@@H](C5)[NH+](C)C', 578.631, 42, 30, 12, 3, 0.4, 2, 2, 0, 46, 10, 5, 4, 0, 0, 101.57, 3.187, 0.3, True, False, False, False, False, False, False, False, False, False, 0.526881720430108), (8552, u'NIL', u'NIL', None, u'4-methyl-N-[3-(4-methylimidazol-1- yl)-5-(trifluoromethyl)phenyl]-3-[[4-(3-pyridyl)pyrimidin-2- yl]amino]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)C(=O)Nc4cc(cc(c4)n5cc(nc5)C)C(F)(F)F', 529.516, 39, 28, 11, 3, 0.392857, 0, 0, 0, 43, 8, 5, 5, 0, 0, 97.62, 3.771, 0.107143, True, False, False, False, False, False, False, False, False, False, 0.50561797752809)] On Jun 30, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 30, 2011, at 9:23 AM, Adrian wrote: SQAlchemy 0.7.1 / pyscopg 2.2.1 / PostgreSQL 9.0 --- I have a weird problem with orm queries that contain custom functions, in this case from postgres contrib modules. When I do a query like this session.query(Entity,
[sqlalchemy] Re: Using custom function expression throws 'TypeError: an integer is required' in orm query
I just tested it and session.execute(query.statement) returns the proper resultset. The 'similarity' functions returns REAL. --- In [13]: query.all() --- TypeError Traceback (most recent call last) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679 @_generative(_no_clauseelement_condition) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1916 1917 if filter: - 1918 rows = filter(rows) 1919 1920 if context.refresh_state and self._only_load_props \ /usr/local/lib/python2.7/dist-packages/sqlalchemy/util/ _collections.pyc in unique_list(seq, hashfunc) 594 if not hashfunc: 595 return [x for x in seq -- 596 if x not in seen 597 and not seen.__setitem__(x, True)] 598 else: TypeError: an integer is required # the last value in the row is the similarity value In [14]: session.execute(query).fetchall() Out[14]: [(10581, u'STI', u'STI', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [4-methyl-3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4ccc(cc4)C[NH +]5CC[NH+](CC5)C', 495.619, 37, 29, 8, 0, 0.275862, 2, 2, 0, 41, 8, 5, 4, 0, 0, 88.68, 2.588, 0.241379, True, False, False, False, False, False, False, False, False, False, 1.0), (8099, u'MPZ', u'MPZ', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'C[NH+]1CC[NH+](CC1)Cc2ccc(cc2)C(=O)Nc3(c3)Nc4nccc(n4)c5cccnc5', 481.592, 36, 28, 8, 0, 0.285714, 2, 2, 0, 40, 8, 5, 4, 0, 0, 88.68, 2.292, 0.214286, True, False, False, False, False, False, False, False, False, False, 0.811594202898551), (9593, u'PRC', u'PRC', None, u'N-[4-methyl-3-[[4-(3- pyridyl)pyrimidin-2-yl]amino]phenyl]pyridine-3-carboxamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4cccnc4', 382.418, 29, 22, 7, 0, 0.318182, 0, 0, 0, 32, 6, 4, 4, 0, 0, 92.69, 2.143, 0.0454545, True, False, False, False, False, False, False, False, False, False, 0.691176470588235), (5653, u'G6G', u'G6G', None, u'N-[3-[[3-[4-(4-methoxyanilino)-1,3,5- triazin-2-yl]-2-pyridyl]amino]-4-methyl-phenyl]-4-[(4- methylpiperazin-1-yl)methyl]benzamide', None, None, u'Cc1ccc(cc1Nc2c(cccn2)c3ncnc(n3)Nc4ccc(cc4)OC)NC(=O)c5ccc(cc5)C[NH +]6CC[NH+](CC6)C', 617.743, 46, 35, 11, 0, 0.314286, 2, 2, 0, 51, 11, 6, 5, 0, 0, 122.83, 3.668, 0.228571, True, False, False, False, False, False, False, False, False, False, 0.619047619047619), (1153, u'406', u'406', None, u'4-[[(1R,3R)-3- (dimethylamino)pyrrolidin-1-yl]methyl]-N-[4-methyl-3-[(4-pyrimidin-5- ylpyrimidin-2-yl)amino]phenyl]-3-(trifluoromethyl)benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cncnc3)NC(=O)c4ccc(c(c4)C(F)(F)F)C[N@@H +]5CC[C@@H](C5)[NH+](C)C', 578.631, 42, 30, 12, 3, 0.4, 2, 2, 0, 46, 10, 5, 4, 0, 0, 101.57, 3.187, 0.3, True, False, False, False, False, False, False, False, False, False, 0.526881720430108), (8552, u'NIL', u'NIL', None, u'4-methyl-N-[3-(4-methylimidazol-1- yl)-5-(trifluoromethyl)phenyl]-3-[[4-(3-pyridyl)pyrimidin-2- yl]amino]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)C(=O)Nc4cc(cc(c4)n5cc(nc5)C)C(F)(F)F', 529.516, 39, 28, 11, 3, 0.392857, 0, 0, 0, 43, 8, 5, 5, 0, 0, 97.62, 3.771, 0.107143, True, False, False, False, False, False, False, False, False, False, 0.50561797752809)] On Jun 30, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 30, 2011, at 9:23 AM, Adrian wrote: SQAlchemy 0.7.1 / pyscopg 2.2.1 / PostgreSQL 9.0 --- I have a weird problem with orm queries that contain custom functions, in this case from postgres contrib modules. When I do a query like this session.query(Entity, func.similarity(Entity.string, 'querystring')).all() # postgres pg_trgm extension I will get the error below. However, when I specify one or all the columns of the Entity individually it works. It also works if the function is in the .order_by() clause. Any ideas where the problem could come from? this seems like it has to do with the type of object being returned from psycopg2, as the Query runs the rows through a uniquing function that uses sets, maybe a comparison is emitting that TypeError. The stack trace doesn't quite make it clear. It would be interesting to see what session.execute(myquery.statement) sends back in the result rows. (I haven't looked up the SIMILARITY function in the PG docs yet to see what it returns). - /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679
Re: [sqlalchemy] Re: Using custom function expression throws 'TypeError: an integer is required' in orm query
does your Entity class have some overridden __eq__(), __cmp__(), __hash__() on it ? I think there might be an issue here but I need a lot more specifics. On Jul 1, 2011, at 6:34 AM, Adrian wrote: I just tested it and session.execute(query.statement) returns the proper resultset. The 'similarity' functions returns REAL. --- In [13]: query.all() --- TypeError Traceback (most recent call last) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in all(self) 1675 1676 - 1677 return list(self) 1678 1679 @_generative(_no_clauseelement_condition) /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.pyc in instances(self, cursor, _Query__context) 1916 1917 if filter: - 1918 rows = filter(rows) 1919 1920 if context.refresh_state and self._only_load_props \ /usr/local/lib/python2.7/dist-packages/sqlalchemy/util/ _collections.pyc in unique_list(seq, hashfunc) 594 if not hashfunc: 595return [x for x in seq -- 596 if x not in seen 597 and not seen.__setitem__(x, True)] 598 else: TypeError: an integer is required # the last value in the row is the similarity value In [14]: session.execute(query).fetchall() Out[14]: [(10581, u'STI', u'STI', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [4-methyl-3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4ccc(cc4)C[NH +]5CC[NH+](CC5)C', 495.619, 37, 29, 8, 0, 0.275862, 2, 2, 0, 41, 8, 5, 4, 0, 0, 88.68, 2.588, 0.241379, True, False, False, False, False, False, False, False, False, False, 1.0), (8099, u'MPZ', u'MPZ', None, u'4-[(4-methylpiperazin-1-yl)methyl]-N- [3-[[4-(3-pyridyl)pyrimidin-2-yl]amino]phenyl]benzamide', None, None, u'C[NH+]1CC[NH+](CC1)Cc2ccc(cc2)C(=O)Nc3(c3)Nc4nccc(n4)c5cccnc5', 481.592, 36, 28, 8, 0, 0.285714, 2, 2, 0, 40, 8, 5, 4, 0, 0, 88.68, 2.292, 0.214286, True, False, False, False, False, False, False, False, False, False, 0.811594202898551), (9593, u'PRC', u'PRC', None, u'N-[4-methyl-3-[[4-(3- pyridyl)pyrimidin-2-yl]amino]phenyl]pyridine-3-carboxamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)NC(=O)c4cccnc4', 382.418, 29, 22, 7, 0, 0.318182, 0, 0, 0, 32, 6, 4, 4, 0, 0, 92.69, 2.143, 0.0454545, True, False, False, False, False, False, False, False, False, False, 0.691176470588235), (5653, u'G6G', u'G6G', None, u'N-[3-[[3-[4-(4-methoxyanilino)-1,3,5- triazin-2-yl]-2-pyridyl]amino]-4-methyl-phenyl]-4-[(4- methylpiperazin-1-yl)methyl]benzamide', None, None, u'Cc1ccc(cc1Nc2c(cccn2)c3ncnc(n3)Nc4ccc(cc4)OC)NC(=O)c5ccc(cc5)C[NH +]6CC[NH+](CC6)C', 617.743, 46, 35, 11, 0, 0.314286, 2, 2, 0, 51, 11, 6, 5, 0, 0, 122.83, 3.668, 0.228571, True, False, False, False, False, False, False, False, False, False, 0.619047619047619), (1153, u'406', u'406', None, u'4-[[(1R,3R)-3- (dimethylamino)pyrrolidin-1-yl]methyl]-N-[4-methyl-3-[(4-pyrimidin-5- ylpyrimidin-2-yl)amino]phenyl]-3-(trifluoromethyl)benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cncnc3)NC(=O)c4ccc(c(c4)C(F)(F)F)C[N@@H +]5CC[C@@H](C5)[NH+](C)C', 578.631, 42, 30, 12, 3, 0.4, 2, 2, 0, 46, 10, 5, 4, 0, 0, 101.57, 3.187, 0.3, True, False, False, False, False, False, False, False, False, False, 0.526881720430108), (8552, u'NIL', u'NIL', None, u'4-methyl-N-[3-(4-methylimidazol-1- yl)-5-(trifluoromethyl)phenyl]-3-[[4-(3-pyridyl)pyrimidin-2- yl]amino]benzamide', None, None, u'Cc1ccc(cc1Nc2nccc(n2)c3cccnc3)C(=O)Nc4cc(cc(c4)n5cc(nc5)C)C(F)(F)F', 529.516, 39, 28, 11, 3, 0.392857, 0, 0, 0, 43, 8, 5, 5, 0, 0, 97.62, 3.771, 0.107143, True, False, False, False, False, False, False, False, False, False, 0.50561797752809)] On Jun 30, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 30, 2011, at 9:23 AM, Adrian wrote: SQAlchemy 0.7.1 / pyscopg 2.2.1 / PostgreSQL 9.0 --- I have a weird problem with orm queries that contain custom functions, in this case from postgres contrib modules. When I do a query like this session.query(Entity, func.similarity(Entity.string, 'querystring')).all() # postgres pg_trgm extension I will get the error below. However, when I specify one or all the columns of the Entity individually it works. It also works if the function is in the .order_by() clause. Any ideas where the problem could come from? this seems like it has to do with the type of object being returned from psycopg2, as the Query runs the rows through a uniquing function that uses sets, maybe a comparison is emitting that TypeError. The stack trace doesn't quite make it clear. It would be interesting to see what session.execute(myquery.statement) sends back in the result rows. (I haven't looked
