Reputation: 3
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
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