title: Localstack running aws lambda (python) with sqlalchemy filter query - causes exception
body: <p>I created an AWS lambda function that uses sqlalchemy to connect to a mysql database.</p>
  <p>The code runs <strong>successfully</strong> both on my development station (lambda function called from main), and from AWS itself as a lambda.</p>
  <p>I am building a test using localstack docker. I created the lambda on localstack and then invoked it. The sqlalchemy query to the database <strong>fails</strong> with an exception. If I modify the sqlalchemy query to a simple query, it works ok.</p>
  <p><em><strong>NOTE:</strong> See a possible workaround in my answer.</em></p>
  <p>The function that fails is <code>get_rows_by_status_and_age</code> and this is the lambda code:</p>
  <pre><code>
  db_connection_str=f'mysql+pymysql://{db_user}:{db_pass}@{db_host}/{db_schema}'
  
  def db_init():
      engine = create_engine(db_connection_str, echo=False, isolation_level=&quot;READ COMMITTED&quot;)
      # logger.info(db_connection_str)
      # logger.info(engine.dialect)
      sm = sessionmaker(bind=engine, expire_on_commit=False)
      session = sm()
      return engine, session
  
  def get_rows_by_status_and_age(session, status_list, min_age_minutes):
      time_current = datetime.datetime.now(datetime.timezone.utc)
      time_old = time_current - datetime.timedelta(minutes=min_age_minutes)
      try:
          q = session.query(func.count(Execution.id)).filter(
              Execution.status.in_(status_list),
              Execution.updated_at &lt; time_old)
          return q.scalar()
      except Exception as e:
          logger.error(&quot;failed with error: {}&quot;.format(e))
          raise
  
  def lambda_handler(event, context):
      engine, session = db_init()
      try:
          count = get_rows_by_status_and_age(session, [1,6,7], 20)
          logger.info(count)
      except Exception as e:
          logger.info('Error in the lambda:')
          logger.exception(e)
  
  
  if __name__ == '__main__':  # pragma: no cover
      logging.basicConfig()
      lambda_handler(&quot;&quot;, &quot;&quot;)
  </code></pre>
  <p>The exception for the above query is a <code>KeyError</code>:</p>
  <pre><code>localstack_container            | 2021-08-24T08:00:48:ERROR:root: failed with error: &lt;function comma_op at 0x7fd9677e0440&gt;
  localstack_container            | 2021-08-24T08:00:48:INFO:root: Error in the lambda:
  localstack_container            | 2021-08-24T08:00:48:ERROR:root: &lt;function comma_op at 0x7fd9677e0440&gt;
  localstack_container            | Traceback (most recent call last):
  localstack_container            |   File &quot;/tmp/localstack/lambda_script_l_656d31ba.py&quot;, line 43, in lambda_handler
  localstack_container            |     count = get_rows_by_status_and_age(session, [1,6,7], 20)
  localstack_container            |   File &quot;/tmp/localstack/lambda_script_l_656d31ba.py&quot;, line 29, in get_rows_by_status_and_age
  localstack_container            |     return q.scalar()
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/orm/query.py&quot;, line 3312, in scalar
  localstack_container            |     ret = self.one()
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/orm/query.py&quot;, line 3282, in one
  localstack_container            |     ret = self.one_or_none()
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/orm/query.py&quot;, line 3251, in one_or_none
  localstack_container            |     ret = list(self)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/orm/query.py&quot;, line 3324, in __iter__
  localstack_container            |     return self._execute_and_instances(context)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/orm/query.py&quot;, line 3349, in _execute_and_instances
  localstack_container            |     result = conn.execute(querycontext.statement, self._params)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/engine/base.py&quot;, line 988, in execute
  localstack_container            |     return meth(self, multiparams, params)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/elements.py&quot;, line 287, in _execute_on_connection
  localstack_container            |     return connection._execute_clauseelement(self, multiparams, params)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/engine/base.py&quot;, line 1098, in _execute_clauseelement
  localstack_container            |     else None,
  localstack_container            |   File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/elements.py&quot;, line 462, in compile
  localstack_container            |     return self._compiler(dialect, bind=bind, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/elements.py&quot;, line 468, in _compiler
  localstack_container            |     return dialect.statement_compiler(dialect, self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 562, in __init__
  localstack_container            |     Compiled.__init__(self, dialect, statement, **kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 319, in __init__
  localstack_container            |     self.string = self.process(self.statement, **compile_kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 350, in process
  localstack_container            |     return obj._compiler_dispatch(self, **kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 2092, in visit_select
  localstack_container            |     for name, column in select._columns_plus_names
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 2092, in &lt;listcomp&gt;
  localstack_container            |     for name, column in select._columns_plus_names
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 1845, in _label_select_column
  localstack_container            |     return result_expr._compiler_dispatch(self, **column_clause_args)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 820, in visit_label
  localstack_container            |     + self.preparer.format_label(label, labelname)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 826, in visit_label
  localstack_container            |     self, within_columns_clause=False, **kw
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 1105, in visit_function
  localstack_container            |     ) % {&quot;expr&quot;: self.function_argspec(func, **kwargs)}
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 1117, in function_argspec
  localstack_container            |     return func.clause_expr._compiler_dispatch(self, **kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 718, in visit_grouping
  localstack_container            |     return &quot;(&quot; + grouping.element._compiler_dispatch(self, **kwargs) + &quot;)&quot;
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.b3651fa4/sqlalchemy/sql/compiler.py&quot;, line 968, in visit_clauselist
  localstack_container            |     sep = OPERATORS[clauselist.operator]
  localstack_container            | KeyError: &lt;function comma_op at 0x7fd9677e0440&gt;
  </code></pre>
  <p>If I modify the query to the following query, it also fails but with a different exception (I removed the func.count()):</p>
  <pre><code>        q = session.query(Execution.id).filter(
              Execution.status.in_(status_list),
              Execution.updated_at &lt; time_old)
          return q.all()
  </code></pre>
  <p>In the last case the exception is a <code>UnsupportedCompilationError</code> as follows:</p>
  <pre><code>localstack_container            | 2021-08-24T09:44:20:ERROR:root: failed with error: Compiler &lt;sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fb84801d9d0&gt; can't render element of type &lt;function in_op at 0x7fb8464b0b90&gt; (Background on this error at: http://sqlalche.me/e/l7de)
  localstack_container            | 2021-08-24T09:44:20:INFO:root: Error in the lambda:
  localstack_container            | 2021-08-24T09:44:20:ERROR:root: Compiler &lt;sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fb84801d9d0&gt; can't render element of type &lt;function in_op at 0x7fb8464b0b90&gt; (Background on this error at: http://sqlalche.me/e/l7de)
  localstack_container            | Traceback (most recent call last):
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 1281, in visit_binary
  localstack_container            |     opstring = OPERATORS[operator_]
  localstack_container            | KeyError: &lt;function in_op at 0x7fb8464b0b90&gt;
  localstack_container            | 
  localstack_container            | During handling of the above exception, another exception occurred:
  localstack_container            | 
  localstack_container            | Traceback (most recent call last):
  localstack_container            |   File &quot;/tmp/localstack/lambda_script_l_a11f462d.py&quot;, line 46, in lambda_handler
  localstack_container            |     count = get_rows_by_status_and_age(session, [1,6,7], 20)
  localstack_container            |   File &quot;/tmp/localstack/lambda_script_l_a11f462d.py&quot;, line 32, in get_rows_by_status_and_age
  localstack_container            |     return q.all()
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/orm/query.py&quot;, line 3168, in all
  localstack_container            |     return list(self)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/orm/query.py&quot;, line 3324, in __iter__
  localstack_container            |     return self._execute_and_instances(context)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/orm/query.py&quot;, line 3349, in _execute_and_instances
  localstack_container            |     result = conn.execute(querycontext.statement, self._params)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/engine/base.py&quot;, line 988, in execute
  localstack_container            |     return meth(self, multiparams, params)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/elements.py&quot;, line 287, in _execute_on_connection
  localstack_container            |     return connection._execute_clauseelement(self, multiparams, params)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/engine/base.py&quot;, line 1098, in _execute_clauseelement
  localstack_container            |     else None,
  localstack_container            |   File &quot;&lt;string&gt;&quot;, line 1, in &lt;lambda&gt;
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/elements.py&quot;, line 462, in compile
  localstack_container            |     return self._compiler(dialect, bind=bind, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/elements.py&quot;, line 468, in _compiler
  localstack_container            |     return dialect.statement_compiler(dialect, self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 562, in __init__
  localstack_container            |     Compiled.__init__(self, dialect, statement, **kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 319, in __init__
  localstack_container            |     self.string = self.process(self.statement, **compile_kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 350, in process
  localstack_container            |     return obj._compiler_dispatch(self, **kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 2117, in visit_select
  localstack_container            |     text, select, inner_columns, froms, byfrom, kwargs
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 2216, in _compose_select_body
  localstack_container            |     t = select._whereclause._compiler_dispatch(self, **kwargs)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 972, in visit_clauselist
  localstack_container            |     c._compiler_dispatch(self, **kw) for c in clauselist.clauses
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 970, in &lt;genexpr&gt;
  localstack_container            |     s
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 972, in &lt;genexpr&gt;
  localstack_container            |     c._compiler_dispatch(self, **kw) for c in clauselist.clauses
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/visitors.py&quot;, line 91, in _compiler_dispatch
  localstack_container            |     return meth(self, **kw)
  localstack_container            |   File &quot;/tmp/localstack/zipfile.183ee235/sqlalchemy/sql/compiler.py&quot;, line 1283, in visit_binary
  localstack_container            |     raise exc.UnsupportedCompilationError(self, operator_)
  localstack_container            | sqlalchemy.exc.UnsupportedCompilationError: Compiler &lt;sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7fb84801d9d0&gt; can't render element of type &lt;function in_op at 0x7fb8464b0b90&gt; (Background on this error at: http://sqlalche.me/e/l7de)
  </code></pre>
  <p>If I leave only the single filter it works:</p>
  <pre><code>        q = session.query(Execution.id).filter(
              Execution.updated_at &lt; time_old)
          return q.all()
  </code></pre>
  <p>From trying to understand the problem, it appears as a problem with compiling a query due maybe to database-dependant syntax, but I printed out the sqlalchemy dialect and it is mysql, like it should be. And like I mentioned it works fine in all other environments other than localstack lambda.</p>
  <p>It seems to be caused somehow by localstack env.</p>
  <p>Any ideas? Thanks.</p>