SQLAlchemy / Drkcore

SQLAlchemyで大文字小文字の違いを無視したLIKE検索を行う場合

Wed, 06 Apr 2016 20:34:15 +0919

likeじゃなくてilikeを使うらしい

Case Insensitive Flask-SQLAlchemy Query

楽ちん

SQLAlchemyでORA-01795を避ける

Tue, 25 Aug 2015 20:56:48 +0919

OracleではIN句に1000以上の要素を指定するとエラーが出る。

例えばSQLAlchemyだとこんな感じのクエリでidsに1000以上の要素を入れるとダメ

Patent.query.filter(Patent.first_publication_date > patent_since).filter(Patent.tbas.any(ThomsonTba.id.in_(ids))).all()

じゃぁどうするの？と調べたら1000未満の要素に分割してor_でつなげばいいんだよという…

実際これで動いたけど、2000超えたらまたエラーが出て書きなおさなきゃいけないのが見えてるからよろしくないよなぁ。

良い方法はないものか…

Pythonで今週がいつからいつまでかを調べる

Fri, 08 Mar 2013 19:06:15 +0919

例えば　SQLAlchemyである週（今週とか先週）で検索をかけたいとか結構ある(calendar daysじゃなくてworking daysとか)。

そういう時に今日が何曜日か調べて月曜日から金曜日までのたし引きをするのがすぐに思いつくんだけどスマートじゃないのでちょっと調べたらdatetime.isocalendarっていうメソッドで今日が今年の第何週かが返ってくる。

というわけなので、週の1日目（月曜）と5日目(金曜)を探せばいい。

>>> from datetime import datetime
>>> today = datetime.now()
>>> today
datetime.datetime(2013, 3, 8, 18, 48, 12, 860947)>>> today.isocalendar()
(2013, 10, 5)
>>> ti = today.isocalendar()
>>> datetime.strptime("{} {} {}".format(ti[0], ti[1], 1), "%Y %W %w")
datetime.datetime(2013, 3, 11, 0, 0)

ん、次の週の月曜日を指している？

というところがハマったポイントでisocalendarは1からはじまるけど%Wは0からはじまるみたい。

なので第何週目かから1を引いておく

>>> datetime.strptime("{} {} {}".format(ti[0], ti[1]-1, 1), "%Y %W %w")
datetime.datetime(2013, 3, 4, 0, 0)
>>> datetime.strptime("{} {} {}".format(ti[0], ti[1]-1, 5), "%Y %W %w")
datetime.datetime(2013, 3, 8, 0, 0)

あとはデータベースに問い合わせをすればよい。

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (5)

Sun, 03 Mar 2013 16:21:14 +0919

reStructuredTextをHTMLにコンバートする

まずはreStructuredTextをHTMLにコンバートするためにdocutilsをインストールします。

pip install docutils

modelを修正してreStructuredTextをHTMLにコンバートするメソッドを追加します。

from sqlalchemy import Column, Integer, String, Text, DateTime
from flaski.database import Base
from datetime import datetime
from docutils.core import publish_parts
overrides = {'doctitle_xform': 0,
             'initial_header_level': 2}

class WikiContent(Base):
    __tablename__ = 'wikicontents'
    id = Column(Integer, primary_key=True)
    title = Column(String(128), unique=True)
    body = Column(Text)
    date = Column(DateTime, default=datetime.now())

    def __init__(self, title=None, body=None, date=None):
        self.title = title
        self.body = body
        self.date = date

    def __repr__(self):
        return '<Title %r>' % (self.title)

    @property
    def html(self):
        parts = publish_parts(source=self.body,
                              writer_name="html",
                              settings_overrides=overrides
                              )
        return parts['html_body']

@propertyデコレータを使うことでcontent.html()とメソッド呼び出しではなくcontent.htmlとプロバティとしてアクセスできるようになります。setting_overridesしているのはタイトルをh1要素にしているので、トップレベルのヘッダーをh2から始めたいからです。

それからpostした時の戻り値もhtmlにコンバートしたものにします。

