[sqlalchemy] Re: Select as
On Nov 16, 5:15 pm, Conor conor.edward.da...@gmail.com wrote: Tomas Zulberti wrote: Hi. I am sort of a newbie on SQLAlchemy. Is there a way to do a query with the ORM, and doing an as on the select. For example: class Example(Base): name = Column(Unicode(512) ) query = session.query(Example.name AS foo) query.all() Every column object has a label method, e.g. Example.name.label(foo). However, the label is lost when you do query.all(), so I'm not sure what you're trying to do here. -Conor Thanks. That solved my problem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: column label and order by
anyone??
On Nov 14, 6:48 pm, rajasekhar911 rajasekhar...@gmail.com wrote:
Hi guys,
how do i apply order by on a column with a label.
My requirement is like this
class x
id,
amount,
date
i have to group based on id and take sum of amount within a date
range.
i am applying a label to sum of amount
now how do i order based on that so that i can get top 5 ..
session.query( func.sum(x.amount).label('tot_amount'), x.id ).
filter(x.datefromdate).filter(x.datetodate).
.group_by(x.id)
.order_by(?)
.limit(5)
thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: column label and order by
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 17 November 2009 11:32
To: sqlalchemy
Subject: [sqlalchemy] Re: column label and order by
anyone??
On Nov 14, 6:48 pm, rajasekhar911 rajasekhar...@gmail.com wrote:
Hi guys,
how do i apply order by on a column with a label.
My requirement is like this
class x
id,
amount,
date
i have to group based on id and take sum of amount within a date
range.
i am applying a label to sum of amount
now how do i order based on that so that i can get top 5 ..
session.query( func.sum(x.amount).label('tot_amount'), x.id ).
filter(x.datefromdate).filter(x.datetodate).
.group_by(x.id)
.order_by(?)
.limit(5)
thanks.
How about (untested):
tot_amount = func.sum(x.amount).label('tot_amount')
session.query(tot_amount, x.id).
filter(x.datefromdate).filter(x.datetodate).
.group_by(x.id)
.order_by(tot_amount)
.limit(5)
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: column label and order by
And you do need to quote the column name in order_by also.
session.query(func.sum(X.amount).label('tot_amount')).group_by(X.date).order_by('tot_amount').limit(10)
generates code
SELECT sum(x.amount) AS tot_amount
FROM x GROUP BY x.date ORDER BY tot_amount
LIMIT 10 OFFSET 0
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: UnboundExecutionError with object on session
I have changed my code to be like this: job = session.merge(job) # Merge docs says that object does not get into the session session.add(job) log.info(Job finished! %s % job.file) When using latest SQLAlchemy trunk and 0.5.6 sometimes i get UnboundExecutionError. Also, i can assure that all my attributes are loaded - i use all of them before and no error occurs. Maybe there's something wrong with the way i'm getting the session: session = Session.object_session(job) if not session: session = Session() On Nov 16, 2009, at 3:27 PM, Michael Bayer wrote: Fernando Takai wrote: Hi all! I've experiencing this problem for some time now and even after debugging, i could not find why it happens. I have a medium sized multi-thread application that manipulates SQLAlchemy objects - the objects are passed from thread to thread, so, when i load an instance i close the session. After working on the object, i need to update the status of the it, so i do something like this: job.status = FINISHED session.add(job) log.info(Finished job %s % job.filename) session.flush() This code works pretty well, but from time to time, i get this on the logs: UnboundExecutionError: Instance Job at 0x413ee50 is not bound to a Session; attribute refresh operation cannot proceed The complete stacktrace is here: http://pastebin.org/54196 Could this be happening because of my pool_recycle setting (300 seconds) ? its not related to the pool. Your objects have expired or unloaded attributes present on them which will attempt to load when accessed. The object must be attached to a Session for this to proceed. the easiest way to deal with this is to merge() the objects into a new thread-local Session before using. Alternatively, ensure all required attributes are loaded. This often requires touching the attributes explicitly in the case of joined table inheritance or lazily-loaded relations(). Also note that calling session.commit() or session.rollback() expires all attributes, so avoid these in the case of objects becoming detached, or expunge() the objects before an expiration occurs. -- Fernando Takai http://twitter.com/fernando_takai --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: column label and order by
session.query( func.sum(x.amount).label('tot_amount'), x.id ).
filter(x.datefromdate).filter(x.datetodate).
.group_by(x.id)
.order_by('tot_amount DESC')
.limit(5)
On Nov 17, 4:55 pm, Mike Conley mconl...@gmail.com wrote:
And you do need to quote the column name in order_by also.
session.query(func.sum(X.amount).label('tot_amount')).group_by(X.date).order_by('tot_amount').limit(10)
generates code
SELECT sum(x.amount) AS tot_amount
FROM x GROUP BY x.date ORDER BY tot_amount
LIMIT 10 OFFSET 0
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: filtering with an association_proxy
scott wrote:
Is there a way to filter a query involving an association_proxy?
For example, say I have a one to many relation between Pages and Tags,
and an association_proxy like this to let me represent tags as a list
of strings.
tag_objects = orm.relation('Tag')
tags = association_proxy('tag_objects', 'name')
Now I want to find all the pages tagged with 'foo'. As far as I know I
have to break the abstraction barrier provided by the
association_proxy and do something like:
sess.query(Page.tag_objects.any(name='foo'))
Is there any mechanism for doing something like this instead?
sess.query(Page.tags.any('foo'))
If there's nothing similar already existing, is this functionality
desirable? It seems like it could be really useful for clarifying
complex filtering, especially involving many to many relations with
association objects. I wrote an example patch implementing this
for .any() and .has(), with tests. I'm happy to post a ticket and
flesh it out more if it seems reasonable.
http://web.mit.edu/storborg/Public/better-associationproxy-filtering.patch
we absolutely would want associationproxy to provide the standard
comparison functions for free - right now its a do-it-yourself thing. If
you want to work on that that would be great !
Thanks,
Scott
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Cascading rules
I have a one-to-many object relation, A-to-B. When an instance of A with several Bs is created it needs to be persisted by SQLAlchemy. I have that via save-update rule. But when I delete an A I DON'T WANT SQLAlchemy to do anything to its Bs - it's taken care of by foreign key constraints in the DB. Instead it deletes them explicitly if there's a delete cascade rule or tries to nullify Bs' FK fields for any other cascade rule combination. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Identical column names in parent and child classes with joined-table inheritance
This does it. One small drawback is that since the field is now
defined as an attribute, one can't query on it (ie. session.query
(class_).filter_by(modified_by='jack')), but we don't envison such a
use case for this funcionality so it's OK for us.
Recap of what was done: table columns were defined as 'modified', but
these columns were renamed in mappers as 'tablename_modified'. Then,
a 'modified' attribute was added to all classes (well, to the
superclass to that effect) that wraps the value of
'tablename_modified'. Phew!
On Nov 12, 5:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
xaotuk wrote:
We have tried suggested, but: field 'modified' exists in both parent
and child tables, when we redefined property 'modified' in mapper with
something like this:
mapper(Child, child_table, properties={'modified' =
child_table.c.modified, ...}), modified field still returned value
from parent's table.
here is an example illustrating how to move modified away as an
attribute name within each mapped class, allowing them to be accessible
separately despite the tables having the same column names. If you want
to set the parent's modified column on the child, use the
parent_modified attribute:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine(sqlite://, echo=True)
m = MetaData()
parent = Table('parent', m,
Column('id', Integer, primary_key=True),
Column('type', String),
Column('modified', String)
)
child = Table('child', m,
Column('id', Integer, ForeignKey('parent.id'),
primary_key=True),
Column('modified', String)
)
m.create_all(engine)
class Parent(object):
def __init__(self, modified):
self.modified = modified
def modified(self):
return self.parent_modified
def _set_modified(self, m):
self.parent_modified = m
modified = property(modified, _set_modified)
class Child(Parent):
def modified(self):
return self.child_modified
def _set_modified(self, m):
self.child_modified = m
modified = property(modified, _set_modified)
mapper(Parent, parent, polymorphic_on=parent.c.type,
polymorphic_identity='parent', properties={
'parent_modified':parent.c.modified})
mapper(Child, child, inherits=Parent, polymorphic_identity='child',
properties={
'child_modified':child.c.modified
})
s = sessionmaker(engine)()
p1 = Parent('p1')
c1 = Child('c1')
assert p1.modified == 'p1'
assert c1.modified == 'c1'
s.add_all([p1, c1])
s.commit()
assert p1.modified == 'p1'
assert c1.modified == 'c1'
We also tried to add property to class like this:
class Parent(object) :
table = None
...
def _fieldFromQuery(self, field):
if not self.table:
return None
return Session.object_session(self).execute(select([getattr
(self.table.c, field)]).where(self.table.c.id==self.id)).scalar()
def _modified(self):
return self._fieldFromQuery(modified)
def _modified_by(self):
return self._fieldFromQuery(modified_by)
modified = property(_modified)
modified_by = property(_modified_by)
where self.table is assigned just before mapper is created.
Mapper itself again redefined attribute 'modified' and 'modified_by'
so we were back to starting problem.
On Oct 29, 3:56 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanbwrote:
Hi,
Can I have identical column names in both parent and child classes
that are part of a joined-table inheritance? These are simply created,
created_by, modified, modified_by columns that are populated by
defaults defined for them (ie. default, server_default, onupdate).
The values are written to the database correctly, but I have a problem
reading them because parent's column values override child values. So,
if the parent has null values for modified, modified_by and the child
some actual values, ORM returns None for child.modified and
child.modified_by.
use the properties dictionary to mapper to redefine the names. or the
declarative equivalent.
seehttp://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-pro...
Suggestions?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Identical column names in parent and child classes with joined-table inheritance
bojanb wrote:
This does it. One small drawback is that since the field is now
defined as an attribute, one can't query on it (ie. session.query
(class_).filter_by(modified_by='jack')), but we don't envison such a
use case for this funcionality so it's OK for us.
you get this by using synonym() for the new modified attribute on each
mapper. I left this out of the example to avoid confusion.
Recap of what was done: table columns were defined as 'modified', but
these columns were renamed in mappers as 'tablename_modified'. Then,
a 'modified' attribute was added to all classes (well, to the
superclass to that effect) that wraps the value of
'tablename_modified'. Phew!
On Nov 12, 5:06 pm, Michael Bayer mike...@zzzcomputing.com wrote:
xaotuk wrote:
We have tried suggested, but: field 'modified' exists in both parent
and child tables, when we redefined property 'modified' in mapper with
something like this:
mapper(Child, child_table, properties={'modified' =
child_table.c.modified, ...}), modified field still returned value
from parent's table.
here is an example illustrating how to move modified away as an
attribute name within each mapped class, allowing them to be accessible
separately despite the tables having the same column names. If you want
to set the parent's modified column on the child, use the
parent_modified attribute:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine(sqlite://, echo=True)
m = MetaData()
parent = Table('parent', m,
Column('id', Integer, primary_key=True),
Column('type', String),
Column('modified', String)
)
child = Table('child', m,
Column('id', Integer, ForeignKey('parent.id'),
primary_key=True),
Column('modified', String)
)
m.create_all(engine)
class Parent(object):
def __init__(self, modified):
self.modified = modified
def modified(self):
return self.parent_modified
def _set_modified(self, m):
self.parent_modified = m
modified = property(modified, _set_modified)
class Child(Parent):
def modified(self):
return self.child_modified
def _set_modified(self, m):
self.child_modified = m
modified = property(modified, _set_modified)
mapper(Parent, parent, polymorphic_on=parent.c.type,
polymorphic_identity='parent', properties={
'parent_modified':parent.c.modified})
mapper(Child, child, inherits=Parent, polymorphic_identity='child',
properties={
'child_modified':child.c.modified
})
s = sessionmaker(engine)()
p1 = Parent('p1')
c1 = Child('c1')
assert p1.modified == 'p1'
assert c1.modified == 'c1'
s.add_all([p1, c1])
s.commit()
assert p1.modified == 'p1'
assert c1.modified == 'c1'
We also tried to add property to class like this:
class Parent(object) :
table = None
...
def _fieldFromQuery(self, field):
if not self.table:
return None
return Session.object_session(self).execute(select([getattr
(self.table.c, field)]).where(self.table.c.id==self.id)).scalar()
def _modified(self):
return self._fieldFromQuery(modified)
def _modified_by(self):
return self._fieldFromQuery(modified_by)
modified = property(_modified)
modified_by = property(_modified_by)
where self.table is assigned just before mapper is created.
Mapper itself again redefined attribute 'modified' and 'modified_by'
so we were back to starting problem.
On Oct 29, 3:56 pm, Michael Bayer mike...@zzzcomputing.com wrote:
bojanbwrote:
Hi,
Can I have identical column names in both parent and child classes
that are part of a joined-table inheritance? These are simply
created,
created_by, modified, modified_by columns that are populated by
defaults defined for them (ie. default, server_default, onupdate).
The values are written to the database correctly, but I have a
problem
reading them because parent's column values override child values.
So,
if the parent has null values for modified, modified_by and the
child
some actual values, ORM returns None for child.modified and
child.modified_by.
use the properties dictionary to mapper to redefine the names. or
the
declarative equivalent.
seehttp://www.sqlalchemy.org/docs/05/mappers.html#customizing-column-pro...
Suggestions?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Moving (new) objects between sessions and committing?
Greetings SA community! I've got mapped objects being created and added to a session, but not committed. Once in a while, I'd like to do something a-la: oid = 42 object = MyClass(oid) sessionA.add(object) # ... time passes, things happen ... then object = get_obj_from_session(oid, sessionA) sessionA.expunge(object) sessionB = gimme_a_new_session() sessionB.add(object) sessionB.commit() sessionB.close() This fails to actually save the object to the db: nothing is inserted as SA seems to think the object is an up to date reflection of some (non-existent) row in the db. The problem, I think, is the InstanceState associated with this object from its initial residence in sessionA. This state has lotsa stuff in it, including a 'key' attribute that looks something like: (model.MyClass, 42) I've read elsewhere that the solution is do delete the instance state 'key', but this only hangs my program with no indication as to why. In any case, this seems like a solution that involves way to much knowledge of the internals for my taste. I've tried a number of things, everything from trying to merge() into the new session, deleting the entire instance state from the object, deleting + expunging from sessionA and other random things, to no avail. Doing a deep copy of the expunged object might be possible, to then add that to the new session, but would involve a lot of work for something that seems like it should be straightforward with SQLAlchemy... My questions are: - why does the instance state think it doesn't need to insert this object, that was never committed? - SA seems to be acting as if the object is in a Detached state after the expunge, even though it was never committed and does not exist in the db. Why and can I somehow force this to Transient before the sessionB.add() or Pending, after? - why doesn't expunge remove all references to the original session/ state -- is there a way to do this? - maybe I'm going about this completely wrong... any other method I should be using? Thanks in advance and regards, Pat Deegan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Moving (new) objects between sessions and committing?
psychogenic wrote: My questions are: - why does the instance state think it doesn't need to insert this object, that was never committed? it was INSERTed in your other session. You probably didn't commit the transaction. The object then gets the key and such is now persistent, until you expunge it, then its detached. - SA seems to be acting as if the object is in a Detached state after the expunge, even though it was never committed and does not exist in the db. Why and can I somehow force this to Transient before the sessionB.add() or Pending, after? blow away the key.There's a make_transient() function in 0.6 which accomplishes this but its generally state = instance_state(obj); del state.key. the object still might have some attributes on it which you don't want, like primary key attributes that were assigned during the INSERT. You probably want to remove those too. Go through the object's attributes and ensure they all look like a row that hasn't yet been inserted. - why doesn't expunge remove all references to the original session/ state -- is there a way to do this? it removes session state but doesn't remove information about the row represented. Otherwise what state should it have ? - maybe I'm going about this completely wrong... any other method I should be using? You probably shouldn't put things into the session that don't represent rows you'd like to see in your database - right now it seems like you're issuing needless SQL and throwing it away. Thanks in advance and regards, Pat Deegan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Writing a new database dialect
Eric Smith wrote: I'm writing a new database dialect for sqlalchemy 0.6 for Netezza. This is on Windows. I have an ODBC driver for Netezza. A couple of questions: - Why isn't there a generic talk to an ODBC source dialect? I thought that was the beauty of ODBC. Is this possible and just hasn't been done, or is there some technical reason that (for example) the SQL Server ODBC dialect won't just work? there is. it is in sqlalchemy/connectors/pyodbc.py . There is also a placeholder for mxodbc if someone wants to take that up. - Is there some documentation on writing a dialect, specifically for 0.6? I've looked around, but not found anything. Or is my best bet just to look at an existing dialect? you should read the docstring in sqlalchemy/engine/base.py Dialect(), the type_migration_guidelines.txt document, as well as TESTING NEW DIALECTS in the README.unittests file. From there you'll be looking to emulate the existing dialects for file and object structure. You also should consider if you'd like your dialect to remain as a separate product or be part of inclusion to SQLA core. There's really no downside to maintaining as a separate product since you can release bugfixes on an independent schedule, and is how IBM's DB2 dialect works - your dialect is installed as a setuptools entrypoint sqlalchemy.dialects.netazza+dbapiname. Okay, thanks. It looks like the easiest thing to do is to clone the mssql dialect (or mssql+pyodbc) and modify that until I get it to work with Netezza. I'll probably grab parts of the postgres dialect, since I think Netezza is based on that. Thanks again for the pointers. Eric. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] model root object in container hierarchy
Hello! I want to introduce an artificial root object into my model to obtain a traversable container hierarchy: class Root: foos = [] bars = [] Foo and Bar are both mapped classes and I had hoped to be able to do something like mapper(Root, properties=dict(foos=relation(Foo), bars=relation(Bar)) It seems, however, that mapper insists on having *some* table to select from (the error message I get is sqlalchemy.exc.ArgumentError: Mapper 'Mapper|Root|None' does not have a mapped_table specified.). Is there a clever way to do this? Many thanks in advance, Oliver P.S.: I know I can always resort to retrieving the foos and bars explicitly with queries as in class Root: @property def foos(self): return Session.query(Foo).all() but I want to keep the ORM stuff out of my model (plus it vexes me that I don't seem to be able to solve this issue...) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=.
