pikesaku’s blog

個人的な勉強メモです。記載内容について一切の責任は持ちません。

SQLAlchamyの勉強!

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テーブルの関連付