flask-sqlalchemy - Flask & SQLAlchemy & PostgreSQL - 在查询中可以将 'int' 转换为 'string' 以允许使用 'like'

Question

使用 Flask 和 SQLAlchemy 是否可以创建一个查询，其中可以将列从数字转换为字符串，以便 .like() 可以用作过滤器？

下面的示例代码说明了我所追求的内容，但是测试 3 是一个错误的语句(即:没有尝试进行强制转换，因此查询失败。错误如下)

测试 1 - 演示标准选择

测试 2 - 演示在 like 上使用 string 的选择

是否可以修改“测试 3”以允许 like 上的 number ？

在 PostgreSQL 中，SQL 查询将是:

SELECT * FROM mytable WHERE number::varchar like '%2%'

任何帮助表示感谢。

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Table, Column, Integer, String

app = Flask(__name__)
app.debug = True
app.config.from_pyfile('config.py')
db = SQLAlchemy( app )

class MyTable(db.Model):
    '''My Sample Table'''
    __tablename__ = 'mytable'

    number = db.Column( db.Integer, primary_key = True  )
    text = db.Column( db.String )

    def __repr__(self):
        return( 'MyTable( ' + str( self.number ) + ', ' + self.text + ')' )

test_1 = (db.session.query(MyTable)
        .all())

print "Test 1 = " + str( test_1 )

test_2 = (db.session.query(MyTable)
    .filter( MyTable.text.like( '%orl%' ) )
    .all())

print "Test 2 = " + str( test_2 )

test_3 = (db.session.query(MyTable)
    .filter( MyTable.number.like( '%2%' ) )
    .all())

和样本数据:

=> select * from mytable;
 number | text  
--------+-------
    100 | Hello
     20 | World

和错误:

Traceback (most recent call last):
File "sample.py", line 33, in <module>
.filter( MyTable.number.like( '%2%' ) )
File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2320, in all
  return list(self)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2438, in __iter__
  return self._execute_and_instances(context)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2453, in _execute_and_instances
  result = conn.execute(querycontext.statement, self._params)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
  return meth(self, multiparams, params)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
  return connection._execute_clauseelement(self, multiparams, params)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
  compiled_sql, distilled_params
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
  context)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
  exc_info
File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
  reraise(type(exception), exception, tb=exc_tb)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
  context)
File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
  cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist: integer ~~ unknown
LINE 3: WHERE mytable.number LIKE '%2%'
^                                
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
'SELECT mytable.number AS mytable_number, mytable.text AS mytable_text \nFROM mytable \nWHERE mytable.number LIKE %(number_1)s' {'number_1': '%2%'}

Answer 1

解决了。 Query 方法过滤器可以带一个表达式，所以解决方案是:

from sqlalchemy import cast

result = (db.session.query(MyTable)
    .filter( cast( MyTable.number, String ).like( '%2%' ) )
    .all())

结果:

Test 3 = [MyTable( 20, World)]

在 SQLAlchemy Query API 文档中找到了信息。