Primitive Data Types in SQLAlchemy

Introduction

Understanding primitive data types is crucial when defining models in SQLAlchemy, the Python SQL toolkit, and ORM. This tutorial provides a guide on using built-in primitive types effectively in your SQLAlchemy models.

Basic Types in SQLAlchemy

SQLAlchemy supports a comprehensive set of primitive data types that align with SQL database types. Each type in SQLAlchemy provides a Python equivalent for various SQL types.

Integer

from sqlalchemy import create_engine, Integer, Column, MetaData, Table

engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
table = Table('example', metadata,
               Column('id', Integer, primary_key=True)
)
metadata.create_all(engine)

String

from sqlalchemy import String

table.append_column(Column('name', String(50)))  # Appending new column to the 'table'
metadata.create_all(engine)

Numeric Types

from sqlalchemy import Numeric, Float

table.append_column(Column('price', Numeric(10, 2)))
table.append_column(Column('quantity', Float))
metadata.create_all(engine)

Using Type Decorators

Type decorators in SQLAlchemy allow for the extension and customization of existing types. Let’s see how we can create a custom JSON-encoded type using a type decorator.

from sqlalchemy.types import TypeDecorator, VARCHAR
import json

class JSONEncodedDict(TypeDecorator):
    """Enables JSON storage by encoding and decoding on the fly."""
    impl = VARCHAR

    def process_bind_param(self, value, dialect):
        if value is not None:
            value = json.dumps(value)
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            value = json.loads(value)
        return value

table.append_column(Column('attributes', JSONEncodedDict(255)))
metadata.create_all(engine)

Advanced Data Types

SQLAlchemy’s advanced data types include Enums, Arrays, and more. Here we will delve into defining Enum and Array types in SQLAlchemy.

Enum Type

import enum
from sqlalchemy import Enum

class MyEnum(enum.Enum):
    foo = 1
    bar = 2
    baz = 3

table.append_column(Column('status', Enum(MyEnum)))
metadata.create_all(engine)

Array Type

# PostgreSQL specific example
from sqlalchemy.dialects.postgresql import ARRAY

table.append_column(Column('data_points', ARRAY(Integer)))
metadata.create_all(engine)

Conclusion

In this tutorial, we explored some of the basic and advanced primitive data types available in SQLAlchemy. With examples from Integer to custom type decorators, this guide should help you build more robust models for your applications using SQLAlchemy.