[Sqlalchemy-tickets] Issue #3268: Custom data type when doing cascaded delete -- errors out (zzzeek/sqlalchemy)

[taibit <iss...@bi...>]

New issue 3268: Custom data type when doing cascaded delete -- errors out
https://bitbucket.org/zzzeek/sqlalchemy/issue/3268/custom-data-type-when-doing-cascaded

taibit:

For custom data type that is UUID-like: 

If custom data types is defined so that python uses hex string and database uses char(16):
The delete statement needs to convert hex string to bytes for database, but
there seems to be a bug when doing cascaded deletes:
the delete does not convert the hex string to bytes.

If python uses byte constant for the custom data type (and bytes for database), then the cascaded delete is ok.


This bug test case uses a custom data type similar to the GUID type listed in sqlalchemy docs:
    Based on tip from sqlalchemy UUID/GUID docs: Can be modified to store binary in CHAR(16) if desired:
    http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#backend-agnostic-guid-type


When you run this file, in the working cases, the last few lines will show successful delete from order_details:

2014-12-09 18:38:49,481 INFO sqlalchemy.engine.base.Engine COMMIT
2014-12-09 18:38:49,482 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-12-09 18:38:49,483 INFO sqlalchemy.engine.base.Engine SELECT orders.id AS o
rders_id, orders.order_name AS orders_order_name, orders.create_at AS orders_cre
ate_at
FROM orders
WHERE orders.id = ?
2014-12-09 18:38:49,483 INFO sqlalchemy.engine.base.Engine ('short_term_order_2'
,)
2014-12-09 18:38:49,486 INFO sqlalchemy.engine.base.Engine SELECT order_details.
a_long_key_id AS order_details_a_long_key_id, order_details.order_id AS order_de
tails_order_id, order_details.create_at AS order_details_create_at
FROM order_details
WHERE ? = order_details.order_id
2014-12-09 18:38:49,486 INFO sqlalchemy.engine.base.Engine ('short_term_order_2'
,)
2014-12-09 18:38:49,488 INFO sqlalchemy.engine.base.Engine DELETE FROM order_det
ails WHERE order_details.a_long_key_id = ?
2014-12-09 18:38:49,488 INFO sqlalchemy.engine.base.Engine ('11e4342387457d747ee
b51180c627777',)
2014-12-09 18:38:49,489 INFO sqlalchemy.engine.base.Engine DELETE FROM orders WH
ERE orders.id = ?
2014-12-09 18:38:49,490 INFO sqlalchemy.engine.base.Engine ('short_term_order_2'
,)
2014-12-09 18:38:49,490 INFO sqlalchemy.engine.base.Engine COMMIT




But in the broken case, the last lines will be an error with this message:

sqlalchemy.exc.StatementError: must be str, not bytes (original cause: TypeError
: must be str, not bytes) 'DELETE FROM order_details WHERE order_details.a_long_
key_id = ?' [{'a_long_key_id': b'11e4342387457d747eeb51180c627777'}]