Skip to content

Reference

dsg_lib.async_database_functions.base_schema

This module defines the base schema for database models in the application.

The module uses SQLAlchemy as the ORM and provides a SchemaBase mixin class per supported database dialect. All models should inherit from one of these classes alongside the declarative Base. Each mixin provides three common columns:

  • pkid: A unique identifier for each record. It's a string representation of a UUID.
  • date_created: The date and time when a particular row was inserted into the table. Defaults to the current UTC time when the row is inserted.
  • date_updated: The date and time when a particular row was last updated. Defaults to the current UTC time whenever the row is updated.

SchemaBaseSQLite sets these timestamps in Python (via default/onupdate callables) since SQLite has no reliable server-side UTC timestamp function. Every other dialect (SchemaBasePostgres, SchemaBaseMySQL, SchemaBaseOracle, SchemaBaseMSSQL, SchemaBaseFirebird, SchemaBaseSybase, SchemaBaseCockroachDB) sets them via a dialect-specific server-side default expression, built by the _build_server_default_schema_base factory below so the column definitions aren't repeated per dialect.

Example: 

from dsg_lib.async_database_functions import base_schema

class MyModel(base_schema.SchemaBaseSQLite):
        # Define your model-specific columns here
        my_column = base_schema.Column(base_schema.String(50))

Author: Mike Ryan Date: 2024/05/16 License: MIT

SchemaBaseSQLite

Schema-base mixin for SQLite models.

SQLite has no reliable server-side UTC timestamp function, so date_created and date_updated are stamped in Python at insert/update time instead of via a server-side default.

Example: 

from dsg_lib.async_database_functions import base_schema
from sqlalchemy.orm import declarative_base

BASE = declarative_base()

class MyModel(base_schema.SchemaBaseSQLite, BASE):
    my_column = base_schema.Column(base_schema.String(50))

Source code in dsg_lib/async_database_functions/base_schema.py 
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
class SchemaBaseSQLite:
    """
    Schema-base mixin for SQLite models.

    SQLite has no reliable server-side UTC timestamp function, so `date_created`
    and `date_updated` are stamped in Python at insert/update time instead of via
    a server-side default.

    Example:
    ```python
    from dsg_lib.async_database_functions import base_schema
    from sqlalchemy.orm import declarative_base

    BASE = declarative_base()

    class MyModel(base_schema.SchemaBaseSQLite, BASE):
        my_column = base_schema.Column(base_schema.String(50))
    ```
    """

    pkid = _build_pkid_column()

    date_created = Column(
        DateTime,
        index=True,
        default=get_utc_now,
        comment=date_created_comment,
    )

    date_updated = Column(
        DateTime,
        index=True,
        default=get_utc_now,
        onupdate=get_utc_now,
        comment=date_updated_comment,
    )

get_utc_now()

Get current UTC datetime using the appropriate method based on Python version.

Python 3.11+ uses datetime.UTC, while earlier versions use datetime.timezone.utc. This prevents deprecation warnings in newer Python versions.

Returns:

Type Description

datetime.datetime: Current UTC datetime
Source code in dsg_lib/async_database_functions/base_schema.py 
47
48
49
50
51
52
53
54
55
56
57
58
59
60
def get_utc_now():
    """
    Get current UTC datetime using the appropriate method based on Python version.

    Python 3.11+ uses datetime.UTC, while earlier versions use datetime.timezone.utc.
    This prevents deprecation warnings in newer Python versions.

    Returns:
        datetime.datetime: Current UTC datetime
    """
    if sys.version_info >= (3, 11):
        return datetime.datetime.now(datetime.UTC)
    else:
        return datetime.datetime.now(datetime.timezone.utc)