Re: [sqlalchemy] Filter on relation???
Ok, i already use session.query, no problem with my code. It works very well at the moment. I m just curious about advanced functionnality of my favourite orm :) Thanks to you, i have discovered that we can : - define associationproxy - define a properties which returns the result of a particular queries i will do experimentations now. Thanks for your patience and sry for my poor english 2009/11/24 Michael Bayer mike...@zzzcomputing.com Conor wrote: I cannot speak to how feasible it is to add this feature to a relation, but if it is added then it would likely be built on top of query options since there is so much overlap. I think your multiple definition approach is possible, but it is not something I would want to attempt. I'd like to add that the most flexible way of all to get any information you like from an object attribute is to just use a query() from scratch, acquired via object_session(self). You're already asking for read-only information, therefore you don't need the peristence side of relation(), and as far as loading everything eagerly via join that can still be accomplished just using a join() + extra cols in query() if you really needed it. for reference the general idea of entirely custom properties is at: http://www.sqlalchemy.org/docs/05/mappers.html#building-query-enabled-properties -- 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.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://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 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] dynamic relation forgets join condition on .count() ?
I have a data model which has accounts and events, and a many-to-many relation between the two using a CalendarEvent class. It boils down to this: class Account(BaseObject): __tablename__ = account id = schema.Column(id, types.Integer(), primary_key=True) class Event(BaseObject): __tablename__ = event id = schema.Column(id, types.Integer(), primary_key=True) class CalendarEvent(mBaseObject): __tablename__ = calendar account_id = schema.Column(types.Integer(), schema.ForeignKey(Account.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) account = orm.relation(Account, backref=orm.backref(calendar, lazy=dynamic)) event_id = schema.Column(types.Integer(), schema.ForeignKey(Event.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) event = orm.relation(Event, lazy=False) the calendar backref works fine when you generate a query for it and generates SQL like this: SELECT calendar.account_id AS calendar_account_id, calendar.event_id AS calendar_event_id, event_1.id AS event_1_id FROM event, calendar LEFT OUTER JOIN event AS event_1 ON event_1.id = calendar.event_id WHERE :param_1 = calendar.account_id but if you use acount.calendar.count() the join conditions disappears and you end up with this: SELECT count(1) AS count_1 FROM calendar, event WHERE %(param_1)s = calendar.account_id which results in an incorrect result. Am I doing something wrong here, or could this be a SQLALchemy bug? If so I can try to boil this down to a failing testcase. Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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.
Re: [sqlalchemy] dynamic relation forgets join condition on .count() ?
Wichert Akkerman wrote: I have a data model which has accounts and events, and a many-to-many relation between the two using a CalendarEvent class. It boils down to this: class Account(BaseObject): __tablename__ = account id = schema.Column(id, types.Integer(), primary_key=True) class Event(BaseObject): __tablename__ = event id = schema.Column(id, types.Integer(), primary_key=True) class CalendarEvent(mBaseObject): __tablename__ = calendar account_id = schema.Column(types.Integer(), schema.ForeignKey(Account.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) account = orm.relation(Account, backref=orm.backref(calendar, lazy=dynamic)) event_id = schema.Column(types.Integer(), schema.ForeignKey(Event.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) event = orm.relation(Event, lazy=False) the calendar backref works fine when you generate a query for it and generates SQL like this: SELECT calendar.account_id AS calendar_account_id, calendar.event_id AS calendar_event_id, event_1.id AS event_1_id FROM event, calendar LEFT OUTER JOIN event AS event_1 ON event_1.id = calendar.event_id WHERE :param_1 = calendar.account_id but if you use acount.calendar.count() the join conditions disappears and you end up with this: SELECT count(1) AS count_1 FROM calendar, event WHERE %(param_1)s = calendar.account_id why is event in either of those queries ? In the first case its valid for the LEFT OUTER JOIN since that's the eager load, but I also see it stated a second time, by itself, not associated to anything. Both queries are incorrect. The mapping you illustrate doesn't involve event at all between Account and Calendar, which wouldn't be rendered as a standalone FROM object unless you are specifically saying filter(Event.foo == 'bar') without establishing your join(). -- 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.
Re: [sqlalchemy] dynamic relation forgets join condition on .count() ?
On 2009-11-25 16:15, Michael Bayer wrote: Wichert Akkerman wrote: I have a data model which has accounts and events, and a many-to-many relation between the two using a CalendarEvent class. It boils down to this: class Account(BaseObject): __tablename__ = account id = schema.Column(id, types.Integer(), primary_key=True) class Event(BaseObject): __tablename__ = event id = schema.Column(id, types.Integer(), primary_key=True) class CalendarEvent(mBaseObject): __tablename__ = calendar account_id = schema.Column(types.Integer(), schema.ForeignKey(Account.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) account = orm.relation(Account, backref=orm.backref(calendar, lazy=dynamic)) event_id = schema.Column(types.Integer(), schema.ForeignKey(Event.id, onupdate=CASCADE, ondelete=CASCADE), primary_key=True, nullable=False) event = orm.relation(Event, lazy=False) the calendar backref works fine when you generate a query for it and generates SQL like this: SELECT calendar.account_id AS calendar_account_id, calendar.event_id AS calendar_event_id, event_1.id AS event_1_id FROM event, calendar LEFT OUTER JOIN event AS event_1 ON event_1.id = calendar.event_id WHERE :param_1 = calendar.account_id but if you use acount.calendar.count() the join conditions disappears and you end up with this: SELECT count(1) AS count_1 FROM calendar, event WHERE %(param_1)s = calendar.account_id why is event in either of those queries ? In the first case its valid for the LEFT OUTER JOIN since that's the eager load, but I also see it stated a second time, by itself, not associated to anything. Both queries are incorrect. The mapping you illustrate doesn't involve event at all between Account and Calendar, which wouldn't be rendered as a standalone FROM object unless you are specifically saying filter(Event.foo == 'bar') without establishing your join(). I should have been more explicity, sorry about that. This is the full query: today = datetime.date.today() query = self.context.calendar\ .filter(models.Event.start_date=today)\ .filter(models.Event.state==active)\ .order_by(models.Event.start_date, models.CalendarEvent.start_time, models.Event.start_time) I was expecting the join to automatically created since the Account.calendar relation depends on it. Adding a .join(model.Event) to the query before the filter() calls indeed fixes this. Does SQLAlchemy indeed assume that my usage of models.Event as query filters has no relation at all to the calendar relation? Wichert. -- Wichert Akkerman wich...@wiggy.net It is simple to make things. http://www.wiggy.net/ It is hard to make things simple. -- 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.
Re: [sqlalchemy] dynamic relation forgets join condition on .count() ?
Wichert Akkerman wrote: I was expecting the join to automatically created since the Account.calendar relation depends on it. Adding a .join(model.Event) to the query before the filter() calls indeed fixes this. Does SQLAlchemy indeed assume that my usage of models.Event as query filters has no relation at all to the calendar relation? Filtering never implies how the tables are joined. SQLA never creates a join implicitly, there is always something you did which told it to do so (in the case of the eager load, lazy=False). When querying, there's any number of ways you might want to join two tables together even though there may be one way that is typical (and it is extremely easy to join in the typical way, but you still have to tell it to do such). As far as the eagerload being involved there's some related discussion on this at http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN -- 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] pass any field as a keyword param to the constructor?
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GenericOrmBaseClass
class Recall(OrmObject):pass
mapper(Recall,recall_table)
record=Recall(RECORD_ID=RECORD_ID,CAMPNO=CAMPNO,MAKETXT=MAKETXT)
session.add(record)
session.flush()
This is not working if using the example set in the url. Is setattr
still working.? What is the proper way to do this.for SA
0.5.6?
SQLAlchemy 0.5 Implementation:
class OrmObject(object):
def __init__(self, **kw):
for key in kw:
if not key.startswith('_') and key in self.__dict__:
setattr(self, key, kw[key])
def __repr__(self):
attrs = []
for key in self.__dict__:
if not key.startswith('_'):
attrs.append((key, getattr(self, key)))
return self.__class__.__name__ + '(' + ', '.join(x[0] + '=' +
repr(x[1]) for x in attrs) + ')'
Thanks,
Lucas
--
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] Dynamic mapper issue
Hi, I'm coming to grief trying to create a Mapper on the fly.
At first I tried this
class Dynamic(object):
pass
Dynamic.mapper = mapper(Dynamic, self.j);
self.j is a join statement. This now maps all the columns from the
tables in the join statement. Which is fine. Except that in the SQL
query it looks something like SELECT table1.id AS id, table2.id AS
id etc etc. So that won't work as the columns don't have alias'.
Next I tried something like this
Dynamic.mapper = mapper(Dynamic,select(fields_to_select,from_obj=
[self.j]),primary_key=[table1.c.id,table2.c.id,table3.c.id])
fields_to_select are the fields I actually need, and they look
something like [table1.c.id,table2.c.id,table3.c.id]. If I want to I
imagine I could individually attach a label() to these and that would
overcome any conflicts with the SQL query. That's not the problem
though, as it gives me this error
mapper Mapper|Dynamic|{ANON 158092684 anon} could not assemble any
primary key columns for mapped table '{ANON 158092684 anon}'
I've looked around and read the docs, been through the newgroup but
can't seem to figure out where I've gone wrong. Any ideas ?
Cheers
--
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.
Re: [sqlalchemy] Weird error with update
one advantage of this syntax is that Python will raise an exception that last_access does not exist. yes, and it will be seen at first running -- 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.
