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

[Feijo]

databases==0.4.1 asyncpg==0.21.0 SQLAlchemy==1.3.20

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

Demo

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?

[Feijo]

It works fine with MySQL, the problem above only happens with PostgreSQL.

[Feijo]

I posted the same question in the Asyncpg repository and they believe it's a bug in Databases.

https://github.com/MagicStack/asyncpg/issues/670#issuecomment-735944599

[vmarkovtsev]

Hey @Feijo you are probably using SQLAlchemy in an unsupported way. Try repeating that construct with a regular, synchronous API:

engine = create_engine("postgresql://user:password@localhost/dbname", echo=True)
print(engine.connect().execute(query).fetchone())

[Feijo]

Hi @vmarkovtsev, I don't think that's the case. It's perfectly fine to query data directly from the table definition. My demo above was just the minimum as possible to reproduce the issue.

Please consider the example below. All you need to do to reproduce the issue is use literal with a date object.

import asyncio
from datetime import date
from databases import Database
from sqlalchemy import select, literal, MetaData, Table, Column, Integer, Date

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

test_table = Table(
    'test',
    MetaData(),
    Column('id', Integer, primary_key=True),
    Column('date', Date)
)

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

    today = date.today()

    query = select([
        test_table.c.id,
        test_table.c.date,
        literal(today) # Without this line it works fine. The problem is that it's assuming string for this type rather than date
    ]).select_from(test_table)

    await database.fetch_all(query)

    await database.disconnect()

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

[vmarkovtsev]

Please test with the synchronous API and report the result.

[Feijo]

Sure, here it goes with the sync API:

from datetime import date
from sqlalchemy import select, literal, MetaData, Table, Column, Integer, Date, create_engine

engine = create_engine('postgresql://user:password@localhost/dbname', echo=True)
metadata = MetaData()

test_table = Table(
    'test',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('date', Date)
)

today = date.today()

query = select([
    test_table.c.id,
    test_table.c.date,
    literal(today)
]).select_from(test_table)

print(engine.connect().execute(query).fetchone())

2020-12-01 09:57:11,674 INFO sqlalchemy.engine.base.Engine select version()
2020-12-01 09:57:11,675 INFO sqlalchemy.engine.base.Engine {}
2020-12-01 09:57:11,676 INFO sqlalchemy.engine.base.Engine select current_schema()
2020-12-01 09:57:11,676 INFO sqlalchemy.engine.base.Engine {}
2020-12-01 09:57:11,689 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-12-01 09:57:11,689 INFO sqlalchemy.engine.base.Engine {}
2020-12-01 09:57:11,690 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-12-01 09:57:11,691 INFO sqlalchemy.engine.base.Engine {}
2020-12-01 09:57:11,691 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2020-12-01 09:57:11,691 INFO sqlalchemy.engine.base.Engine {}
2020-12-01 09:57:11,692 INFO sqlalchemy.engine.base.Engine SELECT test.id, test.date, %(param_1)s AS anon_1
FROM test
2020-12-01 09:57:11,693 INFO sqlalchemy.engine.base.Engine {'param_1': datetime.date(2020, 12, 1)}
(1, datetime.datetime(2020, 1, 1, 0, 0), datetime.date(2020, 12, 1))

[Feijo]

Any news about it? https://github.com/MagicStack/asyncpg/issues/670#issuecomment-735944599