CODEWITHSUNDEEP
sql-serversqlalchemy
Mark Hall
Mark Hall

Reputation: 33

Define a varbinary(max) column using sqlalchemy on MS SQL Server

I'm querying an SQL Server database using SQLAlchemy and need to cast a column to varbinary(max). The thing I am struggling with is the "max" part. I can get the cast to work for any actual number (say varbinary(20)), but I cannot find how to get it to work for the "max" size of the varbinary column.

Any pointers? Links? Solutions?

Regards, Mark

Upvotes: 3

Views: 5343

Answers (3)

Philip
Philip

Reputation: 974

The sqlalchemy team resolved this issue based on this thread (Power of community!). You can find it here Large Text/Binary Type Deprecation

To use VARBINARY(max) use LargetBinary as datatype, and set the flag deprecate_large_types=True

From the site; create an engine with the flag deprecate_large_types=True:

eng = create_engine("mssql+pymssql://user:pass@host/db",deprecate_large_types=True)

When this flag is True, the UnicodeText, Text and LargeBinary datatypes, when used to render DDL, will render the types NVARCHAR(max), VARCHAR(max), and VARBINARY(max), respectively. This is a new behavior as of the addition of this flag.

Upvotes: 0

zzzeek
zzzeek

Reputation: 75317

I hope you've all noticed we have virtually this exact recipe in the main documentation for custom compilation, without any monkeypatching, here:

http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#changing-compilation-of-types

@compiles(String, 'mssql')
@compiles(VARCHAR, 'mssql')
def compile_varchar(element, compiler, **kw):
    if element.length == 'max':
        return "VARCHAR('max')"
    else:
        return compiler.visit_VARCHAR(element, **kw)

foo = Table('foo', metadata,
    Column('data', VARCHAR('max'))
)

Upvotes: 5

van
van

Reputation: 77082

SQLAlchemy does not support this out of the box (create a feature request on sqlalchemy trac).

In order to make it work for you, hack it: add the following method to the MSTypeCompiler class in sqlalchemy\dialects\mssql\base.py:

def visit_VARBINARY(self, type_):
    if type_.length == 'MAX':
        return "VARBINARY(MAX)"
    else:
        return "VARBINARY(%d)" % type_.length

and then use the query with the MSVarBinary type:

from sqlalchemy.dialects.mssql.base import MSVarBinary
...
q = ... cast(mytable.c.mycolumn, MSVarBinary('MAX')) ...

Upvotes: 2

Related Questions