Add Binary constructor and bytes parameter serialization

[bgunebakan]

PEP 249 (DB-API 2.0) specifies that drivers should provide a Binary(string) type constructor for working with binary data. crate-python currently omits it, and passing raw bytes as a SQL parameter raises TypeError from orjson because json_encoder() has no handler for bytes.

Current behaviour

Create a table with binary columns:

CREATE TABLE t_binary (flags BIT(8))

conn = crate.client.connect("localhost:4200")
cur = conn.cursor()
cur.execute("INSERT INTO t_binary (data) VALUES (?)", (b"\x00\xff",))
# TypeError: Type is not JSON serializable: bytes

Root cause

json_encoder() in src/crate/client/http.py covers Decimal, datetime, date, and time, but not bytes. There is also no Binary symbol exported from the module.

Notes

• This implementation is needed to implement Types: Add support for BINARY columns sqlalchemy-cratedb#12

[amotl]

Hi Bilal. Thank you for investigating. Because CrateDB does not provide a data type for storing bytes, the driver or another layer would need to encode/decode to/from an Unicode-safe representation and store the data into a STRING column, like this other patch tries to exercise it with SQLAlchemy.

• Types: Add support for BINARY columns sqlalchemy-cratedb#12

I don't know how this would work on the DBAPI driver level, but if you see a way to support the feature right here, and use it for building the SQLAlchemy support upon, we will be more than happy.

[mfussenegger]

Can you clarify what kind of advantage a Binary type constructor would bring?

The bitstring type (bit) is intentionally serialized as a string of 0/1 bits via JSON because there is no Binary type in JSON.

cr> create table tbl (flags bit(8));
CREATE OK, 1 row affected (0.098 sec)
cr> insert into tbl (flags) values ('11110001');
INSERT OK, 1 row affected (0.032 sec)
cr> select * from tbl;
+-------------+
| flags       |
+-------------+
| B'11110001' |
+-------------+
SELECT 1 row in set (0.013 sec)

So this can already be used just fine via crate-python.

Note also that bit() isn't really suited for arbitrary binary payloads. Imho it can't/shouldn't be used for a Binary column in sqlalchemy.

[bgunebakan]

Thanks. I validated all of this against a real CrateDB 6.4.3 with integration tests, covered all of my claims and @mfussenegger suggestion.
As a conclusion, I'm closing this. the reported symptom is real, but the framing and the my proposed fix are wrong.

The TypeError is real. json_encoder() in src/crate/client/http.py has branches for Decimal, datetime, date and time.

1. I used a BIT(8) column, but BIT is not a binary-payload type. @mfussenegger is right that it already works today with a plain str.

cur.execute("INSERT INTO t (flags) VALUES (?)", ("11110001",))
cur.execute("SELECT flags FROM t")   # -> "B'11110001'"

2. This is not a blocker for Types: Add support for BINARY columns sqlalchemy-cratedb#12. That PR emulates BINARY as base64-in-STRING at the SQLAlchemy layer.

So #12 needs no change here.