SQLAlchemy encrypt a column without automatically decrypting upon retrieval

Question

Currently, I use EncryptedType from sqlalchemy_utils in order to automatically encrypt data on the way in to the table and decrypt the data when it's being retrieved from the table, using some predefined string as the encryption key. This works fine, but now requirements have changed and while I still need to encrypt the data on the way in to the table, I now need to keep that data encrypted when it is retrieved. I'm not sure if this is something that EncryptedType supports or if there is any other way to do this using SQLAlchemy without rolling my own in what I would assume would be the cryptography library.

Example of my table:

class MyTable(db.Model):
    __tablename__ = "my_table"
    id = db.Column(db.Integer, primary_key=True, autoincrement="auto")
    name = db.Column(db.String(50), nullable=False)
    username = db.Column(EncryptedType(db.String, _key), nullable=True)
    password = db.Column(EncryptedType(db.String, _key), nullable=True)

Answer 1

I found that using the cryptography library, which is what EncryptedType uses to handle encryption, was the simplest solution here.

I added an __init__ method to the model class and handled the encryption there.

from cryptography.fernet import Fernet
key = "my_encryption_key_here"

class MyTable(db.Model):
    __tablename__ = "my_table"
    id = db.Column(db.Integer, primary_key=True, autoincrement="auto")
    name = db.Column(db.String(50), nullable=False)
    username = db.Column(EncryptedType(db.String, _key), nullable=True)
    password = db.Column(EncryptedType(db.String, _key), nullable=True)
    auth_password = db.Column(EncryptedType(db.String, _key), nullable=True)

    def __init__(self, name, username, password, auth_password):
        cipher_suite = Fernet(key)
        self.name = name
        self.username = cipher_suite.encrypt(bytes(username))
        self.password = cipher_suite.encrypt(bytes(password))