Share
Embed
Slide 1
Slide 1 text
2018/7/28 ⻑野Python会 with NSEG
peewee
by ABE Hiroki aka hATrayflood
Slide 2
Slide 2 text
2018/7/28 ⻑野Python会 with NSEG
お前
誰︖
by ABE Hiroki aka hATrayflood
Slide 3
Slide 3 text
2018/7/28 ⻑野Python会 with NSEG
⾃⼰紹介
・名前︓アベヒロキ (@hATrayflood)
・職業︓⾃宅サーバ管理者
・URL︓rayflood.org/diary-temp/
・地元︓東部町
by ABE Hiroki aka hATrayflood
Slide 4
Slide 4 text
2018/7/28 ⻑野Python会 with NSEG
django 2.0
リリース
by ABE Hiroki aka hATrayflood
Slide 5
Slide 5 text
2018/7/28 ⻑野Python会 with NSEG
おめでとう
ございます
by ABE Hiroki aka hATrayflood
Slide 6
Slide 6 text
2018/7/28 ⻑野Python会 with NSEG
前回の話
nseg100 pythonでwebサーバ
speakerdeck.com/h_rayflood
/nseg100-pythondewebsaba
by ABE Hiroki aka hATrayflood
Slide 7
Slide 7 text
2018/7/28 ⻑野Python会 with NSEG
peewee
by ABE Hiroki aka hATrayflood
Slide 8
Slide 8 text
2018/7/28 ⻑野Python会 with NSEG
って
何︖
by ABE Hiroki aka hATrayflood
Slide 9
Slide 9 text
2018/7/28 ⻑野Python会 with NSEG
O/Rマッパー
by ABE Hiroki aka hATrayflood
Slide 10
Slide 10 text
2018/7/28 ⻑野Python会 with NSEG
対応DB
by ABE Hiroki aka hATrayflood
Slide 11
Slide 11 text
2018/7/28 ⻑野Python会 with NSEG
SQLite
MySQL
Postgres
by ABE Hiroki aka hATrayflood
Slide 12
Slide 12 text
2018/7/28 ⻑野Python会 with NSEG
Oracle
SQL Server
などは不可
by ABE Hiroki aka hATrayflood
Slide 13
Slide 13 text
2018/7/28 ⻑野Python会 with NSEG
インストール
by ABE Hiroki aka hATrayflood
Slide 14
Slide 14 text
2018/7/28 ⻑野Python会 with NSEG
pip3 install peewee
by ABE Hiroki aka hATrayflood
Slide 15
Slide 15 text
2018/7/28 ⻑野Python会 with NSEG
使い⽅
by ABE Hiroki aka hATrayflood
Slide 16
Slide 16 text
2018/7/28 ⻑野Python会 with NSEG
from peewee import *
db = SqliteDatabase("test.db")
class User(Model):
name = CharField()
email = CharField()
class Meta:
database = db
db.create_tables([User])
by ABE Hiroki aka hATrayflood
Slide 17
Slide 17 text
2018/7/28 ⻑野Python会 with NSEG
CREATE TABLE "user"
("id" INTEGER NOT NULL PRIMARY KEY,
"name" VARCHAR(255) NOT NULL,
"email" VARCHAR(255) NOT NULL)
idは⾃動で定義されるAUTOINCREMENT型
※sqliteではintegerとprimary keyの
組み合わせでも機能する
by ABE Hiroki aka hATrayflood
Slide 18
Slide 18 text
2018/7/28 ⻑野Python会 with NSEG
user = User(name="hATrayflood", \
email="h.rayfl[email protected]")
user.save()
user = User.get(User.email==\
"h.rayfl[email protected]")
user.name = "h.rayflood"
user.save()
by ABE Hiroki aka hATrayflood
Slide 19
Slide 19 text
2018/7/28 ⻑野Python会 with NSEG
users = []
users.append({"name":"hATrayflood", \
"email":"h.rayfl[email protected]"})
with db.manual_commit():
db.begin()
try:
User.insert_many(users).execute()
db.commit()
except:
db.rollback()
by ABE Hiroki aka hATrayflood
Slide 20
Slide 20 text
2018/7/28 ⻑野Python会 with NSEG
トランザクションもできる
ハッシュのリストで⼀括投⼊も可能
さらに簡単に
with db.atomic():
User.insert_many(users).execute()
for row in db.batch_commit(users, 100):
User.create(**row)
by ABE Hiroki aka hATrayflood
Slide 21
Slide 21 text
2018/7/28 ⻑野Python会 with NSEG
データ型
by ABE Hiroki aka hATrayflood
Slide 22
Slide 22 text
2018/7/28 ⻑野Python会 with NSEG
IntegerField
BigIntegerField
SmallIntegerField
AutoField
BigAutoField
FloatField
DoubleField
DecimalField
by ABE Hiroki aka hATrayflood
Slide 23
Slide 23 text
2018/7/28 ⻑野Python会 with NSEG
CharField
FixedCharField
TextField
BlobField
BitField
BigBitField
UUIDField
BinaryUUIDField
by ABE Hiroki aka hATrayflood
Slide 24
Slide 24 text
2018/7/28 ⻑野Python会 with NSEG
DateTimeField
DateField
TimeField
TimestampField
IPField
BooleanField
BareField
ForeignKeyField
by ABE Hiroki aka hATrayflood
Slide 25
Slide 25 text
2018/7/28 ⻑野Python会 with NSEG
だいたい使える
docs.peewee-orm.com/en/latest/peewee
/models.html#field-types-table
null, unique, collationなども指定可能
docs.peewee-orm.com/en/latest/peewee
/models.html#field-initialization-arguments
by ABE Hiroki aka hATrayflood
Slide 26
Slide 26 text
2018/7/28 ⻑野Python会 with NSEG
WHERE句
演算⼦
by ABE Hiroki aka hATrayflood
Slide 27
Slide 27 text
2018/7/28 ⻑野Python会 with NSEG
& | == !=
< <= > >=
<< >> % **
^ ~
by ABE Hiroki aka hATrayflood
Slide 28
Slide 28 text
2018/7/28 ⻑野Python会 with NSEG
docs.peewee-orm.com/en/latest
/peewee/query_operators.html
⾒慣れないの
ありますね。。
by ABE Hiroki aka hATrayflood
Slide 29
Slide 29 text
2018/7/28 ⻑野Python会 with NSEG
<< は IN
by ABE Hiroki aka hATrayflood
Slide 30
Slide 30 text
2018/7/28 ⻑野Python会 with NSEG
User.select().where(User.name<<["hoge", "fuga"])
# WHERE name IN ("hoge", "fuga")
by ABE Hiroki aka hATrayflood
Slide 31
Slide 31 text
2018/7/28 ⻑野Python会 with NSEG
>> は IS
by ABE Hiroki aka hATrayflood
Slide 32
Slide 32 text
2018/7/28 ⻑野Python会 with NSEG
User.select().where(User.name>>None)
# WHERE name IS NULL
by ABE Hiroki aka hATrayflood
Slide 33
Slide 33 text
2018/7/28 ⻑野Python会 with NSEG
% は LIKE
by ABE Hiroki aka hATrayflood
Slide 34
Slide 34 text
2018/7/28 ⻑野Python会 with NSEG
User.get(User.name%"%ray%")
# WHERE name LIKE "%ray%"
# mysql
# WHERE name LIKE BINARY "%ray%"
by ABE Hiroki aka hATrayflood
Slide 35
Slide 35 text
2018/7/28 ⻑野Python会 with NSEG
** は ILIKE
by ABE Hiroki aka hATrayflood
Slide 36
Slide 36 text
2018/7/28 ⻑野Python会 with NSEG
User.get(User.name**"%ray%")
# WHERE name ILIKE "%ray%"
# mysql
# WHERE name LIKE "%ray%"
by ABE Hiroki aka hATrayflood
Slide 37
Slide 37 text
2018/7/28 ⻑野Python会 with NSEG
^ は XOR
~ は NOT
by ABE Hiroki aka hATrayflood
Slide 38
Slide 38 text
2018/7/28 ⻑野Python会 with NSEG
User.select().where(~(User.name<<["hoge", "fuga"]))
# WHERE NOT (name IN ("hoge", "fuga"))
User.select().where(~(User.name>>None))
# WHERE NOT (name IS NULL)
by ABE Hiroki aka hATrayflood
Slide 39
Slide 39 text
2018/7/28 ⻑野Python会 with NSEG
(XORは⽤途が思いつかない)
by ABE Hiroki aka hATrayflood
Slide 40
Slide 40 text
2018/7/28 ⻑野Python会 with NSEG
⼀対多
by ABE Hiroki aka hATrayflood
Slide 41
Slide 41 text
2018/7/28 ⻑野Python会 with NSEG
class Post(Model):
user = ForeignKeyField(User)
text = TextField()
posted_at = TimestampField()
class Meta:
database = db
db.create_tables([Post])
by ABE Hiroki aka hATrayflood
Slide 42
Slide 42 text
2018/7/28 ⻑野Python会 with NSEG
CREATE TABLE "post"
("id" INTEGER NOT NULL PRIMARY KEY,
"user_id" INTEGER NOT NULL,
"text" TEXT NOT NULL,
"posted_at" INTEGER NOT NULL,
FOREIGN KEY ("user_id")
REFERENCES "user" ("id"))
外部キー制約まで⾃動⽣成する
by ABE Hiroki aka hATrayflood
Slide 43
Slide 43 text
2018/7/28 ⻑野Python会 with NSEG
post = Post(user=user, text="hoge hoge")
post.posted_at = datetime.datetime.now()
post.save()
posts = Post.select().where(Post.user==\
user).order_by(Post.posted_at.desc())
posts.count()
by ABE Hiroki aka hATrayflood
Slide 44
Slide 44 text
2018/7/28 ⻑野Python会 with NSEG
多対多
by ABE Hiroki aka hATrayflood
Slide 45
Slide 45 text
2018/7/28 ⻑野Python会 with NSEG
class Tag(Model):
label = TextField()
class Meta:
database = db
class Post(Model):
user = ForeignKeyField(User, backref="posts")
text = TextField()
posted_at = TimestampField()
tags = ManyToManyField(Tag, backref="posts")
class Meta:
database = db
db.create_tables([Tag, Post, \
Post.tags.get_through_model()])
by ABE Hiroki aka hATrayflood
Slide 46
Slide 46 text
2018/7/28 ⻑野Python会 with NSEG
完全⾃動ではないものの
中間テーブルを⽣成できる
Post.tags.get_through_model()
by ABE Hiroki aka hATrayflood
Slide 47
Slide 47 text
2018/7/28 ⻑野Python会 with NSEG
CREATE TABLE "post_tag_through"
("id" INTEGER NOT NULL PRIMARY KEY,
"post_id" INTEGER NOT NULL,
"tag_id" INTEGER NOT NULL,
FOREIGN KEY ("post_id")
REFERENCES "post" ("id"),
FOREIGN KEY ("tag_id")
REFERENCES "tag" ("id"))
by ABE Hiroki aka hATrayflood
Slide 48
Slide 48 text
2018/7/28 ⻑野Python会 with NSEG
tag1 = Tag(label="python")
tag1.save()
tag2 = Tag(label="peewee")
tag2.save()
post.tags.add(tag1)
post.tags.add([tag2])
by ABE Hiroki aka hATrayflood
Slide 49
Slide 49 text
2018/7/28 ⻑野Python会 with NSEG
post.tags.remove(tag1)
post.tags.remove([tag2])
post.tags.clear()
add()もremove()も
事前に要save()
by ABE Hiroki aka hATrayflood
Slide 50
Slide 50 text
2018/7/28 ⻑野Python会 with NSEG
posts = Post.select().where(Post.tags==tag1)
posts.count()
by ABE Hiroki aka hATrayflood
Slide 51
Slide 51 text
2018/7/28 ⻑野Python会 with NSEG
File "test.py", line 81, in
posts.count()
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1574, in inner
return method(self, database, *args, **kwargs)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1829, in count
return Select([clone], [fn.COUNT(SQL('1'))]).scalar(database)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1574, in inner
return method(self, database, *args, **kwargs)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1815, in scalar
row = self.tuples().peek(database)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1574, in inner
return method(self, database, *args, **kwargs)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1802, in peek
rows = self.execute(database)[:n]
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1574, in inner
return method(self, database, *args, **kwargs)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1645, in execute
return self._execute(database)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 1796, in _execute
cursor = database.execute(self)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 2653, in execute
return self.execute_sql(sql, params, commit=commit)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 2647, in execute_sql
self.commit()
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 2438, in __exit__
reraise(new_type, new_type(*exc_args), traceback)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 177, in reraise
raise value.with_traceback(tb)
File "c:\Program Files\Python36\lib\site-packages\peewee.py", line 2640, in execute_sql
cursor.execute(sql, params or ())
peewee.OperationalError: no such column: t1.tags
by ABE Hiroki aka hATrayflood
Slide 52
Slide 52 text
2018/7/28 ⻑野Python会 with NSEG
ManyToManyField専⽤の
ショートカットクエリはないので
テーブル結合を⾃⼒でがんばれ
by ABE Hiroki aka hATrayflood
Slide 53
Slide 53 text
2018/7/28 ⻑野Python会 with NSEG
query = Post.select()
thru = Post.tags.get_through_model()
alias1 = thru.alias()
query = query.switch(Post).join(alias1)\
.where(alias1.tag==tag1)
alias2 = thru.alias()
query = query.switch(Post).join(alias2)\
.where(alias2.tag==tag2)
query.sql()
by ABE Hiroki aka hATrayflood
Slide 54
Slide 54 text
2018/7/28 ⻑野Python会 with NSEG
('SELECT "t1"."id", "t1"."user_id",
"t1"."text", "t1"."posted_at"
FROM "post" AS "t1"
INNER JOIN "post_tag_through" AS "t2"
ON ("t2"."post_id" = "t1"."id")
INNER JOIN "post_tag_through" AS "t3"
ON ("t3"."post_id" = "t1"."id")
WHERE (("t2"."tag_id" = ?)
AND ("t3"."tag_id" = ?))', [1, 2])
by ABE Hiroki aka hATrayflood
Slide 55
Slide 55 text
2018/7/28 ⻑野Python会 with NSEG
web
で使う
by ABE Hiroki aka hATrayflood
Slide 56
Slide 56 text
2018/7/28 ⻑野Python会 with NSEG
おさらい
by ABE Hiroki aka hATrayflood
Slide 57
Slide 57 text
2018/7/28 ⻑野Python会 with NSEG
db = SqliteDatabase("test.db")
class User(Model):
name = CharField()
email = CharField()
class Meta:
database = db
by ABE Hiroki aka hATrayflood
Slide 58
Slide 58 text
2018/7/28 ⻑野Python会 with NSEG
とにかく
こいつが
やっかい
by ABE Hiroki aka hATrayflood
Slide 59
Slide 59 text
2018/7/28 ⻑野Python会 with NSEG
なんでモデルの定義で
コネクション必要なのか
これがわからない
by ABE Hiroki aka hATrayflood
Slide 60
Slide 60 text
2018/7/28 ⻑野Python会 with NSEG
webならリクエスト時
に接続して切断
または起動時に数本
プールしたい
by ABE Hiroki aka hATrayflood
Slide 61
Slide 61 text
2018/7/28 ⻑野Python会 with NSEG
やり⽅
by ABE Hiroki aka hATrayflood
Slide 62
Slide 62 text
2018/7/28 ⻑野Python会 with NSEG
models.py:
from peewee import *
db = MySQLDatabase(None)
class BaseModel(Model):
class Meta:
database = db
class User(BaseModel):
name = CharField()
email = CharField()
by ABE Hiroki aka hATrayflood
Slide 63
Slide 63 text
2018/7/28 ⻑野Python会 with NSEG
実は接続情報空のまま
インスタンス⽣成できる
by ABE Hiroki aka hATrayflood
Slide 64
Slide 64 text
2018/7/28 ⻑野Python会 with NSEG
web.py:
from bottle import *
from models import *
app = Bottle()
db.init("glnagano_python", host="localhost", port=3306, \
user="glnagano_python", password="glnagano_python")
@app.hook("before_request")
def before_request():
db.connect()
@app.hook("after_request")
def after_request():
db.close()
@app.route('/')
def index():
return "%d users exists" % User.select().count()
run(app, host='0.0.0.0', debug=True)
by ABE Hiroki aka hATrayflood
Slide 65
Slide 65 text
2018/7/28 ⻑野Python会 with NSEG
web起動時に接続情報を設定し
リクエスト前後に接続・切断を
差し込める
by ABE Hiroki aka hATrayflood
Slide 66
Slide 66 text
2018/7/28 ⻑野Python会 with NSEG
できること
はわかった
けどこれ
by ABE Hiroki aka hATrayflood
Slide 67
Slide 67 text
2018/7/28 ⻑野Python会 with NSEG
メドイ
by ABE Hiroki aka hATrayflood
Slide 68
Slide 68 text
2018/7/28 ⻑野Python会 with NSEG
もっと簡単に
やれない︖
by ABE Hiroki aka hATrayflood
Slide 69
Slide 69 text
2018/7/28 ⻑野Python会 with NSEG
できる
by ABE Hiroki aka hATrayflood
Slide 70
Slide 70 text
2018/7/28 ⻑野Python会 with NSEG
そう、
flaskならね
by ABE Hiroki aka hATrayflood
Slide 71
Slide 71 text
2018/7/28 ⻑野Python会 with NSEG
models_flask.py:
from peewee import *
from playhouse.flask_utils import *
db = FlaskDB()
class User(db.Model):
name = CharField()
email = CharField()
by ABE Hiroki aka hATrayflood
Slide 72
Slide 72 text
2018/7/28 ⻑野Python会 with NSEG
web_flask.py:
from flask import *
from models_flask import *
app = Flask(__name__)
app.config.from_json("config.json")
db.init_app(app)
@app.route('/')
def index():
return "%d users exists" % User.select().count()
app.debug = True
app.run(host="0.0.0.0")
by ABE Hiroki aka hATrayflood
Slide 73
Slide 73 text
2018/7/28 ⻑野Python会 with NSEG
config.json:
{
"DATABASE":{
"engine":"MySQLDatabase",
"name":"glnagano_python",
"host":"localhost",
"port":3306,
"user":"glnagano_python",
"password":"glnagano_python",
"charset":"utf8mb4"}
}
by ABE Hiroki aka hATrayflood
Slide 74
Slide 74 text
2018/7/28 ⻑野Python会 with NSEG
playhouseのFlaskDBが
まとめて⾯倒⾒ます
by ABE Hiroki aka hATrayflood
Slide 75
Slide 75 text
2018/7/28 ⻑野Python会 with NSEG
しかもplayhouseは
peewee標準添付
by ABE Hiroki aka hATrayflood
Slide 76
Slide 76 text
2018/7/28 ⻑野Python会 with NSEG
使うしかない︕
このflask_utilsを︕
(aary
by ABE Hiroki aka hATrayflood
Slide 77
Slide 77 text
2018/7/28 ⻑野Python会 with NSEG
(bottleでも似たようなことができる、らしい)
Bottle Peewee
github.com/klen/bottle-peewee
by ABE Hiroki aka hATrayflood
Slide 78
Slide 78 text
2018/7/28 ⻑野Python会 with NSEG
・リンク集
twitter @hATrayflood
http://twitter.com/hATrayflood
github coleifer/peewee
https://github.com/coleifer/peewee
peewee 3.6.4 documentation
http://docs.peewee-orm.com/en/latest/
SQLiteの⾃動インクリメント - しょぼんメモリ （´・ω・｀）
https://shobon.hatenablog.com/entry/2014/03/30/210444
by ABE Hiroki aka hATrayflood
Slide 79
Slide 79 text
2018/7/28 ⻑野Python会 with NSEG
Field types table
http://docs.peewee-orm.com/en/latest/peewee/models.html#field-types-table
Field initialization arguments
http://docs.peewee-orm.com/en/latest/peewee/models.html#field-initialization-arguments
Query operators
http://docs.peewee-orm.com/en/latest/peewee/query_operators.html
Flask Utils
http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#flask-utils
Bottle Peewee
https://github.com/klen/bottle-peewee
by ABE Hiroki aka hATrayflood