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?
SQLAlchemy considers the database to be a relational algebra engine, not just a collection of tables. Rows can be selected from not only tables but also joins and other select statements; any of these units can be composed into a larger structure.
One way to store a list as a column value is to convert that list to a string, that would map to database data type varchar . Another way is to convert the list to a serialized binary representation (using pickle library, for example) and store the value as a database value of type blob .
The text() construct is used to compose a textual statement that is passed to the database mostly unchanged. It constructs a new TextClause, representing a textual SQL string directly as shown in the below code − from sqlalchemy import text t = text("SELECT * FROM students") result = connection.
PickleType. Holds Python objects, which are serialized using pickle. SchemaType. Mark a type as possibly requiring schema-level DDL for usage.
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.
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)
).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With