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

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

Start with the get_or_create path in tortoise/models.py around lines 1060-1065, then reproduce the failure using the linked minimal example. Trace the naive and timezone-aware datetime queries through the SQLite backend in tortoise/backends/sqlite/client.py; done should mean the expected behavior is established and covered for the UNIQUE-constraint case.