sqlalchemy helpers

SUOU provides several helpers to make sqlalchemy learning curve less steep.

In fact, there are pre-made column presets for a specific purpose.

Columns

suou.sqlalchemy.id_column(typ: SiqType, *, primary_key: bool = True, **kwargs)

Marks a column which contains a SIQ.

Warning

id_column() expects SIQ’s!

suou.sqlalchemy.snowflake_column(*, primary_key: bool = True, **kwargs)

Same as id_column() but with snowflakes.

XXX this is meant ONLY as means of transition; for new stuff, use id_column() and SIQ.

suou.sqlalchemy.match_column(length: int, regex: str | Pattern, /, case: StringCase = StringCase.AS_IS, *args, constraint_name: str | None = None, **kwargs) → Incomplete[Column[str]]

Syntactic sugar to create a String() column with a check constraint matching the given regular expression.

TODO application side validation

suou.sqlalchemy.secret_column(length: int = 64, max_length: int | None = None, gen: ~typing.Callable[[int], bytes] = <built-in function urandom>, nullable=False, **kwargs)

Column filled in by default with random bits (64 by default). Useful for secrets.

New in 0.6.0

suou.sqlalchemy.bool_column(value: bool = False, nullable: bool = False, **kwargs) → Column[bool]

Column for a single boolean value.

New in 0.4.0

suou.sqlalchemy.username_column(length: int = 32, regex: str | Pattern = '[a-z_][a-z0-9_-]+', *args, case: StringCase = StringCase.LOWER, nullable: bool = False, **kwargs) → Incomplete[Column[str] | Column[str | None]]

Construct a column containing a unique handle / username.

Username must match the given regex and be at most length characters long.

New in 0.8.0

suou.sqlalchemy.unbound_fk(target: str | Column | InstrumentedAttribute, typ: _T | None = None, **kwargs) → Column[_T | LargeBinary(length=16)]

Shorthand for creating a “unbound” foreign key column from a column name, the referenced column.

“Unbound” foreign keys are nullable and set to null when referenced object is deleted.

If target is a string, make sure to pass the column type at typ= (default: IdType aka varbinary(16))!

New in 0.5.0

suou.sqlalchemy.bound_fk(target: str | Column | InstrumentedAttribute, typ: _T = None, **kwargs) → Column[_T | LargeBinary(length=16)]

Shorthand for creating a “bound” foreign key column from a column name, the referenced column.

“Bound” foreign keys are not nullable and cascade when referenced object is deleted. It means, parent deleted -> all children deleted.

If target is a string, make sure to pass the column type at typ= (default: IdType aka varbinary(16))!

New in 0.5.0

Column pairs

suou.sqlalchemy.age_pair(*, nullable: bool = False, **ka) → tuple[Column, Column]

Return a SIS-compliant age representation, i.e. a date and accuracy pair.

Accuracy is represented by a small integer: 0 = exact 1 = month and day 2 = year and month 3 = year 4 = estimated year

suou.sqlalchemy.author_pair(fk_name: str, *, id_type: type | TypeEngine = LargeBinary(length=16), sig_type: type | None = None, nullable: bool = False, sig_length: int | None = 2048, **ka) → tuple[Column, Column]

Return an owner ID/signature column pair, for authenticated values.

suou.sqlalchemy.parent_children(keyword: str, /, *, lazy='selectin', **kwargs) → tuple[Incomplete[Relationship[Any]], Incomplete[Relationship[Any]]]

Self-referential one-to-many relationship pair. Parent comes first, children come later.

keyword is used in back_populates column names: convention over configuration. Naming it otherwise will BREAK your models.

Additional keyword arguments can be sourced with parent_ and child_ argument prefixes, obviously.

Changed in 0.5.0: the both relationship()s use lazy=’selectin’ attribute now by default.

Misc

class suou.sqlalchemy.BitSelector(column, flag: int)

“Virtual” column representing a single bit in an integer column (usually a BigInteger).

Mimicks peewee’s ‘BitField()’ behavior, with SQLAlchemy.

New in 0.6.0

suou.sqlalchemy.match_constraint(col_name: str, regex: str, /, dialect: str = 'default', constraint_name: str | None = None) → CheckConstraint

Shorthand for a check constraint. Several dialects are supported.

suou.sqlalchemy.a_relationship(primary=None, /, j=None, *, lazy='selectin', **kwargs)

Shorthand for relationship() that sets lazy=’selectin’ by default.

New in 0.6.0

suou.sqlalchemy.declarative_base(domain_name: str, master_secret: bytes, metadata: dict | None = None, **kwargs) → type[DeclarativeBase]

Drop-in replacement for sqlalchemy.orm.declarative_base() taking in account requirements for SIQ generation (i.e. domain name).

suou.sqlalchemy.want_column(cls: type[DeclarativeBase], col: Column[_T] | str) → Column[_T]

Return a table’s column given its name.

XXX does it belong outside any scopes?