sqlalchemy的op函数:
源码: def op(self, opstring, precedence=0, is_comparison=False): """produce a generic operator function. e.g.:: somecolumn.op("*")(5) produces:: somecolumn * 5 This function can also be used to make bitwise operators explicit. For example:: somecolumn.op('&')(0xff) is a bitwise AND of the value in ``somecolumn``. :param operator: a string which will be output as the infix operator between this element and the expression passed to the generated function. :param precedence: precedence to apply to the operator, when parenthesizing expressions. A lower number will cause the expression to be parenthesized when applied against another operator with higher precedence. The default value of ``0`` is lower than all operators except for the comma (``,``) and ``AS`` operators. A value of 100 will be higher or equal to all operators, and -100 will be lower than or equal to all operators. .. versionadded:: 0.8 - added the 'precedence' argument. :param is_comparison: if True, the operator will be considered as a "comparison" operator, that is which evaluates to a boolean true/false value, like ``==``, ``>``, etc. This flag should be set so that ORM relationships can establish that the operator is a comparison operator when used in a custom join condition. .. versionadded:: 0.9.2 - added the :paramref:`.Operators.op.is_comparison` flag. .. seealso:: :ref:`types_operators` :ref:`relationship_custom_operator` """ operator = custom_op(opstring, precedence, is_comparison) def against(other): return operator(self, other) return against
如文档中描述,sqlalchemy提供位运算符操作:
from sqlalchemy.sql import operators
operators.op(MyTable.id, '&', 1)
相当于mysql中的:
mytable.id & 1
也可以直接用orm对象调用op:
MyTable.id.op("&")(1)
同时也支持自定义的操作:
一下为项目中的应用实例: ip_obligee_list = ip_obligee_list.filter( IPObligee.ip.has(IP.searchable.op('~*')( escape_search_keyword(ip_keyword))))
其中op('~*)为postgresql中的模糊查询:
~
和~*
,!~
和!~*
~
表示匹配正则表达式,且区分大小写。
~*
表示匹配正则表达式,且不区分大小写。
可以通过这两个操作符来实现like和ilike一样的效果,如下:
1.匹配以“张”开头的字符串
select * from table where name ~ '^张';
2.匹配以“小”结尾的字符串
select * from table where name ~ '小$';
其实这里的^和$就是正则表达式里的用法。
~~
和~~*
,!~~
和!~~*
~~
等效于like,~~*
等效于ilike。
!~~
等效于not like,!~~*
等效于not ilike。