Repository navigation
BINARY column type support #108
Description
I have a question regarding accessing delta lake tables by python. Similar to this SQLAlchemy example, I tried to save something in a BINARY column like:
example_bytes = Column(BINARY) # also tried Binary, LargeBinary, ...but that crashes with:
sqlalchemy.exc.StatementError: (builtins.AttributeError) module 'databricks.sql' has no attribute 'Binary'
I'm wondering if databricks-python-sql doesn't support binary column yet? Other basic types work fine. Also executing raw sql by the connector works fine. (I'm using hive metastore if this is relevant.)
Activity
susodapop commentedon Oct 24, 2023
Following up on this now that we've implemented SQLAlchemy 2 on
main. We have a much better understanding of how SQLAlchemy handles types now and there are some limitations of Databricks itself that make this unfeasible for the moment. Namely,databricks-sql-connectordoesn't have a way to bind parameter values ofBINARYtype. sqla needs every data type to bindable for parameterisation. Since DBR doesn't support binding BINARY values, our dialect doesn't either. This same limitation applies toARRAY,MAP, andSTRUCTtypes.That said, where there is a will there is a way. While DBR doesn't support parameterisation of these types, you can write a string SQL query that includes them. DBR understands hexadecimal strings and can translate them to
BINARYtypes during ingestion. And pysql will deliver them asio.Buffer's during egress.So if this is necessary for your use-case, you can implement a workaround by using your own custom SQLAlchemy type class (which extends
TypeDecorator). You'll need to implement theprocess_bind_param,process_literal_param, andprocess_result_valuemethods for this custom type in such a way that the...parammethods accept anio.Bufferand convert it to a hexadecimal string and render it inline with the query text.We've marked this as a
FutureFeatureto be added to this dialect here. If DBR adds support for binding these parameter types then it would be very easy to implement as part of our repository. If not, then inlining these values could work. Although would not be strictly safe from SQL injection, since an arbitrary value would be rendered in the resulting SQL string.Although this may not be as risky, since hexadecimal strings cannot include any of the dangerous SQL commands: the hexadecimal charset doesn't include
;or enough consonants to spellDROP,UPDATE,CREATEetc.[-]Binary sqlalchemy column errors out[/-][+]BINARY column type support[/+]vikrantpuppala commentedon Jul 8, 2026
Closing this as the issue is quite stale - please reopen if help is required