带日期提升的SQLAlchemy文字：查询参数的输入无效（应为str，Get-date）

Question

将SQLAlchemy的literal与date对象一起使用时，会引发异常

演示

# 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())

例外情况

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)

为什么会有str

编辑

它在MySQL上工作得很好，所以我猜问题出在asyncpg

Answer 1

关于asyncpg框架，这当然是一个问题，因为数据库驱动程序正在处理数据库文本和python对象之间的类型转换

您的错误由prepared_stmt.pyx on line 169中的_encode_bind_msg函数引发：

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

asyncpg文档表明，由于输入无效，此错误被认为是编码问题

我相信postgres的asyncpg编解码器可以在here找到，问题可能会在pgproto.date_encode函数中出现

您应该尝试根据asyncpg API-reference手动设置编码/解码，以进一步验证此问题的根源