current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

I have two functions in Postgesql database:

  • id_generator, which generates id from timestamp
  • timestamp_from_id(id), which reverses id to timestamp

Let's assume this is my model:

class DatModel(object):
    id = Column(
        BigInteger,
        primary_key=True,
        server_default=text('id_generator()')
    )
    name = Column(String(50), index=True)

What I want to do is query by timestamp generated by timestamp_from_id(id) function, for example:

dbsession.query(DatModel).filter(DatModel.timestamp > datetime.now()).all()

or

obj = dbsession.query(DatModel).first()
created = obj.timestamp

My question is:

How to create virtual column based on postgres function?

Thanks in advance

Edit:

Unsatisfying method #1

As Roman Dryndik suggested possible solution is to use func. It's working method, but little problematic way while retrieving timestamp:

timestamp = session.execute(func.timestamp_from_id(obj.id)).fetchone()

I'd prefer to do it something like:

timestamp = obj.timestamp

Unsatisfying method #2

Second possible solution is to use Compilation Extension

from datetime import datetime

from sqlalchemy import DateTime
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.functions import FunctionElement

class Timestamp(FunctionElement):
    type = DateTime()
    name = 'timestamp'

@compiles(Timestamp)
def default_timestamp(element, compiler, **kw):
    return compiler.visit_function(element)

@compiles(Timestamp, 'postgresql')
def pg_created(element, compiler, **kw):
    arg1 = list(element.clauses)[0]
    return 'timestamp_from_id({})'.format(compiler.process(arg1))


# filter example
obj = session.query(DatModel).filter(Timestamp < datetime.now()).first()

# get timestamp attribute example
timestamp = session.execute(Timestamp(obj.id)).fetchone()
share|improve this question

3 Answers 3

up vote 1 down vote

Please read Using column_property section of SQL Expressions as Mapped Attributes documentation.

Using it you should be able to do:

class DatModel(Base):
    id = Column(
        Integer,
        primary_key=True,
        server_default=text('id_generator()')
    )
    name = Column(String(50), index=True)

    timestamp = column_property(func.timestamp_from_id(id))

timestamp will also be included in your query, and you can use it in the filtering and/or ordering expressions:

q = (session.query(DatModel)
     .filter(DatModel.timestamp > datetime.now())
     .order_by(DatModel.timestamp.desc())
    )
share|improve this answer
    
It is possible, that solution works, but not for me. I have mixin model and column property gave me an error: InvalidRequestError: Mapper properties (i.e. deferred,column_property(), relationship(), etc.) must be declared as @declared_attr callables on declarative mixin classes. But it lead me to the right solution. Thank you very much! –  voy Feb 10 at 11:13
up vote 0 down vote

If I correctly understand your problem you can call the stored procedure timestamp_from_id using func.

So you will get something like this:

from sqlalchemy import func

# some code here

dbsession.query(DatModel).filter(func.timestamp_from_id(DataModel.id) > datetime.now()).all()

# some code here

Didn't try the code. Probably you should do func.timestamp_from_id(DataModel.id).scalar() as well.

There are some useful info here and here.

share|improve this answer
    
Thanks for your reply. Please, see my edit. –  voy Feb 9 at 17:03
up vote 0 down vote accepted

The solution is to use hybrid_property and expression (docs)

class DatModel(Base)::
    id = Column(
        BigInteger,
        primary_key=True,
        server_default=text('id_generator()')
    )

    @hybrid_property
    def timestamp(self):
        return object_session(self). \
            scalar(select([func.timestamp_from_id(self.id)]))

    @created.expression
    def timestamp(self):
        return func.timestamp_from_id(self.id)

Example usage:

obj = DatModel()
with transaction.manager:
    session.add(obj)
obj = session.query(DatModel).one()

assert type(obj.timestamp) == datetime.datetime
assert obj.timestamp.date() == datetime.date.today()


objs = session.query(DatModel) \
    .filter(DatModel.timestamp < datetime.datetime.now())
assert objs.count() == 1

objs = session.query(Gateway) \
    .filter(DatModel.timestamp > datetime.datetime.now())
assert objs.count() == 0
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.