Can't insert value into TSRANGE column using SqlAlchemy-CodePudding

I'm a beginner in Python and SqlAlchemy altogether.

I have this table

import sqlalchemy
from sqlalchemy import BIGINT
from sqlalchemy.dialects.postgresql import UUID, TSRANGE

from application import metadata
from models.Staff import _staff_table_name

StaffAvailability = sqlalchemy.Table(
    "staff_availability",
    metadata,
    sqlalchemy.Column(
        "id",
        BIGINT,
        autoincrement=True,
        primary_key=True
    ),
    sqlalchemy.Column(
        "staff_id",
        UUID,
        sqlalchemy.ForeignKey(f"{_staff_table_name}.staff_id", ondelete="CASCADE"),
        nullable=False
    ),
    sqlalchemy.Column("during", TSRANGE(), nullable=True),
    sqlalchemy.Column("created_at", sqlalchemy.DateTime(timezone=True), nullable=True, server_default="now()"),
)

Now if I try to insert any values into this, it returns error

    staff_availability = StaffAvailability.insert()
    avail_id = await database.execute(staff_availability, values={
        "staff_id": kwargs.get("staff_id"),
        "during": DateTimeRange(datetime(2021, 3, 22, 10, 0, 0), datetime(2021, 3, 25, 10, 0, 0))
    })

I imported DateTimeRange from psycopg2

from psycopg2.extras import DateTimeRange

I've no idea why this is not working. I've tried other ways as well using text function

"during": text("'[2022-10-10 10:00, 2020-10-10 12:00)'")

where I imported the text function from sqlalchemy

from sqlalchemy import text

I also tried importing text function like this

from sqlalchemy.sql import text

This is the error that it generates when I try inserting using DateTimeRange

 result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute
  File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $2: DateTimeRange(datetime.datetime(2021, 3,... (list, tuple or Range object expected (got type <class 'psycopg2._range.DateTimeRange'>))

when I try to insert value using the text function, I get this error

File "/Users/koushik/Documents/projects/colorus81/env/lib/python3.8/site-packages/fastapi/encoders.py", line 144, in jsonable_encoder
    raise ValueError(errors)
ValueError: [TypeError("'Range' object is not iterable"), TypeError('vars() argument must have __dict__ attribute')]

CodePudding user response：

Try to use the type Range provided by the asyncpg driver:

from asyncpg.types import Range

during = Range(datetime(2021, 3, 22, 10, 0, 0), datetime(2021, 3, 25, 10, 0, 0))

CodePudding user response：

After trying out every kind of way. This is how I've solved it.

It's strange how this one worked.

from sqlalchemy.sql import text

   from_time_str = from_time.strftime("%Y-%m-%d %H:%M:%S")
    to_time_str = to_time.strftime("%Y-%m-%d %H:%M:%S")
    stmt = text(f"insert into staff_availability(staff_id, during) values (:staff_id, '[{from_time_str},{to_time_str})') returning id")
    stmt = stmt.bindparams(staff_id=kwargs.get('staff_id'))

However, this doesn't work

from_time_str = from_time.strftime("%Y-%m-%d %H:%M:%S")
    to_time_str = to_time.strftime("%Y-%m-%d %H:%M:%S")
    stmt = text(
        f"insert into staff_availability(staff_id, during) "
        f"values (:staff_id, :during) returning id")
    stmt = stmt.bindparams(staff_id=kwargs.get('staff_id'), during=f'[{from_time_str},{to_time_str})')

If I put the during value in bind param, it doesn't work and returns this error.

File "/Users/koushik/Documents/projects/colorus81/env/lib/python3.8/site-packages/asyncpg/connection.py", line 1684, in __execute
    return await self._do_execute(
  File "/Users/koushik/Documents/projects/colorus81/env/lib/python3.8/site-packages/asyncpg/connection.py", line 1731, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute
  File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: '[2022-10-10 10:00:00,2022-10-10 14:00:0... (list, tuple or Range object expected (got type <class 'str'>))