🦔

【SQLAlchemy】Generic Typesと各種DBの型 対応表

2022/02/28に公開

はじめに

PythonDBを使いたいといったとき、よく利用するのがSQLAlchemy

モデルクラスと、テーブル定義をマッピングさせるときに型定義を以下のような感じで行う。

from sqlalchemy.schema import Column
from sqlalchemy.types import Integer, String

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key = True)
    name = Column(String(200))
    age = Column(Integer)
    email = Column(String(100))

この型定義は、DBの種類にかかわらず共通で使えて汎用的に作れます。(Generic Types)

ただ、実際どうテーブルの型定義に変換されるの?引数省略できる場合あるけど、その場合桁数とかはどうなってるの?
とモヤモヤしてたので、対応表を作成してみた。

↓各種DBに対してcreate table実行したソース

検証ソース
import enum

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column

from sqlalchemy.types import BigInteger, Boolean, Date, DateTime, Enum
from sqlalchemy.types import Float, Integer, Interval, LargeBinary
from sqlalchemy.types import MatchType, Numeric, PickleType
from sqlalchemy.types import SmallInteger, String, Text, Time, Unicode, UnicodeText

# mysqlセッション
# host = "localhost"
# user = "hoge"
# password = "passw0rd"
# database = "hogedb"
# # pip install mysqlclient
# engine = create_engine(f'mysql://{user}:{password}@{host}/{database}?charset=utf8', echo=True)

# # postgresql セッション
# host = "localhost"
# user = "hoge"
# password = "passw0rd"
# database = "hogedb"
# schema = "hogeschema"
# # pip install psycopg2
# engine = create_engine(f'postgresql://{user}:{password}@{host}/{database}?options=-csearch_path%3D{schema}', echo=True)

# # oracle セッション
# host = "localhost"
# user = "hogeuser"
# password = "passw0rd"
# service = "pdb01"
# # pip install cx_Oracle
# # https://rainbow-engine.com/sqlalchemy-error-troubleshoot/
# engine = create_engine(f'oracle://{user}:{password}@{host}:1521/?service_name={service}', echo=True)

# # SQLServer セッション
# host = "localhost"
# user = "sa"
# password = "Pass0wrd12345"
# database = "master"
# # pip install pymssql
# engine = create_engine(f'mssql+pymssql://{user}:{password}@{host}:1433/{database}', echo=True)

# SQLite セッション
file_path = r"C:\work\database.db"
# pip install pymssql
engine = create_engine(f'sqlite:///{file_path}', echo=True)

session = scoped_session(
    sessionmaker(
        autocommit=False,
        autoflush=False,
        bind=engine,
    )
)

Base = declarative_base()


class Color(enum.Enum):
    RED = 1
    GREEN = 2
    BLUE = 3


class ColumnSample(Base):
    __tablename__ = 'column_sample'
    id = Column(Integer, primary_key=True)
    col_big_integer = Column(BigInteger)
    col_boolean = Column(Boolean)
    col_date = Column(Date)
    col_date_time = Column(DateTime)
    col_enum = Column(Enum(Color))
    col_float_default = Column(Float)
    col_float = Column(Float(5, False, 3))
    col_integer = Column(Integer)
    col_interval = Column(Interval)
    col_large_binary_default = Column(LargeBinary)
    col_large_binary1 = Column(LargeBinary(255))
    col_large_binary2 = Column(LargeBinary(256))
    col_match_type = Column(MatchType)
    col_numeric_default = Column(Numeric)
    col_numeric = Column(Numeric(5, 3))
    col_pickle_type = Column(PickleType)
    col_small_integer = Column(SmallInteger)
    col_string_default = Column(String)  # mysql、oracleの場合は、length指定必須
    col_string = Column(String(100))
    col_text_default = Column(Text)
    col_text = Column(Text(100))  # postgresqlの場合は、length指定できない
    col_time = Column(Time)  # oracleの場合は、指定できない
    col_unicode_default = Column(Unicode)  # mysql、oracleの場合は、length指定必須
    col_unicode = Column(Unicode(100))
    col_unicode_text_default = Column(UnicodeText)
    col_unicode_text = Column(UnicodeText(100))  # postgresqlの場合は、length指定出来ない


