Interval type column raises TypeError on record access

[steinitzu]

Hi

I found an issue when selecting from a table with an Interval column, this should parse to a timedelta object. When used with databases, sqlalchemy raises a TypeError when trying to access the column in the result:

~/projects/dummy/scripts/interval.py in <module>
     33 
     34 loop = asyncio.get_event_loop()
---> 35 result = loop.run_until_complete(do())

/usr/lib/python3.7/asyncio/base_events.py in run_until_complete(self, future)
    577             raise RuntimeError('Event loop stopped before Future completed.')
    578 
--> 579         return future.result()
    580 
    581     def stop(self):

~/projects/dummy/scripts/interval.py in do()
     28 
     29     row = await db.fetch_one(thing.select())
---> 30     result = row["duration"]  # This fails
     31     return result
     32 

~/projects/dummy/.venv/lib/python3.7/site-packages/databases/backends/postgres.py in __getitem__(self, key)
    112 
    113         if processor is not None:
--> 114             return processor(raw)
    115         return raw
    116 

~/projects/dummy/.venv/lib/python3.7/site-packages/sqlalchemy/sql/sqltypes.py in process(value)
   1927                 if value is None:
   1928                     return None
-> 1929                 return value - epoch
   1930 
   1931         return process

TypeError: unsupported operand type(s) for -: 'datetime.timedelta' and 'datetime.datetime'

It works fine when using the same table with sqlalchemy engine directly.
Suspecting it's because the result processor ( https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/sqltypes.py#L1914 ) executes when used with databases, but not with sqlalchemy. Question is why?
My understanding is that it should not be executed for postgres cause it has a native interval type.

Here is a script to reproduce the problem:

import asyncio
from datetime import timedelta

from sqlalchemy import Column, create_engine, MetaData, Table, Interval
from databases import Database

DB_URL = "postgresql://dev:dev@localhost:6808/dev"

metadata = MetaData()

thing = Table(
    "thing",
    metadata,
    Column("duration", Interval, nullable=False),
    extend_existing=True,
)


async def do():
    db = Database(DB_URL)
    if not db.is_connected:
        await db.connect()

    engine = create_engine(DB_URL)
    metadata.create_all(engine)

    await db.execute(thing.insert().values(duration=timedelta(days=7)))

    row = await db.fetch_one(thing.select())
    result = row["duration"]  # This fails
    # But works with sqlalchemy engine, e.g.
    # return next(engine.execute(thing.select()))["duration"]
    return result


loop = asyncio.get_event_loop()
result = loop.run_until_complete(do())

[lovelydinosaur]

Thanks for this. Best first step for us getting this resolved would be for someone to submit a pull request which includes a failing test case for this column type.

[steinitzu]

@tomchristie I was planning on fixing this in #149 , but I'm completely lost.

This is only an issue in asyncpg cause it doesn't use RowProxy but its own record type, so has to resolve result processors itself. The new aiopg backend works fine.

A quick fix would be to hard code result processor for this and other problem data types which isn't great.

Better would be to somehow coerce the asyncpg record to sqla RowProxy for full compatibility, but that looks seriously difficult since asyncpg doesn't implement DB-API.

Do you have any suggestions?

[gvbgduh]

@steinitzu there's some scope in https://github.com/encode/databases/issues/128, but in the nutshell, there're slight syntax differences between asyncpg and psycopg2 (aiopg). So it's worth checking the diff of rendered sql on both backends. I'll try to have a look.

As of RowProxy, I believe in sort of the opposite approach -> the dict cursor or annotated values. As the DB knows from the query what's requested it can provide column names with values packed on the DB backend without relying on sqlalchemy meta. It generally solves problems with raw queries.

Eg. :

# aiopg
cursor(cursor_factory=psycopg2.extras.RealDictCursor)

# mysql
cursor = yield from conn.cursor(aiomysql.DictCursor)

# aiosqlite
connection.row_factory = aiosqlite.Row

[circlingthesun]

This is non issue when using sqlalchemy.dialects.postgresql.INTERVAL instead of sqlalchemy.Interval.