[sqlalchemy] selecting an amibiguous column, but not caring from which table
Hi,
I have the following situation:
There are two tables, A and B , both of which have an id column.
In a certain part of the code I have an ORM query object.
That query object is returned by some function and I have no apriori
knowledge of its structure.
I am assured, though that there will be an 'id' column. It might be a
SELECT from A, a SELECT from B or a SELECT from A join B ON
A.id=B.id.
I would like to execute a query.values('id').
This works for the first two cases but in the case of a join it
complains because it does not know if I mean A.id or B.id.
The thing is that I obviously do not care because they are both equal
as a result of the join condition.
I cannot select A.id or B.id because there is always a case of a query
that does not have A or B.
Is there a way to solve this elegantly? I.e. without having to inspect
that structure of the query first.
--
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] Selecting the right table instance in a self referential join
I have the following mapper:
orm.mapper(Xxx,xxx_table, inherits=Resource,
polymorphic_identity=u'xxx',
properties={'children' : orm.relation(Xxx,
backref=orm.backref('parent', remote_side=[Xxx.c.id]),
primaryjoin=xxx_table.c.rid==xxx_table.c.parent_id)})
When I issue the following join, I get as the selected entity the parent
side rather than the child side of the join.
query = sqlalchemy.orm.query(Xxx)
query = query.join('parent', aliased=True)
query = query.filter(some criterion)
The SQL that is generated is as follows:
SELECT anon_1.resource_id AS anon_1_resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT
resource.id AS resource_id
FROM resource INNER JOIN xxx ON resource.id =
xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name .
What I really want is
SELECT resource_id AS resource_id
FROM resource INNER JOIN xxx ON resource.id = xxx.id INNER JOIN (SELECT
resource.id AS resource_id
FROM resource INNER JOIN xxx ON resource.id =
xxx.id) AS anon_1 ON anon_1.xxx_id = xxx.parent_id
WHERE anon_1.resource_name .
Any help is appreciated.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/Lm2ZI32QbvEJ.
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: Selecting the right table instance in a self referential join
I seem to have solved it by aliasing the first instance too query = sqlalchemy.orm.query(Xxx) *alias = SA.orm.aliased(Xxx)* query = query.join(*(alias,'parent')*, aliased=True) query = query.filter(some criterion) But this basically succeeded by magic when I just tried all sorts of stuff. I can't really understand why an addition of an alias caused the SQL not have an additional alias. Is there a place in the doc that explains this? I don't feel safe with these magical solutions, they tend to break on SA upgrades. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Uv0aBPk1sS4J. 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: What is the Sqlalchemy syntax for having ORDER BY and LIMIT on DELETE
Thanks, I'll check it out. I need the LIMIT in order to delete a lot of old rows in a loop without locking concurrent transactions out for too long (and getting the ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction) error. Can make the timeout longer but that will not let other processes get a chance at querying. The ORDER BY is just for aesthetics, so that I always delete the oldest ones first and not create holes, but I can live without it. On Jun 14, 2:32 pm, Rami Chowdhury rami.chowdh...@gmail.com wrote: On Mon, Jun 13, 2011 at 15:42, Moshe C. mos...@gmail.com wrote: What is the syntax for the where id in (select ... ) ? Does the sqlalchemy.sql.where() function and the in_() operator fit your needs? Can I also ask: why do you want to ORDER BY on a DELETE? On Jun 13, 5:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: That's a MySQL specific syntax you might be better off not using, perhaps you could say delete from table where id in (select id from table order by timestamp limit 10). To get the exact statement, it's probably easiest just to emit the string SQL. If you wanted the sqlalchemy.sql.delete() construct to do it you'd need to subclass Delete, add order_by() and limit() to it, and augment its compilation as described inhttp://www.sqlalchemy.org/docs/core/compiler.html On Jun 13, 2011, at 10:10 AM, Moshe C. wrote: Hi, I am using Sqlalchemy 0.6.5 . How do I generate the following statement usin Sqlalchemy expressions (not ORM). DELETE FROM table ORDER BY timestamp LIMIT 10; TIA -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- Rami Chowdhury Never assume malice when stupidity will suffice. -- Hanlon's Razor +44-7581-430-517 / +1-408-597-7068 / +88-0189-245544 -- 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] What is the Sqlalchemy syntax for having ORDER BY and LIMIT on DELETE
Hi, I am using Sqlalchemy 0.6.5 . How do I generate the following statement usin Sqlalchemy expressions (not ORM). DELETE FROM table ORDER BY timestamp LIMIT 10; TIA -- 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: What is the Sqlalchemy syntax for having ORDER BY and LIMIT on DELETE
What is the syntax for the where id in (select ... ) ? On Jun 13, 5:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: That's a MySQL specific syntax you might be better off not using, perhaps you could say delete from table where id in (select id from table order by timestamp limit 10). To get the exact statement, it's probably easiest just to emit the string SQL. If you wanted the sqlalchemy.sql.delete() construct to do it you'd need to subclass Delete, add order_by() and limit() to it, and augment its compilation as described inhttp://www.sqlalchemy.org/docs/core/compiler.html On Jun 13, 2011, at 10:10 AM, Moshe C. wrote: Hi, I am using Sqlalchemy 0.6.5 . How do I generate the following statement usin Sqlalchemy expressions (not ORM). DELETE FROM table ORDER BY timestamp LIMIT 10; TIA -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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] migrating from 0.4 to 0.6 - PrimaryKeyConstraint.keys()
What replaces the keys() method of PrimaryKeyConstraint that existed in 0.4 ? TIA -- 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=en.
[sqlalchemy] distinct on two fields with a count
How do I write an expression (in sqlalchemy 0.4.6) that will generate the following expression: select count(distinct field1, field2) from tableA; I know how to do it for one field and I know how to do it with no count using distinct=True, but not this combination. -- 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=en.
[sqlalchemy] needed save_or_update(), save() didn't work
Trying to find out if I hit a bug or it is me doing something wrong.
Using version 0.4.6
when creating an object and then calling session.save() I get:
Instance 'res...@-0x486e4074' is already persistent
It works save_or_update() with, but I don't see why I should use that.
I did read that using session.mapper can cause this but I use
orm.mapper.
Here is the mapping code:
metadata = sa.MetaData()
sm = orm.sessionmaker(autoflush=True, transactional=True,
bind=engine)
Model.session = orm.scoped_session(sm)
person_table = sa.Table('person', metadata, autoload = True,
autoload_with=engine)
person_relative_table = sa.Table('person_relative', metadata,
autoload = True, autoload_with=engine)
resume_table = sa.Table('resume', metadata, autoload = True,
autoload_with=engine)
workplace_table = sa.Table('workplace', metadata, autoload =
True, autoload_with=engine)
resume_workplace_table = sa.Table('resume_workplace',
metadata, autoload = True, autoload_with=engine)
orm.mapper(self.Person, person_table, properties = {
'relatives' : orm.relation(self.Person,
secondary=person_relative_table,
primaryjoin=person_table.c.id==person_relative_table.c.person_id,
secondaryjoin=person_relative_table.c.relative_id==person_table.c.id,
backref='followers'),
'resumes' : orm.relation(self.Resume, backref='person')
}
)
orm.mapper(self.Resume, resume_table, properties = {
'workplaces' : orm.relation(self.Workplace,
secondary=resume_workplace_table, backref='resumes')
}
)
orm.mapper(self.Workplace, workplace_table)
--~--~-~--~~~---~--~~
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: needed save_or_update(), save() didn't work
Thanks.
Given that I am not going to upgrade very soon, is it right to
conclude that there was a bug in 0.4.6, or is my usage wrong?
On Jun 3, 5:28 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Moshe C. wrote:
Trying to find out if I hit a bug or it is me doing something wrong.
Using version 0.4.6
when creating an object and then calling session.save() I get:
Instance 'res...@-0x486e4074' is already persistent
It works save_or_update() with, but I don't see why I should use that.
I did read that using session.mapper can cause this but I use
orm.mapper.
session.save() is only used to persist transient instances. It is
deprecated (as is update() and save_or_update()) and not present in
version 0.5. Upgrade to at least 0.4.8 if not 0.5 and use session.add()
(equivalent to save_or_update()), which eliminates the need for the user
to distinguish between transient and detached instances. For a
description of what the heck im talking about when i say transient and
detached
seehttp://www.sqlalchemy.org/docs/05/session.html#quickie-intro-to-objec...
.
Here is the mapping code:
metadata = sa.MetaData()
sm = orm.sessionmaker(autoflush=True, transactional=True,
bind=engine)
Model.session = orm.scoped_session(sm)
person_table = sa.Table('person', metadata, autoload = True,
autoload_with=engine)
person_relative_table = sa.Table('person_relative', metadata,
autoload = True, autoload_with=engine)
resume_table = sa.Table('resume', metadata, autoload = True,
autoload_with=engine)
workplace_table = sa.Table('workplace', metadata, autoload =
True, autoload_with=engine)
resume_workplace_table = sa.Table('resume_workplace',
metadata, autoload = True, autoload_with=engine)
orm.mapper(self.Person, person_table, properties = {
'relatives' : orm.relation(self.Person,
secondary=person_relative_table,
primaryjoin=person_table.c.id==person_relative_table.c.person_id,
secondaryjoin=person_relative_table.c.relative_id==person_table.c.id,
backref='followers'),
'resumes' : orm.relation(self.Resume, backref='person')
}
)
orm.mapper(self.Resume, resume_table, properties = {
'workplaces' : orm.relation(self.Workplace,
secondary=resume_workplace_table, backref='resumes')
}
)
orm.mapper(self.Workplace, workplace_table)
--~--~-~--~~~---~--~~
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: needed save_or_update(), save() didn't work
Well, I thought it was transient.
If you refer to the code in the first post:
If I create a new Resume object and save() it , it works.
If before the save(), I fetch a Workplace object from the DB, then save
() fails and I need to use save_and_update().
So the fact that I queried the DB for a related object, makes the
Resume object not transient anymore?
On Jun 3, 8:56 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Moshe C. wrote:
Thanks.
Given that I am not going to upgrade very soon, is it right to
conclude that there was a bug in 0.4.6, or is my usage wrong?
it is not a bug. save() is used only for transient instances.
On Jun 3, 5:28 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Moshe C. wrote:
Trying to find out if I hit a bug or it is me doing something wrong.
Using version 0.4.6
when creating an object and then calling session.save() I get:
Instance 'res...@-0x486e4074' is already persistent
It works save_or_update() with, but I don't see why I should use that.
I did read that using session.mapper can cause this but I use
orm.mapper.
session.save() is only used to persist transient instances. It is
deprecated (as is update() and save_or_update()) and not present in
version 0.5. Upgrade to at least 0.4.8 if not 0.5 and use
session.add()
(equivalent to save_or_update()), which eliminates the need for the user
to distinguish between transient and detached instances. For a
description of what the heck im talking about when i say transient and
detached
seehttp://www.sqlalchemy.org/docs/05/session.html#quickie-intro-to-objec...
.
Here is the mapping code:
metadata = sa.MetaData()
sm = orm.sessionmaker(autoflush=True, transactional=True,
bind=engine)
Model.session = orm.scoped_session(sm)
person_table = sa.Table('person', metadata, autoload = True,
autoload_with=engine)
person_relative_table = sa.Table('person_relative', metadata,
autoload = True, autoload_with=engine)
resume_table = sa.Table('resume', metadata, autoload = True,
autoload_with=engine)
workplace_table = sa.Table('workplace', metadata, autoload =
True, autoload_with=engine)
resume_workplace_table = sa.Table('resume_workplace',
metadata, autoload = True, autoload_with=engine)
orm.mapper(self.Person, person_table, properties = {
'relatives' : orm.relation(self.Person,
secondary=person_relative_table,
primaryjoin=person_table.c.id==person_relative_table.c.person_id,
secondaryjoin=person_relative_table.c.relative_id==person_table.c.id,
backref='followers'),
'resumes' : orm.relation(self.Resume, backref='person')
}
)
orm.mapper(self.Resume, resume_table, properties = {
'workplaces' : orm.relation(self.Workplace,
secondary=resume_workplace_table, backref='resumes')
}
)
orm.mapper(self.Workplace, workplace_table)
--~--~-~--~~~---~--~~
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: needed save_or_update(), save() didn't work
In code: t = Model.Resume() t.id = something w = Model.session.query(Model.Workplace).filter_by(id=idd).first() # model.save(t) model.save_or_update(t) Without the query line, save() would have worked, but the query is on another object. There is a relation between the objects, but it is not clear how querying on another object makes the Resume object non- transient. On Jun 3, 10:55 pm, Michael Bayer mike...@zzzcomputing.com wrote: Moshe C. wrote: Well, I thought it was transient. If you refer to the code in the first post: your first post has a mapping only. There is no illustration of how you're querying, or using save() or load. --~--~-~--~~~---~--~~ 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: needed save_or_update(), save() didn't work
Weird, the first assertion already fails, but I am not using ScopedSession.mapper. See the code in the first post. On Jun 3, 11:28 pm, Michael Bayer mike...@zzzcomputing.com wrote: easy way to ensure things are working as expected: Moshe C. wrote: In code: t = Model.Resume() t.id = something assert t not in Model.session w = Model.session.query(Model.Workplace).filter_by(id=idd).first() assert t not in Model.session # model.save(t) model.save_or_update(t) --~--~-~--~~~---~--~~ 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: needed save_or_update(), save() didn't work
full code:
import sqlalchemy as sa
from sqlalchemy import orm
class Model:
session = None
class Person(object):
@staticmethod
def query():
return Model.session.query(Model.Person)
class PersonRelative(object):
@staticmethod
def query():
return Model.session.query(Model.PersonRelative)
class Resume(object):
@staticmethod
def query():
return Model.session.query(Model.Resume)
class Workplace(object):
@staticmethod
def query():
return Model.session.query(Model.Workplace)
class ResumeWorkplace(object):
@staticmethod
def query():
return Model.session.query(Model.ResumeWorkplace)
def __init__(self, engine):
Call me before using any of the tables or classes in the
model.
metadata = sa.MetaData()
sm = orm.sessionmaker(autoflush=True, transactional=True,
bind=engine)
Model.session = orm.scoped_session(sm)
person_table = sa.Table('person', metadata, autoload = True,
autoload_with=engine)
person_relative_table = sa.Table('person_relative', metadata,
autoload = True, autoload_with=engine)
resume_table = sa.Table('resume', metadata, autoload = True,
autoload_with=engine)
workplace_table = sa.Table('workplace', metadata, autoload =
True, autoload_with=engine)
resume_workplace_table = sa.Table('resume_workplace',
metadata, autoload = True, autoload_with=engine)
orm.mapper(self.Person, person_table, properties = {
'relatives' : orm.relation(self.Person,
secondary=person_relative_table,
primaryjoin=person_table.c.id==person_relative_table.c.person_id,
secondaryjoin=person_relative_table.c.relative_id==person_table.c.id,
backref='followers'),
'resumes' : orm.relation(self.Resume, backref='person')
}
)
orm.mapper(self.Resume, resume_table, properties = {
'workplaces' : orm.relation(self.Workplace,
secondary=resume_workplace_table, backref='resumes')
}
)
orm.mapper(self.Workplace, workplace_table)
def commit(self):
Model.session.commit()
def save(self, obj):
Model.session.save(obj)
def save_or_update(self, obj):
Model.session.save_or_update(obj)
def flush(self):
Model.session.flush()
def delete(self, obj):
Model.session.delete(obj)
def clear(self):
Model.session.clear()
On Jun 3, 11:47 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Moshe C. wrote:
Weird, the first assertion already fails, but I am not using
ScopedSession.mapper. See the code in the first post.
On Jun 3, 11:28 pm, Michael Bayer mike...@zzzcomputing.com wrote:
easy way to ensure things are working as expected:
Moshe C. wrote:
In code:
t = Model.Resume()
t.id = something
assert t not in Model.session
its not a full example. no imports are illustrated including what
orm.mapper() might be doing. There is obviously code which is setting up
Session.mapper() or otherwise code within Resume().__init__() doing
something similar.
w = Model.session.query(Model.Workplace).filter_by(id=idd).first()
assert t not in Model.session
# model.save(t)
model.save_or_update(t)
--~--~-~--~~~---~--~~
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] Filtering by count of child object
Let's say I have a many to many relationship as for example in:
orm.mapper(self.Person, person_table, properties = {
'relatives' : orm.relation(self.Person,
secondary=person_relative_table,
primaryjoin=person_table.c.id==person_relative_table.c.person_id,
secondaryjoin=person_relative_table.c.relative_id==person_table.c.id,
backref='followers'),
}
Is there an elegant ORM-only expresssion to retrieve all the Persons
that have more than 2 Relatives?
Creating the non-ORM count-join-group by-having expression on the
person_relative_table is easy, but you end up with person ids that you
still have to convert to Person objects.
TIA
Moshe
--~--~-~--~~~---~--~~
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] question regarding aliases in ORM, within an expression
Table T has a self referential parent_id column. 'parent' is an
orm.relation using that column.
I have the following code which obviously does not work
myquery = T.query()
myquery = myquery.outerjoin('parent', aliased=True)
myquery = myquery.reset_joinpoint()
myquery = myquery.order_by(func.if_(T.c.parent_id==None, T.c.name,
T.c.name))
With the 'reset_joinpoint' call, both T.c.name's in the last line will
refer to the first
instance of T. Without that call, both will refer to the joined
instance (will use the alias).
What I want is the first T.c.name in the if_() expression to refer to
the first table instance, and the second T.c.name to refer to the
joined table instance. I.e. the second T.c.name only should use the
alias.
How do I make that work?
--~--~-~--~~~---~--~~
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] preventing aliases
How can I prevent aliases fro appearing in the query? I have hit a MySQL bug that is related to a very biq SQL query string being sent and I am trying to shorten it. I might need an alias on one of the columns, though. The query is created originally by ORM query.compile() and then I create a UNION selection using union() of a couple of those. TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] ORM - Unmapping a class
How would I unmap a class ( so that I can map it to another table from a parallel but distinct DB) ? I can't seem to find the documentation for the Mapper object itself. I may be going the wrong way about it. I want to map the same class to tables in different databases at different times. Where is the recommended point to change the association? The mapper ? The session? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM - Unmapping a class
Thanks.
I do not want to clear all maps, in order not to have to remap some
classes that don't change.
I think (3) answers my needs. I have an app. which has a mode which
tells it with which of 2 DBs (with same schema) it is working . This
mode is not changed often.
If I understood this correctly, I don't really need to touch the
mappings at all since the tables remain the same, but just change the
connection.
I'll try it out.
On Nov 25, 5:25 pm, Michael Bayer [EMAIL PROTECTED] wrote:
Moshe C. wrote:
How would I unmap a class ( so that I can map it to another table from
a parallel but distinct DB) ?
I can't seem to find the documentation for the Mapper object itself.
I may be going the wrong way about it.
I want to map the same class to tables in different databases at
different times.
Where is the recommended point to change the association? The mapper ?
The session?
all classes can be unmapped using clear_mappers(). As for unmapping and
remapping individual classes, we don't provide a public API for this since
the general procedure is problematic - it wouldn't work for mapped classes
where the mappers are related to other mappers (extremely common), and
needing to unmap/remap classes as a matter of course, except for testing
purposes, would perform very poorly and is generally a poor pattern of
use.
There's many ways to relate a single class to multiple tables at different
times, and it mostly depends on what you're trying to do.
Methods for this include:
1. querying against an alternate selectable, which relates to the original
mapped table:
sess.query(MyClass).select_from(some_select).all()
2. Using non_primary mappers. This allows a view to be produced against
an alternate table but persistence is not supported:
mapper(MyClass, someothertable, non_primary=True)
3. If you are merely looking to share the mapping among the same table as
represented in multiple databases, rebinding the session to different
engines will work for a full table approach, or the sharding API can be
used for more of an automatic approach.
4. For a full-blown persist the class in multiple tables approach,
earlier versions of SQLA supported a concept called entity_name. this
feature has been removed in 0.5 since it is essentially redundant against
pure python techniques which integrate more nicely with 0.5s paradigms
(I'm adding this to the Wiki now):
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData(create_engine('sqlite://', echo=True))
t1 = Table('t1', metadata, Column('id', Integer, primary_key=True))
t2 = Table('t2', metadata, Column('id', Integer, primary_key=True))
metadata.create_all()
def map_class_to_some_table(cls, table, entity_name, **kw):
newcls = type.__new__(type, entity_name, (cls, ), {})
mapper(newcls, table, **kw)
return newcls
class Foo(object):
pass
T1Foo = map_class_to_some_table(Foo, t1, T1Foo)
T2Foo = map_class_to_some_table(Foo, t2, T2Foo)
sess = sessionmaker()()
sess.add_all([T1Foo(), T1Foo(), T2Foo(), T1Foo()])
print sess.query(T1Foo).all()
print sess.query(T2Foo).all()
the key assumption when using the entity_name approach is that the
application must be explicitly aware of which mapped class its using.
In previous versions, you didn't have to specify entity_name except when
interacting with the Session. But since a mapping does affect class
behavior, we've now moved towards the specify it up front pattern.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] How to rebind a ScopedSession?
The ScopedSession class has no bind_table method. Is there a way to get at a Session object from ScopedSession object? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] confused about how functions work
table.update(criterion, values={'last_edited' : func.now()} ).execute
()
works
but
table.update(criterion ).execute({'last_edited' : func.now()})
does not. It tries to set 'last_edited' to functions object.
Can someone clarify the difference ?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] ORM Query that selects only a subset of the columns
For Query there is an add_column() method, but I do not see a remove column method. Initializing a Query requires a full mapped class, so how can I select on only a subset of the columns. I want to do this for ding a DISTINCT query on only a couple of columns. TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
0.4.6 On Nov 19, 11:12 pm, Bobby Impollonia [EMAIL PROTECTED] wrote: What version of SQLA are you using? In .5 , you can pass individual columns instead of a mapped class to session.query. On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. [EMAIL PROTECTED] wrote: For Query there is an add_column() method, but I do not see a remove column method. Initializing a Query requires a full mapped class, so how can I select on only a subset of the columns. I want to do this for ding a DISTINCT query on only a couple of columns. TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM Query that selects only a subset of the columns
Apparently, nothing changed specifically with the values() functionality. It was added as _values() in 0.4.5 . I guess 0.4.6 was the release of this feature, then. Cool. Thanks for your help On Nov 20, 12:47 am, Michael Bayer [EMAIL PROTECTED] wrote: if 0.4.6 works you're great. take a look at the changelog to see what bugs have been fixed between 0.4.6 and 0.4.8. Moshe C. wrote: I have tried it out on 0.4.6 and it is working nicely. You mentioned 0.4.7 . Is there any bug I should be aware of in 0.4.6? I cannot upgrade in the near future. On Nov 20, 12:19 am, Michael Bayer [EMAIL PROTECTED] wrote: query.distinct().values() or query.values(func.distinct(func.count(table.c.column))) Moshe C. wrote: How would that work with distinct() ? I see it returns an iterator and not a Query. On Nov 19, 11:39 pm, Michael Bayer [EMAIL PROTECTED] wrote: 0.4.7 and above supports query.values(col1, col2, ...) . use that. Bobby Impollonia wrote: Yeah, with .4 there isn't really a way have an ORM query that doesn't select at least one ORM object (possibly with additional columns/ objects added via add_column/ add_entity). You can use the select() construct instead if pulling all the columns of the mapped class is unacceptable. On Wed, Nov 19, 2008 at 1:25 PM, Moshe C. [EMAIL PROTECTED] wrote: 0.4.6 On Nov 19, 11:12 pm, Bobby Impollonia [EMAIL PROTECTED] wrote: What version of SQLA are you using? In .5 , you can pass individual columns instead of a mapped class to session.query. On Wed, Nov 19, 2008 at 12:17 PM, Moshe C. [EMAIL PROTECTED] wrote: For Query there is an add_column() method, but I do not see a remove column method. Initializing a Query requires a full mapped class, so how can I select on only a subset of the columns. I want to do this for ding a DISTINCT query on only a couple of columns. TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] ORM: Retrieving table from mapped class
Hi, Given a mapped ORM class, is it possible to retrieve from it the Table instabce to which it was mapped? TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM: Retrieving table from mapped class
No, I am not using declarative . On Nov 17, 2:43 am, Bobby Impollonia [EMAIL PROTECTED] wrote: Are you using declarative? If so, your class will have a property called __table__ On Sun, Nov 16, 2008 at 4:04 PM, Moshe C. [EMAIL PROTECTED] wrote: Hi, Given a mapped ORM class, is it possible to retrieve from it the Table instabce to which it was mapped? TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM: Retrieving table from mapped class
thanks On Nov 17, 2:53 am, Michael Bayer [EMAIL PROTECTED] wrote: class_mapper(theclass).mapped_table On Nov 16, 2008, at 7:50 PM, Moshe C. wrote: No, I am not using declarative . On Nov 17, 2:43 am, Bobby Impollonia [EMAIL PROTECTED] wrote: Are you using declarative? If so, your class will have a property called __table__ On Sun, Nov 16, 2008 at 4:04 PM, Moshe C. [EMAIL PROTECTED] wrote: Hi, Given a mapped ORM class, is it possible to retrieve from it the Table instabce to which it was mapped? TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] avoiding locks with SA
Let's say you have two concurrent processes where each might increment
some integer field in some table row.
If you query first and the increment in memory and then update, you
need to query with_lockmode('update') to avoid the case where both
processes read the same value and the do the same increment.
A simpler method is to use SQL s.a. set field = field+1.
Q: How do you generate such SQL with SA ?
Is there a better method to do it?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Temporarily disabling autoflush
Hi, I have an autoflush session and I need it to be so. For one specific query though, I would like no flush to occur. How can I set an existing autoflush session to not autoflush and then reset it back to autoflush? TIA Moshe --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to union with orm, or maybe class inheritance?
regarding union, maybe I am missing something basic. I use the
session.query() with the orm stuff. How do I get the selects from
those ?
On Jun 17, 8:23 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 17, 12:49 pm, Moshe C. [EMAIL PROTECTED] wrote:
I have 3 tables:
general_product {id, name, price} mapped to GeneralProduct
electrical_product {id, name, price, voltage} mapped to
ElectricalProduct
food_product {id,name, price, calories} mapped to FoodProduct
1) I want to create a union query on the above 3 selecting only id,
name and price (which are common fields).
How do I do that? Could find the doc for union only in the non-orm
case.
unions are accomplshed using the union() function, as in
union(select([table.c.x, table.c.y]), select([othertable.c.x,
othertable.c.y]))
2) Since this smells of inheritance, I read about class inheritance
mapping but couldn't decide if and which method applies here.
Help would be greatly appreciated.
I think svil's assumption of joined table inheritance is incorrect
here, since each table contains name and price. This would be
concrete table inheritance, described
athttp://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_...
.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: failure with correlate()
My previous description was a bit simplistic. Turns out the problem
only occurs when a join is involved.
See the following code:
###
from sqlalchemy import *
from sqlalchemy import orm
class One(object): pass
class Many(object): pass
sm = orm.sessionmaker(autoflush=True, transactional=True)
session = orm.scoped_session(sm)
engine = create_engine('mysql://u:[EMAIL PROTECTED]/db')
engine.echo = True
metadata = MetaData(engine)
one_table = Table('one', metadata,
Column('id', Integer, primary_key=True),
Column('a', Integer),
Column('b', Integer)
)
orm.mapper(One, one_table, properties = { 'many' :
orm.relation(Many) })
many_table = Table('many', metadata,
Column('id', Integer, primary_key=True),
Column('one_id', Integer, ForeignKey('one.id')))
orm.mapper(Many, many_table )
metadata.create_all()
myquery = session.query(One).filter(One.many.any(Many.c.id==333)) ##
1
myquery = myquery.filter(One.c.a==55)
#myquery = myquery.add_entity(Many).join('many')##
2
print myquery
###
1) When I run it as it is, i.e. line ##2 is commented out the output
is:
SELECT one.id AS one_id, one.a AS one_a, one.b AS one_b
FROM one
WHERE (EXISTS (SELECT 1
FROM many
WHERE one.id = many.one_id AND many.id = %s)) AND one.a = %s ORDER BY
one.id
which is OK
2) Uncommenting the join in line ##2 causes the following exception
when trying to print the query:
raise exceptions.InvalidRequestError(Select statement '%s' returned
no FROM clauses due to auto-correlation; specify correlate(tables)
to control correlation manually. % self)
3) This is the reason I decided to use correlate(). When I added
correlate(Many) in line ##1 like this:
myquery =
session.query(One).filter(One.many.any(Many.c.id==333).correlate(Many))
## 1
I got the following exception when tryint to print the query:
AttributeError: type object 'Many' has no attribute '_cloned_set'
What is my mistake?
On May 27, 5:42 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 27, 2008, at 4:39 AM, Moshe C. wrote:
I have the following mapping for classes One and Many:
mapper(One, one_table, properties={'many':relation(Many)})
mapper(Many, many_table)
and the following query:
session
.query(One).filter(One.many.any(Many.name.like(expr)).correlate(Many))
I get the exception
exceptions.AttributeError: type object 'Many' has no attribute
'_cloned_set'
What am I doing wrong ?
(correlate(None) works, but does not produce the desired query since
One is also added to the FROM clause)
correlate() accepts Table objects for now.
but also, it doesnt make any sense since any() is designed to produce
a subquery which correlates to the outer One table; correlating to
Many is meaningless here since Many isn't in the enclosing
select() statement.
If any() is producing a query with a FROM list other than just
Many (and you are not introducing other tables inside of your
expr), that would be amazing since we have dozens and dozens of unit
tests for any() in all kinds of situations, please produce a working
test case and file a ticket.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] order of execution with MapperExtension
I have a mapper created with a mapper extension that has an after_update() override. For a table in the mapper I do an update and then a commit(). This is the resulting order of execution: update instance (setting an attribute on the mapped class) commit after_update called on instance I.e. The after_update is called after the commit (after the commit has actually committed to the DB , in fact). How can I cause it to be called before the commit? I have tried flushing explicitly before committing (although the session is autoflush) but it didn't change the behavior. Trying to use before_update instead, didn't change things either. Why the order is important : In the after_update I save to another table of the session. I would like the commit() call to commit the whole transaction - both the original update and the insertion in the aftre_update(). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
PLEASE IGNORE PREVIOUS. It turns out that explicitly flushing does change the order (made a silly coding error before). I am all set, but the question remains why autoflush isn't enough. On May 23, 2:15 pm, Moshe C. [EMAIL PROTECTED] wrote: I have a mapper created with a mapper extension that has an after_update() override. For a table in the mapper I do an update and then a commit(). This is the resulting order of execution: update instance (setting an attribute on the mapped class) commit after_update called on instance I.e. The after_update is called after the commit (after the commit has actually committed to the DB , in fact). How can I cause it to be called before the commit? I have tried flushing explicitly before committing (although the session is autoflush) but it didn't change the behavior. Trying to use before_update instead, didn't change things either. Why the order is important : In the after_update I save to another table of the session. I would like the commit() call to commit the whole transaction - both the original update and the insertion in the aftre_update(). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
I traced what is happening in the code. I don't fully understand it but I hope the following will help. The crucial point is that in my after_update() method I create a mapped object and call session.save() using the same session (but different table). This is the sequence of events: - I call session.commit() - session._prepare_impl is called and the if self.autoflush: is entered and flush() is called - my after_update() is called - session commit is called again from within unitofwork.flush() - it calls self.transaction.commit() which calls self._prepare_impl() - In this call to _prepare_impl() the if self.autoflush: is not entered and there is no flushing Here is the stack at this point: self._prepare_impl() self.session.flush() self.uow.flush(self, objects) session.commit() self.transaction.commit() self._prepare_impl() - On May 23, 6:07 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 10:44 AM, Moshe Cohen wrote: Evidently, when the autoflush occurred within the commit(), the database transaction COMMIT itself happened before the call to after_update() . thats not how it works. the steps are: session.commit() session.flush() mapper._save_obj() before_update/before_insert() UPDATE/INSERT after_update/after_insert() engine.commit() The fact is that explicitly calling session.flush() immediately before calling session.commit(), changed the final state of the DB. This means the commit() with autoflush is not equivalent to flush and then commit. it would be helpful if you could provide the evidence you're basing this on. it sounds like your Session is not actually within a transaction when you call flush(), so that the flush() begins and commits its own transaction. If transactional=True, this would be a bug. But then calling session.commit() should raise an error, so not sure how you'd achieve that behavior, and I'm not seeing any codepath that could produce that behavior - when transactional=True, a begin() is issued in all cases before any flush call. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: order of execution with MapperExtension
OK, thanks very much. My wrong assumption was that saves within the hook functions will make it into the current flush. Preceding the commit() with a manual flush, causes the commit to flush this new saves (that occured in the flush). Makes sense now :-) On May 24, 2:52 am, Michael Bayer [EMAIL PROTECTED] wrote: On May 23, 2008, at 6:54 PM, Moshe C. wrote: I traced what is happening in the code. I don't fully understand it but I hope the following will help. The crucial point is that in my after_update() method I create a mapped object and call session.save() using the same session (but different table). session.save() within a mapper extension's before/after update/insert does nothing for the span of that flush. The full list of objects to be sent to the database is already determined at that point and new objects in the session won't get flushed until the next flush (which in this case is outside of the commit()). if you'd like to affect the flush plan upon flush(), you'd have to implement before_flush() on a SessionExtension. Or just stick to manually calling flush() if that solves the particular ordering issue. This is the sequence of events: - I call session.commit() - session._prepare_impl is called and the if self.autoflush: is entered and flush() is called - my after_update() is called - session commit is called again from within unitofwork.flush() this is normal. the unitofwork has its own transaction, which may or may not be a subtransaction. in this case, its a subtransaction; nothing actually happens. - it calls self.transaction.commit() which calls self._prepare_impl() - In this call to _prepare_impl() the if self.autoflush: is not entered and there is no flushing this is because the flush guards against reentrant calls to itself. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] 0.3 -- 0.4 : dictionaries for execute()
This is probably very simple, but I am searching the docs and not finding the answer :-( In 0.3 I had a working statement of the form: engine.execute(table.select(), cond_dict) where cond_dict is a dictionary of column names mapped to values. In 0.4.6 this does not work. What is produced in SQL is a select statement w/o the WHERE clause. What is the correct way to migrate it, while still using the dictionary object? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.3 -- 0.4 : dictionaries for execute()
Thanks. So I guess you cannot use the dictionary argument as is. It was very convenient. On May 21, 6:16 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 21, 2008, at 11:06 AM, Moshe C. wrote: This is probably very simple, but I am searching the docs and not finding the answer :-( In 0.3 I had a working statement of the form: engine.execute(table.select(), cond_dict) where cond_dict is a dictionary of column names mapped to values. In 0.4.6 this does not work. What is produced in SQL is a select statement w/o the WHERE clause. What is the correct way to migrate it, while still using the dictionary object? table.select().where(and_(*[table.c[k] == v for k, v in cond_dict.iteritems()])) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: retrieving mapped objects from within a session
Got back to this issue after a while. The SessionExtension objects allows me to hook on to a session and get notified of various events. My question is different: Given a session, before commit, how can I query it to know what is going to happen at commit. My intention is to derive from that, a corresponding change to another table, for the purpose of audit trail. On Apr 6, 7:40 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 6, 2008, at 11:44 AM,MosheC. wrote: Is it possible to retrieve mapped objects from a session. The motivation: I want to maintain history log tables where a row is added per each update or insert on the corresponding main table. The schema of the tables is identical except for an additional timestamp in the history table. I want to do this in one place and wrapping the commit() function seems appropriate. take a look at SessionExtension: http://www.sqlalchemy.org/docs/04/sqlalchemy_orm_session.html#docstri... --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Ordering results ina self-referential join
Hi,
Node is an orm mapped class, which is self-referential.
myquery = Node.query()
myquery = myquery.join('parent', aliased=True)
myquery = myquery.filter(Node.c.desc.like('%something'))
myquery = myquery.order_by(Node.c.name)
The last line orders by the 'name' of the 2nd joined table.
How can I add another order_by (after the one above) that orders by
some column of the first instance of the table?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Ordering results ina self-referential join
Both methods cause a crash (yes, on 0.4.5) .
converting a tuple to a list in sqlalchemy/orm/query.py fixes it for
one of the methods, for the other you need to do the opposite, convert
a list to a tuple.
File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in
list_sources
myquery =
myquery.reset_joinpoint().order_by(model.Source.c.popularity)
File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in
starargs_as_list
return func(self, *to_list(args[0], []), **kwargs)
File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in
order_by
q._order_by = q._order_by + criterion
TypeError: can only concatenate list (not tuple) to list
On May 6, 8:45 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 6, 2008, at 1:43 PM, Moshe C. wrote:
Hi,
Node is an orm mapped class, which is self-referential.
myquery = Node.query()
myquery = myquery.join('parent', aliased=True)
myquery = myquery.filter(Node.c.desc.like('%something'))
myquery = myquery.order_by(Node.c.name)
The last line orders by the 'name' of the 2nd joined table.
How can I add another order_by (after the one above) that orders by
some column of the first instance of the table?
place another order_by() either before the join(), or after calling
reset_joinpoint(). Make sure you're on 0.4.5.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Ordering results ina self-referential join
I couldn't create a simple test case, but I have analyzed the cause of
the problem.
The order_by() method is sent a list as an argument, but the argument
'criterion' becomes a tuple
because of the def order_by(self, *criterion) syntax.
in the case of if self._aliases_tail: , 'criterion' becomes a list
again, but if _aliases_tail is None it remains a tuple.
Now the cause of the problem is that on the first call to order_by(),
self._aliases_tail exists, and on the 2nd call, following the
reset_joinpoint() call, it is None. Therefore the '_order_by member'
is initialized as a list, and later a tuple is attempted to be
concatenated and hence the failure.
The calling code from my source looks like this:
myquery = Node.query()
myquery = myquery.join('parent', aliased=True)
myquery = myquery.order_by(Node.c.name) #
_aliases_tail exists for this call
myquery = myquery.reset_joinpoint().order_by(Node.c.popularity) #
_aliases_tail is None for this call
On May 6, 10:25 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On May 6, 2008, at 3:08 PM, Moshe C. wrote:
Both methods cause a crash (yes, on 0.4.5) .
converting a tuple to a list in sqlalchemy/orm/query.py fixes it for
one of the methods, for the other you need to do the opposite, convert
a list to a tuple.
File '/home/moshe/top/webapp/rma/rma/controllers/list.py', line 215 in
list_sources
myquery =
myquery.reset_joinpoint().order_by(model.Source.c.popularity)
File '/home/moshe/top/webapp/rma/sqlalchemy/util.py', line 198 in
starargs_as_list
return func(self, *to_list(args[0], []), **kwargs)
File '/home/moshe/top/webapp/rma/sqlalchemy/orm/query.py', line 571 in
order_by
q._order_by = q._order_by + criterion
TypeError: can only concatenate list (not tuple) to list
I cant reproduce that at all, even sending purposely wacky arguments
to the previous order_by().can you please provide a test case ?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] retrieving mapped objects from within a session
Is it possible to retrieve mapped objects from a session. The motivation: I want to maintain history log tables where a row is added per each update or insert on the corresponding main table. The schema of the tables is identical except for an additional timestamp in the history table. I want to do this in one place and wrapping the commit() function seems appropriate. TIA --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] insert of a sequence of dictionaries
The following line:
r = cnnctn.execute(insert(t),
{'xkey': 'k1','yval':1},
{'xkey': 'k2','yval':2},
{'xkey': 'k3'})
Cause the following :
INSERT INTO `A` (xkey, yval) VALUES (%s, %s)
[['k1', 1], ['k2', 2], ['k3', 1]]
i.e. the unspecified value in the 3rd dict is copied from the 1st one.
This is quite surprising as I would have expected null() to be used in
such cases.
Is there a rationale behind this?
Is there a way to have some non-full dicts as in the above example so
that it will behave as I expected?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: outerjoin constructor throws exception from 0.3.9. Bug or user error?
Raising this after 3 days, still hoping for help :-) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Testing for validity of a Connection
I was hoping there was something more elegant than just trying and
catching a possible exception.
The motivation is just simpler and more readable code like
if not connection.is_valid():
get another one
On Aug 30, 5:25 pm, Paul Johnston [EMAIL PROTECTED] wrote:
How about, conn.execute('select 1')
On 8/30/07, Moshe C. [EMAIL PROTECTED] wrote:
How can I test whether a connection object is valid and hasn't, for
example, been time outed by the server?
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
