How to use postgres numeric range with SQLAlchemy

Question

I can't happen to find much on using Postgres range types in SQLAlchemy other than this. Does anyone know how to insert new column values into a numrange column? Does anyone have some code snippets or more documentation than the one I already found?

Answer 1

This has been added to the official documentation: https://bitbucket.org/zzzeek/sqlalchemy/issue/3046/postgresql-range-types-need-better

---

Had to dig around for this, but when in doubt, check the tests! The SQLAlchemy tests for the range types use the underlying psycopg2 types.

from psycopg2.extras import NumericRange
from sqlalchemy import create_engine, Column, Integer
from sqlalchemy.dialects.postgresql import INT4RANGE
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql:///example', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base(bind=engine)


class Example(Base):
    __tablename__ = 'example'

    id = Column(Integer, primary_key=True)
    window = Column(INT4RANGE, nullable=False)


Base.metadata.create_all()

session.add(Example(window=NumericRange(2, 6)))
session.add(Example(window=NumericRange(4, 8)))
session.commit()