@app.route("/<title>", methods=["POST"])
def post_content(title=None):
    ...略...
    return content.html

httpieでちょっとテストしてみます

$ http --form POST http://localhost:5000/rsttest body="rst **strong** and *italic*"
HTTP/1.0 200 OK
Content-Type: text/html; charset=utf-8
Content-Length: 85
Server: Werkzeug/0.8.3 Python/2.7.3
Date: Sun, 03 Mar 2013 06:29:59 GMT

<div class="document">
<p>rst <strong>strong</strong> and <em>italic</em></p>
</div>

正しく変換されています。

テンプレートを修正する

ブラウザでアクセスした場合にもきちんとHTMLが表示されるようにJinja2テンプレートを修正します。

show_content.html

{% extends "layout.html" %}
{% block body %}
<h1>{{content.title}}</h1>
<div>{{content.html|safe}}</div>
<p>{{content.date}}</p>
{% endblock %}

content.bodyをhtmlに変更するだけですが、そのままだとHTMLタグがエスケープされてしまうのでsafeフィルターを付ける必要があります。

クリックで書き換えられるようにする

wikiのコンテンツエリアをクリックした時にformの編集画面に切り替わるようにします。そのためにjeditableプラグインを利用するのでminifyバージョンをstaticディレクトリにダウンロードしておいてください。

これはajaxで通信するので編集画面に切り替わった際に元データをGETするためのAPIを用意しておきます。

app.pyに次の関数を追加します。

@app.route("/rst/<title>")
def show_rst(title):
    content = WikiContent.query.filter_by(title=title).first()
    if content is None:
        abort(404)
    return content.body

単にrstなデータを返しているだけです。

続いてテンプレートも修正します。

show_content.html

{% extends "layout.html" %}
{% block body %}
<h1>{{content.title}}</h1>
<div class="editable_textarea">{{content.html|safe}}</div>
<p>{{content.date}}</p>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="{{url_for('static', filename='jquery.jeditable.mini.js')}}"></script>
<script>
  $(".editable_textarea").editable("{{content.title}}", { 
      type   : 'textarea',
      submitdata: { _method: "post" },
      name     : 'body',
      loadurl  : '/rst/' + '{{content.title}}',
      rows     : 10,
      submit : 'OK',
      cancel : 'cancel',
      cssclass : "editable"
  });
</script>
{% endblock %}

jeditableで操作するためにdiv要素にクラスを追加しています。

bodyの最後にjQueryとjeditableを呼び出しています。先ほどapp.pyに追加した関数はloadurlで呼び出すようになっています。

コンテンツをクリックすると編集画面になるので

日本酒に対する熱い思いをぶつけます。

ここまでのGitHub

まとめ

reStructuredTextなWikiのための機能はひと通り揃いました。あとは見栄えを良くするためにStylus+Nib+Nibbleあたりでデザインをちょこまかいじればいいでしょう。

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (4)

Sun, 03 Mar 2013 13:19:54 +0919

POSTメソッドでデータを変更できるようにする

今回変更するのはapp.pyのみです。

SQLAlchemyを使ってデータを取得できるようになったので、データの追加、更新をできるようにします。そのためにはpython対話環境で行ったようにdb_sessionが必要なのでflaski.databaseモジュールをimportします。またリクエストの最後に、セッションの後片付けをする必要があります(shutdown_session)。

POST時にformにアクセスするのでflaskからrequestをインポートしています(1行目)。さらに、更新時を書き換えたいのでdatetimeモジュールもインポートしています。

from flask import Flask, render_template, abort, request
from flaski.models import WikiContent
from flaski.database import db_session
from datetime import datetime

app = Flask(__name__)
app.config['DEBUG'] = True

@app.teardown_request
def shutdown_session(exception=None):
    db_session.remove()

@app.route("/")
def hello():
    contents = WikiContent.query.all()
    return render_template("index.html", contents=contents)

@app.route("/<title>", methods=["GET"])
def show_content(title):
    content = WikiContent.query.filter_by(title=title).first()
    if content is None:
        abort(404)
    return render_template("show_content.html", content=content)

