SQLAlchemy 1.4 Documentation

SQLAlchemy 시작하기 - Part 2

Install

pip install sqlalchmey

Normal Usage

Declarative Mapping

Declarative Mapping is the typical way that mappings are constructed in modern SQLAlchemy

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base

# declarative base class
Base = declarative_base()

# an example mapping using the base
class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

A new class called User will be the class to which we map this table. Within the class, we define details about the table to which we’ll be mapping, primarily the table name, and names and datatypes of columns:

A class using Declarative at a minimum needs a tablename attribute, and at least one Column which is part of a primary key

Enum type (Choice)

String Enum

from sqlalchemy import Column, Integer, String, Enum
import enum

class MyitineraryStatusChoices(enum.Enum):
    CUR = 'CUR', 'Current'
    EXP = "EXP", 'Expired'
    UPC = "UPC", 'Up-Coming'

class MyItinerary(AbstractBase, Base):
    __tablename__ = "flanb_my_itinerary"

    id = Column(Integer, primary_key=True, autoincrement=True)
    country_id = Column(Integer, nullable=True)
    status = Column(Enum(MyitineraryStatusChoices, values_callable=lambda obj: [e.value[0] for e in obj],
                            impl=String(6)), nullable=False, default=MyitineraryStatusChoices.UPC)

Int Enum

from sqlalchemy import Column, Integer, String, Enum
import enum

class IntEnum(types.TypeDecorator):
    impl = Integer()

    def __init__(self, enumtype, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self._enumtype = enumtype

class SourceChoices(enum.Enum):
    USER = 0
    AI = 1

class Message(Base):
    __tablename__ = "nchatbot_messages"

    id = Column(Integer, primary_key=True, autoincrement=True)
    source = Column('source', IntEnum(SourceChoices), nullable=False,
                         default=SourceChoices.USER.value)

Abstract Model

class AbstractBase(Base):
    __abstract__ = True

    created_user = Column(sa.String(150), nullable=False)
    last_modified_user = Column(sa.String(150), nullable=False)