About the behavior of Model.get_or_create () of peewee in Python

Introduction

I'm using Python's peewee ORM and ignore it if it already exists, [Model.get_or_create ()](http://docs.peewee-orm.com/en/latest/peewee/querying.html # create-) to INSERT only if the record doesn't exist When I used the method called or-get), I stumbled a little, so I'll leave a note.

If I read the manual properly, it would have been written.

Environment where operation was confirmed

What I tried to do

Create a table with a compound unique key for device_id and page, INSERT, if the record does not exist I wanted to ignore the record if it existed.

For that, I used a method called get_or_create ().

Table.get_or_create(device_id=1, page=1, data="AAA") Is INSERTed, Table.get_or_create(device_id=1, page=1, data="BBB") This should be ignored.

Whole code

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:
        #This is a record created,
        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}")

        #This should be ignored
        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)

result

The first INSERT was successful. Ignoring the next INSERT fails.

The cause can be found by looking at the query in this part.

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

In addition to the unique key, the column you want to process as data is also included in the search conditions, so It was determined that the record did not exist, and the INSERT attempt failed.

Detailed error log
/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

How to fix

Data required for INSERT other than the compound unique key can be passed in the defaults argument of the get_or_create () method.

In other words Table.get_or_create(device_id=1, page=1, data="AAA") Where I was writing Table.get_or_create(device_id=1, page=1, defaults={'data':"AAA"}) It should be done.

This was written near the end of Model.get_or_create () as follows.

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.

The entire modified version of the code


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)

Modified version results

It was as intended.

/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

Recommended Posts

About the behavior of Model.get_or_create () of peewee in Python
[Note] About the role of underscore "_" in Python
About the features of Python
A reminder about the implementation of recommendations in Python
About the behavior of yield_per of SqlAlchemy
About the basics list of Python basics
Tank game made with python About the behavior of tanks
About the behavior of enable_backprop of Chainer v2
About the virtual environment of python version 3.7
The result of installing python in Anaconda
The basics of running NoxPlayer in Python
In search of the fastest FizzBuzz in Python
Output the number of CPU cores in Python
[Python] Sort the list of pathlib.Path in natural sort
Get the caller of a function in Python
Match the distribution of each group in Python
[python] behavior of argmax
the zen of Python
Make a copy of the list in Python
About the difference between "==" and "is" in python
A note about the python version of python virtualenv
Find the divisor of the value entered in python
Find the solution of the nth-order equation in python
The story of reading HSPICE data in Python
Solving the equation of motion in Python (odeint)
Output in the form of a python array
About __all__ in python
About the behavior of Queue during parallel processing
About the * (asterisk) argument of python (and itertools.starmap)
In Python, change the behavior of the method depending on how it is called
Experience the good calculation efficiency of vectorization in Python
Sort in Python. Next, let's think about the algorithm.
How to get the number of digits in Python
[python] Get the list of classes defined in the module
About testing in the implementation of machine learning models
The story of FileNotFound in Python open () mode ='w'
About the inefficiency of data transfer in luigi on-memory
Learn the design pattern "Chain of Responsibility" in Python
Implement the solution of Riccati algebraic equations in Python
Get the size (number of elements) of UnionFind in Python
Not being aware of the contents of the data in python
About the uncluttered arrangement in the import order of flake8
Reproduce the execution example of Chapter 4 of Hajipata in Python
Let's use the open data of "Mamebus" in Python
Implemented the algorithm of "Algorithm Picture Book" in Python3 (Heapsort)
[Python] Outputs all combinations of elements in the list
Get the URL of the HTTP redirect destination in Python
Reproduce the execution example of Chapter 5 of Hajipata in Python
To do the equivalent of Ruby's ObjectSpace._id2ref in Python
Check the asymptotic nature of the probability distribution in Python
Towards the retirement of Python2
Download the file in Python
Find the difference in Python
About the Python module venv
Think about architecture in python
About the enumerate function (python)
About various encodings of Python 3
Equivalence of objects in Python
About the components of Luigi
Implementation of quicksort in Python
About "for _ in range ():" in python