速習 SQLAlchemy の基本

WEB+DB PRESS Vol.46

の [速習] Python の2章「Pythonとデータベース」で SQLAlchemy の紹介があったので触ってみました。

ほんの5ページの記事で、最初の取っ掛かりとしての基本的な部分がとても分かり易いです。サンプルの見よう見まねで書いてみました。テーブルの等価結合を感覚的にやってみたらできたのだけど、条件の複数指定をどうセットして良いか分からなかった(T T)

#!/bin/env python

# define database
import sqlalchemy
config = {"sqlalchemy.url": "sqlite:////tmp/test.db"}
engine = sqlalchemy.engine_from_config(config)

from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import MetaData

db_session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=engine))
metadata = MetaData()

# define table
from sqlalchemy import Column, Table, types
from sqlalchemy.orm import mapper, relation

person = Table("person", metadata,
        Column("uid", types.Integer, primary_key=True),
        Column("uname", types.Unicode),
        Column("gid", types.Integer)
       )

group = Table("group", metadata,
        Column("gid", types.Integer, primary_key=True),
        Column("gname", types.Unicode)
        )

# make object for mapping
class Person(object):
    pass

class Group(object):
    pass

# do mapping
mapper(Person, person)
mapper(Group, group)

# make table
metadata.create_all(bind=engine)

# DML functions
def insert_or_update_person(uid, uname, gid, p, db_session):
    p.uid = uid
    p.uname = uname
    p.gid = gid
    try:
        db_session.save(p)
    except:
        db_session.save_or_update(p)
    db_session.commit()

def insert_or_update_group(gid, gname, g, db_session):
    g.gid = gid
    g.gname = gname
    try:
        db_session.save(g)
    except:
        db_session.save_or_update(g)
    db_session.commit()

# Main
if __name__ == '__main__':
    # make sample data
    for i in xrange(1, 7):
        p = Person()
        insert_or_update_person(i, u"user0" + str(i), i*10%20+10, p, db_session)

    for i in xrange(1, 4):
        g = Group()
        insert_or_update_group(i*10, u"group" + str(i) + "0", g, db_session)

    # query to table
    p_query = db_session.query(Person)
    g_query = db_session.query(Group)

    # output query for each table
    print "Person data :", p_query.count()
    for p in p_query:
        print " ", p.uid, p.uname, p.gid
        print " ", "-" * 15

    print "Group data :", g_query.count()
    for g in g_query:
        print " ", g.gid , g.gname
        print " ", "-" * 15

    # update data
    print "Update Person data :"
    p_upd_query = db_session.query(Person).filter(Person.uid == 3)
    p_upd = p_upd_query.one()

    insert_or_update_person(p_upd.uid, u"t2y", 10, p_upd, db_session)
    print " ", p_upd.uid, p_upd.uname, p_upd.gid

    print "Update Group data :"
    g_upd_query = db_session.query(Group).filter(Group.gid == 10)
    g_upd = g_upd_query.one()
    insert_or_update_group(g_upd.gid, u"developer", g_upd, db_session)
    print " ", g_upd.gid, g_upd.gname

    # delete data
    print "Delete Group data : group20"
    g_dlt_query = db_session.query(Group).filter(Group.gname == u"group20")
    g_dlt = g_dlt_query.one()
    db_session.delete(g_dlt)
    db_session.commit()

    # join table
    print "combination query :"
    com_query = db_session.query(Person, Group).filter(Person.gid == Group.gid)
    for pg in com_query:
        print " ", pg[0].uid, pg[0].uname, pg[0].gid, pg[1].gname

実行結果。

Person data : 6
  1 user01 20
  ---------------
  2 user02 10
  ---------------
  3 user03 20
  ---------------
  4 user04 10
  ---------------
  5 user05 20
  ---------------
  6 user06 10
  ---------------
Group data : 3
  10 group10
  ---------------
  20 group20
  ---------------
  30 group30
  ---------------
Update Person data :
  3 t2y 10
Update Group data :
  10 developer
Delete Group data : group20
combination query :
  2 user02 10 developer
  3 t2y 10 developer
  4 user04 10 developer
  6 user06 10 developer

リファレンス:
SQLAlchemy