Fail inserting a varbinary value

[allaboutmikey]

With the following table structure, I am seemingly unable to insert image data into the ppe_image field:

CREATE TABLE [dbo].[ppe_types](
    [ppe_id] [tinyint] IDENTITY(1,1) NOT NULL,  
    [ppe_type] [nvarchar](20) NOT NULL, 
    [ppe_image] [varbinary](max) NULL

The code looks like:

with io.BytesIO() as output:
    image.save(output, format="jpeg")
    with pytds.connect(dsn='connection info removed', as_dict=True) as conn:
        with conn.cursor() as cur:
            cur.execute("insert into ppe_types (ppe_type, ppe_image) values (%s, %s)", (args.image_name, output))

and the error is:

Traceback (most recent call last):
  File "/tmp/pyloadimage.py", line 30, in <module>
    cur.execute("insert into ppe_types (ppe_type, ppe_image) values (%s, %s)", (args.image_name, output))
  File "/usr/lib/python3/dist-packages/pytds/__init__.py", line 657, in execute
    self._execute(operation, params)
  File "/usr/lib/python3/dist-packages/pytds/__init__.py", line 634, in _execute
    named_params = self._session._convert_params(named_params)
  File "/usr/lib/python3/dist-packages/pytds/tds.py", line 993, in _convert_params
    for name, value in parameters.items()]
  File "/usr/lib/python3/dist-packages/pytds/tds.py", line 993, in <listcomp>
    for name, value in parameters.items()]
  File "/usr/lib/python3/dist-packages/pytds/tds.py", line 982, in make_param
    column.type = self.conn.type_inferrer.from_value(value)
  File "/usr/lib/python3/dist-packages/pytds/tds_types.py", line 2515, in from_value
    sql_type = self._from_class_value(value, type(value))
  File "/usr/lib/python3/dist-packages/pytds/tds_types.py", line 2600, in _from_class_value
    raise tds_base.DataError('Cannot infer TDS type from Python value: {!r}'.format(value))
pytds.tds_base.DataError: Cannot infer TDS type from Python value: <_io.BytesIO object at 0x7fdf04f3ebf8>

Fair enough that it can't infer a type from bytes, but can it not get type info from the database column directly? Failing the inference, I thought I'd just specifiy the type myself. The tds_types code seems to have types for varbinary and even Image, but I can't see anywhere that I can specify the type for my parameter. Do I have to construct my own column object? I read of someone doing that in another issue, but can't find any documentation for that object.

Edited to get the code markers right

[denisenkom]

You should pass bytes object instead of BytesIO object. Try this cur.execute("insert into ppe_types (ppe_type, ppe_image) values (%s, %s)", (args.image_name, output.getvalue()))

[allaboutmikey]

So I made that change:

image=Image.open(args.image_path)
with io.BytesIO() as output:
    image.save(output, format="jpeg")
    with pytds.connect(dsn='removed', as_dict=True) as conn:
        with conn.cursor() as cur:
            cur.execute("insert into ppe_types (ppe_type, ppe_image) values (%s, %s)", (args.image_name, output.getvalue()))

The error is now:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/pytds/tds_base.py", line 319, in force_unicode
    return s.decode('utf8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xff in position 0: invalid start byte

Still doesn't seem happy with the parameter. I'm not sure why it's decoding anything when the field is a bytes field.

[allaboutmikey]

I have brute forced the query to get my work done, but it is a bit ugly! Code is modified to:

with io.BytesIO() as output:
    image.save(output, format="jpeg")

# removes the outer b and quotes from eg: b'1234' and makes it like 0x1234 which is a TSQL binary literal
hex = "0x" + str(binascii.hexlify(output.getvalue()))[2:-1]

with pytds.connect(dsn='removed', as_dict=True) as conn:
    with conn.cursor() as cur:
        cur.execute(f"insert into ppe_types (ppe_type, ppe_image) values (%s, {hex})", (args.image_name,))

which works, but is obviously not properly parameterised. I still haven't been able to figure out how to get plain bytes into a parameter. :(

Edit: Tracing through the code mentioned in the stack trace of the original version: in the _execute function (line 602 in init.py) the param_definition (line 635) has the field as an nvarchar(MAX) I assume this is the cause of my problem.

If I select the same field from the table and then call the description method on the cursor, I get the tuple: ('ppe_image', 165, None, 0, None, None, 1) which is a varbinary.

From tds_base.py: BIGVARBINTYPE = XSYBVARBINARY = 165 # 0xA5

I'm starting to go cross-eyed (it's after 5), so I'll have to come back to figuring out how this happens next time I'm here.

[denisenkom]

You would also need to wrap your bytes value with pytds.Binary, here is example: https://github.com/denisenkom/pytds/blob/master/tests/types_test.py#L124

[allaboutmikey]

Ah ha! The wrapping thing is what I was looking for. That's working perfectly now with all my ugly kludges removed. Thanks for your input. BTW I've said it before, but this is a great module! Well done on writing it. This would be a useful addition to the documentation IMHO. Is there a way I can help adding it?

[denisenkom]

Thanks, here is information about docs: Source for documentation is here https://github.com/denisenkom/pytds/tree/master/docs, rendered docs can be seen here https://github.com/denisenkom/pytds/tree/master/docs. You can start by adding that after Table Valued Parameters section.