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.
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.
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)
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.
/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
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.
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)
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