sqlalchemyでMySQLを使う

前回はMySQLdbを使ってMySQLを利用しましたが、今回はsqlalchemyを使ってみます。
また、位置情報を利用するため、sqlalchemyだけでなく、geoalchemyを利用します。
geoalchemyについてはまた後日書きます。

準備

インストールはpipとかeasy_installとかで簡単に出来るはずです。

from sqlalchemy import Column, Integer, String, Text, ForeignKey, DECIMAL, DATETIME, create_engine
from sqlalchemy.orm import session maker, relationship
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy import GeometryColumn, Point
from geoalchemy.mysql import MySQLComparator

今回はこれだけimportしておけばいいでしょう。

テーブル定義

前回同様、以下のSQLでMySQLにDBとテーブルを作成します。

create database geosample;
use geosample;
create table place (
	id int auto_increment,
	name varchar(255),
	latlng geometry not null,
	created datetime not null,
	modified datetime not null,
	primary key (id),
	spatial key spot_latlng_index(latlng)
) engine=MyISAM default character set utf8;

create table user (
	id int auto_increment,
	user_name varchar(32),
	home_id int,
	created datetime not null,
	modified datetime not null,
	primary key (id),
	foreign key (home_id) references place(id)
) engine=MyISAM default character set utf8;

これをsqlalchemyでマッピングするために、テーブルを表すクラスを定義します。

Base = declarative_base()
class Place(Base):
	__tablename__ = "place"
	__table_args__ = {"mysql_engine": "MyISAM"}
	id = Column("id", Integer, primary_key=True, autoincrement=True)
	name = Column("name", String(255))
	latlng = GeometryColumn("latlng", Point(dimension=2, srid=4326), \
			nullable=False, comparator=MySQLComparator)
	created = Column("created", DATETIME, default=datetime.now, nullable=False)
	modified = Column("modified", DATETIME, default=datetime.now, nullable=False)
	user = relationship("User")
	def __init__(self, name, latlng, created, modified):
		# self.id = id  #いらない
		self.name = name
		self.latlng = latlng
		self.created = created
		self.modified = modified
	
	def __repr__(self):
		return "<Place>(name: {name}, latlng:{latlng})"\
			.format(name=self.name, latlng=self.latlng)

class User(Base):
	__tablename__ = "user"
	__table_args__ = {"mysql_engine": "MyISAM"}
	id = Column("id", Integer, primary_key=True, autoincrement=True)
	user_name = Column("user_name", String(255))
	home_id = Column("home_id", Integer, ForeignKey("place.id"))
	created = Column("created", DATETIME, default=datetime.now, nullable=False)
	modified = Column("modified", DATETIME, default=datetime.now, nullable=False)
	def __init__(self, user_name, home_id, created, modified):
		self.user_name = user_name
		self.home_id = home_id
		self.created = created
		self.modified = modified
	
	def __repr__(self):
		return "<User>(user_name:{name}, home_id:{home_id})"\
			.format(name=self.user_name, home_id=self.home_id)

このように定義します。
外部キー制約も

user = relationship("User")
home_id = Column("home_id", Integer, ForeignKey("place.id"))

の2行で実現できている、はずです。

使用準備

DBにアクセスするために、MySQLへの接続とそのセッションを作成する準備をします。

# echo=Trueでログを吐く
engine = create_engin("mysql://[user]:[passwd]@[host]/[dbname]",\
				encoding="utf-8", echo=False)
Session = sessionmaker(bind=engine)
# session = Session()としてセッションを作成

テーブル定義とSessionの定義あたりをまとめておいてimportするようにするといいと思います。
ここではmodel.pyというファイルに保存したことにします。

！！！セッションはスレッドセーフでないため、作りっぱなしなんてことがないようにしましょう！！！

セッションの使い方

基本構文

from model import *
import datetime

session = Session()
# session.begin() いらないっぽい
now = datetime.datetime.now()

try:
	# select
	user = session.query(User).first()
	# update
	user.user_name = "hogehoge"
	# delete
	session.delete(user)
	# insert
	new_user = User("foobar", 3, now, now)
	session.add(new_user)
	session.flush()
	session.commit()
except Exception, e:
	print e
	session.rollback()
session.close()

こんな感じです。
updateとdeleteについてはselectして、その結果に対して行うイメージです。
sqlの実行についてはこのページが非常に分かりやすいです。

select結果の辞書化

selectした結果をjsonにしたい！、辞書にしたい！、なんて時は

import json
session = Session()
users = session.query(User.id, User.user_name).all()
dict_user = [user._asdict() for user in users]
json_user = json.dumps(dict_user)

このように、selectするときに取得するカラム名を指定すれば、