What type is used to store byte strings in SQLAlchemy?

Question

I'm trying to store byte strings in a PostgreSQL database using SQLAlchemy.

My model looks like this:

class UserAccount(db.Model):     id = db.Column(db.Integer, primary_key=True)     username = db.Column(db.String)     password_hash = db.Column(db.String)     password_salt = db.Column(db.String) 

I want password_hash and password_salt to both be byte strings (i.e. str, since I'm using Python 2.7), but even if I pass bytes into it, SQLAlchemy seems to turn them into unicode. I've also tried using convert_unicode=False in the model, but I get the same result.

What do I use to store bytes in a SQLAlchemy model?

Answer 1

Use the LargeBinary type, which will use the bytea type on PostgreSQL. You can also use the BYTEA type directly if you're only going to use PostgreSQL.

There are other SQL standard types, such as BINARY, but it's typically easier to use LargeBinary since SQLAlchemy will use the preferred type for each database.

Answer 2

For shorter byte strings you can also use TypeDcorator, to wrap the String column class. Then use your custom type class HexByteString as the Column type:

from sqlalchemy.types import Column, String, TypeDecorator from sqlalchemy.ext.declarative import declarative_base   class HexByteString(TypeDecorator):     """Convert Python bytestring to string with hexadecimal digits and back for storage."""      impl = String      def process_bind_param(self, value, dialect):         if not isinstance(value, bytes):             raise TypeError("HexByteString columns support only bytes values.")         return value.hex()      def process_result_value(self, value, dialect):         return bytes.fromhex(value) if value else None   Base = declarative_base()  class MyModel(Base):     data = Column(HexByteString) 

You can then assign bytes instances to the data attribute of MyModel instances and they will be converted to strings with hexdecimal digits and back when passed to/from the database. This will double the storage size, of course, but makes it easier to see what's stored in the column when looking at the database directly.

You can replace bytes.hex() / bytes.fromhex() (which needs Python 3.5+) with any other encoding scheme which safely stores bytes in Ascii strings, for example Base64 (base64.b64_encode(value).decode() / base64.b64_decode(value)).