Flask & SQLAlchemy & PostgreSQL - In a query can an 'int' be cast to a 'string' to permit use of 'like'

Question

Using Flask and SQLAlchemy is it possible to create a query where a column can be cast from a number to a string so that .like() can be used as a filter?

The sample code below illustrates what I'm after, however Test 3 is a broken statement (ie: No attempt at casting so the query fails. Error is below)

Test 1 - demonstrates a standard select

Test 2 - demonstrates a select using like on a string

Can 'test 3' be modified to permit a like on a number?

In PostgreSQL the SQL query would be:

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

Any assistance gratefully appreciated.

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())

And the sample data:

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

And the error:

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

Solved. The Query method filter can take an expression, so the solution is:

from sqlalchemy import cast

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

With the result:

Test 3 = [MyTable( 20, World)]

Found the information in the SQLAlchemy Query API documentation.