CODEWITHSUNDEEP
pythonoracle11gsqlalchemy
user3632887
user3632887

Reputation: 3

Bitwise operator in SQLAlchemy: Error when using Oracle Database

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.

Upvotes: 0

Views: 683

Answers (1)

univerio
univerio

Reputation: 20548

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

Upvotes: 0

Related Questions