SQLAlchemyでデータベース定義を楽したい
花粉対策のマスクを忘れるという大失態で目と鼻が大変なことになりがちなKibaraです。
今回は、SQLAlchemyのMetadataを活用してみます。
前回、SQLAlchemyの基本操作を学習した記事はこちら。
www.sw-mono.blog
事前準備
以下のように環境を準備しています。
データベース
> psql --version psql (PostgreSQL) 11.1 > \d users テーブル "public.users" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト ------+----------+----------+---------------+------------ id | smallint | | not null | name | text | | | age | smallint | | | インデックス: "users_pkey" PRIMARY KEY, btree (id) localdb=# select * from users; id | name | age ----+--------+----- 1 | aoki | 27 2 | iida | 31 3 | usui | 18 4 | endou | 24 5 | oshima | 46 (5 行)
Python
SQLAlchemyとPsycopg2をインストールします。
> conda install sqlalchemy psycopg2 > conda list psycopg2 psycopg2 2.7.6.1 py37h7a1dbc1_0 > conda list sqlalchemy sqlalchemy 1.2.15 py37he774522_0
Metadataって何ですか
ドキュメントには以下のように書かれています。
MetaData is a container object that keeps together many different features of a database (or multiple databases) being described.
Describing Databases with MetaData
MetaDataは、記述されているデータベースにの様々な情報を、
まとめて保持するコンテナオブジェクトです。
なるほど。データベースのメタなデータね。
実際の中身を見てみたい！
engine = create_engine(
'postgresql://[user]:[pass]@[ip]:[port]/[db]',
echo=True)
metadata = MetaData(engine)
print(vars(metadata))
このようにすると……
{
'tables': immutabledict({}),
'schema': None,
'naming_convention': immutabledict({'ix': 'ix_%(column_0_label)s'}),
'_schemas': set(),
'_sequences': {},
'_fk_memos': defaultdict(<class 'list'>, {}),
'_bind': Engine(postgresql://***:***@***:***/***)
}
辞書型で出力できます。
データベースの接続情報やテーブル、スキーマ等の情報を持っているようです。
テーブルの情報を持たせる
Pythonで記述したテーブル定義をMetadataに持たせてみます。
Base = declarative_base() metadata = MetaData(engine) class Users(Base): __table__ = Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String(50)), Column('age', Integer)) print(vars(metadata))
これを実行すると、以下のような出力が得られます。 テーブル定義がちゃんと入ってますね、
{
'tables': immutabledict(
{
'users': Table('users',
MetaData(bind=Engine(postgresql://***:***@***:***/***)),
Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
Column('name', String(length=50), table=<users>),
Column('age', Integer(), table=<users>),
schema=None)
}
(略)
この情報を使ってmetadata.create_all()とすることで、
データベースにテーブルを作成することができます。
データベースからメタ情報を取得する
ここまでの内容だとだから何だと言われそうですが、
データベースのテーブル定義を取得してmetadataに格納することができます。
metadata = MetaData(engine) metadata.reflect() print(vars(metadata))
データベースの接続情報を紐づけたMetadataに対して、
reflect()メソッドを実行しました。
この操作によって、データベースの定義がロードされます。
{
'tables': immutabledict({
'users': Table('users', MetaData(bind=Engine(postgresql://***:***@***:***/***)),
Column('id', SMALLINT(), table=<users>, primary_key=True, nullable=False),
Column('name', TEXT(), table=<users>),
Column('age', SMALLINT(), table=<users>),
schema=None
)}
(略)
テーブル定義を楽する
ここからが本題です。
テーブル定義のベースとなるdeclarative_baseですが、
実はこれもメタデータを持っています。
Base = declarative_base() print(vars(Base.metadata))
このBase.metadataにreflect()したメタデータを持たせることで、
テーブル定義が劇的に楽になります！
通常の定義だと、Columnでカラム名や型、制約を記述する必要がありました。
3カラムぐらいであれば良いですが、大規模なデータベースだと気が滅入るほど書かなければなりません。
# 通常の定義 Base = declarative_base() class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True), name = Column(String(50)), age = Column(Integer))
しかしreflect()を利用することで、テーブル名さえ記述すればよくなります。
# メタデータを使った定義 meta = Metadata(engine) meta.reflect() Base = declarative_base(metadata=meta) class Users(Base): __tablename__ = 'users'
注意点
ただし、この方法にはいくつか注意する点があります。
遅い
engineの設定でSQL文をコンソール出力するようにしてreflect()するとわかりますが、
相当な回数のSQL文を発行しています。
データベースの規模が小さい場合は気になりませんが、
大規模な構成になると数十秒掛かる場合もあるようです。
常駐型のプログラムで起動時にreflect()しておけば良いなら問題にはならないですが、
ジョブスケジューラ等で起動->終了を繰り返すようなスクリプトだと、
速度面で厳しい場合もあります。
主キーが必須
テーブル定義には主キーの設定が必須です。
例えば、以下の例はエラーとなります。
# primary_key の指定がないのでエラーとなる class Users(Base): __tablename__ = 'users' id = Column(Integer) name = Column(String(50)) age = Column(Integer) # sqlalchemy.exc.ArgumentError: Mapper Mapper|Users|users could not assemble # any primary key columns for mapped table 'users'
しかし、データベース側のテーブル定義は主キーを必須としません。
reflect()で取得したテーブル定義に主キーがない場合、
同様のエラーが発生してしまいます。
回避策として、カラムをオーバーライドする方法があります。
主キーには設定されていないがデータの重複がなく、主キーとして使えるカラムがあれば、
以下のようにそのカラムだけ定義することで、回避が可能です。
class Users(Base): __table__ = Table('users', metadata, Column('id', Integer, primary_key=True), autoload=True, extend_existing=True)
extend_existing=Trueのオプションで、カラムのオーバーライドを許可しています。
この回避策はドキュメントでも紹介されています。
Overriding Reflected Columns
まとめ
今回はデータベースからテーブル定義を取得して、コーディングを楽にするTipsを紹介しました。
遅い、主キー必須という制約はあるものの、条件さえ合えば便利な機能ではないでしょうか。
積極的に利用したいと思った機能でした。
以上です。
