SQLAlchemy を使ってみる。
目次
インストール
pip install sqlalchemy
テーブルの作成
テーブルを作成するときは、ORMを使うと便利。
Baseを継承して 、カラムをメンバに定義したうえで、 Base.metadata.create_all(engine) を呼び出す。
from sqlalchemy import *
from sqlalchemy.orm import *
Base = declarative_base()
class Page(Base):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
url = Column(Text, unique=True, nullable=False)
html = Column(Text , nullable=True)
http_status = Column(Integer, default=0, nullable=True )
status = Column(Integer, default=0, nullable=True )
def is_not_found :
return self.http_status == 404;
engine = create_engine('sqlite:///./urls.db', echo=True)
Base.metadata.create_all(engine)
手順のおさらい
- 必要なパッケージをimport
Base=declarative_base()を使う- RDBMSに接続する
create_all する
engine = create_engine('sqlite:///./urls.db', echo=True)
echo=True にすると接続と実行ログが出るので重宝するので、初回実行は必ずTrueにしておく。
テーブル定義のクラス。
class Page(Base):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
url = Column(Text, unique=True, nullable=False)
html = Column(Text , nullable=True)
http_status = Column(Integer, default=0, nullable=True )
status = Column(Integer, default=0, nullable=True )
メンバ変数で入れていく。
テーブル名を __tablename__ で代入する。
カラムには、Column( TYPE, *options ) で指定する。
TYPEには sqlalchemy.Integer sqlalchemy.String 等がある。
Base.metadata.create_all() は複数回呼び出しても大丈夫。なかったら作るだけ。既存のテーブルを消したり、再定義しない。
INSERT/UPDATE をする。
データの投入はクラスを使うと便利。
ORMを使うためクラスのインスタンス変数をメインにいじっていく。
また、接続はすべてSession単位で扱われる。
SessionはBegin / Commit を抽象化したものだと思えばいい。
INSERT INTO をするには Page( url=i ) で クラスインスタンスを作成し、session.add(page) でレコードを追加する。
そして、最後にsession.commit() で コミットする。
INSERT の例
Session = sessionmaker(bind=engine)
session = Session()
url = 'https://web.wm.auone.jp/lx/regular/?p=2'
page = Page( url=i )
ret = session.add(page)
session.flush()
session.commit()
UPDATE について
UPDATE 句については、クラスのメンバ変数を更新し、最後にCommitする。
page = session.query(Page).filter( Page.html == (None) ).first()
page.html = fetch( page.url )
page.http_status = 200
page.status = 1
session.commit()
クラスインスタンスを作成する代わりに、Fiter( SELECT ) でオブジェクトとしてレコードを取り出してから、メンバ変数を更新し、最後に COMMITする。
また、UPDATEは、条件にマッチするものを一括で更新することも有りえます。
session.query(Page).filter( Page.id < 100 ).update( {status=0} )
DELETEする。
DELETEにも２つの考え方があります。ひとつはオブジェクトをレコードをから消す。もう一つは検索条件にマッチするものを消す。
オブジェクトを取得して消す場合
page = session.query(Page).filter( Page.id == 1 ).one()
session.delete(page)
session.commit()
検索条件にマッチするものを消す場合。
session.query(Page).filter( Page.id == 1 ).delete()
INSERT・UPDATE・DELETE のキャンセル
セッションは begin 同等なので キャンセルできる。
session.rollback()
SELECT について。
テーブルからデータを取り出すには、SELECT句をWrapした query を使う。
またWhere句をWrapしたfilter / filter_by を使う。
pages = session.query(Page).all()
page = session.query(Page).first()
query した後に all () や first() を呼び出すことが必要。 これらのメソッドを実行することでPreared statement が実行される。
all () / first() とその仲間
SELECTをするためには、 query().filter().all() のように書くことが出来る。 all () の他には次のようなものがある。
レコードを返すメソッド一覧
- all () 配列を返す。
- first () クラス(またはNone)を返す。
- count () 個数を返す
- one 系
- one() filterの結果が１つだけになることを期待する。
- one_or_none() one と同じ
- scalar() ORMマッピングしないときに使う。
one()について
one は考え方が、少し面倒ですね。
one() は filterの結果が１つだけになることを期待する。つまり、where で絞り込んだ結果が複数返ってきたら sqlalchemy.orm.exc.MultipleResultsFound を返してエラーになる。また、一つも見つからない場合は sqlalchemy.orm.exc.NoResultFound を返す。
one_or_none について
one_or_none は 結果が０件のときにone がsqlalchemy.orm.exc.NoResultFound 例外を送出する代わりに None を返す。
scalar について
scalar() は one() を内部的に実行する。
これは scalar 値を取り出すのに使う。たとえば、 select count(id) や select id from tableを実行するときに使う。実際にはcount ()が あるしORM使うしあんまり使わないかも？
count について
count() は COUNT(*) を実行してくれる。
個数が少ないなら all() してlen()見ても良いのですが。count の方がいいね。
filter の書き方例
where句の代わりにFilter()を使ってデータを取り出す。
session.query(Page).filter( Page.id == 1 ).one()
これは、クラスに入れてある 変数 id が Colmun( ) を継承していて Column の演算子オーバーロードを使っているので クォートなしに書ける。詳しくは dir( Your_ORM_Class.colname ) などすればわかる。
この演算子オーバーロードのお陰で記述がクオートなしでスッキリする。こういうのはPHPやRubyのORMにも真似してほしい所。
IS NULL / IS Not NULL
IS NULL は == None と解釈する。
IS Not NULL は != None と解釈する。
次のように書く
session.query(Page).filter( Page.html == None )
session.query(Page).filter( Page.html != None )
演算子オーバーロードが嫌いな人は、次のように関数で書くことが出来る。
session.query(Page).filter( Page.html.is_(None) )
session.query(Page).filter( Page.html.isnot(None) )
COLNAME = VALUE / COLNAME > VALUE
where 句の基本 COLNAME = VALUEは is null で見たように、 == VALUE で書く。
is not nullの書式を覚えてしまえば、他も全て同じに解釈できて便利です。
session.query(Page).filter( Page.id == 1 )
session.query(Page).filter( Page.id != 1 )
session.query(Page).filter( Page.id < 10 )
session.query(Page).filter( Page.id >= 10 )
session.query(Page).filter( Page.id == None )
ちなみに、経験則ですが、ORMの使用法を見るとき、is not null とLIKE を見るといい、そこにORMの特徴が出るのでis not null / like を調べればだいたい使い方がわかる。
AND について
ANDは、条件を複数突っ込めば ANDになる。とてもかんたん。
session.query(Page).filter( Page.id > 51 , Page.id < 100 )
LIKE 句について
LIKE は、直接文字列を突っ込みますね。% が Python の文字列フォーマット指定子なので上手に使ってください。
query.filter(Page.url.like('%search_string%'))
フォーマットするときは
search="search_string"
query.filter(Page.url.like( "%%%s%%" % search )) # お世辞にも読み易いと言えない
直接SQLのWhere句を入れたい
Where句は複雑になるので、ORMに頼らずに直接指定したい場合。此の目的にはsqlalchemy.text が使える。
session.query(Item).filter( text(' id < 10 ' ) ).all()
session.query(Item).filter( text(' id > 10 and id < 12 ' ) ).all()
この場合はPrepareを経由せずに実行されるので、SQL Injection の可能性がある。そのためbindParam するには次のようにする。
プリペアドステートメントの使い方は、RDBMSによって多少異なるが、大抵はプレイスホルダを ?で書くか、 :param_name のようにコロンで書く
sqlalchemy.text() では :name は使えるが ? は使えないみたい
session.query(Item).filter( text(' id > :value1 and id < :value2' ) ).params(value1=10, value2=12).all()
これは動かなかった。( 追加調査が必要 かも
items = session.query(Item).filter( text(' id = ? ' ) ).params(1)
SQL Injection を防ぐためには text() をで直接使うなというのではなく、ユーザーから入力される文字列に対して bind を実行するのである。
limit / offset
これは予想通りの記述。
session.query(Item).limit(10).offset(10)
ORDER BY / ORDER BY DESC
ORDER BY は かんたん。でも 降順はちょっとめんどくさい
ASC
session.query(Item).lorder_by( Item.id )
DESC
session.query(Item).order_by( Item.id.desc() )
session.query(Item).order_by( desc(Item.id) )
session.query(Item).order_by( " id desc " )
order_by_desc/ order_by_asc みたいな関数でも良いと思うんだけど、なかったので上記のように書く必要がある。
既存のテーブルを扱うクラスについて
既存のテーブルを扱うときは、必要なカラム分だけメンバ変数に取り出してもいい。全カラムを指定するのは流石に面倒だろうし。
from sqlalchemy import *
from sqlalchemy.orm import *
Base = declarative_base()
class Item(IBase):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
status = Column(Integer, default=0, nullable=True )
もちろんマッピングしてないカラムについてはORMから見えなくなる。
まとめ
ORMとして非常に書きやすいと思います。今回は sqlalchemy.ormがメインで、sqlalchemy.sql.expression については殆ど言及してないです。
expression を使ってもSQLを組み立てるより手軽でかなり使いやすいのでおすすめです。
たとえば、updateについては ORMを使わずに次のように書くことが出来ます。
公式ドキュメントからの引用
update(users).where(users.c.id==5).values(name='user #5')
users.update().where(users.c.id==5).values(name='user #5')
どんな使い方をしたとしてもSQL/ RDBMS を扱うには SQLAlchemy は期待にかなう優秀なツールですね。
参考資料
http://www.sqlalchemy.org/library.html#reference
http://stackoverflow.com/questions/4186062/sqlalchemy-order-by-descending
