SQLAlchemy Datatypes

SQLAlchemy provides a variety of field types that can be used to define the structure of your database tables. Here are some of the most commonly used ones:

  1. Integer: This type represents an integer and translates to INT in SQL. Example: db.Column(db.Integer)

  2. Float: This type represents a floating point number and translates to FLOAT in SQL. Example: db.Column(db.Float)

  3. Boolean: This type represents a boolean (True or False) value and translates to BOOLEAN in SQL. Example: db.Column(db.Boolean)

  4. String(size): This type represents a string with a maximum length. It translates to VARCHAR(size) in SQL. Example: db.Column(db.String(50))

  5. Text: This type represents a variable length string, without a maximum length. It translates to TEXT in SQL. Example: db.Column(db.Text)

  6. DateTime: This type represents a date and time. It translates to DATETIME in SQL. Example: db.Column(db.DateTime)

  7. Date: This type represents a date. It translates to DATE in SQL. Example: db.Column(db.Date)

  8. Time: This type represents a time. It translates to TIME in SQL. Example: db.Column(db.Time)

  9. Enum: This type represents a list of string-based options. It translates to ENUM in SQL. Example: db.Column(db.Enum('option1', 'option2'))

  10. Binary: This type represents a binary blob. It translates to BLOB in SQL. Example: db.Column(db.Binary)

  11. PickleType: This type represents a Python pickle object. It translates to BLOB in SQL. Example: db.Column(db.PickleType)

  12. LargeBinary: This type represents a binary blob, with size in bytes. It translates to BLOB in SQL. Example: db.Column(db.LargeBinary)

  13. Numeric: This type represents a numeric column for precise values, like monetary values. You can specify the precision and scale. Example: db.Column(db.Numeric(10, 2))

These are just a few of the available types. Each type can also take additional arguments to add constraints or alter behavior. For example, you can make a column non-nullable (i.e., it must have a value) by passing nullable=False.

Remember that the types are translated into SQL types according to the dialect of the database you’re using, so there may be slight variations depending on whether you’re using MySQL, PostgreSQL, SQLite, etc.

Absolutely! Let’s create a model for a fictional “Product” table for an e-commerce application. This will allow us to demonstrate a variety of data types in a practical context:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Enum
import enum

db = SQLAlchemy()

class ProductCategory(enum.Enum):
    ELECTRONICS = 1
    CLOTHING = 2
    HOME = 3
    BOOKS = 4
    SPORTS = 5

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)
    is_available = db.Column(db.Boolean, default=True)
    added_on = db.Column(db.DateTime, default=db.func.current_timestamp())
    category = db.Column(db.Enum(ProductCategory), nullable=False)
    image = db.Column(db.LargeBinary)

    def __repr__(self):
        return f'<Product {self.name}>'

In this example:

  1. id is an integer column used as the primary key.
  2. name is a string column with a maximum length of 100 characters. It is a required field (i.e., nullable=False).
  3. description is a text column, which can store a longer string compared to the String type.
  4. price is a numeric column, which is ideal for storing precise decimal values like monetary amounts. It is a required field.
  5. quantity is an integer column, also a required field.
  6. is_available is a boolean column with a default value of True.
  7. added_on is a datetime column that defaults to the current timestamp when a product is added.
  8. category is an enum column, which means it can only take one of a limited set of values defined in the ProductCategory enum. It’s also a required field.
  9. image is a large binary column, suitable for storing binary data like image files.

This Product model represents a table in the SQL database, where each column of the table is represented by an instance variable. The type of each column is determined by the SQLAlchemy type given as the first argument to db.Column. Each instance of the Product class corresponds to a row in the Product table.