tessl/pypi-sqlalchemy
Python SQL toolkit and Object Relational Mapper providing full power and flexibility of SQL
types.mddocs/
Type System and Data Types
SQL data type system including numeric, string, date/time, binary, and specialized types with database-specific variants and custom type creation. SQLAlchemy's type system provides database abstraction and Python value processing.
Capabilities
Numeric Types
Integer, floating-point, and precision numeric types for numerical data.
class Integer:
"""Integer type with platform-specific sizing."""
def __init__(self, **kwargs):
"""
Create integer type.
Parameters:
- autoincrement: bool, enable auto-increment
"""
class BigInteger(Integer):
"""Large integer type (BIGINT)."""
class SmallInteger(Integer):
"""Small integer type (SMALLINT)."""
class Numeric:
"""Fixed-precision numeric type (NUMERIC/DECIMAL)."""
def __init__(self, precision=None, scale=None, **kwargs):
"""
Create numeric type.
Parameters:
- precision: int, total number of digits
- scale: int, number of digits after decimal point
- asdecimal: bool, return Python Decimal (default True)
"""
class Float:
"""Floating-point type with optional precision."""
def __init__(self, precision=None, scale=None, **kwargs):
"""
Create float type.
Parameters:
- precision: int, precision specification
- scale: int, scale specification (some databases)
- asdecimal: bool, return Python Decimal (default False)
"""
class Double(Float):
"""Double-precision floating-point type."""
# Type constants for standard SQL types
INT: Integer
INTEGER: Integer
BIGINT: BigInteger
SMALLINT: SmallInteger
NUMERIC: Numeric
DECIMAL: Numeric
FLOAT: Float
REAL: Float
DOUBLE: Double
DOUBLE_PRECISION: DoubleString and Text Types
Variable and fixed-length string types with encoding support.
class String:
"""Variable-length string type (VARCHAR)."""
def __init__(self, length=None, **kwargs):
"""
Create string type.
Parameters:
- length: int, maximum string length
- collation: str, database collation
- convert_unicode: bool, handle unicode conversion
"""
class Text:
"""Large text type for long strings."""
def __init__(self, length=None, **kwargs):
"""
Create text type.
Parameters:
- length: int, maximum text length (if supported)
- collation: str, database collation
"""
class Unicode(String):
"""Unicode string type with automatic conversion."""
def __init__(self, length=None, **kwargs):
"""
Create unicode string type.
Parameters:
- length: int, maximum string length
- error: str, unicode error handling
"""
class UnicodeText(Text):
"""Unicode text type for large text with automatic conversion."""
# String type constants
VARCHAR: String
CHAR: String
TEXT: Text
CLOB: Text
NVARCHAR: Unicode
NCHAR: UnicodeDate and Time Types
Date, time, and timestamp types with timezone support.
class Date:
"""Date type for date-only values."""
def __init__(self, **kwargs):
"""Create date type."""
class Time:
"""Time type for time-only values."""
def __init__(self, timezone=False, **kwargs):
"""
Create time type.
Parameters:
- timezone: bool, include timezone information
"""
class DateTime:
"""Date and time type (DATETIME/TIMESTAMP)."""
def __init__(self, timezone=False, **kwargs):
"""
Create datetime type.
Parameters:
- timezone: bool, include timezone information
"""
class Interval:
"""Time interval type for durations."""
def __init__(self, native=True, **kwargs):
"""
Create interval type.
Parameters:
- native: bool, use native INTERVAL type if available
- second_precision: int, precision for seconds
- day_precision: int, precision for days
"""
# Date/time type constants
DATE: Date
TIME: Time
DATETIME: DateTime
TIMESTAMP: DateTimeBinary and LOB Types
Binary data and large object types for non-text data storage.
class LargeBinary:
"""Binary large object type (BLOB/BYTEA)."""
def __init__(self, length=None, **kwargs):
"""
Create binary type.
Parameters:
- length: int, maximum binary length
"""
class PickleType:
"""Type for storing Python objects via pickle serialization."""
def __init__(self, protocol=None, pickler=None, **kwargs):
"""
Create pickle type.
Parameters:
- protocol: int, pickle protocol version
- pickler: pickle module to use
- mutable: bool, track object mutations
"""
# Binary type constants
BINARY: LargeBinary
VARBINARY: LargeBinary
BLOB: LargeBinaryBoolean and Specialized Types
Boolean and other specialized data types.
class Boolean:
"""Boolean type with database-specific handling."""
def __init__(self, create_constraint=True, **kwargs):
"""
Create boolean type.
Parameters:
- create_constraint: bool, create CHECK constraint for emulation
- name: str, constraint name for emulation
"""
class Enum:
"""Enumeration type with constraint generation."""
def __init__(self, *enums, **kwargs):
"""
Create enum type.
Parameters:
- enums: enumeration values
- name: str, enum type name
- native_enum: bool, use native ENUM type
- create_constraint: bool, create CHECK constraint
- length: int, string length for VARCHAR fallback
"""
class JSON:
"""JSON data type with database-specific operators."""
def __init__(self, none_as_null=False, **kwargs):
"""
Create JSON type.
Parameters:
- none_as_null: bool, store None as SQL NULL vs JSON null
"""
class UUID:
"""UUID type with string or binary storage."""
def __init__(self, as_uuid=True, **kwargs):
"""
Create UUID type.
Parameters:
- as_uuid: bool, return Python UUID objects vs strings
"""
# Specialized type constants
BOOLEAN: BooleanArray and Composite Types
Array types and composite data structures.
class ARRAY:
"""Array type (PostgreSQL and others)."""
def __init__(self, item_type, **kwargs):
"""
Create array type.
Parameters:
- item_type: TypeEngine, array element type
- as_tuple: bool, return tuples instead of lists
- dimensions: int, number of array dimensions
- zero_indexes: bool, use zero-based indexing
"""
class TupleType:
"""Composite tuple type for multiple values."""
def __init__(self, *types, **kwargs):
"""
Create tuple type.
Parameters:
- types: TypeEngine objects for tuple elements
"""Type System Framework
Base classes and decorators for custom type implementation.
class TypeDecorator:
"""Base class for custom type implementations."""
impl: TypeEngine # Underlying type implementation
cache_ok: bool = True # Caching safety flag
def process_bind_param(self, value, dialect):
"""
Process Python value for database binding.
Parameters:
- value: Python value to process
- dialect: database dialect
Returns:
Any: Value for database binding
"""
def process_result_value(self, value, dialect):
"""
Process database value for Python use.
Parameters:
- value: database value to process
- dialect: database dialect
Returns:
Any: Python value for application use
"""
def copy(self, **kwargs):
"""
Create copy of type with modifications.
Parameters:
- kwargs: attributes to override
Returns:
TypeDecorator: Copied type instance
"""
class TypeEngine:
"""Base class for all SQL types."""
def bind_processor(self, dialect):
"""
Return function for processing bind parameters.
Parameters:
- dialect: database dialect
Returns:
callable or None: Processing function
"""
def result_processor(self, dialect, coltype):
"""
Return function for processing result values.
Parameters:
- dialect: database dialect
- coltype: column type from database
Returns:
callable or None: Processing function
"""
def compare_values(self, x, y):
"""
Compare two values for equality.
Parameters:
- x: first value
- y: second value
Returns:
bool: True if values are equal
"""
class UserDefinedType(TypeEngine):
"""Base for user-defined custom types."""
def get_col_spec(self, **kwargs):
"""
Return database column specification.
Returns:
str: Database-specific column type specification
"""Type Coercion and Casting
Type coercion utilities for expression handling.
def type_coerce(expr, type_):
"""
Coerce expression to specific type without casting.
Parameters:
- expr: expression to coerce
- type_: target type
Returns:
TypeCoerce: Type-coerced expression
"""
class TypeCoerce:
"""Expression with type coercion applied."""
def __init__(self, expr, type_):
"""
Create type coercion.
Parameters:
- expr: expression to coerce
- type_: target type
"""Usage Examples
Basic Type Usage
from sqlalchemy import Table, Column, Integer, String, DateTime, Boolean
from sqlalchemy import MetaData, create_engine
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(100), nullable=False),
Column('email', String(255), unique=True),
Column('is_active', Boolean, default=True),
Column('created_at', DateTime, server_default=func.now())
)Precision Numeric Types
from sqlalchemy import Numeric, Float
products = Table('products', metadata,
Column('id', Integer, primary_key=True),
Column('price', Numeric(10, 2)), # 10 digits, 2 decimal places
Column('weight', Float(precision=24)), # Single precision
Column('rating', Numeric(3, 2)) # 0.00 to 9.99
)Custom Type Example
from sqlalchemy import TypeDecorator, String
import json
class JSONType(TypeDecorator):
"""Custom JSON type using string storage."""
impl = String
cache_ok = True
def process_bind_param(self, value, dialect):
if value is not None:
return json.dumps(value)
return None
def process_result_value(self, value, dialect):
if value is not None:
return json.loads(value)
return None
# Usage
settings = Table('settings', metadata,
Column('id', Integer, primary_key=True),
Column('config', JSONType())
)Array and Specialized Types
from sqlalchemy.dialects.postgresql import ARRAY, UUID as PG_UUID
from sqlalchemy import JSON
# PostgreSQL-specific types
logs = Table('logs', metadata,
Column('id', PG_UUID, primary_key=True),
Column('tags', ARRAY(String(50))),
Column('metadata', JSON),
Column('levels', ARRAY(Integer, dimensions=2)) # 2D array
)
# Cross-database JSON
documents = Table('documents', metadata,
Column('id', Integer, primary_key=True),
Column('data', JSON) # Works with PostgreSQL, MySQL 5.7+, SQLite 3.38+
)Enum Type Usage
from sqlalchemy import Enum
# String-based enum
status_enum = Enum('pending', 'processing', 'completed', 'failed',
name='order_status')
orders = Table('orders', metadata,
Column('id', Integer, primary_key=True),
Column('status', status_enum, default='pending')
)
# Python enum integration
from enum import Enum as PyEnum
class OrderStatus(PyEnum):
PENDING = 'pending'
PROCESSING = 'processing'
COMPLETED = 'completed'
FAILED = 'failed'
orders_v2 = Table('orders_v2', metadata,
Column('id', Integer, primary_key=True),
Column('status', Enum(OrderStatus), default=OrderStatus.PENDING)
)Install with Tessl CLI
npx tessl i tessl/pypi-sqlalchemy