SQLAlchemy: How to Exclude Password from Results

Introduction

When fetching data using SQLAlchemy, it’s important to handle sensitive information like passwords carefully. This article shows various ways to exclude passwords from query results in SQLAlchemy.

Using Deferred Columns

Deferred column loading allows specific columns to be loaded only when accessed. Mark the password column as deferred:

from sqlalchemy.orm import deferred

# Define your User model

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String)
    email = Column(String)
    password = deferred(Column(String))

# Query without loading password
dummy_query = session.query(User).all()

for user in dummy_query:
    print(user.username)  # Password is not queried yet

Loading Columns on Demand

If you need the password in some cases but not others, load it on demand:

# Load password on demand
user_with_pass = session.query(User).options(undefer('password')).get(some_user_id)
print(user_with_pass.password)

Using load_only()

To explicitly tell SQLAlchemy which columns to load, use the load_only() function:

from sqlalchemy.orm import load_only

# Query only specific columns excluding the password
dummy_query = session.query(User).options(load_only('id', 'username', 'email')).all()

Directly Excluding Columns

It’s also possible to directly query all but the password column:

# Exclude password by not mentioning it
query = session.query(User.id, User.username, User.email).all()

Hybrid Properties for Security

Use SQLAlchemy’s hybrid properties to wrap the password in a computed attribute that doesn’t explicitly expose the password:

from sqlalchemy.ext.hybrid import hybrid_property

# Add a hybrid property that doesn't return the password

class User(Base):
    ...
    
    @hybrid_property
    def safe_data(self):
        return { 'id': self.id, 'username': self.username, 'email': self.email }

# Query and use safe_data instead of the row
dummy_query = session.query(User).all()
for user in dummy_query:
    print(user.safe_data)

Automating Exclusion

For a more advanced approach, override methods in the base class to exclude passwords by default:

class SafeQuery(Query):

    def instances(self, result, context):
        """Override the default instance creation method to exclude passwords"""
        return (self._safe_instance(row) for row in result)

    def _safe_instance(self, row):
        data = super().instances(row)
        del data['password']
        return data

Conclusion

In conclusion, SQLAlchemy provides various methods to exclude passwords or any sensitive information from query results. Use these techniques wisely to ensure that your application’s data remains secure.