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

はじめに

PythonでDBを使いたいといったとき、よく利用するのが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でもいいのかな…って気がしてる