glnagano-python peewee

glnagano-python peewee

D05b3b2abff3f73f249a01d60257bdde?s=128

hATrayflood

July 28, 2018
Tweet

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