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

I don't have any experience working with SQLAlchemy, but according to this page the length parameter is optional and defaults to None. Could it be that None represents "MAX"? — Deniz Dogan
– Deniz Dogan, Commented Jun 18, 2010 at 8:35
unfortunately not. That just produces a CAST(x as VARBINARY) that then causes a "The column would be truncated" error. — Mark Hall
– Mark Hall, Commented Jun 18, 2010 at 8:53

zzzeek · Accepted Answer · 2010-06-21 14:47:43Z

6

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

answered Jun 21, 2010 at 14:47

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

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

3 Comments

van Over a year ago

@Mike: Strange, but haven't seen this one before. Would this be the best way to handle db-migration where the type(s) are missing in the target DB: say, migrating BIT from mssql to sqlite?

Mark Hall Over a year ago

A very nice solution. I'll have to look into the compiler extension a bit more.

zzzeek Over a year ago

I don't think we have BIT as a base type, it would be easiest just to subclass UserDefinedType: sqlalchemy.org/docs/reference/sqlalchemy/…

van · Accepted Answer · 2010-06-18 13:20:30Z

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

answered Jun 18, 2010 at 13:20

van

77.5k13 gold badges174 silver badges179 bronze badges

Comments

Philip · Accepted Answer · 2018-10-25 18:34:42Z

0

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.

answered Oct 25, 2018 at 18:34

Philip

97411 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related