Bitwise operator in SQLAlchemy: Error when using Oracle Database

Question

I need to write some queries using bitwise logic AND/OR, but only when I use oracle database, I receive the following error:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01036: illegal variable name/number [SQL: 'SELECT "Cars"."Id", "Cars"."Name", "Cars"."Price" \nFROM "Cars" \nWHERE ("Cars"."Price" & :Price_1) > :param_1'] [parameters: {'Price_1': 32768, 'param_1': 0}]

if I use PostgreSql or Sqlite, I receive the expected answer.

create_engine('sqlite:///cars.sqlite3') OK! create_engine('postgresql+psycopg2://xxx:yyy@localhost:5432/db_sql_alchemy') OK! create_engine('oracle+cx_oracle://xxx:yyy@localhost:49161/xe') ERROR!

Other operations such as select, where clauses, table creation, are working as expected in all 3 databases.

Watching the error log, it seems that the query its not being translated correctly to oracle syntax. I was expecting something like this:

SELECT "Cars"."Id", "Cars"."Name", "Cars"."Price" FROM "Cars" WHERE (BitAnd("Cars"."Price", 32768) > 0);

The operation that generates the error is:

stm = stm.where(cars.c.Price.op('&')(0x8000) > 0)

I am using Python 2.7.12 and SQLAlchemy==1.1.2.

Oracle does not appear to have the & operator. Try the BITAND function instead. — univerio
op() generics render as is. No translation involved (iirc). You got what you requested. — Ilja Everilä
Thank you for your answer, but I am using sqlalchemy -SQL Expression Language-. The syntax for bitwise operation is equal for any database choice, I suppose. I'd like to skip writing raw sql because I need to use more than one database. — user3632887
The syntax is not the same for every database. If you need to support multiple DB backends transparently you'll need to create a custom construct and customize the compilation accordingly. — univerio
Thanks @univerio. I think I am not being able to understand some basic concepts. when you say "you'll need to create a custom construct and customize the compilation accordingly", I need follow this steps because the bitwise operator '&' is not defined and when using -meth:.ColumnOperators.op- there is no compilation for the specific databse? in the docs, the operator +, is compiled according the DB choice In which cases do we have compilation for the speficic engine? Could you give more detais, code samples, patters, on how to "custom construct and customize the compilation accordingly" — user3632887

univerio univerio · Accepted Answer · 2016-11-18T20:15:56

Here's an example of creating a custom operator that compiles differently on different backends:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.elements import ColumnElement, Visitable, Grouping
from sqlalchemy.sql.operators import custom_op, is_precedent

class BitwiseAnd(ColumnElement):
    type = Integer()
    operator = custom_op("&", precedence=6)

    def __init__(self, left, right):
        if not isinstance(left, Visitable):
            left = bindparam("bitand", left, unique=True)
        if not isinstance(right, Visitable):
            right = bindparam("bitand", right, unique=True)
        self.left = left
        self.right = right

    def self_group(self, against=None):
        if is_precedent(self.operator, against):
            return Grouping(self)
        else:
            return self


@compiles(BitwiseAnd)
def _compile_bitwise_and(element, compiler, **kwargs):
    left = element.left.self_group(against=element.operator)
    right = element.right.self_group(against=element.operator)
    return compiler.process(element.operator(left, right))


@compiles(BitwiseAnd, "oracle")
def _compile_bitwise_and_oracle(element, compiler, **kwargs):
    return compiler.process(func.BITAND(element.left, element.right))

q = select([BitwiseAnd(BitwiseAnd(1, 2), BitwiseAnd(3, 4))])
print(q.compile(dialect=mysql.dialect()))
# SELECT (%s & %s) & (%s & %s) AS anon_1
print(q.compile(dialect=oracle.dialect()))
# SELECT BITAND(BITAND(:bitand_1, :bitand_2), BITAND(:bitand_3, :bitand_4)) AS anon_1 FROM DUAL

Bitwise operator in SQLAlchemy: Error when using Oracle Database

1 Answers