linearmoney.ext.sqlalchemy
SQLAlchemy ORM integrations for linearmoney.
Install with pip install "linearmoney[sqlalchemy]".
1"""SQLAlchemy ORM integrations for linearmoney. 2 3Install with `pip install "linearmoney[sqlalchemy]"`. 4""" 5 6__all__ = [ 7 "VectorMoney", 8 "AtomicMoney", 9] 10 11import decimal 12 13from sqlalchemy.types import TypeDecorator, String, Integer 14from sqlalchemy import Dialect 15 16import linearmoney as lm 17 18 19class VectorMoney(TypeDecorator): 20 """SQLAlchemy column type that automatically serializes and 21 deserializes a `linearmoney.vector.MoneyVector` to and from a String column 22 for storage in the db. 23 24 This type preserves all vector information including the currency space, so 25 it should be used whenever non-destructive storage is desired. 26 27 The disadvantage of this column type is that it uses a sqlalchemy String column 28 (VARCHAR) underneath, so in-db aggregate functions like SUM and MAX cannot 29 be used and these operations need to be performed in Python if they are needed. 30 31 Another consequence of this is that comparisons in where clauses don't work as 32 expected. For example, 33 `select(VectorModel).where(VectorModel.money_column > money_vector)` will compare 34 the serialized string of `money_vector` against the string stored in the db for the 35 greater than operator, so it will not give a correct result based on the actual 36 monetary values of the vectors. 37 38 Examples: 39 >>> from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase 40 >>> 41 >>> from linearmoney.ext.sqlalchemy import VectorMoney 42 >>> 43 >>> class BaseModel(DeclarativeBase): ... 44 >>> 45 >>> class LMExample(BaseModel): 46 ... 47 ... __tablename__ = "lm_example" 48 ... 49 ... id: Mapped[int] = mapped_column(primary_key=True) 50 ... money_column: Mapped[VectorMoney] = mapped_column(VectorMoney) 51 """ 52 53 impl = String 54 55 # We add an empty docstring to `cache_ok`, so that pdoc doesn't 56 # include the parent class' docstring. 57 cache_ok = True 58 """""" 59 60 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 61 # We ignore the override error because violating Liskov doesn't make any difference 62 # when the argument isn't typed. 63 def process_bind_param( 64 self, value: lm.vector.MoneyVector, dialect: Dialect # type: ignore[override] 65 ): 66 """Serialize the `MoneyVector` to a `str` compatible with the sqlalchemy `String` 67 column type.""" 68 69 return lm.vector.store(value) 70 71 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 72 # We ignore the override error because violating Liskov doesn't make any difference 73 # when the argument isn't typed. 74 def process_result_value(self, value: str, dialect: Dialect): # type: ignore[override] 75 """Deserialize the `str` stored in the db to a `MoneyVector` equivalent to the vector 76 that was originally stored.""" 77 78 return lm.vector.restore(value) 79 80 81class AtomicMoney(TypeDecorator): 82 """SQLAlchemy column type that automatically serializes and 83 deserializes a `linearmoney.vector.MoneyVector` as an atomic value in the smallest 84 denomination of `currency` to and from an integer column for storage in the db. 85 86 This type is intended to be used with single-currency applications. It 87 evaluates the stored asset vector in a single-currency space defined by 88 the `currency` argument provided on column declaration. 89 90 This means that attempting to store a money vector from a different currency space 91 will result in a `linearmoney.exceptions.SpaceError`. 92 93 The reason for using a single-currency space in the internal calculations instead 94 of checking the space of the passed in vector is to ensure that 95 the value read from the database is the same value that was written to it. 96 97 For example, if we store a vector of (0 EUR, 10 USD,) in an AtomicMoney column 98 for USD, then it will store the integer 1000 in the database. We will get the 99 same value stored if we store a vector of (10 USD,). The difference is that 100 we lose the information about the currency space when storing the value as an 101 integer, so when we read the values from the db, both with deserialize to (10 USD,). 102 By enforcing a single-currency space on write, we ensure that the values read 103 from the database are actually the values that were written to the database, and 104 we ensure that the integrity of math on those values will not be compromised 105 through any subsequent writes/reads to/from the database. 106 107 The main advantage of this column type is that it allows the use of in-db aggregate 108 functions like SUM and MAX as well as ordered comparisons. The disadvantage is 109 that it can only be used with single-currency applications or with a manual 110 conversion step before passing any values into sqlalchemy's column operations. 111 For this reason, multi-currency applications should generally choose `VectorMoney` 112 instead. 113 114 Examples: 115 >>> from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase 116 >>> 117 >>> import linearmoney as lm 118 >>> from linearmoney.ext.sqlalchemy import AtomicMoney 119 >>> 120 >>> class BaseModel(DeclarativeBase): ... 121 >>> 122 >>> CURRENCY = lm.data.currency("USD") 123 >>> 124 >>> class LMExample(BaseModel): 125 ... 126 ... __tablename__ = "lm_example" 127 ... 128 ... id: Mapped[int] = mapped_column(primary_key=True) 129 ... money_column: Mapped[AtomicMoney] = mapped_column(AtomicMoney(CURRENCY)) 130 """ 131 132 impl = Integer 133 # We add an empty docstring to `cache_ok`, so that pdoc doesn't 134 # include the parent class' docstring. 135 cache_ok = True 136 """""" 137 138 _currency: lm.data.CurrencyData 139 140 _forex: lm.vector.ForexVector 141 _space: lm.vector.CurrencySpace 142 143 def __init__(self, currency: lm.data.CurrencyData, *args, **kwargs) -> None: 144 """""" 145 super().__init__() 146 self._currency = currency 147 self._forex = lm.vector.forex({"base": currency.iso_code, "rates": {}}) 148 self._space = lm.vector.space(self.forex) 149 150 @property 151 def currency(self) -> lm.data.CurrencyData: 152 """The `CurrencyData` provided in the column constructor. 153 154 Defines the single-currency `forex` and `space` for converting 155 the `MoneyVector` into an integer on serialization.""" 156 157 return self._currency 158 159 @property 160 def forex(self) -> lm.vector.ForexVector: 161 """The `ForexVector` representing the single-currency 162 rates used to convert the `MoneyVector` to and from an integer.""" 163 164 return self._forex 165 166 @property 167 def space(self) -> lm.vector.CurrencySpace: 168 """The single-currency `CurrencySpace` used to convert the 169 `MoneyVector` to and from an integer.""" 170 171 return self._space 172 173 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 174 # We ignore the override error because violating Liskov doesn't make any difference 175 # when the argument isn't typed. 176 def process_bind_param( 177 self, value: lm.vector.MoneyVector, dialect: Dialect # type: ignore[override] 178 ): 179 """Serialize the `MoneyVector` to an `int` for storage in the sqlalchemy `Integer` 180 column type.""" 181 182 return lm.scalar.atomic( 183 lm.vector.evaluate(value, self.currency.iso_code, self.forex), 184 self.currency, 185 ) 186 187 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 188 # We ignore the override error because violating Liskov doesn't make any difference 189 # when the argument isn't typed. 190 def process_result_value(self, value: int, dialect: Dialect): # type: ignore[override] 191 """Deserialize the `int` stored in the db to a `MoneyVector` equivalent to the vector 192 that was originally stored.""" 193 194 exponent = decimal.Decimal(10) ** decimal.Decimal(self.currency.data["places"]) 195 decimal_value = decimal.Decimal(value) / exponent 196 return lm.vector.asset(decimal_value, self.currency.iso_code, self.space)
class
VectorMoney(sqlalchemy.sql.visitors.Visitable, typing.Generic[~_T]):
20class VectorMoney(TypeDecorator): 21 """SQLAlchemy column type that automatically serializes and 22 deserializes a `linearmoney.vector.MoneyVector` to and from a String column 23 for storage in the db. 24 25 This type preserves all vector information including the currency space, so 26 it should be used whenever non-destructive storage is desired. 27 28 The disadvantage of this column type is that it uses a sqlalchemy String column 29 (VARCHAR) underneath, so in-db aggregate functions like SUM and MAX cannot 30 be used and these operations need to be performed in Python if they are needed. 31 32 Another consequence of this is that comparisons in where clauses don't work as 33 expected. For example, 34 `select(VectorModel).where(VectorModel.money_column > money_vector)` will compare 35 the serialized string of `money_vector` against the string stored in the db for the 36 greater than operator, so it will not give a correct result based on the actual 37 monetary values of the vectors. 38 39 Examples: 40 >>> from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase 41 >>> 42 >>> from linearmoney.ext.sqlalchemy import VectorMoney 43 >>> 44 >>> class BaseModel(DeclarativeBase): ... 45 >>> 46 >>> class LMExample(BaseModel): 47 ... 48 ... __tablename__ = "lm_example" 49 ... 50 ... id: Mapped[int] = mapped_column(primary_key=True) 51 ... money_column: Mapped[VectorMoney] = mapped_column(VectorMoney) 52 """ 53 54 impl = String 55 56 # We add an empty docstring to `cache_ok`, so that pdoc doesn't 57 # include the parent class' docstring. 58 cache_ok = True 59 """""" 60 61 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 62 # We ignore the override error because violating Liskov doesn't make any difference 63 # when the argument isn't typed. 64 def process_bind_param( 65 self, value: lm.vector.MoneyVector, dialect: Dialect # type: ignore[override] 66 ): 67 """Serialize the `MoneyVector` to a `str` compatible with the sqlalchemy `String` 68 column type.""" 69 70 return lm.vector.store(value) 71 72 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 73 # We ignore the override error because violating Liskov doesn't make any difference 74 # when the argument isn't typed. 75 def process_result_value(self, value: str, dialect: Dialect): # type: ignore[override] 76 """Deserialize the `str` stored in the db to a `MoneyVector` equivalent to the vector 77 that was originally stored.""" 78 79 return lm.vector.restore(value)
SQLAlchemy column type that automatically serializes and
deserializes a linearmoney.vector.MoneyVector to and from a String column
for storage in the db.
This type preserves all vector information including the currency space, so it should be used whenever non-destructive storage is desired.
The disadvantage of this column type is that it uses a sqlalchemy String column (VARCHAR) underneath, so in-db aggregate functions like SUM and MAX cannot be used and these operations need to be performed in Python if they are needed.
Another consequence of this is that comparisons in where clauses don't work as
expected. For example,
select(VectorModel).where(VectorModel.money_column > money_vector) will compare
the serialized string of money_vector against the string stored in the db for the
greater than operator, so it will not give a correct result based on the actual
monetary values of the vectors.
Examples:
>>> from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase >>> >>> from linearmoney.ext.sqlalchemy import VectorMoney >>> >>> class BaseModel(DeclarativeBase): ... >>> >>> class LMExample(BaseModel): ... ... __tablename__ = "lm_example" ... ... id: Mapped[int] = mapped_column(primary_key=True) ... money_column: Mapped[VectorMoney] = mapped_column(VectorMoney)
def
process_bind_param( self, value: linearmoney.vector.MoneyVector, dialect: sqlalchemy.engine.interfaces.Dialect):
64 def process_bind_param( 65 self, value: lm.vector.MoneyVector, dialect: Dialect # type: ignore[override] 66 ): 67 """Serialize the `MoneyVector` to a `str` compatible with the sqlalchemy `String` 68 column type.""" 69 70 return lm.vector.store(value)
Serialize the MoneyVector to a str compatible with the sqlalchemy String
column type.
def
process_result_value(self, value: str, dialect: sqlalchemy.engine.interfaces.Dialect):
75 def process_result_value(self, value: str, dialect: Dialect): # type: ignore[override] 76 """Deserialize the `str` stored in the db to a `MoneyVector` equivalent to the vector 77 that was originally stored.""" 78 79 return lm.vector.restore(value)
Deserialize the str stored in the db to a MoneyVector equivalent to the vector
that was originally stored.
Inherited Members
- sqlalchemy.sql.type_api.TypeDecorator
- TypeDecorator
- impl_instance
- coerce_to_is_types
- Comparator
- comparator_factory
- type_engine
- load_dialect_impl
- process_literal_param
- literal_processor
- bind_processor
- result_processor
- bind_expression
- column_expression
- coerce_compared_value
- copy
- get_dbapi_type
- compare_values
- sort_key_function
- sqlalchemy.sql.base.SchemaEventTarget
- dispatch
- sqlalchemy.sql.type_api.TypeEngine
- render_bind_cast
- render_literal_cast
- hashable
- should_evaluate_none
- evaluates_none
- copy_value
- python_type
- with_variant
- as_generic
- dialect_impl
- adapt
- compile
class
AtomicMoney(sqlalchemy.sql.visitors.Visitable, typing.Generic[~_T]):
82class AtomicMoney(TypeDecorator): 83 """SQLAlchemy column type that automatically serializes and 84 deserializes a `linearmoney.vector.MoneyVector` as an atomic value in the smallest 85 denomination of `currency` to and from an integer column for storage in the db. 86 87 This type is intended to be used with single-currency applications. It 88 evaluates the stored asset vector in a single-currency space defined by 89 the `currency` argument provided on column declaration. 90 91 This means that attempting to store a money vector from a different currency space 92 will result in a `linearmoney.exceptions.SpaceError`. 93 94 The reason for using a single-currency space in the internal calculations instead 95 of checking the space of the passed in vector is to ensure that 96 the value read from the database is the same value that was written to it. 97 98 For example, if we store a vector of (0 EUR, 10 USD,) in an AtomicMoney column 99 for USD, then it will store the integer 1000 in the database. We will get the 100 same value stored if we store a vector of (10 USD,). The difference is that 101 we lose the information about the currency space when storing the value as an 102 integer, so when we read the values from the db, both with deserialize to (10 USD,). 103 By enforcing a single-currency space on write, we ensure that the values read 104 from the database are actually the values that were written to the database, and 105 we ensure that the integrity of math on those values will not be compromised 106 through any subsequent writes/reads to/from the database. 107 108 The main advantage of this column type is that it allows the use of in-db aggregate 109 functions like SUM and MAX as well as ordered comparisons. The disadvantage is 110 that it can only be used with single-currency applications or with a manual 111 conversion step before passing any values into sqlalchemy's column operations. 112 For this reason, multi-currency applications should generally choose `VectorMoney` 113 instead. 114 115 Examples: 116 >>> from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase 117 >>> 118 >>> import linearmoney as lm 119 >>> from linearmoney.ext.sqlalchemy import AtomicMoney 120 >>> 121 >>> class BaseModel(DeclarativeBase): ... 122 >>> 123 >>> CURRENCY = lm.data.currency("USD") 124 >>> 125 >>> class LMExample(BaseModel): 126 ... 127 ... __tablename__ = "lm_example" 128 ... 129 ... id: Mapped[int] = mapped_column(primary_key=True) 130 ... money_column: Mapped[AtomicMoney] = mapped_column(AtomicMoney(CURRENCY)) 131 """ 132 133 impl = Integer 134 # We add an empty docstring to `cache_ok`, so that pdoc doesn't 135 # include the parent class' docstring. 136 cache_ok = True 137 """""" 138 139 _currency: lm.data.CurrencyData 140 141 _forex: lm.vector.ForexVector 142 _space: lm.vector.CurrencySpace 143 144 def __init__(self, currency: lm.data.CurrencyData, *args, **kwargs) -> None: 145 """""" 146 super().__init__() 147 self._currency = currency 148 self._forex = lm.vector.forex({"base": currency.iso_code, "rates": {}}) 149 self._space = lm.vector.space(self.forex) 150 151 @property 152 def currency(self) -> lm.data.CurrencyData: 153 """The `CurrencyData` provided in the column constructor. 154 155 Defines the single-currency `forex` and `space` for converting 156 the `MoneyVector` into an integer on serialization.""" 157 158 return self._currency 159 160 @property 161 def forex(self) -> lm.vector.ForexVector: 162 """The `ForexVector` representing the single-currency 163 rates used to convert the `MoneyVector` to and from an integer.""" 164 165 return self._forex 166 167 @property 168 def space(self) -> lm.vector.CurrencySpace: 169 """The single-currency `CurrencySpace` used to convert the 170 `MoneyVector` to and from an integer.""" 171 172 return self._space 173 174 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 175 # We ignore the override error because violating Liskov doesn't make any difference 176 # when the argument isn't typed. 177 def process_bind_param( 178 self, value: lm.vector.MoneyVector, dialect: Dialect # type: ignore[override] 179 ): 180 """Serialize the `MoneyVector` to an `int` for storage in the sqlalchemy `Integer` 181 column type.""" 182 183 return lm.scalar.atomic( 184 lm.vector.evaluate(value, self.currency.iso_code, self.forex), 185 self.currency, 186 ) 187 188 # SQLAlchemy types `value` argument as Any | None, which makes sense for an overload. 189 # We ignore the override error because violating Liskov doesn't make any difference 190 # when the argument isn't typed. 191 def process_result_value(self, value: int, dialect: Dialect): # type: ignore[override] 192 """Deserialize the `int` stored in the db to a `MoneyVector` equivalent to the vector 193 that was originally stored.""" 194 195 exponent = decimal.Decimal(10) ** decimal.Decimal(self.currency.data["places"]) 196 decimal_value = decimal.Decimal(value) / exponent 197 return lm.vector.asset(decimal_value, self.currency.iso_code, self.space)
SQLAlchemy column type that automatically serializes and
deserializes a linearmoney.vector.MoneyVector as an atomic value in the smallest
denomination of currency to and from an integer column for storage in the db.
This type is intended to be used with single-currency applications. It
evaluates the stored asset vector in a single-currency space defined by
the currency argument provided on column declaration.
This means that attempting to store a money vector from a different currency space
will result in a linearmoney.exceptions.SpaceError.
The reason for using a single-currency space in the internal calculations instead of checking the space of the passed in vector is to ensure that the value read from the database is the same value that was written to it.
For example, if we store a vector of (0 EUR, 10 USD,) in an AtomicMoney column for USD, then it will store the integer 1000 in the database. We will get the same value stored if we store a vector of (10 USD,). The difference is that we lose the information about the currency space when storing the value as an integer, so when we read the values from the db, both with deserialize to (10 USD,). By enforcing a single-currency space on write, we ensure that the values read from the database are actually the values that were written to the database, and we ensure that the integrity of math on those values will not be compromised through any subsequent writes/reads to/from the database.
The main advantage of this column type is that it allows the use of in-db aggregate
functions like SUM and MAX as well as ordered comparisons. The disadvantage is
that it can only be used with single-currency applications or with a manual
conversion step before passing any values into sqlalchemy's column operations.
For this reason, multi-currency applications should generally choose VectorMoney
instead.
Examples:
>>> from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase >>> >>> import linearmoney as lm >>> from linearmoney.ext.sqlalchemy import AtomicMoney >>> >>> class BaseModel(DeclarativeBase): ... >>> >>> CURRENCY = lm.data.currency("USD") >>> >>> class LMExample(BaseModel): ... ... __tablename__ = "lm_example" ... ... id: Mapped[int] = mapped_column(primary_key=True) ... money_column: Mapped[AtomicMoney] = mapped_column(AtomicMoney(CURRENCY))
AtomicMoney(currency: linearmoney.data.CurrencyData, *args, **kwargs)
currency: linearmoney.data.CurrencyData
160 @property 161 def forex(self) -> lm.vector.ForexVector: 162 """The `ForexVector` representing the single-currency 163 rates used to convert the `MoneyVector` to and from an integer.""" 164 165 return self._forex
The ForexVector representing the single-currency
rates used to convert the MoneyVector to and from an integer.
167 @property 168 def space(self) -> lm.vector.CurrencySpace: 169 """The single-currency `CurrencySpace` used to convert the 170 `MoneyVector` to and from an integer.""" 171 172 return self._space
The single-currency CurrencySpace used to convert the
MoneyVector to and from an integer.
def
process_bind_param( self, value: linearmoney.vector.MoneyVector, dialect: sqlalchemy.engine.interfaces.Dialect):
177 def process_bind_param( 178 self, value: lm.vector.MoneyVector, dialect: Dialect # type: ignore[override] 179 ): 180 """Serialize the `MoneyVector` to an `int` for storage in the sqlalchemy `Integer` 181 column type.""" 182 183 return lm.scalar.atomic( 184 lm.vector.evaluate(value, self.currency.iso_code, self.forex), 185 self.currency, 186 )
Serialize the MoneyVector to an int for storage in the sqlalchemy Integer
column type.
def
process_result_value(self, value: int, dialect: sqlalchemy.engine.interfaces.Dialect):
191 def process_result_value(self, value: int, dialect: Dialect): # type: ignore[override] 192 """Deserialize the `int` stored in the db to a `MoneyVector` equivalent to the vector 193 that was originally stored.""" 194 195 exponent = decimal.Decimal(10) ** decimal.Decimal(self.currency.data["places"]) 196 decimal_value = decimal.Decimal(value) / exponent 197 return lm.vector.asset(decimal_value, self.currency.iso_code, self.space)
Deserialize the int stored in the db to a MoneyVector equivalent to the vector
that was originally stored.
Inherited Members
- sqlalchemy.sql.type_api.TypeDecorator
- impl_instance
- coerce_to_is_types
- Comparator
- comparator_factory
- type_engine
- load_dialect_impl
- process_literal_param
- literal_processor
- bind_processor
- result_processor
- bind_expression
- column_expression
- coerce_compared_value
- copy
- get_dbapi_type
- compare_values
- sort_key_function
- sqlalchemy.sql.base.SchemaEventTarget
- dispatch
- sqlalchemy.sql.type_api.TypeEngine
- render_bind_cast
- render_literal_cast
- hashable
- should_evaluate_none
- evaluates_none
- copy_value
- python_type
- with_variant
- as_generic
- dialect_impl
- adapt
- compile