# 続く

GETでアクセスした場合にデータを取得、POSTでアクセスすると新規追加または更新を行いたいのでapp.routeのメソッドを限定します。

@app.route("/<title>", methods=["GET"])

POST用のコードが続きます。titleでデータを検索して存在しない場合には追加、存在する場合は更新処理をしています。bodyはformで渡されることを想定しています。

# 続き
@app.route("/<title>", methods=["POST"])
def post_content(title=None):
    if title is None:
        abort(404)
    content = WikiContent.query.filter_by(title=title).first()
    if content is None:
        content = WikiContent(title,
                              request.form["body"]
                              )
    else:
        content.body = request.form["body"]
        content.date = datetime.now()
    db_session.add(content)
    db_session.commit()
    return content.body

if __name__ == "__main__":
    app.run()

HTTP経由でデータの追加更新をテストする

HTMLでフォームを用意するのは面倒なので、今回はhttpieを利用してコマンドラインから操作してみます。

pipでインストールします

pip install httpie # $HOME/.virtualenvs/flaski/binにインストールされます

新規データを登録します。app.pyを実行してサーバーを起動しておくのを忘れないでください。

$ http --form POST http://localhost:5000/httpie body="test from httpie"
HTTP/1.0 200 OK
Content-Type: text/html; charset=utf-8
Content-Length: 16
Server: Werkzeug/0.8.3 Python/2.7.3
Date: Sun, 03 Mar 2013 04:09:36 GMT
test from httpie

--formオプション(または-f)をつけるとformで送られます。データが新規に追加されていることをブラウザで確認します。

データが既に存在する場合には更新されることも確認します。

$ http --form POST http://localhost:5000/httpie body="modified from httpie"

まとめ

FlaskiにPOST経由でのデータ追加、更新を実装しました。

ここまでのGitHub

POSTメソッドでデータを変更

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (3)

Sun, 03 Mar 2013 12:19:23 +0919

Flaskでモデルを使う

先に用意したモデルをFlaskで使うにモデルをimportします。

ルートにアクセスしたらコンテンツのタイトル一覧を表示します。さらに、/titleにアクセスしたら内容を表示するようにします。

app.py

from flask import Flask, render_template, abort
from flaski.models import WikiContent

app = Flask(__name__)
app.config['DEBUG'] = True

@app.route("/")
def hello():
    contents = WikiContent.query.all()
    return render_template("index.html", contents=contents)

@app.route("/<title>", methods=["GET"])
def show_content(title):
    content = WikiContent.query.filter_by(title=title).first()
    if content is None:
        abort(404)
    return render_template("show_content.html", content=content)

if __name__ == "__main__":
    app.run()

database.pyでBaseクラスにqueryメソッドを追加しておいたので、WIkiContentクラスから、allで全件検索、filter_byでフィルタリングができるようになっています。

app.routeで<>で囲むと変数としてキャプチャしています。title名で検索をかけ、データが存在しない場合は404エラーを返します(abort(404))。

index.html

{% extends "layout.html" %}
{% block body %}
<h1>Flaski</h1>
<img src="{{url_for('static', filename='snake.jpg')}}" alt="snake"/>
<ul>
{% for content in contents %}
<li><a href="{{url_for('show_content', title=content.title)}}">{{content.title}}</a></li>
{% endfor%}
</ul>
{% endblock %}

for文でcontentsをループさせ、タイトルをリンク付きでリスト表示させる処理を新たに追加しています。タイトルはapp.pyから送る必要がないので、ハードコードするようにしました。

新たに個別のコンテンツ用のテンプレートも用意します

show_content.html

{% extends "layout.html" %}
{% block body %}
<h1>{{content.title}}</h1>
<div>{{content.body}}</div>
<p>{{content.date}}</p>
{% endblock %}

コンテンツがリスト表示されることを確認します。

ここまでのGitHub

モデルを扱う

まとめ

FlaskとSQLAlchemyを連携させてデータ検索、表示するようにしました。

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (2)

Sun, 03 Mar 2013 09:41:56 +0919

