Storing MySQL binary(16) with JayDeBeApi

Question

I need to store a UUID as binary(16) in MySQL using JayDeBeApi, which uses JDBC under the hood. When I try to save the data using Python bytes, the data is not converted/or stored correctly.

When using one parameter

curs.execute('INSERT INTO `user` (userId) VALUES (?)',
             (uuid.uuid4().bytes))

there is the following error:

Parameter index out of range (2 > number of parameters, which is 1).

When using two parameters

curs.execute('INSERT INTO `user` (userId,username) VALUES (?, ?)',
             (uuid.uuid4().bytes, 'some_user'))

There's no error, but the data doesn't get stored correctly

mysql> select * from user;
+------------------------------------+-----------+
| userId                             | username  |
+------------------------------------+-----------+
| 0x00000000000000000000000000000000 | some_user |
+------------------------------------+-----------+

When I used pymysql it worked fine using uuid.uuid4().bytes. So what is the correct way to save the bytes (or just the UUID) using JayDeBeApi?

Here is a reproducible example.

UPDATE: (still open)

So I found a solution using the UNHEX() function. Problem is that the UNHEX() function isn't portable. The reason I switched from pymysql to jaydebeapi is because I need to use H2 for testing. And H2 apparently doesn't have this same function. So if anyone has a different solution that doesn't involve using this function (or any at all), I would prefer that solution.

So for now, this question is still open.

Answer 1

Note: Not a complete solution. I'm still looking for a way to do this without the use of a function as the function is not portable.

So with the help of Inserting and selecting UUIDs as binary(16), I ended up using the UNHEX function to just un-hex the actual UUID string.

curs.execute("INSERT INTO `user` (userId,username) VALUES (UNHEX(?), ?)",
             (user_id.hex, 'some_user'))

mysql> select * from user;
+------------------------------------+-----------+
| userId                             | username  |
+------------------------------------+-----------+
| 0x7F69F93B0E9F48D0AE8C7D04C78B530F | some_user |
+------------------------------------+-----------+

Also, it seems dabblack was correct about the "one parameter" problem in regards to the tuple. When I try to use one parameter, if I don't add a comma, it's does work correctly. I'm not much of a Python user, so this is new to me.

curs.execute("INSERT INTO `user` (userId) VALUES (UNHEX(?))",
            (user_id.hex,))