Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@cnaos

PythonのpeeweeのModel.get_or_create()の挙動について

Posted at

はじめに

PythonのpeeweeというORMを使っていて、すでにレコードが存在する場合は無視、レコードが存在しない場合だけINSERTするためにModel.get_or_create()というメソッドを使ったところ、少しつまづいたのでメモを残しておきます。

マニュアルをちゃんと読めば書いてあったんですけどね。

動作を確認した環境

  • Pyton 3.7
  • peewee 3.14.0

やろうとしたこと

device_idとpageの複合ユニークキーを持つテーブルを作って、
レコードが存在しない場合はINSERT、
レコードが存在する場合は無視するようにしたかった。

そのためにget_or_create()というメソッドを使った。

Table.get_or_create(device_id=1, page=1, data="AAA")
はINSERTされて、
Table.get_or_create(device_id=1, page=1, data="BBB")
これは無視されるはず。

コード全体

import logging.config

import yaml
from peewee import *
from playhouse.shortcuts import model_to_dict

logger = logging.getLogger("main")
db = SqliteDatabase(':memory:')


class Table(Model):
    device_id = IntegerField()
    page = IntegerField()
    data = CharField()

    class Meta:
        indexes = (
            (('device_id', 'page'), True),
        )
        database = db


if __name__ == '__main__':
    logging_conf_yaml = open('logging_conf.yaml', 'r', encoding='utf-8').read()
    logging.config.dictConfig(yaml.safe_load(logging_conf_yaml))

    Table.create_table()
    try:
        # こっちはレコードが作成されて、
        result1_get, result1_created = Table.get_or_create(device_id=1, page=1, data="AAA")
        logger.info(f"result1_get={model_to_dict(result1_get)}, result1_created={result1_created}")

        # こっちは無視されるはず
        result2_get, result2_created = Table.get_or_create(device_id=1, page=1, data="BBB")
        logger.info(f"result2_get={model_to_dict(result2_get)}, result2_created={result2_created}")

    except DatabaseError as e:
        logger.exception(e)

結果

最初のINSERTは成功。
次のINSERTの無視は失敗。

原因はこの部分のクエリを見ると分かる。

2020-12-30 07:27:29,924  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE ((("t1"."device_id" = ?) AND ("t1"."page" = ?)) AND ("t1"."data" = ?)) LIMIT ? OFFSET ?', [1, 1, 'BBB', 1, 0])
2020-12-30 07:27:29,924  peewee  DEBUG ('BEGIN', None)
2020-12-30 07:27:29,925  peewee  DEBUG ('INSERT INTO "table" ("device_id", "page", "data") VALUES (?, ?, ?)', [1, 1, 'BBB'])
2020-12-30 07:27:29,925  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE ((("t1"."device_id" = ?) AND ("t1"."page" = ?)) AND ("t1"."data" = ?)) LIMIT ? OFFSET ?', [1, 1, 'BBB', 1, 0])
2020-12-30 07:27:29,925  main  ERROR UNIQUE constraint failed: table.device_id, table.page

ユニークキーの他にデータとして処理したいカラムも検索条件に入っているため、
レコードが存在しないと判定され、INSERTしようとして失敗したのだ。

詳細なエラーログ
/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/bin/python /Users/cnaos/workbench-experiment/peewee-get_or_create/main2.py
2020-12-30 07:27:29,921  peewee  DEBUG ('CREATE TABLE IF NOT EXISTS "table" ("id" INTEGER NOT NULL PRIMARY KEY, "device_id" INTEGER NOT NULL, "page" INTEGER NOT NULL, "data" VARCHAR(255) NOT NULL)', [])
2020-12-30 07:27:29,922  peewee  DEBUG ('CREATE UNIQUE INDEX IF NOT EXISTS "table_device_id_page" ON "table" ("device_id", "page")', [])
2020-12-30 07:27:29,923  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE ((("t1"."device_id" = ?) AND ("t1"."page" = ?)) AND ("t1"."data" = ?)) LIMIT ? OFFSET ?', [1, 1, 'AAA', 1, 0])
2020-12-30 07:27:29,923  peewee  DEBUG ('BEGIN', None)
2020-12-30 07:27:29,923  peewee  DEBUG ('INSERT INTO "table" ("device_id", "page", "data") VALUES (?, ?, ?)', [1, 1, 'AAA'])
2020-12-30 07:27:29,924  main  INFO result1_get={'id': 1, 'device_id': 1, 'page': 1, 'data': 'AAA'}, result1_created=True
2020-12-30 07:27:29,924  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE ((("t1"."device_id" = ?) AND ("t1"."page" = ?)) AND ("t1"."data" = ?)) LIMIT ? OFFSET ?', [1, 1, 'BBB', 1, 0])
2020-12-30 07:27:29,924  peewee  DEBUG ('BEGIN', None)
2020-12-30 07:27:29,925  peewee  DEBUG ('INSERT INTO "table" ("device_id", "page", "data") VALUES (?, ?, ?)', [1, 1, 'BBB'])
2020-12-30 07:27:29,925  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE ((("t1"."device_id" = ?) AND ("t1"."page" = ?)) AND ("t1"."data" = ?)) LIMIT ? OFFSET ?', [1, 1, 'BBB', 1, 0])
2020-12-30 07:27:29,925  main  ERROR UNIQUE constraint failed: table.device_id, table.page
Traceback (most recent call last):
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6860, in get
    return clone.execute(database)[0]
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 4258, in __getitem__
    return self.row_cache[item]
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6472, in get_or_create
    return query.get(), False
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6865, in get
    (clone.model, sql, params))
