My use case is a simple CRUD app which has a history table (patterned on
the example in the SQLAlchemy docs) and a "current" table.
I want to write a simple context manager so that
f = db.session.query(Foo).all()
gives current values while:
with app.as_of(date(2014, 05, 31)):
f = db.session.query(Foo).all()
gives historical numbers.
We have a set of materialized views which give end-of-month snapshots which
is why I'm trying to figure out how to make things "swappable" after the
class is defined.
Since the suite of the context manager may be rather complex, I'm hoping to
do this as a context manager. You hint there might be something explicit I
could do at the query level. That could work for my use case (context
manager could set a global).
I'll look at the pointers below.
Thanks again,
Michael
On Wednesday, June 18, 2014 6:57:41 PM UTC-4, Michael Bayer wrote:
>
> Mapper has a _set_with_polymorphic() method that will do this.
>
> Though I'm looking at your original use case, that is you want to select
> from a view, and I'm not really sure why you need to set it on the
> outside.
>
> Persist to a table select from a view would look like:
>
> the_view = Table('the_view', Base.metadata, Column('col1', ...),
> Column('col2', ...))
>
> class MyClass(Base):
> __tablename__ = 'the_table'
> col1 = Column(...)
> col2 = Column(...)
> # ...
>
> __mapper_args__ = {'with_polymorphic': ('*', the_view)}
>
> I'm not 100% sure that whole thing works if a "polymorphic_on" isn't set,
> as with_polymorphic wasn't intended to be used for this use case (but
> there's nothing else that's very great for this case that isn't explicit at
> the Query level).
>
>
> On 6/18/14, 6:46 PM, Michael Weylandt wrote:
>
> Bumping this, is there a way to do a "replacement" with_polymorphic
> instead of an "addition"?
>
> Michael
>
> On Tuesday, April 8, 2014 3:59:34 PM UTC-4, Michael Weylandt wrote:
>>
>> On Monday, April 7, 2014 5:11:48 PM UTC-4, Michael Bayer wrote:
>>>
>>>
>>> On Apr 7, 2014, at 2:46 PM, Michael Weylandt <michael....@gmail.com>
>>> wrote:
>>>
>>> > Since the logic creating the view is a little hairy, I'd like to avoid
>>> replicating it in Python and just
>>> > have SQLAlchemy redirect all SELECT statements to the view instead of
>>> the underlying table.
>>>
>>> the with_polymorphic setting of mapper will allow this:
>>>
>>> class A(Base):
>>> __tablename__ = 'a'
>>>
>>> id = Column(Integer, primary_key=True)
>>>
>>> s1 = select([A.__table__])
>>>
>>> A.__mapper__.with_polymorphic = ('*', s1)
>>>
>>
>> Hi Mike,
>>
>> Thanks for the reply -- looking at the docs, with_polymorphic sounds
>> like just what I need. SQLAlchemy really is an amazing system.
>>
>> I've almost got it working: I can send selects to the view _and_ the
>> original table, but not _just_ to the view.
>>
>> Reading the docs, I think I'm stuck at the point where select()'s
>> from_obj can only add elements to the FROM instead of replacing them. [1]
>>
>> Minimal example:
>>
>> from sqlalchemy import create_engine, select
>> from sqlalchemy.orm import sessionmaker
>> from sqlalchemy import Column, Integer, String, DateTime
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import event, DDL
>> from datetime import datetime
>>
>> Base = declarative_base()
>> class Foo(Base):
>> __tablename__ = 'FooTable'
>> __viewname__ = 'FooView'
>>
>> id = Column(Integer, primary_key=True)
>> name = Column(String)
>> description = Column(String)
>> time = Column(DateTime, default=datetime.now)
>>
>> ddl_str = """CREATE VIEW %s AS
>> SELECT * FROM %s GROUP BY name HAVING time = max(time)"""
>> event.listen(Foo.__table__, "after_create",
>> DDL(ddl_str % (Foo.__viewname__, Foo.__tablename__)))
>>
>> ## You didn't have a from_obj here so this may be wrong, but there's no
>> re-direct without one
>> s1 = select([Foo.__table__], from_obj = Foo.__viewname__)
>> Foo.__mapper__.with_polymorphic = ("*", s1)
>>
>> engine = create_engine("sqlite:///:memory:", echo=True)
>> Base.metadata.create_all(engine)
>>
>> session = sessionmaker(bind=engine)()
>>
>> session.add(Foo(name="Foo 1", description="The First Foo"))
>> session.add(Foo(name="Foo 2", description="The Second Foo"))
>> session.add(Foo(name="Foo 3", description="The Third Foo"))
>> session.commit()
>>
>> session.add(Foo(name="Foo 2", description="The Second Foo (ed)"))
>> session.commit()
>>
>> print session.query(Foo.description).all()
>>
>> [1]
>> http://docs.sqlalchemy.org/en/rel_0_8/core/selectable.html#sqlalchemy.sql.expression.select.params.from_obj
>>
> --
> 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 sqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
