pythonのORM：SQLAlchemy の基本的な使い方

SQLAlchemy を使ってみる。

SQLAlchemy を使ってみる。
目次
インストール
テーブルの作成
- 手順のおさらい
RDBMS への接続
- テーブル定義のクラス。
INSERT/UPDATE をする。
SELECT について。
- all () / first() とその仲間
- filter の書き方例
limit / offset
ORDER BY / ORDER BY DESC
- ASC
- DESC
既存のテーブルを扱うクラスについて
まとめ
参考資料

インストール

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 する

RDBMS への接続

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 ) # is null
session.query(Page).filter( Page.html != None ) # is not null

演算子オーバーロードが嫌いな人は、次のように関数で書くことが出来る。

session.query(Page).filter( Page.html.is_(None) ) # is null
session.query(Page).filter( Page.html.isnot(None) ) # is not null

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 )  # is NULL

ちなみに、経験則ですが、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

それマグで！

知識はカップより、マグでゆっくり頂きます。　takuya_1stのブログ