Insert value into VARBINARY column using sqlalchemy

Question

I am using a MS SQL database, together with Python and sqlalchemy. One table has a column defined as

CREATE TABLE MyTable (
    ...
    [address] [varbinary] (16) NULL
    ...

The table class MyTable is generated by means of table reflection. Assigning a string value to MyTable.address in my Python code triggers the exception

sqlalchemy.exc.StatementError: (builtins.TypeError) string argument without an encoding

when writing it to the database. Passing a bytearray instead yields the database error

(pymssql.OperationalError) (257, 
b'Implicit conversion from data type varchar to varbinary is not allowed. 
Use the CONVERT function to run this query.DB-Lib error message 20018, severity 16 [...]

I understand that an encoding is required to store a string in a binary column, but how can I provide that encoding? How else could I write binary data to the database using the ORM capabilities of sqlalchemy?

For I am creating my table classes through reflection, I cannot modify the SQL code directly like described in the answer to Insert binary file into MSSQL db (varbinary) with python pymssql

Reading the sqlalchemy documentation on MS SQL also yielded no solution.

JumboClip · Accepted Answer · 2015-05-27 07:08:19Z

2

Try to convert first the data to varbinary before saving it to the database because the data type of the address field is varbinary.

select convert(varbinary,[the string or data to be convert])

answered May 27, 2015 at 7:08

JumboClip

1838 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hermann Schachner Over a year ago

It seems I did not make clear that I want to use sqlalchemy's ORM to avoid writing SQL code. I changed my question accordingly.

juan · Accepted Answer · 2020-02-20 14:16:51Z

2

Assuming that, like in my case, address is a large integer, one can write

a = 72037797995605455
MyTable.address = a.to_bytes(16, 'big')

edited Feb 20, 2020 at 14:16

juan

82.3k52 gold badges165 silver badges198 bronze badges

answered May 28, 2015 at 15:14

Hermann Schachner

5506 silver badges21 bronze badges

Collectives™ on Stack Overflow

Insert value into VARBINARY column using sqlalchemy

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related