CODEWITHSUNDEEP
pythonsqlalchemyasyncpg
AndreFeijo
AndreFeijo

Reputation: 10629

SQLAlchemy literal with date raises: Invalid input for query argument (expected str, got date)

When using SQLAlchemy's literal with a date object, it raises an exception.

Demo

# Versions used:
# databases 0.4.1
# asyncpg 0.21.0
# SQLAlchemy 1.3.20

import asyncio
from datetime import date
from databases import Database
from sqlalchemy import select, literal

database = Database('postgresql://user:password@localhost/dbname')

async def run():
    await database.connect()

    today = date.today()
    query = select([
        literal(today).label('today') # <-- The problem is here
    ])

    await database.fetch_all(query)

    await database.disconnect()

loop = asyncio.get_event_loop()
loop.run_until_complete(run())

Exception

Traceback (most recent call last):
  File "test.py", line 21, in <module>
    loop.run_until_complete(run())
  File "C:\Python38\lib\asyncio\base_events.py", line 616, in run_until_complete
    return future.result()
  File "test.py", line 14, in run
    rows = await database.fetch_all(query)
  File "C:\Python38\lib\site-packages\databases\core.py", line 140, in fetch_all
    return await connection.fetch_all(query, values)
  File "C:\Python38\lib\site-packages\databases\core.py", line 239, in fetch_all
    return await self._connection.fetch_all(built_query)
  File "C:\Python38\lib\site-packages\databases\backends\postgres.py", line 160, in fetch_all
    rows = await self._connection.fetch(query, *args)
  File "C:\Python38\lib\site-packages\asyncpg\connection.py", line 443, in fetch
    return await self._execute(query, args, 0, timeout)
  File "C:\Python38\lib\site-packages\asyncpg\connection.py", line 1445, in _execute
    result, _ = await self.__execute(
  File "C:\Python38\lib\site-packages\asyncpg\connection.py", line 1454, in __execute
    return await self._do_execute(query, executor, timeout)
  File "C:\Python38\lib\site-packages\asyncpg\connection.py", line 1476, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 178, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 160, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.date(2020, 11, 30) (expected str, got date)

Why is it expecting a str?

Edit

It works fine with MySQL, so my guess is that the problem is in asyncpg.

Upvotes: 1

Views: 1436

Answers (1)

Wolfgang Michael Kraus
Wolfgang Michael Kraus

Reputation: 116

It certainly is an issue regarding the asyncpg-framework as type convertion between database literal and python objects are being handled by the database driver.

Your error is being thrown by the _encode_bind_msg function in prepared_stmt.pyx on line 169:

raise exceptions.DataError(
    'invalid input for query argument'
    ' ${n}: {v} ({msg})'.format(
        n=idx + 1, v=value_repr, msg=e)) from e

The asyncpg documentation indicates that this error is being assumed to be an encoding issue due to invalid input.

I believe the asyncpg codecs for postgres can be found here and the problem may arise during the pgproto.date_encode function.

You should try setting your encoding/decoding manually according to the asyncpg API-reference to further validate the origin of this issue.

Upvotes: 1

Related Questions