CODEWITHSUNDEEP
sql-serversqlalchemy
Hermann Schachner
Hermann Schachner

Reputation: 550

Insert value into VARBINARY column using sqlalchemy

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.

Upvotes: 2

Views: 3189

Answers (2)

Hermann Schachner
Hermann Schachner

Reputation: 550

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

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

Upvotes: 2

JumboClip
JumboClip

Reputation: 183

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])

Upvotes: 2

Related Questions