[sqlalchemy] Re: relation metainfo
On Monday 09 June 2008 03:56:20 Michael Bayer wrote:
On Jun 8, 2008, at 5:02 AM, [EMAIL PROTECTED] wrote:
hi
i'm trying to make a metainfo extractor off some relation
attribute. by metainfo i mean the klas and attr-name of this and
of the other side of the relation-attribute, or of the parent or
child of the relation regardless the start point.
e.g. possible usage and testing invariants is like:
a = about_relation( someklas.someproperty)
print a.name, a.klas, 'is_parent:', a.is_parent
assert a.thisside == a
b = a.otherside
print b.name, b.klas, 'is_parent:', b.is_parent
assert a.otherside.otherside == a
if a.is_parent:
assert a.child is a.otherside
assert a.parent is a.thisside
else:
assert a.parent is a.otherside
assert a.child is a.thisside
e.g. if Parent.kids / Kid.parent are 1:many:
print about_relation( Parent.kids).otherside.name - 'mama'
print about_relation( Parent.kids).otherside.klas - 'Kid'
print about_relation( Parent.kids).child.klas - 'Kid'
print about_relation( Kid.parent).child.klas - 'Kid'
print about_relation( Kid.parent).parent.klas - 'Parent'
print about_relation( Kid.parent).otherside.name - 'kids'
def about_relation(descriptor):
prop = descriptor.property
return {
'child':{'name': prop.key, 'klas': prop.mapper.class_}
'parent':{'klas':prop.parent.class_}
}
I don't understand what is_parent means (nor 'thissside' and
'otherside'). every relation has a parent and child.
given the descriptor Kid.parent, it is considered thisside, and the
other end descriptor (Parent.kids) is otherside; is_parent is which
role the particular descriptor is, from a 1-to-many terminology.
maybe i use wrong naming ...
--~--~-~--~~~---~--~~
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: relation metainfo
okay here anopther way to represent it. this side is the given
prop/descriptor, and then the hops towards other end side:
MtoN:
containerthis: getattr( prop.parent.class_, prop.key)
midthis: getattr( prop.secondary, prop.remote_side[0])
- this: getattr( prop.parent.class_, prop.local_side[0])
midother:getattr( prop.secondary, prop.remote_side[1])
- other:getattr( prop.mapper.class_, prop.local_side[1])
containerother: getattr( prop.mapper.class_, ??)
1toN:
containerthis: getattr( prop.parent.class_, prop.key)
other:getattr( prop.mapper.class_, prop.remote_side[0])
- this:getattr( prop.parent.class_, prop.local_side[0])
other:getattr( prop.mapper.class_, ??)
*to1:
this: getattr( prop.parent.class_, prop.key) -
this:getattr( prop.parent.class_, prop.local_side[0])
- other:getattr( prop.mapper.class_, prop.remote_side[0])
other:getattr( prop.mapper.class_, ??)
did i get it right?
how the backrefs would come above?
is the assoc_proxy pattern looking similar as plain m2m or would be
very different?
On Monday 09 June 2008 03:56:20 Michael Bayer wrote:
On Jun 8, 2008, at 5:02 AM, [EMAIL PROTECTED] wrote:
hi
i'm trying to make a metainfo extractor off some relation
attribute. by metainfo i mean the klas and attr-name of this and
of the other side of the relation-attribute, or of the parent or
child of the relation regardless the start point.
e.g. possible usage and testing invariants is like:
a = about_relation( someklas.someproperty)
print a.name, a.klas, 'is_parent:', a.is_parent
assert a.thisside == a
b = a.otherside
print b.name, b.klas, 'is_parent:', b.is_parent
assert a.otherside.otherside == a
if a.is_parent:
assert a.child is a.otherside
assert a.parent is a.thisside
else:
assert a.parent is a.otherside
assert a.child is a.thisside
e.g. if Parent.kids / Kid.parent are 1:many:
print about_relation( Parent.kids).otherside.name - 'mama'
print about_relation( Parent.kids).otherside.klas - 'Kid'
print about_relation( Parent.kids).child.klas - 'Kid'
print about_relation( Kid.parent).child.klas - 'Kid'
print about_relation( Kid.parent).parent.klas - 'Parent'
print about_relation( Kid.parent).otherside.name - 'kids'
def about_relation(descriptor):
prop = descriptor.property
return {
'child':{'name': prop.key, 'klas': prop.mapper.class_}
'parent':{'klas':prop.parent.class_}
}
I don't understand what is_parent means (nor 'thissside' and
'otherside'). every relation has a parent and child.
--~--~-~--~~~---~--~~
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: Optimizing a slow query
On Jun 8, 5:09 am, beewee [EMAIL PROTECTED] wrote: Hi, thanks for your answers. Other improvements would include (as previously stated by Michael) would be to make sure you have indexed all of the items in your WHERE, ORDER BY, and ON clauses. I created this index: create index viewforum on forum_post (topic_id, id); Is this right? As a quick check, try reducing the 140,000 offset to 0 If I do so, the query is really fast (0.01 sec), but with a big offset it still takes more than 10 seconds :/ This is what EXPLAIN says now:http://paste.pocoo.org/show/64838/ I executed OPTIMIZE TABLE forum_post but it didn't speed up the queries. That narrows the problem down to the sub-select then, since this select will only return 15 rows, but it has to run through a minimum of 140,015 rows to get those 15. To verify this is the issue, try doing the sub-select by itself and doing and explain on it to verify that is the issue. Simplified query: explain SELECT ... FROM (SELECT ... FROM forum_post WHERE 9250 = forum_post.topic_id ORDER BY forum_post.id LIMIT 14, 15) AS anon_1 LEFT OUTER JOIN ... ON anon_1.forum_post_id = forum_attachment_1.post_id LEFT OUTER JOIN ... ON anon_1.forum_post_author_id = portal_user_1.id ORDER BY anon_1.forum_post_id, forum_attachment_1.id, portal_user_1.id; Try to do just the sub-select: SELECT ... FROM forum_post WHERE 9250 = forum_post.topic_id ORDER BY forum_post.id LIMIT 14, 15; and an explain on it: SELECT ... FROM forum_post WHERE 9250 = forum_post.topic_id ORDER BY forum_post.id LIMIT 14, 15 Doing a quick sanity check on my system, a similar query to this subselect with a LIMIT 14, 15 takes about 2 seconds after a server restart and about 0.750 seconds once the indexes have been cached in memory. This is on an old 2.8 GHz Xeon with Hyperthreading with a software RAID filesystem that runs about 150 MB/s and 4 GB of memory for mysql with a dabase index size of 8 GB. So really, your 10-second query time is only 5 times slower which could be explained by not having enough memory devoted to the correct MySQL buffers, so MySQL starts having to wait on the hard drive. Take a look at some tuning parameters that depend upon the database engine that you are using. If you're still out of ideas, consider putting a timestamp on the records and indexing the timestamp. You can then do a query based upon a time range which I know is fast since I do it all the time on a 750-million record stock database. Can you explain this a little bit more? The forum_post table already has a time stamp (pub_date = the date when the post was written), but I have no idea how to filter the records using this column to just get the posts of a specific page of a specific topic. If you index the timestamp, then you can provide this as a filter. For example, in your sub-select, if you limit it to posts to the topic within the last 30 days, then your sub-select query could easily only have to deal with 5,000 records instead of 140,000. For example: From: SELECT ... FROM forum_post WHERE 9250 = forum_post.topic_id ORDER BY forum_post.id LIMIT 14, 15 To: SELECT ... FROM forum_post WHERE 9250 = forum_post.topic_id AND pub_date = DATE_SUB(NOW(), INTERVAL 31 DAY) ORDER BY forum_post.id LIMIT 15 This should greatly limit the number of rows that the database must deal with internally. Also, keep in mind that it is very important that you make sure that the DATE_SUB calculation can be done just once, and not for every row. For example, pub_date = DATE_SUB(NOW(), INTERVAL 31 DAY) will be very fast, but TO_DAYS(pub_date) - TO_DAYS(NOW()) 31 will be extremely slow since the database will have to do the calculation for every row. Regards, Eric --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Troublesome relation
Hi,
I have had the following relation working ok for some time, but a recent
update of SQLAlchemy means it's now asking for a remote_side argument. I'm
really not too sure what too put in there - I've never really understood
that parameter, or foreign_keys.
VulnResDesc.mapper.add_property('rawvulns', sao.relation(VulnRes.mapper,
primaryjoin = sa.and_(VulnRes.targetid == VulnResDesc.targetid,
VulnMap.vulndescid == VulnResDesc.id,
VulnMap.tool == VulnRes.tool,
VulnMap.toolvulnid == VulnRes.toolvulnid),
foreign_keys = [VulnResDesc.c.targetid, VulnResDesc.c.id],
viewonly = True,
uselist = True))
If you'd like some explanation what it's doing, this is part of a tracking
system for security scans. VulnRes is each raw result from a tool, VulnDesc
(not used here) is a textual description of a finding, and VulnMap maps
VulnRes to VulnDesc. VulnResDesc is a view, that gets the distinct
VulnDesc's for each Target. The idea is that the rawvulns relation takes you
from a VulnDesc on a particular Target, to a list of the VulnRes that relate
to this. Not an easy relation :-)
So, what do I put in remote_side? I tried copying foreign_keys, and the app
started, but the relation is always empty.
Any help appreciated!
Paul
--~--~-~--~~~---~--~~
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: relation metainfo
On Jun 9, 2008, at 1:09 AM, [EMAIL PROTECTED] wrote: okay here anopther way to represent it. this side is the given prop/descriptor, and then the hops towards other end side: MtoN: containerthis: getattr( prop.parent.class_, prop.key) midthis: getattr( prop.secondary, prop.remote_side[0]) - this: getattr( prop.parent.class_, prop.local_side[0]) midother:getattr( prop.secondary, prop.remote_side[1]) - other:getattr( prop.mapper.class_, prop.local_side[1]) containerother: getattr( prop.mapper.class_, ??) 1toN: containerthis: getattr( prop.parent.class_, prop.key) other:getattr( prop.mapper.class_, prop.remote_side[0]) - this:getattr( prop.parent.class_, prop.local_side[0]) other:getattr( prop.mapper.class_, ??) *to1: this: getattr( prop.parent.class_, prop.key) - this:getattr( prop.parent.class_, prop.local_side[0]) - other:getattr( prop.mapper.class_, prop.remote_side[0]) other:getattr( prop.mapper.class_, ??) did i get it right? how the backrefs would come above? is the assoc_proxy pattern looking similar as plain m2m or would be very different? association proxy is a 1-M - M-1 look at prop.direction in (ONETOMANY, MANYTOONE, MANYTOMANY) to figure the direction of the relation, since that's what your is_parent concept seems to derive from. the PropertyLoader is pretty agnostic about backref/collection/etc with regards to direction. --~--~-~--~~~---~--~~ 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: Troublesome relation
On Jun 9, 2008, at 10:50 AM, Paul Johnston wrote:
Hi,
I have had the following relation working ok for some time, but a
recent update of SQLAlchemy means it's now asking for a remote_side
argument. I'm really not too sure what too put in there - I've never
really understood that parameter, or foreign_keys.
VulnResDesc.mapper.add_property('rawvulns',
sao.relation(VulnRes.mapper,
primaryjoin = sa.and_(VulnRes.targetid == VulnResDesc.targetid,
VulnMap.vulndescid == VulnResDesc.id,
VulnMap.tool == VulnRes.tool,
VulnMap.toolvulnid == VulnRes.toolvulnid),
foreign_keys = [VulnResDesc.c.targetid, VulnResDesc.c.id],
viewonly = True,
uselist = True))
ultimately all relations distill the join condition into a set of
pairs, above it would be:
[ (VulnResDesc.targetid, VulnRes.targetid) ]
i.e., the column pairs that are involved with the VulnResDesc and
VulnRes mappers. Column pairs that deal with VulnMap are not of any
use to the relation() since we can't write to them, we cant use them
as the source of newly generated foreign keys, and we can't compare to
them in a lazy load.
So using foreign_keys which only deals with these columns should
probably work by itself:
foreign_keys = [VulnResDesc.targetid]
The remote_side argument, if needed, would be remote_side=
[VulnRes.targetid] since that is the right side of the relation.
--~--~-~--~~~---~--~~
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 null dates
On Jun 9, 2008, at 11:34 AM, Christoph Zwerschke wrote: I need to order a table by start and end dates, where null values should be interpreted as prior to all values for start dates and later than all values for end dates. This could be realized with nulls first, nulls last, but it seems this did not make it into SQLAlchemy yet (ticket #723). A workaround (for PostgreSQL) would be something like this: order_by coalesce(start, timestamp '-infinity'), coalesce(end_date, timestamp 'infinity') But I don't find how to construct this with SQLAlchemy. Any ideas? Any better solutions? I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0 ELSE x . We have case() construct for that. For your version, use func.coalesce(start, literal_column(timestamp '- infinity')) . --~--~-~--~~~---~--~~ 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: PostgreSQL and inserting
Hi. Currently sa does select after insert to get the new serial value? Is my understanding correct? Thx 2008/6/7, Michael Bayer [EMAIL PROTECTED]: On Jun 6, 2008, at 9:11 PM, Cliff Wells wrote: Just an FYI, in PostgreSQL 8.2.4 they added a new feature returning which can be used to avoid the need for an additional query (to get the value of serial columns) when inserting records: test=# create table foo ( id serial primary key not null, name text ); test=# insert into foo ( name ) values ( 'test 1' ) returning id; id 1 (1 row) test=# insert into foo ( name ) values ( 'test 2' ) returning *; id | name +- 2 | test 2 (1 row) Thought it would be worth mentioning. the PG dialect supports RETURNING; I think its the pg_returning keyword argument to insert(). Still remaining to do is to modify the PG dialect such that this is used automatically when available for the primary key columns, or perhaps in combination with a dialect agnostic hint, so that the ORM makes usage of it implicitly. --~--~-~--~~~---~--~~ 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 null dates
Michael Bayer wrote: I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0 ELSE x . We have case() construct for that. Yes, but that still leaves me with having to code infinity some way. For your version, use func.coalesce(start, literal_column(timestamp '- infinity')) . Thank you. literal_column was exactly what I was looking for. Though I still would like to have nulls first/last as well, this construct is also useful when checking date ranges with between. Thanks again for your quick and helpful answer, as always... -- Christoph --~--~-~--~~~---~--~~ 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] Cannot abort wxPython thread with SQLAlchemy
Hello All, I am using delayedresult (which is a class to do threading in wxPython) for a query with SQLAlchemy, using SQLite. I have an 'opened' session in the main App thread. I create another session under the delayedresult thread. When I try to stop this thread with a dedicated button, the thread doesn't abort and goes on till it sends the result. Does anybody knows how to tackle this issue ? Should I close the first session under the main App ? Something else ? Many thanks in advance for any hints Dominique --~--~-~--~~~---~--~~ 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: reading from one database and writing to another
On Jun 6, 12:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 6, 2008, at 2:29 PM, qhfgva wrote: We have (what I think of as) a moderately complicated database configuration and I'm hoping there will be a way to configure sqlalchemy to deal with it. The basic scenario is like this: There are N mysql servers in different geographical regions that are all replicating against one master. In the interest of speed the rule in each location is to do reads which are very frequent against the local copy of the database and if there is a write to do that against the master. As an added wrinkle the user has an option to write to the master with a master_pos_wait so that the current process will wait until replication has caught up with the update just executed. Hopefully that makes sense and gives enough of a flavor of what I've got in mind. I'm pretty new to sqlalchemy. Is the above feasible? If so are there examples to compare with and learn from doing something similar? Where (api/code) would I start looking to accomplish the above? Any tips to get me going would be much appreciated. easiest approach is to use multiple sessions, multiple engines. Your app would need to know which engine it wants to talk to, and binds a session to that engine. Binding is described here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_getting_bin... Thanks I'll take a look. I left out what I think is an important part of this scenario (or maybe it's trivial - I don't have a good perspective on this yet). In any case, I would like to use the ORM component of sqlalchemy and completely hide the fact that the read/ write connections are possibly different. (They might become the same connection if the local database becomes unaccessible and/or is too far behind the master). In other words I'd like to have a handle to, say, a user object, and do reads/updates to it with the programmer using this object not caring about how the work gets done. So for instance I select a number of user objects that come from the local database. Later I update a field on one of these and the update takes place on the master directly. Is that weird? Doable? Unfortunately this is the environment I need to get this working with. As a side note, we manage this difference by hand now, it's really annoying which is why I'd love to abstract it away. thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Ordering null dates
On Jun 9, 2008, at 12:56 PM, Christoph Zwerschke wrote: Michael Bayer wrote: I tend to use a CASE statement for this: CASE WHEN x IS NULL THEN 0 ELSE x . We have case() construct for that. Yes, but that still leaves me with having to code infinity some way. For your version, use func.coalesce(start, literal_column(timestamp '- infinity')) . Thank you. literal_column was exactly what I was looking for. Though I still would like to have nulls first/last as well, this construct is also useful when checking date ranges with between. yeah we have the ticket, sooner or later unless someone patches :) --~--~-~--~~~---~--~~ 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: PostgreSQL and inserting
On Jun 9, 2008, at 12:23 PM, Eric Lemoine wrote: Hi. Currently sa does select after insert to get the new serial value? Is my understanding correct? Thx no, SA executes the sequence corresponding to the integer primary key beforehand. --~--~-~--~~~---~--~~ 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: reading from one database and writing to another
u're talking about full replication... what's the possiblity of local-readonly DB to have different (older) data than that of the master? and how this should be tackled? theoreticaly from what i get of the multi-sessions/engine approach, then your two sessions (one writeable:master, one readonly:local) may have different objects which represent/has to be one object? On Monday 09 June 2008 21:22:48 qhfgva wrote: On Jun 6, 12:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 6, 2008, at 2:29 PM, qhfgva wrote: We have (what I think of as) a moderately complicated database configuration and I'm hoping there will be a way to configure sqlalchemy to deal with it. The basic scenario is like this: There are N mysql servers in different geographical regions that are all replicating against one master. In the interest of speed the rule in each location is to do reads which are very frequent against the local copy of the database and if there is a write to do that against the master. As an added wrinkle the user has an option to write to the master with a master_pos_wait so that the current process will wait until replication has caught up with the update just executed. Hopefully that makes sense and gives enough of a flavor of what I've got in mind. I'm pretty new to sqlalchemy. Is the above feasible? If so are there examples to compare with and learn from doing something similar? Where (api/code) would I start looking to accomplish the above? Any tips to get me going would be much appreciated. easiest approach is to use multiple sessions, multiple engines. Your app would need to know which engine it wants to talk to, and binds a session to that engine. Binding is described here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_gettin g_bin... Thanks I'll take a look. I left out what I think is an important part of this scenario (or maybe it's trivial - I don't have a good perspective on this yet). In any case, I would like to use the ORM component of sqlalchemy and completely hide the fact that the read/ write connections are possibly different. (They might become the same connection if the local database becomes unaccessible and/or is too far behind the master). In other words I'd like to have a handle to, say, a user object, and do reads/updates to it with the programmer using this object not caring about how the work gets done. So for instance I select a number of user objects that come from the local database. Later I update a field on one of these and the update takes place on the master directly. Is that weird? Doable? Unfortunately this is the environment I need to get this working with. As a side note, we manage this difference by hand now, it's really annoying which is why I'd love to abstract it away. thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: reading from one database and writing to another
On Jun 9, 2008, at 2:22 PM, qhfgva wrote: Thanks I'll take a look. I left out what I think is an important part of this scenario (or maybe it's trivial - I don't have a good perspective on this yet). In any case, I would like to use the ORM component of sqlalchemy and completely hide the fact that the read/ write connections are possibly different. (They might become the same connection if the local database becomes unaccessible and/or is too far behind the master). that is going to be very hard to accomplish as the Session does not have a clustering rules engine built into it in order to determine read/write locations, nor is that within its scope. It can only handle class X talks to engine Y. In other words I'd like to have a handle to, say, a user object, and do reads/updates to it with the programmer using this object not caring about how the work gets done. So for instance I select a number of user objects that come from the local database. Later I update a field on one of these and the update takes place on the master directly. Is that weird? Doable? Unfortunately this is the environment I need to get this working with. you need to make a facade on top of Session which accomplishes this, most likely by using multiple sessions. Rebinding a single session mid-stream is somewhat possible as well, but you'd want to ensure that the session is not engaged in a current transaction when this happens. Another, more dramatic approach is to handle the multiple engines as the SQL execution level; look at 0.5 and the ConnectionProxy class. The ConnectionProxy is simple but you'd have to intelligently inspect the statements coming in to determine where they should be routed. Of particular concern would be again transactional state; when a flush() occurs and writes data to the write engine, the flush() still needs to read from that same engine in some cases, such as when deleting collections where it wants to locate the set of primary keys. Similarly, if a read operation detects a certain state that is then not present on the different-engined write operation (or vice versa), that inconsistent state can lead to errors. --~--~-~--~~~---~--~~ 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: reading from one database and writing to another
we're also assuming that you've exhausted these approaches (which would be a lot more appropriate for this sort of thing): http://forge.mysql.com/wiki/MySQL_Proxy http://dev.mysql.com/doc/refman/5.0/en/replication.html On Jun 9, 2008, at 2:22 PM, qhfgva wrote: On Jun 6, 12:34 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 6, 2008, at 2:29 PM, qhfgva wrote: We have (what I think of as) a moderately complicated database configuration and I'm hoping there will be a way to configure sqlalchemy to deal with it. The basic scenario is like this: There are N mysql servers in different geographical regions that are all replicating against one master. In the interest of speed the rule in each location is to do reads which are very frequent against the local copy of the database and if there is a write to do that against the master. As an added wrinkle the user has an option to write to the master with a master_pos_wait so that the current process will wait until replication has caught up with the update just executed. Hopefully that makes sense and gives enough of a flavor of what I've got in mind. I'm pretty new to sqlalchemy. Is the above feasible? If so are there examples to compare with and learn from doing something similar? Where (api/code) would I start looking to accomplish the above? Any tips to get me going would be much appreciated. easiest approach is to use multiple sessions, multiple engines. Your app would need to know which engine it wants to talk to, and binds a session to that engine. Binding is described here: http://www.sqlalchemy.org/docs/04/session.html#unitofwork_getting_bin ... Thanks I'll take a look. I left out what I think is an important part of this scenario (or maybe it's trivial - I don't have a good perspective on this yet). In any case, I would like to use the ORM component of sqlalchemy and completely hide the fact that the read/ write connections are possibly different. (They might become the same connection if the local database becomes unaccessible and/or is too far behind the master). In other words I'd like to have a handle to, say, a user object, and do reads/updates to it with the programmer using this object not caring about how the work gets done. So for instance I select a number of user objects that come from the local database. Later I update a field on one of these and the update takes place on the master directly. Is that weird? Doable? Unfortunately this is the environment I need to get this working with. As a side note, we manage this difference by hand now, it's really annoying which is why I'd love to abstract it away. thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy in virtualenv Instructions
On Mon, Apr 14, 2008 at 12:12 PM, Lukasz Szybalski [EMAIL PROTECTED] wrote: On Mon, Apr 14, 2008 at 11:38 AM, jason kirtland [EMAIL PROTECTED] wrote: Lukasz Szybalski wrote: On Mon, Apr 14, 2008 at 11:30 AM, jason kirtland [EMAIL PROTECTED] wrote: Lukasz Szybalski wrote: Hello, Below you can find instructions on how to setup sqlalchemy in virtual environment. http://lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8 Installing SQLAlchemy in a virtualenv is the same as for any package on listed on PYPI: What does this line do? $ source myenv/bin/activate http://pypi.python.org/pypi/virtualenv#activate-script Which version of sqlalchemy does it install? current stable? trunk? or? $ easy_install SQLAlchemy The latest on pypi. You can also do $ easy_install SQLAlchemy==dev # for svn trunk $ easy_install SQLAlchemy==0.4.5 # whatever version http://peak.telecommunity.com/DevCenter/EasyInstall Thanks guys, the active script was the last piece of the puzzle. Updated: http://lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8 Lucas I have mysqldb installed in the system wide install how do I tell virtualenv to use it? I don't see a need to install it in virtualenv again so I guess I just have to givea right path? How, and in which file? Thanks, Lucas File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQLAlchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mysql.py, line 1430, in dbapi import MySQLdb as mysql ImportError: No module named MySQLdb --~--~-~--~~~---~--~~ 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: reading from one database and writing to another
Michael Bayer wrote: Thanks I'll take a look. I left out what I think is an important part of this scenario (or maybe it's trivial - I don't have a good perspective on this yet). In any case, I would like to use the ORM component of sqlalchemy and completely hide the fact that the read/ write connections are possibly different. (They might become the same connection if the local database becomes unaccessible and/or is too far behind the master). that is going to be very hard to accomplish as the Session does not have a clustering rules engine built into it in order to determine read/write locations, nor is that within its scope. It can only handle class X talks to engine Y. Just an FYI, I am actually splitting reads and writes in a fairly straightforward way in my web application by inferring the intent of the request based upon the method. We developed the application so that all requests that write to the database are in a POST. Everything else uses other methods (primarily GET). We use a scoped session, ensuring that each request gets its own session and then wrote some WSGI middleware which will automatically bind the session to the correct database instance (one of the masters, or the correct slave) based upon the request method. We also automatically wrap POST's in a transaction, and roll back upon errors. FWIW, this middleware is like 20 lines of code long. If your app is not web-based you might have trouble getting away with something like this :) -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ 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: reading from one database and writing to another
On Jun 9, 2008, at 4:57 PM, Jonathan LaCour wrote: Michael Bayer wrote: Thanks I'll take a look. I left out what I think is an important part of this scenario (or maybe it's trivial - I don't have a good perspective on this yet). In any case, I would like to use the ORM component of sqlalchemy and completely hide the fact that the read/ write connections are possibly different. (They might become the same connection if the local database becomes unaccessible and/or is too far behind the master). that is going to be very hard to accomplish as the Session does not have a clustering rules engine built into it in order to determine read/write locations, nor is that within its scope. It can only handle class X talks to engine Y. Just an FYI, I am actually splitting reads and writes in a fairly straightforward way in my web application by inferring the intent of the request based upon the method. We developed the application so that all requests that write to the database are in a POST. Everything else uses other methods (primarily GET). We use a scoped session, ensuring that each request gets its own session and then wrote some WSGI middleware which will automatically bind the session to the correct database instance (one of the masters, or the correct slave) based upon the request method. We also automatically wrap POST's in a transaction, and roll back upon errors. FWIW, this middleware is like 20 lines of code long. If your app is not web-based you might have trouble getting away with something like this :) yup, my approach for this uses decorators: @readonly def do_something_readonly(request_vars): @writeable def do_something_writeable(request_vars): the scoped_session is configured by the decorator. but this still has an explicitness to it that it seemed the poster was looking to avoid. but perhaps I mis-read. --~--~-~--~~~---~--~~ 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: Cannot abort wxPython thread with SQLAlchemy
Dominique wrote: I am using delayedresult (which is a class to do threading in wxPython) for a query with SQLAlchemy, using SQLite. I have an 'opened' session in the main App thread. I create another session under the delayedresult thread. When I try to stop this thread with a dedicated button, the thread doesn't abort and goes on till it sends the result. As Python has no way to actually terminate a thread, can you explain what you mean by stop this thread? Are you simply cloning the code from the wxPython example, with the delayedresult.AbortEvent() object, and calling .set() on it? -Peter --~--~-~--~~~---~--~~ 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: Cannot abort wxPython thread with SQLAlchemy
Hi Peter, Thank you very much for answering. On 10 juin, 02:38, Peter Hansen [EMAIL PROTECTED] wrote: As Python has no way to actually terminate a thread, can you explain what you mean by stop this thread? Are you simply cloning the code from the wxPython example, with the delayedresult.AbortEvent() object, and calling .set() on it? That's exactly what I do. My Abort button is linked to an abort function which calls abortEvent.set(), like in the demo. In the producer function, I launch the query. What I'd like to do is to be able to stop the thread, while the query is being done. Is it possible or am I trying to do something impossible ? Thanks Dominique --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
