SQLAlchemy: How To Represent Data In Python Differently Than In The Database

In a (postgres) SQLAlchemy model/class, I have several columns that are 'price' columns. I have read that using numeric/money/float types for this sort of data is not a good idea,

Solution 1:

I would not recommend using validators for this. Instead, I'd recommend using TypeDecorator; see this discussion within the SQLAlchemy documentation. You'll create a TypeDecorator for a Price type that multiplies by 100 on bind processing and divides on row processing. Here's a simple example that handles uuid types from some code I have:

from sqlalchemy.types import TypeDecorator, CHAR
import uuid


class GUID(TypeDecorator):
# Also see:
# http://docs.sqlalchemy.org/en/latest/core/custom_types.html#backend-agnostic-guid-type

impl = CHAR

    def process_bind_param(self, value, dialect):
        if value is None:
            return value
        else:
            if not isinstance(value, uuid.UUID):
                 return uuid.UUID(value).hex
            else:
                return value.hex

    def process_result_value(self, value, dialect):
        if value is None:
            return value
        else:
            return uuid.UUID(value)

Having impl set to char is wrong for everything besides sqlite, because that won't set the length of the column that gets declared. This should give you a good idea how to put together the price type.