Actually, looks like the problem is with psycopg2's handling of range
types, specifically with integers. Test attached. Will forward to psycopg2
maintainers.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import (
INT4RANGE,
NUMRANGE
)
import psycopg2
from psycopg2.extras import NumericRange
# Sqlalchemy db interactions
Base = declarative_base()
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
intrange = Column(INT4RANGE)
numrange = Column(NUMRANGE)
e = create_engine("postgresql://[email protected]:5432/app", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
# Insert via string type
foo_one = Foo(id=1, intrange='[1, 10]', numrange='[1.0, 10.0]')
# Insert via NumericRange type
foo_two = Foo(id=2, intrange=NumericRange(lower=1, upper=10, bounds='[]'), numrange=NumericRange(lower=1.0, upper=10.0, bounds='[]'))
sess.add_all([foo_one, foo_two])
sess.commit()
foo_one = sess.query(Foo).filter_by(id=1).first()
foo_two = sess.query(Foo).filter_by(id=2).first()
# Psycopg2 db interactions
conn = psycopg2.connect('dbname=app user=brian')
cur = conn.cursor()
foo_three = Foo(id=3, intrange=NumericRange(lower=1, upper=10, bounds='[]'), numrange=NumericRange(lower=1.0, upper=10.0, bounds='[]'))
cur.execute("INSERT INTO foo (id, intrange, numrange) VALUES (%s, %s, %s)", ((foo_three.id,), (foo_three.intrange,), (foo_three.numrange,)))
conn.commit()
cur.execute('SELECT intrange FROM foo WHERE id=3;')
foo_three.intrange = cur.fetchone()
cur.execute('SELECT numrange FROM foo WHERE id=3;')
foo_three.numrange = cur.fetchone()
cur.close()
conn.close()
# Tests
# These pass: string and NumericRange types the same when committed
assert foo_one.intrange == foo_two.intrange
assert foo_one.numrange == foo_two.numrange
# These pass: Sqlalchemy and psycopg2 the same
assert foo_three.intrange == (foo_one.intrange,)
assert foo_three.numrange == (foo_one.numrange,)
# These pass, but should not?
assert foo_one.intrange == NumericRange(lower=1, upper=11, bounds='[)')
assert foo_two.intrange == NumericRange(lower=1, upper=11, bounds='[)')
assert foo_three.intrange == (NumericRange(lower=1, upper=11, bounds='[)'),)
# These pass: numeric types work fine
assert foo_one.numrange == NumericRange(lower=1.0, upper=10.0, bounds='[]')
assert foo_two.numrange == NumericRange(lower=1.0, upper=10.0, bounds='[]')
assert foo_three.numrange == (NumericRange(lower=1.0, upper=10.0, bounds='[]'),)
# These fail, but should pass?
assert foo_one.intrange == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_two.intrange == NumericRange(lower=1, upper=10, bounds='[]')
assert foo_three.intrange == (NumericRange(lower=1, upper=10, bounds='[]'),)
