How to insert value for varbinary type data in SQL?-CodePudding

I have a python script that can provide some certain data in byte array, but it cannot be converted to a string (because of invisible characters).

Now that I want to insert its value to a varbinary type field in a mysql database, how can I do it?

CodePudding user response：

To insert a value for a varbinary type data in SQL, you can use the following syntax:

INSERT INTO table_name (field_name) VALUES (X'0123456789ABCDEF');

In this example, table_name is the name of the table where the varbinary data will be inserted, and field_name is the name of the field with the varbinary data type.

To insert a value from a byte array in Python, you can use the binascii.hexlify() function to convert the byte array to a hexadecimal string, and then use the bytes.fromhex() function to convert the hexadecimal string to a bytes object. This bytes object can then be inserted into the varbinary field using the above syntax.

Here is an example:

import binascii

# Some byte array data
data = b'\x01\x23\x45\x67\x89\xAB\xCD\xEF'

# Convert the byte array to a hexadecimal string
hex_string = binascii.hexlify(data)

# Convert the hexadecimal string to a bytes object
bytes_object = bytes.fromhex(hex_string)

# Insert the bytes object into the varbinary field
INSERT INTO table_name (field_name) VALUES (X'0123456789ABCDEF');

Alternatively, you can use the bytes() function to directly convert the byte array to a bytes object, and then use the above syntax to insert it into the varbinary field. Here is an example:

# Some byte array data
data = b'\x01\x23\x45\x67\x89\xAB\xCD\xEF'

# Convert the byte array to a bytes object
bytes_object = bytes(data)

# Insert the bytes object into the varbinary field
INSERT INTO table_name (field_name) VALUES (X'0123456789ABCDEF');