Types¶

Overview¶

mapped_column() derives the datatype and nullability from the Mapped annotation The two qualities that mapped_column() derives from the Mapped annotation are:

datatype - the Python type given inside Mapped, as contained within the typing.Optional construct if present, is associated with a TypeEngine subclass such as Integer, String, DateTime, or Uuid, to name a few common types.

The datatype is determined based on a dictionary of Python type to SQLAlchemy datatype. This dictionary is completely customizable, as detailed in the next section Customizing the Type Map. The default type map is implemented as in the code example below:

from typing import Any
from typing import Dict
from typing import Type

import datetime
import decimal
import uuid

from sqlalchemy import types

# default type mapping, deriving the type for mapped_column()
# from a Mapped[] annotation
type_map: Dict[Type[Any], TypeEngine[Any]] = {
    bool: types.Boolean(),
    bytes: types.LargeBinary(),
    datetime.date: types.Date(),
    datetime.datetime: types.DateTime(),
    datetime.time: types.Time(),
    datetime.timedelta: types.Interval(),
    decimal.Decimal: types.Numeric(),
    float: types.Float(),
    int: types.Integer(),
    str: types.String(),
    uuid.UUID: types.Uuid(),
}

References¶

https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html

Custom Types Map

import datetime

from sqlalchemy import BIGINT, Integer, NVARCHAR, String, TIMESTAMP
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped, mapped_column, registry

class Base(DeclarativeBase):
type_annotation_map = {
int: BIGINT,
datetime.datetime: TIMESTAMP(timezone=True),
str: String().with_variant(NVARCHAR, "mssql"),
}

class SomeClass(Base):
tablename = "some_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    date: Mapped[datetime.datetime]
    status: Mapped[str]

Below illustrates the CREATE TABLE statement generated for the above mapping, first on the Microsoft SQL Server backend, illustrating the NVARCHAR datatype:

from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects import mssql, postgresql
print(CreateTable(SomeClass.**table**).compile(dialect=mssql.dialect()))

CREATE TABLE some_table (
  id BIGINT NOT NULL IDENTITY,
  date TIMESTAMP NOT NULL,
  status NVARCHAR(max) NOT NULL,
  PRIMARY KEY (id)
  )