はじめに

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