glnagano-python peewee

https://glnagano.connpass.com/event/94198/

Transcript

1. 2018/7/28 ⻑野Python会 with NSEG peewee by ABE Hiroki aka hATrayflood
2. 2018/7/28 ⻑野Python会 with NSEG お前 誰︖ by ABE Hiroki aka hATrayflood
3. 2018/7/28 ⻑野Python会 with NSEG ⾃⼰紹介 ・名前︓アベヒロキ (@hATrayflood) ・職業︓⾃宅サーバ管理者 ・URL︓rayflood.org/diary-temp/ ・地元︓東部町 by ABE Hiroki aka hATrayflood
4. 2018/7/28 ⻑野Python会 with NSEG django 2.0 リリース by ABE Hiroki aka hATrayflood
5. 2018/7/28 ⻑野Python会 with NSEG おめでとう ございます by ABE Hiroki aka hATrayflood
6. 2018/7/28 ⻑野Python会 with NSEG 前回の話 nseg100 pythonでwebサーバ speakerdeck.com/h_rayflood /nseg100-pythondewebsaba by ABE Hiroki aka hATrayflood
7. 2018/7/28 ⻑野Python会 with NSEG peewee by ABE Hiroki aka hATrayflood
8. 2018/7/28 ⻑野Python会 with NSEG って 何︖ by ABE Hiroki aka hATrayflood
9. 2018/7/28 ⻑野Python会 with NSEG O/Rマッパー by ABE Hiroki aka hATrayflood
10. 2018/7/28 ⻑野Python会 with NSEG 対応DB by ABE Hiroki aka hATrayflood
11. 2018/7/28 ⻑野Python会 with NSEG SQLite MySQL Postgres by ABE Hiroki aka hATrayflood
12. 2018/7/28 ⻑野Python会 with NSEG Oracle SQL Server などは不可 by ABE Hiroki aka hATrayflood
13. 2018/7/28 ⻑野Python会 with NSEG インストール by ABE Hiroki aka hATrayflood
14. 2018/7/28 ⻑野Python会 with NSEG pip3 install peewee by ABE Hiroki aka hATrayflood
15. 2018/7/28 ⻑野Python会 with NSEG 使い⽅ by ABE Hiroki aka hATrayflood
16. 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
17. 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
18. 2018/7/28 ⻑野Python会 with NSEG user = User(name="hATrayflood", \ email="h.rayflood@gmail.com") user.save() user = User.get(User.email==\ "h.rayflood@gmail.com") user.name = "h.rayflood" user.save() by ABE Hiroki aka hATrayflood
19. 2018/7/28 ⻑野Python会 with NSEG users = [] users.append({"name":"hATrayflood", \ "email":"h.rayflood@gmail.com"}) with db.manual_commit(): db.begin() try: User.insert_many(users).execute() db.commit() except: db.rollback() by ABE Hiroki aka hATrayflood
20. 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
21. 2018/7/28 ⻑野Python会 with NSEG データ型 by ABE Hiroki aka hATrayflood
22. 2018/7/28 ⻑野Python会 with NSEG IntegerField BigIntegerField SmallIntegerField AutoField BigAutoField FloatField DoubleField DecimalField by ABE Hiroki aka hATrayflood
23. 2018/7/28 ⻑野Python会 with NSEG CharField FixedCharField TextField BlobField BitField BigBitField UUIDField BinaryUUIDField by ABE Hiroki aka hATrayflood
24. 2018/7/28 ⻑野Python会 with NSEG DateTimeField DateField TimeField TimestampField IPField BooleanField BareField ForeignKeyField by ABE Hiroki aka hATrayflood
25. 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
26. 2018/7/28 ⻑野Python会 with NSEG WHERE句 演算⼦ by ABE Hiroki aka hATrayflood
27. 2018/7/28 ⻑野Python会 with NSEG & | == != < <= > >= << >> % ** ^ ~ by ABE Hiroki aka hATrayflood
28. 2018/7/28 ⻑野Python会 with NSEG docs.peewee-orm.com/en/latest /peewee/query_operators.html ⾒慣れないの ありますね。。 by ABE Hiroki aka hATrayflood
29. 2018/7/28 ⻑野Python会 with NSEG << は IN by ABE Hiroki aka hATrayflood
30. 2018/7/28 ⻑野Python会 with NSEG User.select().where(User.name<<["hoge", "fuga"]) # WHERE name IN ("hoge", "fuga") by ABE Hiroki aka hATrayflood
31. 2018/7/28 ⻑野Python会 with NSEG >> は IS by ABE Hiroki aka hATrayflood
32. 2018/7/28 ⻑野Python会 with NSEG User.select().where(User.name>>None) # WHERE name IS NULL by ABE Hiroki aka hATrayflood
33. 2018/7/28 ⻑野Python会 with NSEG % は LIKE by ABE Hiroki aka hATrayflood
34. 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
35. 2018/7/28 ⻑野Python会 with NSEG ** は ILIKE by ABE Hiroki aka hATrayflood
36. 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
37. 2018/7/28 ⻑野Python会 with NSEG ^ は XOR ~ は NOT by ABE Hiroki aka hATrayflood
38. 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
39. 2018/7/28 ⻑野Python会 with NSEG (XORは⽤途が思いつかない) by ABE Hiroki aka hATrayflood
40. 2018/7/28 ⻑野Python会 with NSEG ⼀対多 by ABE Hiroki aka hATrayflood
41. 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
42. 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
43. 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
44. 2018/7/28 ⻑野Python会 with NSEG 多対多 by ABE Hiroki aka hATrayflood
45. 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
46. 2018/7/28 ⻑野Python会 with NSEG 完全⾃動ではないものの 中間テーブルを⽣成できる Post.tags.get_through_model() by ABE Hiroki aka hATrayflood
47. 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
48. 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
49. 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
50. 2018/7/28 ⻑野Python会 with NSEG posts = Post.select().where(Post.tags==tag1) posts.count() by ABE Hiroki aka hATrayflood
51. 2018/7/28 ⻑野Python会 with NSEG File "test.py", line 81, in <module> 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
52. 2018/7/28 ⻑野Python会 with NSEG ManyToManyField専⽤の ショートカットクエリはないので テーブル結合を⾃⼒でがんばれ by ABE Hiroki aka hATrayflood
53. 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
54. 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
55. 2018/7/28 ⻑野Python会 with NSEG web で使う by ABE Hiroki aka hATrayflood
56. 2018/7/28 ⻑野Python会 with NSEG おさらい by ABE Hiroki aka hATrayflood
57. 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
58. 2018/7/28 ⻑野Python会 with NSEG とにかく こいつが やっかい by ABE Hiroki aka hATrayflood
59. 2018/7/28 ⻑野Python会 with NSEG なんでモデルの定義で コネクション必要なのか これがわからない by ABE Hiroki aka hATrayflood
60. 2018/7/28 ⻑野Python会 with NSEG webならリクエスト時 に接続して切断 または起動時に数本 プールしたい by ABE Hiroki aka hATrayflood
61. 2018/7/28 ⻑野Python会 with NSEG やり⽅ by ABE Hiroki aka hATrayflood
62. 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
63. 2018/7/28 ⻑野Python会 with NSEG 実は接続情報空のまま インスタンス⽣成できる by ABE Hiroki aka hATrayflood
64. 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
65. 2018/7/28 ⻑野Python会 with NSEG web起動時に接続情報を設定し リクエスト前後に接続・切断を 差し込める by ABE Hiroki aka hATrayflood
66. 2018/7/28 ⻑野Python会 with NSEG できること はわかった けどこれ by ABE Hiroki aka hATrayflood
67. 2018/7/28 ⻑野Python会 with NSEG メドイ by ABE Hiroki aka hATrayflood
68. 2018/7/28 ⻑野Python会 with NSEG もっと簡単に やれない︖ by ABE Hiroki aka hATrayflood
69. 2018/7/28 ⻑野Python会 with NSEG できる by ABE Hiroki aka hATrayflood
70. 2018/7/28 ⻑野Python会 with NSEG そう、 flaskならね by ABE Hiroki aka hATrayflood
71. 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
72. 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
73. 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
74. 2018/7/28 ⻑野Python会 with NSEG playhouseのFlaskDBが まとめて⾯倒⾒ます by ABE Hiroki aka hATrayflood
75. 2018/7/28 ⻑野Python会 with NSEG しかもplayhouseは peewee標準添付 by ABE Hiroki aka hATrayflood
76. 2018/7/28 ⻑野Python会 with NSEG 使うしかない︕ このflask_utilsを︕ (aary by ABE Hiroki aka hATrayflood
77. 2018/7/28 ⻑野Python会 with NSEG (bottleでも似たようなことができる、らしい) Bottle Peewee github.com/klen/bottle-peewee by ABE Hiroki aka hATrayflood
78. 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
79. 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