TableDoesNotExist: <Model: Table> instance matching query does not exist:
SQL: SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE ((("t1"."device_id" = ?) AND ("t1"."page" = ?)) AND ("t1"."data" = ?)) LIMIT ? OFFSET ?
Params: [1, 1, 'BBB', 1, 0]

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/cnaos/workbench-experiment/peewee-get_or_create/main2.py", line 34, in <module>
    result2_get, result2_created = Table.get_or_create(device_id=1, page=1, data="BBB")
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6474, in get_or_create
    raise exc
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6469, in get_or_create
    return cls.create(**kwargs), True
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6338, in create
    inst.save(force_insert=True)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 6548, in save
    pk = self.insert(**field_dict).execute()
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 1898, in inner
    return method(self, database, *args, **kwargs)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 1969, in execute
    return self._execute(database)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 2730, in _execute
    return super(Insert, self)._execute(database)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 2465, in _execute
    cursor = database.execute(self)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 3142, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 3136, in execute_sql
    self.commit()
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 2902, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/lib/python3.7/site-packages/peewee.py", line 3129, in execute_sql
    cursor.execute(sql, params or ())
peewee.IntegrityError: UNIQUE constraint failed: table.device_id, table.page

修正方法

複合ユニークキー以外のINSERT時に必要なデータはget_or_create()メソッドのdefaults引数でわたせば良い。

つまり、
Table.get_or_create(device_id=1, page=1, data="AAA")
と書いていたところを
Table.get_or_create(device_id=1, page=1, defaults={'data':"AAA"})
とすれば良い。

これはModel.get_or_create()の最後の方に以下のように書かれていた。

Any keyword argument passed to get_or_create() will be used in the get() portion of the logic, except for the defaults dictionary, which will be used to populate values on newly-created instances.

修正版のコード全体


import logging.config

import yaml
from peewee import *
from playhouse.shortcuts import model_to_dict

logger = logging.getLogger("main")
db = SqliteDatabase(':memory:')


class Table(Model):
    device_id = IntegerField()
    page = IntegerField()
    data = CharField()

    class Meta:
        indexes = (
            (('device_id', 'page'), True),
        )
        database = db


if __name__ == '__main__':
    logging_conf_yaml = open('logging_conf.yaml', 'r', encoding='utf-8').read()
    logging.config.dictConfig(yaml.safe_load(logging_conf_yaml))

    Table.create_table()

    try:
        result1_get, result1_created = Table.get_or_create(device_id=1, page=1, defaults={'data': "AAA"})
        logger.info(f"result1_get={model_to_dict(result1_get)}, result1_created={result1_created}")

        result2_get, result2_created = Table.get_or_create(device_id=1, page=1, defaults={'data': "BBB"})
        logger.info(f"result2_get={model_to_dict(result2_get)}, result2_created={result2_created}")

    except DatabaseError as e:
        logger.exception(e)

修正版の結果

意図通りになった。

/Users/cnaos/.local/share/virtualenvs/peewee-get_or_create-D9CO1zCP/bin/python /Users/cnaos/workbench-experiment/peewee-get_or_create/main3.py
2020-12-30 07:39:46,980  peewee  DEBUG ('CREATE TABLE IF NOT EXISTS "table" ("id" INTEGER NOT NULL PRIMARY KEY, "device_id" INTEGER NOT NULL, "page" INTEGER NOT NULL, "data" VARCHAR(255) NOT NULL)', [])
2020-12-30 07:39:46,982  peewee  DEBUG ('CREATE UNIQUE INDEX IF NOT EXISTS "table_device_id_page" ON "table" ("device_id", "page")', [])
2020-12-30 07:39:46,982  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE (("t1"."device_id" = ?) AND ("t1"."page" = ?)) LIMIT ? OFFSET ?', [1, 1, 1, 0])
2020-12-30 07:39:46,983  peewee  DEBUG ('BEGIN', None)
2020-12-30 07:39:46,983  peewee  DEBUG ('INSERT INTO "table" ("device_id", "page", "data") VALUES (?, ?, ?)', [1, 1, 'AAA'])
2020-12-30 07:39:46,983  main  INFO result1_get={'id': 1, 'device_id': 1, 'page': 1, 'data': 'AAA'}, result1_created=True
2020-12-30 07:39:46,984  peewee  DEBUG ('SELECT "t1"."id", "t1"."device_id", "t1"."page", "t1"."data" FROM "table" AS "t1" WHERE (("t1"."device_id" = ?) AND ("t1"."page" = ?)) LIMIT ? OFFSET ?', [1, 1, 1, 0])
2020-12-30 07:39:46,984  main  INFO result2_get={'id': 1, 'device_id': 1, 'page': 1, 'data': 'AAA'}, result2_created=False
2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?