SQLAlchemyとは?
PythonからDBを扱うライブラリ
接続管理だけでなく、DBをオブジェクトに抽象化する。
→SQLを記述せずとも、オブジェクトへのメソッドで操作(insert,delete,select等)が可能
サンプルプログラム①
# cat ./sqlalchemy-study.py #!/usr/bin/env python # -*- coding: utf-8 -*- from sqlalchemy import create_engine Engine = create_engine('sqlite:///:memory:', echo=True) from sqlalchemy.ext import declarative Base = declarative.declarative_base() from sqlalchemy import Column, Integer, Unicode, UnicodeText class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) title = Column(Unicode(255), nullable=False) text = Column(UnicodeText) def __repr__(self): return "<Post(title='%s', text='%s')>" % (self.title, self.text) Base.metadata.create_all(Engine)
実行結果
2016-07-16 14:13:41,878 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2016-07-16 14:13:41,878 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:13:41,878 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2016-07-16 14:13:41,879 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:13:41,879 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("posts") 2016-07-16 14:13:41,879 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:13:41,880 INFO sqlalchemy.engine.base.Engine CREATE TABLE posts ( id INTEGER NOT NULL, title VARCHAR(255) NOT NULL, text TEXT, PRIMARY KEY (id) ) 2016-07-16 14:13:41,880 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:13:41,880 INFO sqlalchemy.engine.base.Engine COMMIT
考察
Base.metadata.create_all(Engine)実行時にPostクラスに記述したコードが実行されている。
これはメタクラスという動きらしい。
ひとまず、こんな機能があるという事で思考停止。
サンプルプログラム②
#!/usr/bin/env python # -*- coding: utf-8 -*- from sqlalchemy import create_engine Engine = create_engine('sqlite:///:memory:', echo=True) from sqlalchemy.ext import declarative Base = declarative.declarative_base() from sqlalchemy import Column, Integer, Unicode, UnicodeText class Post(Base): __tablename__ = 'posts' id = Column(Integer, primary_key=True) title = Column(Unicode(255), nullable=False) text = Column(UnicodeText) def __repr__(self): return "<Post(title='%s', text='%s')>" % (self.title, self.text) Base.metadata.create_all(Engine) from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=Engine) session = Session() post = Post(title=u'title_1', text=u'first') session.add(post) session.commit() query = session.query(Post) for ent in query.all(): print ent.title
実行結果
2016-07-16 14:20:11,257 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2016-07-16 14:20:11,257 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:20:11,257 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2016-07-16 14:20:11,257 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:20:11,258 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("posts") 2016-07-16 14:20:11,258 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:20:11,259 INFO sqlalchemy.engine.base.Engine CREATE TABLE posts ( id INTEGER NOT NULL, title VARCHAR(255) NOT NULL, text TEXT, PRIMARY KEY (id) ) 2016-07-16 14:20:11,259 INFO sqlalchemy.engine.base.Engine () 2016-07-16 14:20:11,259 INFO sqlalchemy.engine.base.Engine COMMIT 2016-07-16 14:20:11,260 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2016-07-16 14:20:11,261 INFO sqlalchemy.engine.base.Engine INSERT INTO posts (title, text) VALUES (?, ?) 2016-07-16 14:20:11,261 INFO sqlalchemy.engine.base.Engine (u'title_1', u'first') 2016-07-16 14:20:11,261 INFO sqlalchemy.engine.base.Engine COMMIT 2016-07-16 14:20:11,261 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2016-07-16 14:20:11,262 INFO sqlalchemy.engine.base.Engine SELECT posts.id AS posts_id, posts.title AS posts_title, posts.text AS posts_text FROM posts 2016-07-16 14:20:11,262 INFO sqlalchemy.engine.base.Engine () title_1
考察
SQLAlchemyのdeclative機能が、オブジェクトの定義とDBテーブルへの関連付けを同時に行っている。
※Postクラスの定義→DBのpostsテーブルの関連付