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

Question

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

Answer 1

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

Answer 2

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')) ...