LargeBinary type fails to create LongBlob in Mysql and creates Blob

[Dan Morozoff]

I am creating a table using the declarative_base() function. LargeBinary type fails to create LongBlob in Mysql and creates Blob.

Please see code below for reference.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, BigInteger, String, Float, Text, Boolean, LargeBinary
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

from DatabaseProperties import DB

# Set up the declarative base for our definitions
Base = declarative_base()


# This is the tables and classes that each object has defined
class reps(Base):
    __tablename__ = 'reps'

    table_id = Column(Integer, primary_key=True)

    id = Column('id', Integer)
    id_asset = Column('i', String(100))

    frame_number = Column('f', BigInteger)
    descriptors = Column('d', LargeBinary)
    keypoints = Column('k', LargeBinary) 

    def __repr__(self):
        return "ID: %d, id_asset: %s" % (self.id, self.id_asset)


def create_all_classes():
    """ This function creates all of the classes """
    engine = create_engine(URL(**DB))
    Base.metadata.create_all(engine)
    print "Tables Generated"
    pass

if __name__ == "__main__":
    create_all_classes()

This code completes without any errors.

The expected behavior is columns d and k should be of type LongBlob, yet they are of Blob.

Thanks in advance.

[Mike Bayer]

Hi there -

The documentation for LargeBinary itself actually names MySQL as an example platform and documents that by default it renders BLOB http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=largebinary#sqlalchemy.types.LargeBinary:

The LargeBinary type corresponds to a large and/or unlengthed binary type for the target platform, such as BLOB on MySQL and BYTEA for PostgreSQL. It also handles the necessary conversions for the DBAPI.

LONGBLOB might also not be very popular because MySQL has a limit of 1G for max_allowed_packet, and to get more than 1G into it requires the use of streaming protocols in conjunction with a prepared statement, which I'm not sure any of the MySQL Python drivers have direct support for (and this would also require bypassing SQLAlchemy's usual APIs) - to get the 1G into the LONGBLOB without prepared/streaming means you have to have the full 1G value in memory at once and send it in a huge buffer, so not very practical.

If your issue is just being able to render LONGBLOB, as opposed to disagreement over LargeBinary's default of BLOB, you can of course use the sqlalchemy.dialects.mysql.LONGBLOB type directly. If you want to combine this with generic LargeBinary you can use LargeBinary().with_variant(LONGBLOB, "mysql").

As far as LargeBinary default, MEDIUMBLOB is likely a good choice of default however changing this would be a major release item and it would have a negative impact on systems using things like Alembic autogenerate, so I'd favor leaving LargeBinary at BLOB and just encouraging the use of with_variant().

[Mike Bayer]

with workarounds provided I'd prefer the longstanding behavior of LargeBinary->BLOB be maintained.