SQLAlchemy Data Types
Explore how to map Python types to SQL data types using SQLAlchemy, understand type differences across databases, and apply best practices for defining data columns. Learn to handle common types like Integer, String with length constraints, Boolean, Enum, and specialized types for serialized data to build robust and portable database schemas.
We'll cover the following...
Python types and their mapping
Each type defined in the example below corresponds to a common SQL data type, but the exact mapping may vary from database to database since they don’t all support the same types. Here are some of the more common types and how they map from Python types to SQLAlchemy types to SQL types.
Python | SQLAlchemy | SQL |
bool | Boolean | BOOLEAN or SMALLINT |
datetime.date | Date | DATE or similar |
datetime.datetime | DateTime | TIMESTAMP or similar |
datetime.timedelta | Interval | INTERVAL, or DATE diff with epoch |
enum.Enum | Enum | VARCHAR with CHECK constraint |
float | Float | FLOAT or REAL |
decimal.Decimal | Numeric | NUMERIC or DECIMAL |
int | Integer | INTEGER |
nested dicts & list | JSON | JSON |
str | String | VARCHAR |
Specific types for specific databases
SQLite uses a string for timestamps, ...