SQLAlchemyを使う

SQLAlchemyを使ってModelをデザインしていきます。今回はdeclarativeを使い、RDBにSQLiteを利用します。

modelは一ヶ所で管理したいのでディレクトリを新たに用意します。

mkdir flaski # flaski/flaski
cd flaski
touch __init__.py

ディレクトリをmoduleとして呼び出したいので、__init__.pyという空のファイルを用意します。flaski/flaski以下は次のファイル構成になります。

├── flaski
    ├── __init__.py
    ├── database.py
    └── models.py

作成するのはWikiなのでタイトル、内容、タイムスタンプをデータベースに記録します。

models.py

from sqlalchemy import Column, Integer, String, Text, DateTime
from flaski.database import Base
from datetime import datetime

class WikiContent(Base):
    __tablename__ = 'wikicontents'
    id = Column(Integer, primary_key=True)
    title = Column(String(128), unique=True)
    body = Column(Text)
    date = Column(DateTime, default=datetime.now())

    def __init__(self, title=None, body=None, date=None):
        self.title = title
        self.body = body
        self.date = date

    def __repr__(self):
        return '<Title %r>' % (self.title)

WikiContentクラスではそれぞれの属性の型や主キー、ユニークかどうかなどを指定しています。__repr__メソッドでは出力した時にどう表示させるかを定義しています。

続いて、database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import os

