Custom data type when doing cascaded delete -- errors out

[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'}]




"""

[Mike Bayer]

your custom type is incorrect. it is critical that process_bind_param and process_result_value deal with symmetric conversions, meaning, the incoming type to process_bind_param must match exactly the outgoing type of process_result_value, and vice versa. this is so that as a value is passed to the database, then returned back, then back into the database again, it can be converted in both directions each time.

in this case that isn't happening, as we have bytes.fromhex(value) on one side, and binascii.hexlify(value) on the other:

>>> bytes.fromhex(binascii.hexlify(bytes.fromhex('11e4342387457d747eeb51180c626666')))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: must be str, not bytes

the SQL echo output makes this clear as you can see a b'' value that was derived from the order_details row is then being applied to the subsequent DELETE, and fails because of the bound processor above - in Python 3, it's also in the upper stack trace:

Traceback (most recent call last):
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 889, in _execute_context
    context = constructor(dialect, self, conn, *args)
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 573, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/sql/type_api.py", line 863, in process
    return impl_processor(process_param(value, dialect))
  File "test.py", line 148, in process_bind_param
    return bytes.fromhex(value)
TypeError: must be str, not bytes

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

the solution is that your type has to coerce to string since that's the data type the bound processor expects:

def process_result_value(self, value, dialect):
    if value is None:
        return value
    else:
        #return uuid.UUID(value)
        return binascii.hexlify(value).decode('ascii')