Interval vs INTERVAL and python_type

[Former user]

SQLAlchemy 1.0.9 Python 2.7.10 Postgres 9.4.4

Seems the SQL Interval type has two spellings in the code. This seems to be causing an issue when using *.python_type

So on Postgres:

test=> \d agate_interval_test Table "public.agate_interval_test" Column | Type | Modifiers --------------+----------+----------- id | integer | interval_fld | interval |

Using SQLAlchemy:

In [23]: from sqlalchemy import Column, MetaData, Table, create_engine

In [24]: from sqlalchemy.engine import Connection

In [25]: engine = create_engine('postgresql://aklaver:@localhost:5432/test')

In [26]: connection = engine.connect()

In [27]: metadata = MetaData(connection)

In [28]: sql_table = Table('agate_interval_test', metadata, autoload=True, autoload_with=connection)

In [31]: for col in sql_table.columns: print col.type ....:
INTEGER INTERVAL

In [32]: for col in sql_table.columns: print col.type.python_type ....:
<type 'int'>

---

NotImplementedError Traceback (most recent call last) <ipython-input-32-7def2730dfe9> in <module>() 1 for col in sql_table.columns: ----> 2 print col.type.python_type 3

/home/aklaver/py_virt/pandas/lib/python2.7/site-packages/sqlalchemy/sql/type_api.pyc in python_type(self) 305 306 """ --> 307 raise NotImplementedError() 308 309 def with_variant(self, type_, dialect_name):

NotImplementedError:

In [45]: sqlalchemy.sql.sqltypes.INTERVAL

AttributeError Traceback (most recent call last) <ipython-input-45-fc36ad754ae1> in <module>() ----> 1 sqlalchemy.sql.sqltypes.INTERVAL

AttributeError: 'module' object has no attribute 'INTERVAL'

In [46]: sqlalchemy.sql.sqltypes.Interval Out[46]: sqlalchemy.sql.sqltypes.Interval

[Mike Bayer]

I'm not seeing any bug here. sqltypes.Interval and postgresql.INTERVAL are two different types; this is the difference between a "generic" type and a SQL Standard or dialect-specific type (see http://docs.sqlalchemy.org/en/rel_1_0/core/type_basics.html#generic-types). The generic Interval type uses an integer as the storage representation by default whereas PG's INTERVAL is supported directly by the backend and is hence not a Python "int", though it could be a datetime.timedelta. I wouldn't rely on the "python_type" attribute in general (I'd sooner deprecate it than have to deal with making it perfectly correct in all cases).

[Former user]

Hmm, I did not get that from the link you posted, in particular:

"The Interval type deals with datetime.timedelta objects. In PostgreSQL, the native INTERVAL type is used; for others, the value is stored as a date which is relative to the “epoch” (Jan. 1, 1970).

Note that the Interval type does not currently provide date arithmetic operations on platforms which do not support interval types natively. Such operations usually require transformation of both sides of the expression (such as, conversion of both sides into integer epoch values first) which currently is a manual procedure (such as via func)."

So the date referred to is actually an integer representing seconds since the epoch?

If so why does it need conversion into an integer?

If python_type is flaky what is the best way to determine what Python type a particular SQLAlchemy type returns?

[Mike Bayer]

OK I was confusing built-in Interval with an example type we have in the docs. Interval deals with timedelta and this seems to be the return value for python_type so I do not know where you're getting "int" from.

built in interval returns timedelta for python_type and pg.INTERVAL raises NotImplemented:

>>> from sqlalchemy import Interval
>>> from sqlalchemy.dialects.postgresql import INTERVAL
>>> i1 = Interval()
>>> i2 = INTERVAL()
>>> i1.python_type
<type 'datetime.timedelta'>
>>> i2.python_type
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/type_api.py", line 380, in python_type
    raise NotImplementedError()
NotImplementedError

is that the only bug here? both the title and the description don't really state what the actual problem is. I will gladly accept a PR for postgresql.INTERVAL.python_type.

So the date referred to is actually an integer representing seconds since the epoch?

no, that's just how one usually hand-rolls date arithmetic on a platform like SQLite that does.

the generic Interval type stores an actual DateTime value that is relative to the "epoch". The source code here is the best way to see what it does:

   def bind_processor(self, dialect):
        impl_processor = self.impl.bind_processor(dialect)
        epoch = self.epoch
        if impl_processor:
            def process(value):
                if value is not None:
                    value = epoch + value
                return impl_processor(value)
        else:
            def process(value):
                if value is not None:
                    value = epoch + value
                return value
        return process

    def result_processor(self, dialect, coltype):
        impl_processor = self.impl.result_processor(dialect, coltype)
        epoch = self.epoch
        if impl_processor:
            def process(value):
                value = impl_processor(value)
                if value is None:
                    return None
                return value - epoch
        else:
            def process(value):
                if value is None:
                    return None
                return value - epoch
        return process

If python_type is flaky what is the best way to determine what Python type a particular SQLAlchemy type returns?

I've yet to see what the real use case for this is, usually if there's some serialization library in use, it would have its own types, and that is what everything gets associated with. If you're just looking at a database, I'd normally look at the database types, since I tend to be schema-oriented in these cases. E.g. suppose the python type comes back as "float". Is that a database type of DECIMAL, FLOAT, NUMERIC, what's the scale / precision, etc.? e.g. "float" is useless. SQL datatypes represent a lot more information than that.

[Former user]

I see the problem, I did not properly format the code portions of my original post and as a result my point was lost in the gibberish. So yes the underling issue is that pg.INTERVAL does not implement python_type. I will see what I can do about putting together a PR for this. The python type comes as decimal.Decimal for DECIMAL and NUMERIC and float for FLOAT and REAL, so I am not seeing the issue. I may not be understanding, but at some point the database type is going to become a Python type when it passes through SQLA, why is that difficult to capture?

[Mike Bayer]

because i can't always guarantee it. The MySQLdb driver might give you back a long whereas PyMSQL gives you an int, the cx_oracle driver has had cases where something that should be an int comes back as float for whatever reason. Most of the type objects try to let the DBAPI driver do the right thing rather than spending extra CPU cycles verifying evertything they send back.

[Former user]

Aah, that was the piece I was missing , the adapter. Thanks.

[Mike Bayer]

• Fixed the .python_type attribute of :class:.postgresql.INTERVAL to return datetime.timedelta in the same way as that of :obj:.types.Interval.python_type, rather than raising NotImplementedError. fixes #3571

→ <<cset 29d6f6e19b01>>

[Mike Bayer]

• Fixed the .python_type attribute of :class:.postgresql.INTERVAL to return datetime.timedelta in the same way as that of :obj:.types.Interval.python_type, rather than raising NotImplementedError. fixes #3571

(cherry picked from commit 29d6f6e19b014bb5ce79032bd8803e32b4da0e5e)

→ <<cset 9ed36c2eed34>>

[Former user]

Sorry about not getting to this and thanks for fixing it.

[Mike Bayer]

it was just a quick one and i finally have time to knock a few out, no worries

[Former user]

Well I took a peek at what you did. I had gotten as far realizing I needed to add the python_type property. Was stuck on how to do the testing. Now I know, for the future.