Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Jonathan Vanasco]

fwiw, I struggled with this a while back and then gave up.

i ended up writing a few filter__xyz() functions that accept/return a 
query.  in the def, I join needed tables and filter.  instead of inspecting 
the query for tables, I just pass in some flags on how to act.

It's not pretty, but it works reliably and was fast to implement.

my queries now look like:

q = session.query(A).filter(A.id>10)
q = filter__as_at(q)

-- 
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.

Re: [sqlalchemy] how to dynamically work with an aliased table?

[Jonathan Vanasco]

It looks like I imported the `sqlalchemy.alias` instead of 
`sqlalchemy.orm.aliased`, and just typod when posting here.

switching to sqlalchemy.orm.aliased -- which I used in an identical manner 
7 other times in this file -- immediately fixed things.

thanks for setting me straight.  looks like I had really tired eyes.


-- 
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.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Chris Withers]

On 25/09/2015 13:58, Mike Bayer wrote:

session.query(A).filter(A.id>10).as_at(now))


you'd need to subclass Query and dig into Query.column_descriptions to 
get at the existing entities, then add all that criterion.
remind me where the docs are for plugging in a subclassed Query into a 
session?


Chris

--
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.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Chris Withers]

On 25/09/2015 16:35, Jonathan Vanasco wrote:

fwiw, I struggled with this a while back and then gave up.

i ended up writing a few filter__xyz() functions that accept/return a 
query.  in the def, I join needed tables and filter.  instead of 
inspecting the query for tables, I just pass in some flags on how to act.


It's not pretty, but it works reliably and was fast to implement.

my queries now look like:

q = session.query(A).filter(A.id>10)
q = filter__as_at(q)


A function doesn't seem terrible, but django-much with the __? ewww ;-)

Chris

--
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.

Re: [sqlalchemy] views

[Mike Bayer]

On 9/25/15 5:26 AM, Chris Withers wrote:

Also forgot to ask... What's support like in Alembic for creating views 
(especially if the views are described by a declarative class as I'm looking 
for below...)


the trick with views is migrating them when the tables change without 
lots of repetition.  I've come up with a recipe for this at 
http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects.









On 25 Sep 2015, at 08:13, Chris Withers  wrote:

Hi All,

Is this still the best way to hand views, or are there later and greater things 
in 1.0+?

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

How would I make a view behave like a normal declarative class (column 
attributes, etc), but while still having it create itself as part of create_all?

cheers,

Chris

--
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.

__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__


--
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.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Jonathan Vanasco]

It's a code management style that we ended up on :(

-- 
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.

[sqlalchemy] "No such event 'instrument_class' for target" for mixin that needs to listen to 'instrument_class'

[Chris Withers]

Hi All,

I have a mixin class here that I've factored out of one project as I 
want to use it in another one:


https://github.com/Mortar/mortar_mixins/blob/master/mortar_mixins/temporal.py

The problem is that importing the module seems to result in the 
following exception being raised:


>>> from mortar_mixins.temporal import Temporal
Traceback (most recent call last):
  File "", line 1, in 
  File "mortar_mixins/temporal.py", line 81, in 
listen(Temporal, 'instrument_class', add_constraints, propagate=True)
  File 
"/Users/chris/vcs/git/mortar_mixins/lib/python2.7/site-packages/sqlalchemy/event/api.py", 
line 89, in listen

_event_key(target, identifier, fn).listen(*args, **kw)
  File 
"/Users/chris/vcs/git/mortar_mixins/lib/python2.7/site-packages/sqlalchemy/event/api.py", 
line 28, in _event_key

(identifier, target))
sqlalchemy.exc.InvalidRequestError: No such event 'instrument_class' for 
target ''


My guess is something to do with it not being mixed into any classes at 
that point.


Weirdly, it seems to work fine, and all the tests run and pass, although 
the nose run will spew the above error in addition to running the tests.


To reproduce, just follow the instructions here:

https://github.com/Mortar/mortar_mixins/blob/master/README.rst#development

Any ideas what I should do to resolve this?

cheers,

Chris

--
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.

[sqlalchemy] funk session usage to add join conditions and where clauses

[Chris Withers]

Hi All,

Suppose I have the following:

from sqlalchemyimport Column, Integer, Text, ForeignKey, and_
from sqlalchemy.dialects.postgresqlimport TSRANGEas Range
from sqlalchemy.ext.declarativeimport declarative_base

Base = declarative_base()

class A(Base):
id = Column(Integer(),primary_key=True)
period = Column(Range(),nullable=False,primary_key=True)
data = Column(Text())

class B(Base):
id = Column(Integer(),primary_key=True)
period = Column(Range(),nullable=False,primary_key=True)
data = Column(Text())
a_id =  Column('a_id', Integer(), ForeignKey('a.id'))

data = Column(Text())

class C(Base):
id = Column(Integer(),primary_key=True)
period = Column(Range(),nullable=False,primary_key=True)
data = Column(Text())
b_id =  Column('b_id', Integer(), ForeignKey('b.id'))

