Float Values Infinity, -Infinity and Nan

[Former user]

Float Values Infinity, -Infinity and Nan are not mapped correctly, they are actually not mapped at all atleast for postgresql. I have attached a sample program that shows the issues here, and I have also attached a patch to the types.py which will solve the writing to the database issue for PostgreSql. I am not sure if different databases implement this differently. I have also not yet looked into reading these values from the database. I think, reading would be fine but testing is needed.

The sample program:

sqlalchemy_test.py: - To run: python sqlalchemy_test.py First run will produce an error. After applying the patch: - patch -u sqlalchemy/types.py types.py.patch It should run fine, and insert correctly into the db.

I am not sure how this was overlooked. Hope it is fixed soon.

-- Hatem Nassrat

[Former user]

Test SqlAlchemy & PGSQL Application

[Former user]

Patch for types.py

[Mike Bayer]

the Infinity/NAN types, from a brief search , are not part of the SQL standard and are postgres specific, so the patch would need to be part of postgres.py.

Also it would be nice if the psycopg2 folks could fix this instead of us. This example illustrates that psycopg2 returns values like float('inf') but will not accept them as bind paramters:

import psycopg2

conn = psycopg2.connect(user='scott', password='tiger', host='127.0.0.1', database='test')

cursor = conn.cursor()

cursor.execute("CREATE TABLE test (value FLOAT(10))")

cursor.execute("insert into test values (%(val)s)", {'val':'Infinity'})
cursor.execute("select * from test")

assert cursor.fetchone()[0](0) == float('inf')


cursor.execute("insert into test values (%(val)s)", {'val':float('inf')})

my ideal patch would look like:

Index: lib/sqlalchemy/databases/postgres.py
===================================================================
--- lib/sqlalchemy/databases/postgres.py    (revision 4183)
+++ lib/sqlalchemy/databases/postgres.py    (working copy)
@@ -64,7 +64,16 @@
         else:
             return "FLOAT(%(precision)s)" % {'precision': self.precision}

-
+    def bind_processor(self, dialect):
+        special = {
+            'inf' : 'Infinity',
+            '-inf' : '-Infinity',
+            'nan' : 'NaN'
+        }
+        def process(value):
+            return special.get(str(value), value)
+        return process
+        
 class PGInteger(sqltypes.Integer):
     def get_col_spec(self):
         return "INTEGER"

and the expected test:

from sqlalchemy import *

meta = MetaData(create_engine('postgres://scott:tiger@127.0.0.1/test', echo=True))

test_table = Table('test_float', meta,
     Column('id', Integer, primary_key=True),
     Column('float_val', Float())
)

meta.drop_all()
meta.create_all()
test_table.insert().execute([   {'float_val': float('inf')},    
    {'float_val': float('-inf')},    
    {'float_val': float('nan')},    
    {'float_val': float('InfinItY')},    
    {'float_val': float('-InFiNity')},    
    {'float_val': float('NaN')},    
](
))

assert [for row in test_table.select().execute().fetchall()](repr(row)) == [inf)', '(2, -inf)', '(3, nan)', '(4, inf)', '(5, -inf)', '(6, nan)']('(1,)

Also id note that "nan" support in Python is pretty bad, which is why the str() is required. a PEP proposing better ways to deal with nan/inf etc. was rejected (pep 754) and theres some recent messages on Python-dev about this.

I'd want to ask the psycopg2 people if they think this should be on their end since i am not enthused at all about adding latency to all PG floating point operations.

Also, this patch does not address the usage of the Decimal type,which is also available with sqlalchemy.types.Float and is also the default with sqlalchemy.types.Numeric. Support and unit tests for those will need to be added as well.

[Former user]

I too believe it should be on the psycopg2 end, since they do return the floating point equivalent. I have used your patch for now, as I need this feature asap. So would you like to contact the psycopg guys?

[Mike Bayer]

I might mention it to the psycopg2 guys but for the patch here, we'll probably commit it but I want it to work for when the "asdecimal=True" flag is set, as well as when using Numeric, not just Float. Also want to make sure more carefully that infinity/nan is truly only within PG and not other DB's (need to talk to other developers). Finally, need to have more unit tests added to testtypes.py/NumericTest for this functionality for it to be a complete checkin.

[Mike Bayer]

woo hoo! psycopg2 fixed this. no changes needed.

#!/usr/bin/env python
'''
Creates a test python db to play with
'''
import sqlalchemy as sa

CON_URL = 'postgres://scott:tiger@localhost/test'

db = sa.create_engine(CON_URL)
meta = sa.MetaData()


test_table = sa.Table(
    'test_float', meta,
    # A table contains design id and blocks
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('float_val', sa.Float())
)

meta.drop_all(db)
meta.create_all(db)
data = [
    {'float_val': float('inf')},
    {'float_val': float('-inf')},
    {'float_val': float('nan')},
    {'float_val': float('InfinItY')},
    {'float_val': float('-InFiNity')},
    {'float_val': float('NaN')},
]

db.execute(test_table.insert(), data)


returned = [{"float_val": row[0]} for row in
    db.execute(sa.select([test_table.c.float_val]).order_by(test_table.c.id))
]
assert repr(data) == repr(returned)

[Mike Bayer]

note the above test is comparing repr() because nan != nan

>>> float("NaN") == float("NaN")
False