SQLAlchemyを組み込む step by step

やることは

• データベースへの接続
• テーブルの定義
• マッピング先のPythonクラスを定義する
• セッションの作成

という感じ。

まずはどのサイトでも書かれているimport
しかしサイトごとに書き方が色々で、最もざっくりしたものだと

from sqlalchemy import *
from sqlalchemy.orm import *

とか。
step by stepで都度必要なものを確認したいので、これは避けたい。

importのあとにまず行うのはデータベースへの接続なようなので、（テーブルを先に定義していることもある）

from sqlalchemy import create_engine

これを__init__pyに記述することにする。

データベースへの接続はざっくり型だと

engine = create_engine('sqlite:///test.db', echo=True)

こんな感じで、丁寧型(この例はpostgresql)だと

url = 'postgresql+psycopg2://pgadmin:password@192.168.1.101:5432/sample'
engine = create_engine(url, echo=True)

こんな感じ。
私の場合、すでにconfg.pyに

SQLALCHEMY_DATABASE_URI = 'sqlite:///flask.data'

こう書いて、__init__pyで

app.config.from_object('app.config')

これで読み込んでいるので

engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'], echo=True)

としておくのでいいと思う。
次にテーブルの定義だが、多くの場合同時にクラスマッピングもやるスタイルになっているようだ。
まず declarative_base() 関数 を使って Base というクラスを生成する必要がある。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(app)

これも__init__.pyに書いておこう。

さて、テーブルの定義の方はmodels.pyに書いてあるのでこちらを編集する。
__init__.pyに書いたBaseを読み込むため、

from app import Base

これまでのFlask-SQLAlchemyの書式では

class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship('User', backref='role', lazy='dynamic')

    def __init__(self, name, users):
        self.name = name
        self.users = users

    def __repr__(self):
        return '<Role %r>' % self.name

こうなっていた定義を

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relation, backref

class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True)
    users = relation('User', backref='role', lazy='dynamic')

    def __init__(self, name, users):
        self.name = name
        self.users = users

    def __repr__(self):
        return '<Role %r>' % self.name

こういうふうに書き換える。TableのカラムやTYPEの定義をimportしておくことも忘れずに。
テーブルの生成は
Base.metadata.create_all(engine)
を実行するのだが、すでにテーブルがあるときには何も起こらない。これも__init__.pyに記述しておくことにする。

from app.models import *
Base.metadata.create_all(engine)

これでいけると思うのだが。

ということで起動してみると、前のdb = SQLAlchemy(app)に絡むところがことごとくエラーになるのでそれらをコメントアウトなり修正してやったところ、無事に起動し、データベースも構築された。もともとあったデータベースをおいておいても破壊されているようすもないので、ここまでのところはちゃんと動作していると思われる。

次に本題の検索関係を構築していく。
まずセッションの作成を行う。
__init__.pyに

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

を追加。そして、検索式や結果を表示するプログラム****.pyに

from app import Session
    session = Session()

を追加。これで準備は整ったはず。

さあ、検索を始めよう。

sample = session.query(Samples).all()
return render_template('/samples/index.html', contents=sample)

こうやったらSamplesの全体がsampleに格納され、/samples/index.htmlにレンダリングされて表示されることを確認した。
イケてるイケてる。

ところが

    sample = session.query(Samples).all()
    if form.name.data:
        if form.aname.data:
             sample = sample.filter(Samples.samplename.like(form.name.data))
        else:
            sample = sample.filter_by(samplename=form.name.data)
    if form.pheno.data:
        if form.apheno.data:
             sample = sample.filter(Samples.phenotype.like(form.pheno.data))
        else:
            sample = sample.filter_by(phenotype=form.pheno.data)

こいうふうに絞込をかけようとすると

AttributeError: 'list' object has no attribute 'filter_by'

こんなエラーが出て止まってしまう。
sample = session.query(Samples).all()
のall()が原因で、こいつがついているとsampleはlistになってしまう。これをはずせばキチンと動いた。

これにて、Flask-SQLAlchemyから素のSQLAlchemyに移行完了した。
さて、そうなると今度は問題のcase、sum、group_byを使えるかどうかだ。

caseの書式は

case([(Exome.zygo == 'het', 1)], [(Exome.zygo == 'hom', 2)], else_=0)

これであっていると思う。
func.sum ... group_byの使い方がいまいちわからない。というかやっぱりJOINを使わないといけないのか。

(func.sum(case([(Exome.zygo == 'het', 1)], [(Exome.zygo == 'hom', 2)], else_=0)).label('i_count)).group_by(Exome.SampleName_phenotype, Exome.Gene_wgEncodeGencodeCompV19)

ここまではいいんじゃないかと思う。
ここで出来上がったi_countを元のExomeにくっつけた仮想テーブルでfilterをかけてi_count >=2となる行をselectして返せばいい。
書くと簡単だけど、これをプログラムにしなければならない。