databese_file = os.path.join(os.path.abspath(os.path.dirname(__file__)), 'wiki.db')
engine = create_engine('sqlite:///' + databese_file, convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    import flaski.models
    Base.metadata.create_all(bind=engine)

database.pyでは実際に利用されるデータベースエンジンの設定をしています。今回はSQLiteを使っています(6行目) init_dbはデータベース初期化のための関数です。

データベース初期化

python対話環境を起動してデータベースを初期化します。

>>> from flaski.database import init_db
>>> init_db()

flaski/flaski/wiki.dbが作成されているので確認してみます。

sqlite3 flaski/wiki.db
sqlite> .schema 
CREATE TABLE wikicontents (
    id INTEGER NOT NULL, 
    title VARCHAR(128), 
    body TEXT, 
    date DATETIME, 
    PRIMARY KEY (id), 
    UNIQUE (title)
);

続いて対話環境からデータを登録してみます

>>> from flaski.database import db_session
>>> from flaski.models import WikiContent
>>> c1 = WikiContent("Flask", "micro framework")
>>> db_session.add(c1)
>>> db_session.commit()
>>> c2 = WikiContent("python", "pppython")
>>> c3 = WikiContent("kobito", "kakure-momojiri")
>>> db_session.add(c2)
>>> db_session.add(c3)
>>> db_session.commit()

確認してみます

$ sqlite3 flaski/wiki.db 
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from wikicontents;
1|Flask|micro framework|2013-03-03 09:23:03.721257
2|python|pppython|2013-03-03 09:23:03.721257
3|kobito|kakure-momojiri|2013-03-03 09:23:03.721257

ここまでのGitHub

モデルの設定

まとめ

SQLAlchemyのdeclarativeを使ってモデル構築と、pythonからデータベースの初期化、データの登録を行いました。

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (1)

Sun, 03 Mar 2013 08:20:25 +0919

初めてのFlaskアプリ

ディレクトリを用意します。

mkdir flaski
cd flaski

以下のコードをapp.pyとして保存します。ちなみにFlaskのサイトに載っているものです。

from flask import Flask
app = Flask(__name__)

@app.route("/")
def hello():
    return "Hello World!"

if __name__ == "__main__":
    app.run()

Flaskで開発する準備が整っていれば、python app.pyと叩けば127.0.0.1:5000でウェブサーバーが起動します。

/にアクセスした場合にHello Worldという文字列を返すように設定して(4-6行目)、app.runでアプリを起動させています。

$ python app.py
 * Running on http://127.0.0.1:5000/

テンプレートの導入

単なる文字列だと味気無いのでテンプレートエンジンを使ってもう少し複雑なHTMLを表示してみます。ディレクトリの構成を表示しておきます。

.
├── app.py
├── static
│   └── snake.jpg
└── templates
    ├── index.html
    └── layout.html

cssや画像などのファイルはstaticというディレクトリに配置します。今回はsnake.jpgを表示してみます。

FlaskのテンプレートエンジンはJinja2です。Flask内でテンプレートを扱うにはrender_template関数を用い、テンプレートはtemplatesディレクトリに配置します。

templatesディレクトリにはlayout.html,index.htmlの2つのファイルを用意します。

layout.html

<!DOCTYPE html>
<html>
  <head>
    <title>flaski</title>
  </head>
  <body>
    {% block body %}{% endblock %}
  </body>
</html>

index.html

{% extends "layout.html" %}
{% block body %}
<h1>{{title}}</h1>
<img src="{{url_for('static', filename='snake.jpg')}}" alt="snake"/>
{% endblock %}

{{}}で囲むと変数を展開することができます。今回はtitleをapp.pyのrender_templateで渡しています。url_forはエンドポイントを指定するとURLに展開する関数です。

app.pyはrender_templateをインポートしてhello関数で使うようにします。

また今回からデバッグモードで動かすようにapp.configをいじっています(3行目)ので、ファイル更新時に自動リスタートしたり、エラーがあればブラウザにデバッグのための情報が出力されるようになります。

from flask import Flask, render_template
app = Flask(__name__)
app.config['DEBUG'] = True

@app.route("/")
def hello():
    return render_template("index.html", title="Flaski")

if __name__ == "__main__":
    app.run()

先ほどと同様にhttp://127.0.0.1:5000/にアクセスすると今度は画像つきのHTMLが表示されます。

ここまでのGitHub

まとめ

FlaskでJinja2のテンプレートエンジンを使ってみました。

Flaskは何であって何でないかによると

Flask itself just bridges to Werkzeug to implement a proper WSGI application and to Jinja2 to handle templating.

ということなので、テンプレートエンジンはJinja2を利用するのがベストです。

補足

Jinja2自体はその上に別の構文を被せることができるので、JadeやHamlも利用することができます。

個人的にはインデントでネストを表現するJadeの構文を気に入っているのでPyJadeを使うことが多いです。

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (0)

Sun, 03 Mar 2013 06:12:12 +0919

Persistentの流れから、次はFlask,SQLAlchemy,Jinja2でもやりますかねみたいな話になったので、サーチかけたんだけどいまいち興味ありそうなヒトがいなさそうなので、Wikiをつくることを題材にしてこっちに書いていくことにした。

興味ありなヒトがいそうだったらハンズオンしてもいいかなぁ。まぁどうせみんなYesodなんでしょうけどねー

仮想環境構築のためのライブラリを導入

仮想環境下で動かしたいので、pip,virtualenv,virtualenvwrapperの3つをインストールします。

curl -O https://raw.github.com/pypa/pip/master/contrib/get-pip.py
sudo python get-pip.py
sudo pip virtualenv
sudo pip install virtualenvwrapper

.bashrcに以下の行を追加(osx+homebrewの場合)してsourceもする(source ~/.bashrcとか)。

source /usr/local/share/python/virtualenvwrapper.sh

仮想環境を構築

今回つくるwikiの名前をflaskiにするので、flaskiという名前の仮想環境をつくります。

mkvirtualenv flaski
New python executable in flaski/bin/python
Installing setuptools............done.
Installing pip...............done.
virtualenvwrapper.user_scripts creating /Users/kzfm/.virtualenvs/flaski/bin/predeactivate
virtualenvwrapper.user_scripts creating /Users/kzfm/.virtualenvs/flaski/bin/postdeactivate
virtualenvwrapper.user_scripts creating /Users/kzfm/.virtualenvs/flaski/bin/preactivate
virtualenvwrapper.user_scripts creating /Users/kzfm/.virtualenvs/flaski/bin/postactivate
virtualenvwrapper.user_scripts creating /Users/kzfm/.virtualenvs/flaski/bin/get_env_details
(flaski)localhost@kzfm:flask $

仮想環境に入っていることはプロンプトの先頭に(flaski)と表示されることでわかります。

パッケージのインストール

FlaskとSQLAlchemyをインストールします。

pip install flask
pip install sqlalchemy

インストールされたパッケージはpip freezeで出力出来ます。

Flask==0.9
Jinja2==2.6
SQLAlchemy==0.8.0b2
Werkzeug==0.8.3
wsgiref==0.1.2

まとめ

Flaskで開発するための環境を構築しました。次からは実際にFlaskでwafを作っていきます。

virtualenvwrapper

補足

pipの使い方

逆に環境をコピーしたい場合には

pip freeze > requirements.txt

で依存するパッケージを作っておいて、別のマシンにファイルをコピーして

pip install -r requirements.txt

とやればいいので便利です。

virtualenvwrapperの使い方

mkvirtualenv [仮想環境名]: 仮想環境をつくる
workon [仮想環境名]: 仮想環境に入る
deactivate: 仮想環境をぬける
lsvirtualenv: 仮想環境一覧を表示
rmvirtualenv: 仮想環境を削除

最近覚えたSQLAlchemyのTips

Mon, 25 Feb 2013 20:14:40 +0919

先週は、レガシーなOracleデータベースと格闘していた(文字エンコーディングの件もその一つ)。

tableとtablenameの違い

__table__は適切に設定済みのテーブルに対して使うらしい。自分でごちゃごちゃと設定したい場合は__tablename__と__table_args__を使うべし

How to override a column name in sqlalchemy using reflection and descriptive syntax

複合プライマリーキーでのone-to-many

主キーが複合プライマリーキーの場合はそれぞれにprimary_key=Trueをつけるが、外部キーで一対多の関連付けをしたい場合には __table_args__で外部キー設定をしておく。

class Protocol(Base):
    __tablename__ = 'tblprotocol'
    id = Column("id", String(32), primary_key=True)
    version = Column("version", String(11), primary_key=True)
    name = Column("name", String(255))

class Experiment(Base):
    __tablename__ = 'tblexperiments'
    __table_args__ = (ForeignKeyConstraint(['protocolid','protocolversion'],
                                      ['tblprotocol.id', 'tblprotocol.version']), {})
    id = Column("id", String(32), primary_key=True)
    protocolid = Column("protocolid", String(32))
    protocolversion = Column("protocolversion", String(11))
    name = Column("name", String(255))
    protocol = relationship("Protocol", backref="experiments")

そもそも主キーの無いテーブル

主キーの無いテーブルはエラーが出るので、適当に複合キーを設定してプライマリーキーにしてしまう。

その他

SQLもORマッパーもわかっていればバランスの取れた設計になっていいのではなかろうかと思った。ORマッパー側から考えたほうがER図が綺麗になりそうだし。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
ミック
翔泳社 / 2520円 ( 2008-02-07 )

SQLAlchemy / Drkcore

SQLAlchemyで大文字小文字の違いを無視したLIKE検索を行う場合

SQLAlchemyでORA-01795を避ける

Pythonで今週がいつからいつまでかを調べる

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (5)

reStructuredTextをHTMLにコンバートする

テンプレートを修正する

クリックで書き換えられるようにする

まとめ

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (4)

POSTメソッドでデータを変更できるようにする

HTTP経由でデータの追加更新をテストする

まとめ

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (3)

Flaskでモデルを使う

まとめ

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (2)

SQLAlchemyを使う

データベース初期化

まとめ

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (1)

初めてのFlaskアプリ

テンプレートの導入

まとめ

補足

FlaskとSQLAlchemyでつくるreStructuredTextなWiki (0)

仮想環境構築のためのライブラリを導入

仮想環境を構築

パッケージのインストール

まとめ

補足

pipの使い方

virtualenvwrapperの使い方

最近覚えたSQLAlchemyのTips

__table__と__tablename__の違い

複合プライマリーキーでのone-to-many

そもそも主キーの無いテーブル

その他

tableとtablenameの違い