在SQLAlchemy中查询浮点值 [英] Querying for floating point values in SQLAlchemy
问题描述
例如:
$ p $ m $ session.query(Model)获得(1);
all_m = session.query(Model).filter(Model.some_float_value,m.some_float_value)
<如何在SQLAlchemy中根据任意精度过滤一个浮点数(例如,有些我可能想要匹配到0.01或其他我可能过滤精度为0.0005)。
例如,我希望能够编写一个通用函数,这样我就可以在代码中编写像这样的查询:
$ b $ (Model.foo ==bar,
match_float(Model.some_float_value,float_val,0.025)。 ))。all()
匹配容差在0.025的近似公差内。然而,我不是很熟悉SQLAlchemy,并且无法找到关于如何创建自定义比较函数的文档或任何内置的函数,这些函数将会做我所需要的。
是否有内置的函数可以使用,为过滤方法提供自定义比较函数的方法,还是我写一个原始查询/过程为了这?
附加信息:
Postgres 9.2
SQLAlchemy 0.9
$ b $ p存储为双精度浮点数
所有模型定义如下(有一个其中很多,这使得明确的列定义不太理想的解决方案):
pre $ engine $ create_engine用户:通过@本地主机:1234 /数据库)
基地= declarative_base()$ b $元数据=元数据(绑定=引擎)
$ b $类模型(基地):
__table__ = Table('model',metadata,autoload = True)
edit
为了清晰起见,我完全重新提出了我的问题。对于混淆,我不能与人沟通...
自定义比较的一个具体示例显示在GeoAlchemy虽然它可能是矫枉过正的你的应用程序)。虽然以下链接中的示例使用边界框来比较空间坐标,但您可以使用更简单的方法在容差范围内进行比较。
请参阅比较器类在这里定义了〜=和其他操作符:
http://geoalchemy-2.readthedocs.org/en/0.2.4/spatial_operators.html
既然你提及您无法找到如何实现自定义比较功能的文档,请参阅此处:
http://docs.sqlalchemy.org/en/latest/core/types.html#types-operators
在相关说明中 - 如果要防止数据库更新在浮点列保持在某个阈值内时触发,请参阅此处的讨论:
https://groups.google.com/forum/#!msg/sqlalchemy/tCpsGZmjk_w/lOwW93qHV0sJ
(免责声明:这是基于谷歌搜索的,我是SQLAlchemy的新手)
To summarize the problem, I can not select any entity against a floating point value using SQLAlchemy.
For example:
m = session.query(Model).get(1);
all_m = session.query(Model).filter(Model.some_float_value, m.some_float_value)
all_m is empty while I would expect it to always have at least one!
How can I filter a float value in SQLAlchemy, based on an arbitrary precision (EG. some I may want to match to 0.01 or others I may filter with a precision of 0.0005).
For example, I want to be able to write a generic function so I can write queries like this in my code:
session.query(Model)\
.filter(Model.foo == "bar",
match_float(Model.some_float_value, float_val, 0.025)).all()
Where there tolerance of the matches anything within an approximate tolerance of 0.025.
However, I am not very familiar with SQLAlchemy, and was unable to locate documentation on how to create custom compare function, or any built in functions that would do what I need.
Is there a built in function I can use, a way to provide a custom compare function for the filter method, or do I a way to write a raw query/procedure for this?
Aditional Info:
Postgres 9.2
SQLAlchemy 0.9
Values are stored as double precision floating point
All Models were defined like so (there are a lot of them, which makes a solution with explicit column definitions less than desirable):
engine = create_engine('postgresql://user:pass@localhost:1234/database')
Base = declarative_base()
metadata = MetaData(bind=engine)
class Model(Base):
__table__ = Table('model', metadata, autoload=True)
edit
Iv'e completely reworded my question for clarity. Sorry for the confusion, I fail at communicating with humans...
One concrete example for custom comparisons is shown in GeoAlchemy (though it may be overkill for your application). While the examples in the link below use bounding boxes for comparing spatial coordinates, you could use a simpler approach to implement comparison within a tolerance.
See for example the Comparator class which defines "~=" and other operators here:
http://geoalchemy-2.readthedocs.org/en/0.2.4/spatial_operators.html
Since you mention that you were unable to find documentation on how to implement a custom compare function, see here:
http://docs.sqlalchemy.org/en/latest/core/types.html#types-operators
On a related note - if you want to prevent database updates from being triggered if a floating point column remains within some threshold, see the discussion here:
https://groups.google.com/forum/#!msg/sqlalchemy/tCpsGZmjk_w/lOwW93qHV0sJ
(Disclaimer: This is based on a google search, I am new to SQLAlchemy)
这篇关于在SQLAlchemy中查询浮点值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!