[sqlalchemy] Re: Number of row updated or deleted
Use rowcount property of the ResultProxy returned from delete/update result = conn.execute(tbl.delete()) count = result.rowcount Note that the quality of the number will depend on the underlying database and Python dbapi. --~--~-~--~~~---~--~~ 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] relations with additional criteria
Hi there, The relation() logic lets you write an additional filter into a relation if you provide your own primaryjoin. The case I'm dealing with is a case where I want to take over the primaryjoin that is autogenerated by the RelationProperty object, but amend it with an extra filter. This turns out to be surprisingly difficult to do in the current codebase. In fact I haven't made it work properly yet. Is there a reason this hook isn't available? Perhaps it's because in my use case I know there is a certain column available on the child table no matter what table it is, and that's not so common. I imagine an optional callable that takes the parent and child tables as arguments and can return a custom filter expression on them. I haven't thought through how this works with many to many relationships yet though. Regards, Martijn --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
Wyatt Lee Baldwin wrote: I have a relation defined like this in a declarative-style class: route = relation( RouteDef, primaryjoin=( (route_number == RouteDef.route_number) (route_begin_date = RouteDef.route_begin_date) ), ) What I *really* want is for `route_number` and `route_begin_date` to be substituted with literal values when I do `instance.route`. Instead, I get a big nasty join that runs forever. Instead of the generated SQL containing a literal date value, for example, the SQL contains the column name, `route_begin_date`. I don't see this join condition related to the parent (or even what the parent is). So an eagerload will produce a big join, sure. The join expression needs to relate the child to the parent in some way. Using literals within that condition is not an issue. --~--~-~--~~~---~--~~ 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: relations with additional criteria
Martijn Faassen wrote: Hi there, The relation() logic lets you write an additional filter into a relation if you provide your own primaryjoin. The case I'm dealing with is a case where I want to take over the primaryjoin that is autogenerated by the RelationProperty object, but amend it with an extra filter. This turns out to be surprisingly difficult to do in the current codebase. In fact I haven't made it work properly yet. Is there a reason this hook isn't available? Perhaps it's because in my use case I know there is a certain column available on the child table no matter what table it is, and that's not so common. Whats missing here is the context. From what you describe, use the primaryjoin and amend with an extra filter, that is just: query(Parent).join(Parent.children).filter(Child.foo==bar) OTOH if the context you're seeking is: Parent.children.filter(Child.foo==bar).all() that is what a dynamic relation does. If you're just looking to get the join condition between the two tables with which to build more complex conditions, its primaryjoin on the RelationProperty, or Parent.children.property.primaryjoin; or you could just say table.join(othertable).onclause. --~--~-~--~~~---~--~~ 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: relations with additional criteria
Michael Bayer wrote:
[snip]
Whats missing here is the context.
I want to define ORM relations in the mapper that I can access without
having to do a manual join.
Normally you'd do this:
mapper(A, a_table,
properties={
'bs': relation(B , backref='a',
primaryjoin=and_(a_table.c_id == b_table.c.a_id,
b_table.c.status == 'FOO'),
})
But I'd like to automate this:
mapper(A, a_table,
properties={
'bs': my_own_relation(B , backref='a'),
})
my_own_relation behaves like relation, except it adds an extra clause
restricting the query, say, b_table.c.status == 'FOO'. It should have
access to the parent and child tables so it can do this generically.
I've tried to accomplish this by overriding _determine_joins and
manipulating self.primaryjoin after the default is set up, but I think
that breaks because backrefs have already been set up earlier. I've
hacked around that now I think, but it's not very pretty.
From what you describe, use the
primaryjoin and amend with an extra filter, that is just:
query(Parent).join(Parent.children).filter(Child.foo==bar)
This presents a potential alternative implementation strategy where this
is implemented using properties on the model classes that do something
like this.
Regards,
Martijn
--~--~-~--~~~---~--~~
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] Orm slow to update why?
i made a test i did that without sql alchemy orm: import MySQLdb import time # Establich a connection db = MySQLdb.connection(host=192.168.45.28, user=apm, passwd=apm, db=test_christian) # Run a MySQL query from Python and get the result set xref = time.time() db.query(UPDATE utilisateur SET Dispo=1 WHERE IdUtilisateur=1) r = db.store_result() print time.time()-xref # Iterate through the result set # Example calls back up to 100 rows # for row in r.fetch_row(100): # print row *EXECUTE* Z:\python TestSql.py 0.10867575 It takes 0.1 s (the database is far away from the code) -- And then i test this (just a part of my application) def SeRendreIndisponible(self,event): # FONCTION COSMOCOM xref = time.time() if self.app.connec[CouplageCosmocom]==0 : return if hasattr(self, 'agent') : try : self.agent.MakeReleased() except : self.app.ReconnexionAgent() self.agent.MakeReleased() print SeRendreIndisponible PARTIE AGENT, time.time()-xref xref = time.time() self.UtilisateurCourant.Dispo = 0 if self.UtilisateurCourant.IdUtilisateur: x = self.ModifBase(self.UtilisateurCourant) # on réactualise l'état de la bdd print , x print SeRendreIndisponible PARTIE ECRITURE ETAT, time.time()-xref def ModifBase(self, objet): if int(self.app.param[Debug]) : print M, objet # try: x = session.merge(objet) session.flush() *EXECUTE* SeRendreIndisponible PARTIE AGENT 0.0 M Declaration.Utilisateur object at 0x032C0DD0 None SeRendreIndisponible PARTIE ECRITURE ETAT 0.9373624 It takes 0.9 seconds with SqlAlchemy (0.1) Why? --~--~-~--~~~---~--~~ 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: Orm slow to update why?
Christian Démolis wrote: i made a test i did that without sql alchemy orm: import MySQLdb import time # Establich a connection db = MySQLdb.connection(host=192.168.45.28, user=apm, passwd=apm, db=test_christian) # Run a MySQL query from Python and get the result set xref = time.time() db.query(UPDATE utilisateur SET Dispo=1 WHERE IdUtilisateur=1) r = db.store_result() print time.time()-xref # Iterate through the result set # Example calls back up to 100 rows # for row in r.fetch_row(100): # print row *EXECUTE* Z:\python TestSql.py 0.10867575 It takes 0.1 s (the database is far away from the code) -- And then i test this (just a part of my application) def SeRendreIndisponible(self,event): # FONCTION COSMOCOM xref = time.time() if self.app.connec[CouplageCosmocom]==0 : return if hasattr(self, 'agent') : try : self.agent.MakeReleased() except : self.app.ReconnexionAgent() self.agent.MakeReleased() print SeRendreIndisponible PARTIE AGENT, time.time()-xref xref = time.time() self.UtilisateurCourant.Dispo = 0 if self.UtilisateurCourant.IdUtilisateur: x = self.ModifBase(self.UtilisateurCourant) # on réactualise l'état de la bdd print , x print SeRendreIndisponible PARTIE ECRITURE ETAT, time.time()-xref def ModifBase(self, objet): if int(self.app.param[Debug]) : print M, objet # try: x = session.merge(objet) session.flush() *EXECUTE* SeRendreIndisponible PARTIE AGENT 0.0 M Declaration.Utilisateur object at 0x032C0DD0 None SeRendreIndisponible PARTIE ECRITURE ETAT 0.9373624 It takes 0.9 seconds with SqlAlchemy (0.1) using the profile module will grant some perspective over what is going on. For one thing, I can see at least four method or function calls in your ORM example that aren't in the DBAPI version. For example, what does MakeReleased() do ? Additionally, there's a try/except there. If an exception is actually thrown, that alone could cause 50% of the time overhead since exception throws are slow. As far as the ORM itself, an ORM using unit of work doesn't know what kind of UPDATE statement or statements should be generated ahead of time so needs to perform many additional steps (fast steps, but more nonetheless) in order to determine what needs to happen. Again the profile module will illustrate this. You also should be on the latest 0.5 release of SQLA as there were some bottlenecks that were fixed earlier in the 0.5 series. --~--~-~--~~~---~--~~ 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: relations with additional criteria
Martijn Faassen wrote:
But I'd like to automate this:
mapper(A, a_table,
properties={
'bs': my_own_relation(B , backref='a'),
})
my_own_relation behaves like relation, except it adds an extra clause
restricting the query, say, b_table.c.status == 'FOO'. It should have
access to the parent and child tables so it can do this generically.
I've tried to accomplish this by overriding _determine_joins and
manipulating self.primaryjoin after the default is set up, but I think
that breaks because backrefs have already been set up earlier. I've
hacked around that now I think, but it's not very pretty.
OK well I'm sure you noticed that RelationProperty was not designed to be
subclassed. I would advise that your my_own_relation() function generate
its own primaryjoin and secondaryjoin conditions which it passes as
arguments to the relation(). If you look at the source of
_determine_joins(), it uses the table.join(othertable).onclause idea, but
more cleanly through a utility function called join_condition().it
calls it twice to accommodate some more exotic use cases. I think in
general its fine to call as join_condition(prop.parent, prop.target) which
will give you what you need.
--~--~-~--~~~---~--~~
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: Relation using = operator and literal values
On Sep 29, 7:55 am, Michael Bayer mike...@zzzcomputing.com wrote: Wyatt Lee Baldwin wrote: I have a relation defined like this in a declarative-style class: route = relation( RouteDef, primaryjoin=( (route_number == RouteDef.route_number) (route_begin_date = RouteDef.route_begin_date) ), ) What I *really* want is for `route_number` and `route_begin_date` to be substituted with literal values when I do `instance.route`. Instead, I get a big nasty join that runs forever. Instead of the generated SQL containing a literal date value, for example, the SQL contains the column name, `route_begin_date`. I don't see this join condition related to the parent (or even what the parent is). So an eagerload will produce a big join, sure. The join expression needs to relate the child to the parent in some way. Using literals within that condition is not an issue. In my view (which may be warped), a Trip has one Route (and many Trips follow the same Route). Here's more context: class Trip(Base): __tablename__ = 'trip' __table_args__ = dict(schema='trans') __mapper_args__ = dict( order_by='trip_begin_date,trip_begin_time,route_number,direction') route_number = Column(Integer, ForeignKey(RouteDef.route_number), primary_key=True) direction = Column(Integer, primary_key=True) service_key = Column(CHAR(3), primary_key=True) trip_number = Column(Integer, primary_key=True) trip_begin_date = Column(Date, primary_key=True) trip_end_date = Column(Date) route_begin_date = Column(Date) pattern_id = Column(Integer) trip_begin_time = Column(Numeric(8)) trip_end_time = Column(Numeric(8)) @property def route(self): This works, but I want to use a `relation`. try: self._route except AttributeError: session = object_session(self) q = session.query(RouteDef) q = q.filter(self.route_number == RouteDef.route_number) q = q.filter(self.route_begin_date = RouteDef.route_begin_date) q = q.filter(self.route_begin_date = RouteDef.route_end_date) self._route = q.one() return self._route Even though `route_number` is defined as a foreign key in the Trip class, there's not actually a FK constraint in the DB; in practice, though, `route_number` *is* a FK. `route_begin_date` perhaps should be a FK, but for legacy reasons, it's not, and that's the reason for the BETWEEN filtering. This is how I tried to define the `relation` but which results in a big join: route = relation(RouteDef, primaryjoin=( (route_number == RouteDef.route_number) (route_begin_date = RouteDef.route_begin_date) (route_begin_date = RouteDef.route_end_date) ) --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
Wyatt Lee Baldwin wrote: In my view (which may be warped), a Trip has one Route (and many Trips follow the same Route). Here's more context: class Trip(Base): __tablename__ = 'trip' __table_args__ = dict(schema='trans') __mapper_args__ = dict( order_by='trip_begin_date,trip_begin_time,route_number,direction') route_number = Column(Integer, ForeignKey(RouteDef.route_number), primary_key=True) direction = Column(Integer, primary_key=True) service_key = Column(CHAR(3), primary_key=True) trip_number = Column(Integer, primary_key=True) trip_begin_date = Column(Date, primary_key=True) trip_end_date = Column(Date) route_begin_date = Column(Date) pattern_id = Column(Integer) trip_begin_time = Column(Numeric(8)) trip_end_time = Column(Numeric(8)) @property def route(self): This works, but I want to use a `relation`. try: self._route except AttributeError: session = object_session(self) q = session.query(RouteDef) q = q.filter(self.route_number == RouteDef.route_number) q = q.filter(self.route_begin_date = RouteDef.route_begin_date) q = q.filter(self.route_begin_date = RouteDef.route_end_date) self._route = q.one() return self._route Even though `route_number` is defined as a foreign key in the Trip class, there's not actually a FK constraint in the DB; in practice, though, `route_number` *is* a FK. `route_begin_date` perhaps should be a FK, but for legacy reasons, it's not, and that's the reason for the BETWEEN filtering. This is how I tried to define the `relation` but which results in a big join: route = relation(RouteDef, primaryjoin=( (route_number == RouteDef.route_number) (route_begin_date = RouteDef.route_begin_date) (route_begin_date = RouteDef.route_end_date) ) OK I hope you can see that the example you originally sent me didn't explain that route_number and route_begin_date are columns, and not int/date objects. Anyway the primaryjoin here looks fine and does represent the same thing you're getting in your route() @property. It's a simple many-to-one with an additional criterion. Nothing needs to be configured in the database as far as foreign keys, configuring it as such within the table metadata is all SQLA cares about. --~--~-~--~~~---~--~~ 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] issue with column_property
Hi
Here's my case: I have
- my own TypeEngine class, MyTypeEngine
- a Table with a Column using MyTypeEngine:
table = Table(tablename, metadata,
Column(columname, MyTypeEngine()),
autoload=True, autoload_with=engine
)
- a class:
class MyClass(object):
pass
- and a mapping:
mapper(MyClass, table)
Pretty standard. Now, instead of queries like this:
SELECT columnname, ... FROM tablename
I'd like queries like this:
SELECT somefunc(columnname),... FROM tablename
I thought I could get that by overriding my column's ColumnProperty
with something like that:
mapper(MyClass, table, properties={
columname: column_property(
sql.func.somefunc(table.c.columname).label(columnname)
)
})
but it doesn't work as I'd like because it seems that my TypeEngine is
no longer involved after querying my table - the function returned by
my TypeEngine's result_process method doesn't seem to be called.
Is this expected? Do I have solutions to that problem?
Thanks a lot,
--
Eric Lemoine
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Tel : 00 33 4 79 44 44 96
Mail : eric.lemo...@camptocamp.com
http://www.camptocamp.com
--~--~-~--~~~---~--~~
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: issue with column_property
Eric Lemoine wrote:
Hi
Here's my case: I have
- my own TypeEngine class, MyTypeEngine
- a Table with a Column using MyTypeEngine:
table = Table(tablename, metadata,
Column(columname, MyTypeEngine()),
autoload=True, autoload_with=engine
)
- a class:
class MyClass(object):
pass
- and a mapping:
mapper(MyClass, table)
Pretty standard. Now, instead of queries like this:
SELECT columnname, ... FROM tablename
I'd like queries like this:
SELECT somefunc(columnname),... FROM tablename
I thought I could get that by overriding my column's ColumnProperty
with something like that:
mapper(MyClass, table, properties={
columname: column_property(
sql.func.somefunc(table.c.columname).label(columnname)
)
})
but it doesn't work as I'd like because it seems that my TypeEngine is
no longer involved after querying my table - the function returned by
my TypeEngine's result_process method doesn't seem to be called.
Is this expected? Do I have solutions to that problem?
somefunc() needs to specify the return type using type_=MyType, unless its
a known generic function that knows to pass through the type of the
first argument as that of the result (such as lower(), for example).
--~--~-~--~~~---~--~~
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: issue with column_property
On Tue, Sep 29, 2009 at 9:04 PM, Michael Bayer mike...@zzzcomputing.com wrote:
Eric Lemoine wrote:
Hi
Here's my case: I have
- my own TypeEngine class, MyTypeEngine
- a Table with a Column using MyTypeEngine:
table = Table(tablename, metadata,
Column(columname, MyTypeEngine()),
autoload=True, autoload_with=engine
)
- a class:
class MyClass(object):
pass
- and a mapping:
mapper(MyClass, table)
Pretty standard. Now, instead of queries like this:
SELECT columnname, ... FROM tablename
I'd like queries like this:
SELECT somefunc(columnname),... FROM tablename
I thought I could get that by overriding my column's ColumnProperty
with something like that:
mapper(MyClass, table, properties={
columname: column_property(
sql.func.somefunc(table.c.columname).label(columnname)
)
})
but it doesn't work as I'd like because it seems that my TypeEngine is
no longer involved after querying my table - the function returned by
my TypeEngine's result_process method doesn't seem to be called.
Is this expected? Do I have solutions to that problem?
somefunc() needs to specify the return type using type_=MyType, unless its
a known generic function that knows to pass through the type of the
first argument as that of the result (such as lower(), for example).
it's a postgis function. How do I specify the return type using
type_=MyType? Sorry i didn't follow you on that one.
thanks
--
Eric Lemoine
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Tel : 00 33 4 79 44 44 96
Mail : eric.lemo...@camptocamp.com
http://www.camptocamp.com
--~--~-~--~~~---~--~~
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: relations with additional criteria
Hey, Michael Bayer wrote: OK well I'm sure you noticed that RelationProperty was not designed to be subclassed. Yeah, my subclass isn't pretty. :) I would advise that your my_own_relation() function generate its own primaryjoin and secondaryjoin conditions which it passes as arguments to the relation(). If you look at the source of _determine_joins(), it uses the table.join(othertable).onclause idea, but more cleanly through a utility function called join_condition().it calls it twice to accommodate some more exotic use cases. I think in general its fine to call as join_condition(prop.parent, prop.target) which will give you what you need. Thanks, I know all about _determine_joins() now that I wrestled with the subclassing approach, and will try to rewrite my code so it uses a similar approach. We'll see how it turns out. Regards, Martijn --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
On Sep 29, 11:53 am, Michael Bayer mike...@zzzcomputing.com wrote: Wyatt Lee Baldwin wrote: In my view (which may be warped), a Trip has one Route (and many Trips follow the same Route). Here's more context: class Trip(Base): __tablename__ = 'trip' __table_args__ = dict(schema='trans') __mapper_args__ = dict( order_by='trip_begin_date,trip_begin_time,route_number,direction') route_number = Column(Integer, ForeignKey(RouteDef.route_number), primary_key=True) direction = Column(Integer, primary_key=True) service_key = Column(CHAR(3), primary_key=True) trip_number = Column(Integer, primary_key=True) trip_begin_date = Column(Date, primary_key=True) trip_end_date = Column(Date) route_begin_date = Column(Date) pattern_id = Column(Integer) trip_begin_time = Column(Numeric(8)) trip_end_time = Column(Numeric(8)) @property def route(self): This works, but I want to use a `relation`. try: self._route except AttributeError: session = object_session(self) q = session.query(RouteDef) q = q.filter(self.route_number == RouteDef.route_number) q = q.filter(self.route_begin_date = RouteDef.route_begin_date) q = q.filter(self.route_begin_date = RouteDef.route_end_date) self._route = q.one() return self._route Even though `route_number` is defined as a foreign key in the Trip class, there's not actually a FK constraint in the DB; in practice, though, `route_number` *is* a FK. `route_begin_date` perhaps should be a FK, but for legacy reasons, it's not, and that's the reason for the BETWEEN filtering. This is how I tried to define the `relation` but which results in a big join: route = relation(RouteDef, primaryjoin=( (route_number == RouteDef.route_number) (route_begin_date = RouteDef.route_begin_date) (route_begin_date = RouteDef.route_end_date) ) OK I hope you can see that the example you originally sent me didn't explain that route_number and route_begin_date are columns, and not int/date objects. Yeah, sorry about that. /appropriately chagrined Anyway the primaryjoin here looks fine and does represent the same thing you're getting in your route() @property. It's a simple many-to-one with an additional criterion. Nothing needs to be configured in the database as far as foreign keys, configuring it as such within the table metadata is all SQLA cares about. I'm still not clear if/how I can replace the property with the relation. When I use the relation shown above, I get the big join. Sorry if I'm being daft. --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
Wyatt Lee Baldwin wrote: Anyway the primaryjoin here looks fine and does represent the same thing you're getting in your route() @property. It's a simple many-to-one with an additional criterion. Nothing needs to be configured in the database as far as foreign keys, configuring it as such within the table metadata is all SQLA cares about. I'm still not clear if/how I can replace the property with the relation. When I use the relation shown above, I get the big join. Sorry if I'm being daft. what big join ? --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
On Sep 29, 2:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Wyatt Lee Baldwin wrote:
Anyway the primaryjoin here looks fine and does
represent the same thing you're getting in your route() @property.
It's
a simple many-to-one with an additional criterion. Nothing needs to be
configured in the database as far as foreign keys, configuring it as
such
within the table metadata is all SQLA cares about.
I'm still not clear if/how I can replace the property with the
relation. When I use the relation shown above, I get the big join.
Sorry if I'm being daft.
what big join ?
When I define the route relation as above (2nd version) and access the
route attribute of a Trip, I eventually get a MemoryError. I assume
this is because of a join. When I use the property version, accessing
route is speedy. Here is the SQL that's being generated from the
relation (inside a `paster shell` session):
In [14]: trip = sess.query(Trip).first()
In [15]: trip.route_number
Out[15]: 925
In [16]: trip.route_begin_date
Out[16]: datetime.date(2007, 5, 13)
In [17]: trip.route
14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] SELECT
trans.route_def.route_number AS trans_route_def_route_number,
trans.route_def.route_begin_date AS trans_route_def_route_be_1,
trans.route_def.route_end_date AS trans_route_def_route_end_date,
trans.route_def.public_route_description AS
trans_route_def_public_r_2, trans.route_def.route_type AS
trans_route_def_route_type, trans.route_def.route_sub_type AS
trans_route_def_route_sub_type, trans.route_def.route_usage AS
trans_route_def_route_usage, trans.route_def.route_sort_order AS
trans_route_def_route_so_3
FROM trans.route_def, trans.trip
WHERE :param_1 = trans.route_def.route_number AND
trans.trip.route_begin_date = trans.route_def.route_begin_date AND
trans.trip.route_begin_date = trans.route_def.route_end_date ORDER BY
route_sort_order
14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] {'param_1': 925}
14:40:01,379 DEBUG [trimet.db.TriMetEngine.0x...2e8c] Col
('TRANS_ROUTE_DEF_ROUTE_NUMBER', 'TRANS_ROUTE_DEF_ROUTE_BE_1',
'TRANS_ROUTE_DEF_ROUTE_END_DATE', 'TRANS_ROUTE_DEF_PUBLIC_R_2',
'TRANS_ROUTE_DEF_ROUTE_TYPE', 'TRANS_ROUTE_DEF_ROUTE_SUB_TYPE',
'TRANS_ROUTE_DEF_ROUTE_USAGE', 'TRANS_ROUTE_DEF_ROUTE_SO_3')
After a delay, with `engine.echo = True`, this starts spewing out
thousands of trans.route_def records and eventually dies. Why is a
literal value (:param_1 = 925) substituted for route_number while the
column name (trans.trip.route_begin_date) is substituted for
route_begin_date?
--~--~-~--~~~---~--~~
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] SQLAlchemy is dropping columns on Oracle 10g
This is very confusing; I have an ORM generated SQL statement that is joining on a specific id. However, when I run it, for some reason, the specific id (that was joined on) is occasionally None! However, when I run the generated SQL copied from the server's debug log in SQLDeveloper, I get all the IDs correctly. To top it off, on those liens where the id is missing, there are several other values missing. Since this runs correctly in SQLDeveloper, could this be a problem with SQLAlchemy? If so, why does it work for most of the rows in a single query? Andrew --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
Wyatt Lee Baldwin wrote:
On Sep 29, 2:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Wyatt Lee Baldwin wrote:
Anyway the primaryjoin here looks fine and does
represent the same thing you're getting in your route() @property.
It's
a simple many-to-one with an additional criterion. Nothing needs to
be
configured in the database as far as foreign keys, configuring it as
such
within the table metadata is all SQLA cares about.
I'm still not clear if/how I can replace the property with the
relation. When I use the relation shown above, I get the big join.
Sorry if I'm being daft.
what big join ?
When I define the route relation as above (2nd version) and access the
route attribute of a Trip, I eventually get a MemoryError. I assume
this is because of a join. When I use the property version, accessing
route is speedy. Here is the SQL that's being generated from the
relation (inside a `paster shell` session):
In [14]: trip = sess.query(Trip).first()
In [15]: trip.route_number
Out[15]: 925
In [16]: trip.route_begin_date
Out[16]: datetime.date(2007, 5, 13)
In [17]: trip.route
14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] SELECT
trans.route_def.route_number AS trans_route_def_route_number,
trans.route_def.route_begin_date AS trans_route_def_route_be_1,
trans.route_def.route_end_date AS trans_route_def_route_end_date,
trans.route_def.public_route_description AS
trans_route_def_public_r_2, trans.route_def.route_type AS
trans_route_def_route_type, trans.route_def.route_sub_type AS
trans_route_def_route_sub_type, trans.route_def.route_usage AS
trans_route_def_route_usage, trans.route_def.route_sort_order AS
trans_route_def_route_so_3
FROM trans.route_def, trans.trip
WHERE :param_1 = trans.route_def.route_number AND
trans.trip.route_begin_date = trans.route_def.route_begin_date AND
trans.trip.route_begin_date = trans.route_def.route_end_date ORDER BY
route_sort_order
14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] {'param_1': 925}
14:40:01,379 DEBUG [trimet.db.TriMetEngine.0x...2e8c] Col
('TRANS_ROUTE_DEF_ROUTE_NUMBER', 'TRANS_ROUTE_DEF_ROUTE_BE_1',
'TRANS_ROUTE_DEF_ROUTE_END_DATE', 'TRANS_ROUTE_DEF_PUBLIC_R_2',
'TRANS_ROUTE_DEF_ROUTE_TYPE', 'TRANS_ROUTE_DEF_ROUTE_SUB_TYPE',
'TRANS_ROUTE_DEF_ROUTE_USAGE', 'TRANS_ROUTE_DEF_ROUTE_SO_3')
After a delay, with `engine.echo = True`, this starts spewing out
thousands of trans.route_def records and eventually dies. Why is a
literal value (:param_1 = 925) substituted for route_number while the
column name (trans.trip.route_begin_date) is substituted for
route_begin_date?
so, first you debug the join condition using
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
and then look for
INFO:sqlalchemy.orm.strategies.LazyLoader:Trip.route lazy loading clause
:param_1 = routedef.route_number AND trip.route_begin_date =
routedef.route_begin_date AND trip.route_begin_date =
routedef.route_end_date
which is wrong. then you ply it with arguments to convince it that the
comparisons are part of what it should be parameterizing.
the most direct way is:
remote_side=[RouteDef.route_number, RouteDef.route_begin_date,
RouteDef.route_end_date]
which will force it to figure out (local, remote) based on those columns
against the primaryjoin, to determine the local remote pairs. you
should probably do it this way.
Another way is:
viewonly=True,
foreign_keys=[route_number, route_begin_date]
this one has it look for columns have dependent values based on what is
considered a foreign key. the viewonly flag means we don't have to
worry about populating those columns during flush, so it matches = and =
operators. then the local remote pairs is taken from that. not as
direct. But this relation() should probably have viewonly=True in any
case.
the ultimate way, which I haven't made very public as of yet, is to
totally spell out the local remote pairs. This doesn't play nicely with
declarative as of yet. I'm not sure yet what to do with this option
since it is the most explicit (so maybe less confusing) way, but then its
very verbose:
foreign_keys=[route_number, route_begin_date],
_local_remote_pairs = [
(route_number, RouteDef.__table__.c.route_number),
(route_begin_date, RouteDef.__table__.c.route_begin_date),
(route_begin_date, RouteDef.__table__.c.route_end_date)
]
--~--~-~--~~~---~--~~
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: SQLAlchemy is dropping columns on Oracle 10g
Andrew wrote: This is very confusing; I have an ORM generated SQL statement that is joining on a specific id. However, when I run it, for some reason, the specific id (that was joined on) is occasionally None! However, when I run the generated SQL copied from the server's debug log in SQLDeveloper, I get all the IDs correctly. To top it off, on those liens where the id is missing, there are several other values missing. Since this runs correctly in SQLDeveloper, could this be a problem with SQLAlchemy? If so, why does it work for most of the rows in a single query? no idea. I don't understand what you mean by the specific id that is joined on is occasionally None, you're joining on it *and* returning it ? is the join an outer join ? in short, there's no way for us to know where the problem lies without many more specifics. --~--~-~--~~~---~--~~ 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: Relation using = operator and literal values
On Sep 29, 3:12 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Wyatt Lee Baldwin wrote:
On Sep 29, 2:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Wyatt Lee Baldwin wrote:
Anyway the primaryjoin here looks fine and does
represent the same thing you're getting in your route() @property.
It's
a simple many-to-one with an additional criterion. Nothing needs to
be
configured in the database as far as foreign keys, configuring it as
such
within the table metadata is all SQLA cares about.
I'm still not clear if/how I can replace the property with the
relation. When I use the relation shown above, I get the big join.
Sorry if I'm being daft.
what big join ?
When I define the route relation as above (2nd version) and access the
route attribute of a Trip, I eventually get a MemoryError. I assume
this is because of a join. When I use the property version, accessing
route is speedy. Here is the SQL that's being generated from the
relation (inside a `paster shell` session):
In [14]: trip = sess.query(Trip).first()
In [15]: trip.route_number
Out[15]: 925
In [16]: trip.route_begin_date
Out[16]: datetime.date(2007, 5, 13)
In [17]: trip.route
14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] SELECT
trans.route_def.route_number AS trans_route_def_route_number,
trans.route_def.route_begin_date AS trans_route_def_route_be_1,
trans.route_def.route_end_date AS trans_route_def_route_end_date,
trans.route_def.public_route_description AS
trans_route_def_public_r_2, trans.route_def.route_type AS
trans_route_def_route_type, trans.route_def.route_sub_type AS
trans_route_def_route_sub_type, trans.route_def.route_usage AS
trans_route_def_route_usage, trans.route_def.route_sort_order AS
trans_route_def_route_so_3
FROM trans.route_def, trans.trip
WHERE :param_1 = trans.route_def.route_number AND
trans.trip.route_begin_date = trans.route_def.route_begin_date AND
trans.trip.route_begin_date = trans.route_def.route_end_date ORDER BY
route_sort_order
14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] {'param_1': 925}
14:40:01,379 DEBUG [trimet.db.TriMetEngine.0x...2e8c] Col
('TRANS_ROUTE_DEF_ROUTE_NUMBER', 'TRANS_ROUTE_DEF_ROUTE_BE_1',
'TRANS_ROUTE_DEF_ROUTE_END_DATE', 'TRANS_ROUTE_DEF_PUBLIC_R_2',
'TRANS_ROUTE_DEF_ROUTE_TYPE', 'TRANS_ROUTE_DEF_ROUTE_SUB_TYPE',
'TRANS_ROUTE_DEF_ROUTE_USAGE', 'TRANS_ROUTE_DEF_ROUTE_SO_3')
After a delay, with `engine.echo = True`, this starts spewing out
thousands of trans.route_def records and eventually dies. Why is a
literal value (:param_1 = 925) substituted for route_number while the
column name (trans.trip.route_begin_date) is substituted for
route_begin_date?
so, first you debug the join condition using
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
and then look for
INFO:sqlalchemy.orm.strategies.LazyLoader:Trip.route lazy loading clause
:param_1 = routedef.route_number AND trip.route_begin_date =
routedef.route_begin_date AND trip.route_begin_date =
routedef.route_end_date
which is wrong. then you ply it with arguments to convince it that the
comparisons are part of what it should be parameterizing.
the most direct way is:
remote_side=[RouteDef.route_number, RouteDef.route_begin_date,
RouteDef.route_end_date]
which will force it to figure out (local, remote) based on those columns
against the primaryjoin, to determine the local remote pairs. you
should probably do it this way.
This works perfectly. I passed over the `remote_side` arg multiple
times because of used for self-referential relationships...
Another way is:
viewonly=True,
foreign_keys=[route_number, route_begin_date]
I could have sworn I tried this at some point... but apparently not
this *exact* combination, because I tried it just now, and it works,
too.
this one has it look for columns have dependent values based on what is
considered a foreign key. the viewonly flag means we don't have to
worry about populating those columns during flush, so it matches = and =
operators. then the local remote pairs is taken from that. not as
direct. But this relation() should probably have viewonly=True in any
case.
I went with this method, since I agree with you about viewonly. Thanks
for the quick answers.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
