search

tortoise / tortoise-orm

Familiar asyncio ORM for python, built with relations in mind
https://tortoise.github.io
Apache License 2.0
4.39k stars 357 forks source link

If datetimefield is not timezone-aware get_or_create fails UNIQUE constraint. #1556

Open mmkhitaryan opened 4 months ago

mmkhitaryan commented 4 months ago

Discussed in https://github.com/tortoise/tortoise-orm/discussions/1555

Originally posted by **mmkhitaryan** February 10, 2024 ```SELECT "sold_date","id","review","created_at","seller_id","buyer_id","purshare_type_id" FROM "purchase" WHERE "id"=94562 AND "sold_date"='2024-02-10 13:55:04' AND "review"='' AND "purshare_type_id"=1 AND "buyer_id"=2 AND "seller_id"=1 LIMIT 2``` This sql query got generated before I made the sold_date timezone.make_aware() ``` SELECT "seller_id","purshare_type_id","sold_date","created_at","buyer_id","id","review" FROM "purchase" WHERE "id"=93707 AND "sold_date"='2024-01-29 04:16:46+00:00' AND "review"='' AND "purshare_type_id"=2 AND "buyer_id"=20 AND "seller_id"=1 LIMIT 2: None ``` Before I made the datetime timezone-aware, get_or_none would fail with UNIQUE CONSTRAINT error. I want to understand, is it a bug or a feature? Because to me it sounds like a bug. IMHO tortoise should at least show warning message that the datetime field must be timezone-aware. Making the sold_date datetime field timezone-aware fixed the problem for me: https://github.com/mmkhitaryan/odinscraper/commit/cfd1f703d034cf9a478729f18ae8608db3ab75e8#diff-e544f072d0253f9a2c630fb985108e2f779a5d5db5e7eb0cb6319304756ceee3R107 I have implemented minimal reproducible example: https://gist.github.com/mmkhitaryan/bf46cb557cae421452ba938989005b70 When you run it, you get "successfully created" message but when the datetime is naive you get error below: ``` (odinscraper-py3.11) @mmkhitaryan ➜ /workspaces/odinscraper/src (master) $ python mrp.py successfully created Traceback (most recent call last): File "/home/codespace/.cache/pypoetry/virtualenvs/odinscraper-6YobV1su-py3.11/lib/python3.11/site-packages/tortoise/models.py", line 1060, in get_or_create await cls.select_for_update().filter(**kwargs).using_db(connection).get(), ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/codespace/.cache/pypoetry/virtualenvs/odinscraper-6YobV1su-py3.11/lib/python3.11/site-packages/tortoise/queryset.py", line 1020, in _execute raise DoesNotExist("Object does not exist") tortoise.exceptions.DoesNotExist: Object does not exist During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/home/codespace/.cache/pypoetry/virtualenvs/odinscraper-6YobV1su-py3.11/lib/python3.11/site-packages/tortoise/models.py", line 1065, in get_or_create return await cls.create(using_db=connection, **defaults, **kwargs), True ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/codespace/.cache/pypoetry/virtualenvs/odinscraper-6YobV1su-py3.11/lib/python3.11/site-packages/tortoise/backends/sqlite/client.py", line 38, in translate_exceptions_ raise IntegrityError(exc) tortoise.exceptions.IntegrityError: UNIQUE constraint failed: tortoises.name ```
mmkhitaryan commented 4 months ago

In my opinion this is a bug, because its unexpected that running get_or_create with the same params results in duplicate

waketzheng commented 2 weeks ago

It is better to show the content of mrp.py in this issue instead of put it into another link.