Cannot render_statement() with IN operator

[AbdealiLoKo]

I have a query like: Book.query.filter(Book.id.in_([1]))

When I try doing a render_statement on this, it gives this error:

In [1]: from sqlalchemy_utils.functions import render_statement

In [2]: from myapp import Book

In [3]: render_statement(Book.query.filter(Book.id.in_([1])))
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-5-7fefdb98cd64> in <module>
----> 1 render_statement(Book.query.filter(Book.id.in_([1])))

~/venv37/lib/python3.7/site-packages/sqlalchemy_utils/functions/render.py in render_statement(statement, bind)
     71     stream = six.moves.cStringIO()
     72     engine = create_mock_engine(bind.engine, stream=stream)
---> 73     engine.execute(statement)
     74
     75     return stream.getvalue()

~/venv37/lib/python3.7/site-packages/sqlalchemy_utils/functions/mock.py in dump(sql, *args, **kwargs)
     41                         value, type_)
     42
---> 43             text = str(Compiler(engine.dialect, sql).process(sql))
     44             text = re.sub(r'\n+', '\n', text)
     45             text = text.strip('\n').strip()

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in __init__(self, dialect, statement, cache_key, column_keys, for_executemany, linting, **kwargs)
    776         self.truncated_names = {}
    777
--> 778         Compiled.__init__(self, dialect, statement, **kwargs)
    779
    780         if self.isinsert or self.isupdate or self.isdelete:

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in __init__(self, dialect, statement, schema_translate_map, render_schema_translate, compile_kwargs)
    449             if self.can_execute:
    450                 self.execution_options = statement._execution_options
--> 451             self.string = self.process(self.statement, **compile_kwargs)
    452
    453             if render_schema_translate:

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in process(self, obj, **kwargs)
    484
    485     def process(self, obj, **kwargs):
--> 486         return obj._compiler_dispatch(self, **kwargs)
    487
    488     def __str__(self):

~/venv37/lib/python3.7/site-packages/sqlalchemy/ext/compiler.py in <lambda>(*arg, **kw)
    546                 class_,
    547                 "_compiler_dispatch",
--> 548                 lambda *arg, **kw: existing(*arg, **kw),
    549             )
    550             setattr(class_, "_compiler_dispatcher", existing)

~/venv37/lib/python3.7/site-packages/sqlalchemy/ext/compiler.py in __call__(self, element, compiler, **kw)
    602             kw["add_to_result_map"] = lambda *args: arm_collection.append(args)
    603
--> 604         expr = fn(element, compiler, **kw)
    605
    606         if arm:

~/myapp/db/models/dialect_compat.py in compile_select(select_stmt, compiler, **kw)
    245         )
    246
--> 247     return compiler.visit_select(select_stmt, **kw)
    248
    249

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in visit_select(self, select_stmt, asfrom, insert_into, fromhints, compound_index, select_wraps_for, lateral, from_linter, **kwargs)
   3426             byfrom,
   3427             toplevel,
-> 3428             kwargs,
   3429         )
   3430

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in _compose_select_body(self, text, select, compile_state, inner_columns, froms, byfrom, toplevel, kwargs)
   3577         if select._where_criteria:
   3578             t = self._generate_delimited_and_list(
-> 3579                 select._where_criteria, from_linter=from_linter, **kwargs
   3580             )
   3581             if t:

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in _generate_delimited_and_list(self, clauses, **kw)
   1706         )
   1707         if lcc == 1:
-> 1708             return clauses[0]._compiler_dispatch(self, **kw)
   1709         else:
   1710             separator = OPERATORS[operators.and_]

~/venv37/lib/python3.7/site-packages/sqlalchemy/ext/compiler.py in <lambda>(*arg, **kw)
    546                 class_,
    547                 "_compiler_dispatch",
--> 548                 lambda *arg, **kw: existing(*arg, **kw),
    549             )
    550             setattr(class_, "_compiler_dispatcher", existing)

~/venv37/lib/python3.7/site-packages/sqlalchemy/ext/compiler.py in __call__(self, element, compiler, **kw)
    602             kw["add_to_result_map"] = lambda *args: arm_collection.append(args)
    603
--> 604         expr = fn(element, compiler, **kw)
    605
    606         if arm:

~/myapp/db/models/dialect_compat.py in compile_binary_expr(binary, compiler, override_operator, **kw)
    111
    112
--> 113     return compiler.visit_binary(binary, override_operator=override_operator, **kw)
    114
    115

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in visit_binary(self, binary, override_operator, eager_grouping, from_linter, lateral_from_linter, **kw)
   2211                     from_linter=from_linter,
   2212                     lateral_from_linter=lateral_from_linter,
-> 2213                     **kw
   2214                 )
   2215

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in _generate_generic_binary(self, binary, opstring, eager_grouping, **kw)
   2263             + opstring
   2264             + binary.right._compiler_dispatch(
-> 2265                 self, eager_grouping=eager_grouping, **kw
   2266             )
   2267         )

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py in _compiler_dispatch(self, visitor, **kw)
     80
     81         else:
---> 82             return meth(self, **kw)
     83
     84     cls._compiler_dispatch = (

~/venv37/lib/python3.7/site-packages/sqlalchemy_utils/functions/mock.py in visit_bindparam(self, bindparam, *args, **kwargs)
     29                 def visit_bindparam(self, bindparam, *args, **kwargs):
     30                     return self.render_literal_value(
---> 31                         bindparam.value, bindparam.type)
     32
     33                 def render_literal_value(self, value, type_):

~/venv37/lib/python3.7/site-packages/sqlalchemy_utils/functions/mock.py in render_literal_value(self, value, type_)
     39
     40                     return super(Compiler, self).render_literal_value(
---> 41                         value, type_)
     42
     43             text = str(Compiler(engine.dialect, sql).process(sql))

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py in render_literal_value(self, value, type_)
   2565         processor = type_._cached_literal_processor(self.dialect)
   2566         if processor:
-> 2567             return processor(value)
   2568         else:
   2569             raise NotImplementedError(

~/venv37/lib/python3.7/site-packages/sqlalchemy/sql/sqltypes.py in process(value)
    537     def literal_processor(self, dialect):
    538         def process(value):
--> 539             return str(int(value))
    540
    541         return process

TypeError: int() argument must be a string, a bytes-like object or a number, not 'list'

These are my versions:

• sqlalchemy_utils: 0.38.2
• sqlalchemy: 1.4.39
• Python: 3.7.4

[kurtmckee]

Please provide your Books table definition as well. Thanks!

[AbdealiLoKo]

Here is a minimum reproducible example:

from sqlalchemy import Column, Integer, create_engine
from sqlalchemy_utils.functions import render_statement
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Book(Base):
    __tablename__ = "book"
    id = Column(Integer, primary_key=True)

engine = create_engine('sqlite:///db.sqlite', echo=True)
session = sessionmaker()
session.configure(bind=engine)
db = session()

qry = db.query(Book).filter(Book.id.in_([1]))

print(str(qry))
print(render_statement(qry))

Run the following:

python -m venv venv
venv/bin/pip install sqlalchemy sqlalchemy-utils
venv/bin/python run.py  # where run.py has the above contents

[kurtmckee]

FANTASTIC! Thanks for posting that example! I'll be able to take a look at this outside of work hours.

[AbdealiLoKo]

@kurtmckee Wondering if you had a chance to look into this ?

[kurtmckee]

Not yet, but I will! Thanks for the ping. :+1: