[sqlalchemy] relationship joined to max() on a foreign table - help needed
Can anyone help me with this interesting (to me) relationship definition in
sqla.
I have some already defined data, not by me, that has, for each user, a
foreign table of contact numbers, where the highest contact number is the
one to use.
For example, users:
Harry, 1081, and Bob 1082
mobiles:
1081;"0412590410", 1082;"0426236245", 1082;"0416253647"
With:
SELECT u.id, u.first_name, m1.mobile
FROM model.tbl_users as u
JOIN model.tbl_mobiles as m1
ON m1.id = (select max(m2.id) from model.tbl_mobiles as m2 where
m2.user_id = u.id)
gives:
1081;"Harry";"0412590410"
1082;"Bob";"0416253647"
What I want to do is model a relationship on the user (eventually with
no_list) where the field is simply the highest number.
I have been hacking away at a try:
mobile = relationship("Mobile",
primaryjoin="Mobile.id ==
select([func.max(Mobile.id)]).where(Mobile.user_id == User.id).as_scalar()",
viewonly=True)
but I can't seem to get the join right. I always get:
ArgumentError: Relationship User.mobile could not determine any unambiguous
local/remote column pairs based on join condition and remote_side
arguments. Consider using the remote() annotation to accurately mark those
elements of the join condition that are on the remote side of the
relationship.
I have tried all sorts of somewhat random remote() and foriegn() bits
around the query to no avail.
I have a complete example here:
https://gist.github.com/mianos/42cf15928f27cc9dfde9996d2e593e78
Ideas? I am sure it's possible. At the moment I am just using a "orderby
desc" on the relationship and using [0] to get the first.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] dogpile cache async_creation_runner question (actually with sqlalchemy but the example has the sqla factored out)
Thanks for the reply Mike. The explanation is somewhat as expected. Based on this, to keep things simple and being bone lazy, I switched the the 'redis' backend, whose locking is outside of the process and works 100% with multiple threads out of the box. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] dogpile cache async_creation_runner question (actually with sqlalchemy but the example has the sqla factored out)
I have some costly aggregations (they take a minute) and I am firing off the aggregations in a separate thread and want to use the dogpile lock so that the client will receive the 'recent' data while the aggregation is occurring in the background. Using flask and sqla (as usual, sqla rocks, as usual). My problem is, if I run an async_creation_runner, as per the recipe, it's running in a separate thread and dogpile complains: Traceback (most recent call last): File /usr/lib/python2.7/threading.py, line 551, in __bootstrap_inner self.run() File /usr/lib/python2.7/threading.py, line 504, in run self.__target(*self.__args, **self.__kwargs) File ./ct.py, line 35, in runner mutex.release() File /home/rfo/ve/local/lib/python2.7/site-packages/dogpile/cache/util.py, line 182, in release assert keys is not None, this thread didn't do the acquire AssertionError: this thread didn't do the acquire As far as I can see, this is true, the mutex was created in the calling thread, not in the async generation thread so this is correct. Can anyone tell me how I am meant to structure the generation with the mutexes? (It seems to me I should be using a proper system level mutex but I assumed that was the purpose of the underlying dogpile locking) This is the gist with my code: https://gist.github.com/mianos/72ba45aed2824875c1a6 Pretty much copied from the example. Thanks in advance. - Rob -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Getting the entity name, ideally the ORM class in the shard selector?
So anyone else who asks this knows, the following works: A query with just a column: qq = session.query(Configuration.name) Normally I would loop, but in this case, taking just the first column: yy = qq.column_descriptions[0]['expr'] yy.expression.table.name gives: ''configurations” Configuration.__table__.name gives: ''configurations” All good, so we can compare on the underlying table names. Sorry to keep asking questions. If we wanted to do this using the ORM objects, is there a way to get the 'Configuration' object to compare to and not compare the strings with the underlying tables? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Getting the entity name, ideally the ORM class in the shard selector?
If the query does not have a table in it we don't get the tables.
For example, the following simple query gets a list of connection_strings
column from the Connection table:
aa = session.query(Connection.connection_string)
aa.column_descriptions
[{'aliased': False,
'expr': sqlalchemy.orm.attributes.InstrumentedAttribute at 0x10eb7f590,
'name': 'connection_string',
'type': String(length=100)}]
Exploring into the expr I am not sure how to get the table from that.
On Tuesday, 23 April 2013 02:32:15 UTC+10, Michael Bayer wrote:
use column_descriptions:
http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.column_descriptions
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Getting the entity name, ideally the ORM class in the shard selector?
We are using the sharding module included in our application. Currently we are selecting shards based on field. This works really well, as the field that gets hashed to select the shard needs to be migrated to the other tables that exist only in that shard. Now we would like to have some static entities in all databases we would like to use the table name to indicated that the table is available in all shards. Ideally we would define a parent class 'unsharded' and derive from that class for all the unsharded entities. I can see the current entities are in _entities, but as that begins with an underscore I don't think I am meant to be using it. What should I use to get the entities from the query? ps. Loving sqlalchemy and just started setting up Alembic for our project (flask, sqlalchemy, postgres). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
