Bitwise AND operation in a select statement support in sqlalchemy

[Balukrishnan]

Table definition

from sqlalchemy import *
testa = Table(
    "testa",
    metadata,
    Column("id", BigInteger, primary_key=True),
    Column("str_var_a", String, nullable=True),
    Colmn("bool_var_a", Boolean, nullable=True),
)

and I need to execute a query like.

select([testa.c.id & 15])

But while executing this query getting an error

Traceback (most recent call last):
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 81, in __and__
    return self.operate(and_, other)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 731, in operate
    return op(self.comparator, *other, **kwargs)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 81, in __and__
    return self.operate(and_, other)
  File "<string>", line 1, in <lambda>
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 67, in operate
    return o[0](self.expr, op, *(other + o[1:]), **kwargs)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 147, in _conjunction_operate
    return and_(expr, other)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 2098, in and_
    return cls._construct(operators.and_, True_, False_, *clauses)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 2028, in _construct
    clauses = [
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 2029, in <listcomp>
    _expression_literal_as_text(clause)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 4569, in _expression_literal_as_text
    return _literal_as_text(element)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 4592, in _literal_as_text
    return _literal_as(element, _no_text_coercion)
  File "/Users/users_name/Projects/x_men/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 4582, in _literal_as
    raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of type <class 'int'> instead

But in Postgres using psql command I can perform the query SELECT id & 15 FROM testa;. Is there any support for this in sqlalchemy.

[Simon King]

SQLAlchemy overrides the & operator:

https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.and_

You can use the "op" function to get at the postgres & operator:

https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.op

Something like this:

select([testa.c.id.op("&")(15)])

Hope that helps,

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/37691a3d-826a-4311-bbdb-6ba3eb67b95a%40googlegroups.com.

[Balukrishnan]

Thank you sir, it's working.

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

[Balukrishnan]

Hi, I need one more help.
By using select([testa.c.id.op("&")(15)]) this query i got the result object but the return value can only accessed by the key 'anon_1'.

I tried to label (and anon_lalel) this query but most of the times query fails and the rest the same result(that is label is not getting applied.

I tried these

• select([testa.c.id.op.label("Id").("&")(15)]) .  => No changes still the value can only accesses by the key anon_1
  
• select([testa.c.id.op("&")(15).label("Id")]) .  => Error (AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'table')
  
• select([testa.c.id.anon_label("Id").op("&")(15)]) .  => Error (TypeError: '_anonymous_label' object is not callable)  
• select([testa.c.id.op("&")(15).anon_label("Id")]) .  => Error (TypeError: '_anonymous_label' object is not callable)

How can I label that column with a known name

[Balu krishnan]

Sorry, It was my mistake. We can label that without any issue.

select([testa.c.id.op("&")(15).label("Id")]) 

This one will work perfectly.

--

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/7665b7ce-44fc-4a9d-baf6-5175b689e2ab%40googlegroups.com.