SQL expression object expected, got object of type <class 'sqlalchemy.sql.elements.BinaryExpression'> instead

[Mark Wilkins]

Hey everyone. I've got a real head scratcher here, so any help at all would be appreciated. 
Details:

• Python 3.6.10
  
• SQLalchemy.__version__ == '1.3.13'
  
• Environment: AWS Lambda
  
• Database: AWS Redshift

I am attempting to perform a simple query like so:

stmnt = (my_table.c.name == 'some name')
q = my_table.select().where(stmnt).limit(10)
result = connection.execute(q).fetchall()

but I get the following error:

Output: 

{

  "errorMessage": "SQL expression object expected, got object of type <class 'sqlalchemy.sql.elements.BinaryExpression'> instead",

  "errorType": "ArgumentError",

  "stackTrace": [

    [

      "/var/task/app.py",

      59,

      "lambda_handler",

      "q = my_table.select().where(stmnt).limit(10)"

    ],

    [

      "<string>",

      2,

      "where",

      ""

    ],

    [

      "/var/task/lib/sqlalchemy/sql/base.py",

      47,

      "_generative",

      "fn(self, *args[1:], **kw)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/selectable.py",

      3477,

      "where",

      "self.append_whereclause(whereclause)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/selectable.py",

      3689,

      "append_whereclause",

      "self._whereclause = and_(True_._ifnone(self._whereclause), whereclause)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/elements.py",

      2098,

      "and_",

      "return cls._construct(operators.and_, True_, False_, *clauses)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/elements.py",

      2030,

      "_construct",

      "for clause in util.coerce_generator_arg(clauses)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/elements.py",

      2030,

      "<listcomp>",

      "for clause in util.coerce_generator_arg(clauses)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/elements.py",

      4569,

      "_expression_literal_as_text",

      "return _literal_as_text(element)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/elements.py",

      4592,

      "_literal_as_text",

      "return _literal_as(element, _no_text_coercion)"

    ],

    [

      "/var/task/lib/sqlalchemy/sql/elements.py",

      4584,

      "_literal_as",

      "\"instead\" % type(element)"

    ]

  ]

}

Function error: Unhandled

Interestingly, it works fine without the where() call, or if I use text() inside of the where() call instead. 

Does anyone have any advice? Its had be stumped for 2 days. 

Thanks!

[Mark Wilkins]

Some additional code incase its relevent:

    # Get DB connection

    engine = setup_env_db_engine(debug=True)

    connection = engine.connect()

    # Configure SQLalchemy

    meta = MetaData(bind=engine)

    meta.reflect()

    my_table = meta.tables.get('my_table')

[Simon King]

Can you show the real code that runs the query? I'm wondering whether
the thing that you are comparing against my_table.c.name is not
actually a simple string.

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/839938dd-ed33-4f55-a836-185ec0689f2f%40googlegroups.com.

[Mike Bayer]

this looks like something up with the environment or the interpreter.   The error message says it got an object whose type() returns "BinaryExpression".   that is exactly the type it is looking for; this class should be a subclass of sqlalchemy.sql.visitors.Visitable.   However for this error to occur, that is suddenly not the case, or the Visitable / BinaryExpression symbol has been modified at runtime, something like that.    It's not clear if this could be some artifact of AWS Lambda, or the way the application is doing imports, or something like that.

the approach here would be to make a test program that produces the error under AWS Lambda, then run it in a local Python environment and see if the results are the same or different.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfyHn5OKiAm8k9QX3R3Gm4xAoZRy7%3Dg33L%3DdO%3DvSDumZg%40mail.gmail.com.

[Mark Wilkins]

This is the actual code, with table names swapped out. I've confirmed that my_table.c.name is infact a column, and the comparison operation generates a BinaryExpression object as it is supposed to. The issue is that where() throws an exception indicating it doesn't accept BinaryExpression objects, even though it is supposed to. 

On Friday, February 14, 2020 at 5:16:40 AM UTC-5, Simon King wrote:

Can you show the real code that runs the query? I'm wondering whether
the thing that you are comparing against my_table.c.name is not
actually a simple string.

Simon

On Wed, Feb 12, 2020 at 11:01 PM Mark Wilkins <cont...@markwilkins.dev> wrote:
>
> Some additional code incase its relevent:
>
>     # Get DB connection
>     engine = setup_env_db_engine(debug=True)
>     connection = engine.connect()
>
>     # Configure SQLalchemy
>     meta = MetaData(bind=engine)
>     meta.reflect()
>
>     my_table = meta.tables.get('my_table')
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mark Wilkins]

This seems to be correct, the issue does not occur if I run the exact same code locally. Lambda must be doing something, somehow, that is causing this. Any advice on how to fix the problem?

On Friday, February 14, 2020 at 9:10:49 AM UTC-5, Mike Bayer wrote:

this looks like something up with the environment or the interpreter.   The error message says it got an object whose type() returns "BinaryExpression".   that is exactly the type it is looking for; this class should be a subclass of sqlalchemy.sql.visitors.Visitable.   However for this error to occur, that is suddenly not the case, or the Visitable / BinaryExpression symbol has been modified at runtime, something like that.    It's not clear if this could be some artifact of AWS Lambda, or the way the application is doing imports, or something like that.

the approach here would be to make a test program that produces the error under AWS Lambda, then run it in a local Python environment and see if the results are the same or different.

On Fri, Feb 14, 2020, at 5:16 AM, Simon King wrote:

Can you show the real code that runs the query? I'm wondering whether

the thing that you are comparing against my_table.c.name is not

actually a simple string.

Simon

On Wed, Feb 12, 2020 at 11:01 PM Mark Wilkins <cont...@markwilkins.dev> wrote:

>

> Some additional code incase its relevent:

>

>     # Get DB connection

>     engine = setup_env_db_engine(debug=True)

>     connection = engine.connect()

>

>     # Configure SQLalchemy

>     meta = MetaData(bind=engine)

>     meta.reflect()

>

>     my_table = meta.tables.get('my_table')

>

>

> --

> SQLAlchemy -

> The Python SQL Toolkit and Object Relational Mapper

>

> http://www.sqlalchemy.org/

>

> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

> ---

> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/839938dd-ed33-4f55-a836-185ec0689f2f%40googlegroups.com.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

If AWS lambda doesn't offer any kind of stepwise debugging facility then you would have to keep trying different things, differnet combinations of configuration / imports, and whatever else you can think of that makes the behavior occur or not occur, until you can isolate it to a single thing.

I don't know how AWS lambda works but if you're saying a three line SQLAlchemy program like the one below fails:

>>> from sqlalchemy import table, select, column

>>> t = table('t', column('a'), column('b'))

>>> stmt = select([t]).where(t.c.a == 'hi')

>>> print(stmt)

SELECT t.a, t.b

FROM t

WHERE t.a = :a_1

that would mean SQLAlchemy itself is setting itself up in some way that is not compatible with AWS lambda.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5ca02432-e957-412d-91a4-4f4c9dd67f0c%40googlegroups.com.