How would I write something that molested the session such that instead 
of writing:


session.query(A, B, C)
   .select_from(A).join(B, and_(A.id==B.a_id,
A.period.contains(now),
B.period.contains(now)).
  .join(C, and_(B.id==C.b_id,
C.period.contains(now)))

...I could write:

session.query(A, B, C).as_at(now)

And instead of writing:

session.query(A).filter(A.id>10).filter(A.period.contains(now))

I could write:

session.query(A).filter(A.id>10).as_at(now))

Lazily yours,

Chris

--
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.

Re: [sqlalchemy] how to dynamically work with an aliased table?

[David Allouche]

> On 24 Sep 2015, at 17:38, Jonathan Vanasco  wrote:
> 
> 
> 
> On Thursday, September 24, 2015 at 3:05:56 AM UTC-4, David Allouche wrote:
> That looks like the right approach. There is probably something else in your 
> actual code that is causing "it [to] not work". 
> 
> To get a better understanding of "it did not work", I would look at the 
> "str(query)" before and after the stuff with the aliased table. 
> 
> The _aliased object (returned by `sqlalchemy.orm.alias()` does not have 
> addressable columns.  touching _aliased.string_id and _aliased.c.string_id 
> both raise errors.
> 
> The rest of the code works fine in production, I just can't seem to figure 
> out how to add this table onto the query under a different name which can be 
> queried against.  The closest thing I could do was to nest everything into 
> subselects -- but the sql is grossly inefficient.


Use sqlalchemy.orm.aliased() instead of .alias(). For example:

bar2 = sqlalchemy.orm.aliased(Bar, name='bar2')

Honestly, by reading the documentation, I am confused about how useful 
orm.alias() is.

But orm.aliased() is the thing I use all over the place to do what you want.

-- 
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.

[sqlalchemy] views

[Chris Withers]

Hi All,

Is this still the best way to hand views, or are there later and greater 
things in 1.0+?


https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

How would I make a view behave like a normal declarative class (column 
attributes, etc), but while still having it create itself as part of 
create_all?


cheers,

Chris

--
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.

[sqlalchemy] funky session usage to add join conditions and where clauses

[Chris Withers]

Hi All,

Suppose I have the following:

from sqlalchemy import Column, Integer, Text, ForeignKey, and_from 
sqlalchemy.dialects.postgresql import TSRANGE as Rangefrom 
sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class A(Base):
id = Column(Integer(), primary_key=True)
period = Column(Range(), nullable=False, primary_key=True)
data = Column(Text())
class B(Base):
id = Column(Integer(), primary_key=True)
period = Column(Range(), nullable=False, primary_key=True)
data = Column(Text())
a_id =  Column('a_id', Integer(), ForeignKey('a.id'))

data = Column(Text())
class C(Base):
id = Column(Integer(), primary_key=True)
period = Column(Range(), nullable=False, primary_key=True)
data = Column(Text())
b_id =  Column('b_id', Integer(), ForeignKey('b.id'))


How would I write something that molested the session such that instead of 
writing:

session.query(A, B, C)
   .select_from(A).join(B, and_(A.id==B.a_id,
A.period.contains(now),
B.period.contains(now)).
  .join(C, and_(B.id==C.b_id,
C.period.contains(now)))


...I could write:

session.query(A, B, C).as_at(now)


And instead of writing:

session.query(A).filter(A.id>10).filter(A.period.contains(now))


I could write:

session.query(A).filter(A.id>10).as_at(now))


Lazily yours,

Chris

-- 
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.

[sqlalchemy] Storing data on an instance that's deleted when the object is expired

[Adrian]

For some methods/properties on a model it might be useful to memoize its 
result.
There are some common decoratos such as cached_property from werkzeug which 
simply add the
value to the object's __dict__ after retrieving it the first time (thus not 
calling the property's getter again).
Or you might end up using custom memoization code that stores the value 
somewhere on the object.

Anyway, the problem with all those things is that I'm likely to cache 
things too long in case they depend
on a column/relationship value since expiring the object won't expire my 
cached data.

Is there any place where I could store custom data associated with an 
instance of a mapped object that
is cleared when the object is expired?

-- 
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.

Re: [sqlalchemy] loading of relationships

[Julien Cigar]

On Thu, Sep 24, 2015 at 11:16:37AM -0400, Mike Bayer wrote:
> 
> 
> On 9/24/15 9:32 AM, Julien Cigar wrote:
> > Hello,
> >
> > I'm using SQLAlchemy 1.0.8 with joinedload inheritance. On one of the
> > Child I have a relationship property and I wondered if there is an easy
> > way to innerjoin=True this relation only in a non-polymorphic context ?
> >
> > In my case I have the base class "Content", a child "Event", and "Event"
> > has a relationship to "Country".
> >
> > So I when I do Session.query(Event) it should INNER JOIN Country, but
> > with orm.with_polymorphic(Content, [Event]) the Country relationship
> > should be LEFT OUTER JOIN, otherwhise the query returns no result ...
> >
> > I wondered if there was already something in SQLAlchemy do handle this
> > case (other than joinedload(), etc ..) ?
> 
> the relationship() is bound to Event only.  If you set innerjoin=True on 
> that relationship(), that handles query(Event), but when you do 
> with_polymorphic(Content, [Event]), the joins *should* be nesting on the 
> right side in modern versions, e.g. SELECT content.*, event.* FROM 
> content LEFT OUTER JOIN (event INNER JOIN country).

I haven't tested with git HEAD but in 1.0.8 the joins aren't nested
(unless I did something wrong)

> 
> At least ideally.   If it's not, that's something to be reported and 
> fixed but there'd be no automatic workaround for now.
> 

I'll make a bug report + test case as soon as I have little time ..

As always, thanks for your quick reply!

> 
> 
> >
> > Thanks!
> > Julien
> >
> 
> -- 
> 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.

-- 
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11  6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

-- 
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.


pgpjnFthq74bz.pgp
Description: PGP signature

Re: [sqlalchemy] how to dynamically work with an aliased table?

[Mike Bayer]

On 9/25/15 2:41 AM, David Allouche wrote:


On 24 Sep 2015, at 17:38, Jonathan Vanasco > wrote:




On Thursday, September 24, 2015 at 3:05:56 AM UTC-4, David Allouche 
wrote:


That looks like the right approach. There is probably something
else in your actual code that is causing "it [to] not work".

To get a better understanding of "it did not work", I would look
at the "str(query)" before and after the stuff with the aliased
table.


The _aliased object (returned by `sqlalchemy.orm.alias()` does not 
have addressable columns.  touching _aliased.string_id and 
_aliased.c.string_id both raise errors.


The rest of the code works fine in production, I just can't seem to 
figure out how to add this table onto the query under a different 
name which can be queried against.  The closest thing I could do was 
to nest everything into subselects -- but the sql is grossly inefficient.



Use sqlalchemy.orm.aliased() instead of .alias(). For example:

  bar2 = sqlalchemy.orm.aliased(Bar, name='bar2')

Honestly, by reading the documentation, I am confused about how useful 
orm.alias() is.


what's orm.alias() ?

>>> from sqlalchemy.orm import alias
Traceback (most recent call last):
  File "", line 1, in 
ImportError: cannot import name alias

there's a sqlalchemy.alias in core, which is very useful for core stuff.




But orm.aliased() is the thing I use all over the place to do what you 
want.


--
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.


--
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.

Re: [sqlalchemy] Storing data on an instance that's deleted when the object is expired

[Mike Bayer]

On 9/25/15 8:22 AM, Adrian wrote:
For some methods/properties on a model it might be useful to memoize 
its result.
There are some common decoratos such as cached_property from werkzeug 
which simply add the
value to the object's __dict__ after retrieving it the first time 
(thus not calling the property's getter again).
Or you might end up using custom memoization code that stores the 
value somewhere on the object.


Anyway, the problem with all those things is that I'm likely to cache 
things too long in case they depend
on a column/relationship value since expiring the object won't expire 
my cached data.


Is there any place where I could store custom data associated with an 
instance of a mapped object that

is cleared when the object is expired?
one difficulty with that is that the "expired' state isn't 
all-or-nothing in some cases; individual attributes can be expired which 
may or may not be part of your attribute's state.


for that reason you want to write an expire handler using the expire 
event: 
http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html#sqlalchemy.orm.events.InstanceEvents.expire 
which will pop your __dict__-cached values when this is intercepted.
In the vast majority of cases this occurs for all attributes so it would 
be feasible to disregard checking the attrs collection.





--
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.


--
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.

Re: [sqlalchemy] views

[Mike Bayer]

On 9/25/15 3:13 AM, Chris Withers wrote:

Hi All,

Is this still the best way to hand views, or are there later and 
greater things in 1.0+?


https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

How would I make a view behave like a normal declarative class (column 
attributes, etc), but while still having it create itself as part of 
create_all?


There's probably some semi-elaborate way to do this, though it would 
take some tinkering.


Any Table can just have as its name the name of your view, and you'd 
link it to declarative with __table__.   That won't get you the 
create_all() part and you might want this Table to be on a separate 
MetaData so that it isn't included in it, but it would get you started.









cheers,

Chris



--
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.

Re: [sqlalchemy] views

[Chris Withers]

Also forgot to ask... What's support like in Alembic for creating views 
(especially if the views are described by a declarative class as I'm looking 
for below...)


> On 25 Sep 2015, at 08:13, Chris Withers  wrote:
> 
> Hi All,
> 
> Is this still the best way to hand views, or are there later and greater 
> things in 1.0+?
> 
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views
> 
> How would I make a view behave like a normal declarative class (column 
> attributes, etc), but while still having it create itself as part of 
> create_all?
> 
> cheers,
> 
> Chris
> 
> -- 
> 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.
> 
> __
> This email has been scanned by the Symantec Email Security.cloud service.
> For more information please visit http://www.symanteccloud.com
> __

-- 
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.