if __name__ == '__main__':
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)

    # col = ColumnSample()
    # col.id = 1
    # session.add(col)
    # session.commit()

一覧

Generic Types MySQL Postgres Oracle SQLServer SQLite
BigInteger bigint bigint NUMBER(19,0) bigint BIGINT
Boolean tinyint(1) boolean NUMBER(*,0) bit BOOLEAN
Date date date DATE date DATE
DateTime datetime timestamp(6) without time zone DATE datetime DATETIME
Enum enum(…) color VARCHAR2(5 CHAR) varchar(5) VARCHAR (5)
Float(引数なし) float double precision FLOAT(126) float(53) FLOAT
Float(5, False, 3) float real FLOAT(126) real FLOAT
Integer int integer NUMBER(*,0) int INTEGER
Interval datetime interval INTERVAL DAY (2) TO SECOND (6) datetime DATETIME
LargeBinary(引数なし) blob bytea BLOB varbinary(max) BLOB
LargeBinary(255) tinyblob bytea BLOB varbinary(255) BLOB
LargeBinary(256) blob bytea BLOB varbinary(256) BLOB
MatchType tinyint(1) boolean NUMBER(*,0) bit BOOLEAN
Numeric(引数なし) decimal(10,0) numeric NUMBER(*,0) numeric(18, 0) NUMERIC
Numeric(5,3) decimal(5,3) numeric(5, 3) NUMBER(5,3) numeric(5, 3) NUMERIC (5, 3)
PickleType blob bytea BLOB varbinary(max) BLOB
SmallInteger smallint smallint NUMBER(*,0) smallint SMALLINT
String(引数なし) × character varying × varchar(max) VARCHAR
String(100) varchar(100) character varying(100) VARCHAR2(100 CHAR) varchar(100) VARCHAR (100)
Text(引数なし) text text CLOB varchar(max) TEXT
Text(100) text × CLOB varchar(100) TEXT(100)
Time time time without time zone × time TIME
Unicode(引数なし) × character varying × nvarchar(max) VARCHAR
Unicode(100) varchar(100) character varying(100) VARCHAR2(100 CHAR) nvarchar(100) VARCHAR (100)
UnicodeText(引数なし) text text CLOB nvarchar(max) TEXT
UnicodeText text × CLOB nvarchar(100) TEXT(100)

まとめ

実際に動作させてみると、ちょくちょく動かない型もあり、動くけどドキュメントを確認すると注意書きがあって、一部DBで望ましくない動作が~って表記もある。
汎用とはいいつつ気を付けたほうがよさそう。

  • 文字列(桁数がある程度決まっている)
    ⇒ String(length)
  • 文字列(桁数がかなり長くなりそうだったら)
    ⇒ Text
  • 整数
    ⇒ Numeric(precision) or Integer or BigInteger
  • 小数
    ⇒ Numeric(precision, scale)
  • バイナリデータ
    ⇒ LargeBinary
  • 日付
    ⇒ Date
  • 日時
    ⇒ DateTime
  • シリアライズデータ
    ⇒ PickleType

ざっくりどれ使えばいいかは、上のような感じだと思う。
あとは必要に応じて適宜採用かな。

さいごに

Generic Typesを使えば、DBの種類によらず汎用的にコードを書ける。

これとは別に、DBの種類ごとに合わせて型定義ができるDialectsとういのもある。

上の表はデータ型に対して何を利用すればよいかの目安になればいいかなと思います。
利用DBが決まっていて、DB設計もしっかり固めてからって場合はDialectsのほうが合ってそう。

一度稼働し始めたシステムでDBの種類を変更するようなことってあまりない気もするので、Dialectsでもいいのかな…って気がしてる

Discussion

ログインするとコメントできます