[sqlalchemy] how to prevent select() to generate a FROM statement ?
Hello,
I have a rather complicated query on a medium sized database (millions
of rows). One part of the query looks like the following :
SELECT
COUNT(sp.id),
COUNT(
(SELECT
sp.id
WHERE
sp.site_id IN (
SELECT
si.id
FROM
sites si
WHERE
si.latitude IS NOT NULL AND si.longitude IS NOT NULL
)
)
) AS foo
FROM
specimens sp
WHERE
sp.determinator_id = 4;
The problem I have is that SQLAlchemy always generate a FROM statement for a
select(),
even with from_obj=[] or from_obj=None, so in my case for the second count() it
generates :
count(
(
SELECT sp.id FROM specimens AS sp WHERE sp.site_id IN (
SELECT sites.id FROM sites WHERE sites.latitude IS NOT NULL AND
sites.longitude IS NOT NULL
)
)
) AS specimen_filtered_georeferenced
The problem is that in my case I don't want the FROM specimens AS sp
statement. Is there a way to avoid that ?
I have the following code for the moment:
(...)
filters = validation.SearchFilter.to_python(request.params)
search = SpecimenSearch(filters)
sp = model.t_specimens.alias('sp')
specimen_where_clause = search.specimen_filters(sp)
specimen_from_clause = sp
specimen_fields = [sp.c.taxonomy_id,
func.count(sp.c.id).label('specimen_filtered'),
# Problem is here #
func.count(
select(
[sp.c.id],
sp.c.site_id.in_(
select(
[model.t_sites.c.id],
and_(
model.t_sites.c.latitude != None,
model.t_sites.c.longitude != None,
)
)
)
)
).label('specimen_filtered_georeferenced'),
##
func.count(sp.c.type_id).label('count_type'),
select(
[func.count(model.t_specimens.c.id)],
model.t_specimens.c.taxonomy_id == sp.c.taxonomy_id
).label('specimen_total_taxonomy')]
(...)
q_specimens = select(specimen_fields,
and_(*specimen_where_clause),
from_obj = [specimen_form_clause],
group_by = sp.c.taxonomy_id
).alias('specimen')
(...)
from_clause_taxonomy = model.t_taxonomies.outerjoin(model.t_families).\
outerjoin(model.t_genuses).outerjoin(model.t_species).\
outerjoin(model.t_subspecies)
q_taxonomy = select(
[q_specimens, model.t_taxonomies.c.id, model.t_families,
model.t_genuses, model.t_species, model.t_subspecies],
and_(*search.taxonomy_filters()),
from_obj = [from_clause_taxonomy.join(q_specimens)]
).order_by(model.t_families.c.name, model.t_genuses.c.name,
model.t_species.c.name, model.t_subspecies.c.name
).apply_labels()
(...)
Thanks,
Julien
--
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to prevent select() to generate a FROM statement ?
On Mar 28, 2008, at 8:06 AM, Julien wrote:
# Problem is here #
func.count(
select(
[sp.c.id],
sp.c.site_id.in_(
select(
[model.t_sites.c.id],
and_(
model.t_sites.c.latitude != None,
model.t_sites.c.longitude != None,
)
)
)
)
).label('specimen_filtered_georeferenced'),
##
I think you want to convert the select to a scalar, i.e.
count(myselect.as_scalar()).
--~--~-~--~~~---~--~~
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: Use of new_instance() in the user-defined-state branch?
On Mar 28, 2008, at 12:55 AM, Phillip J. Eby wrote: Sadly, about the only way for me to implement that without code duplication will be to temporarily change the item's __class__ to a subclass with an empty __init__ method. Unless there's a way to change the generated __init__ method to take an extra flag or check a per-thread variable to skip the bits you don't want? What are the bits you don't want run, anyway? That is, what specifically mustn't happen? an end-user's __init__ method is not run when the object is loaded from the DB since the ORM is going to populate its state explicitly. its for similar reasons that pickle.loads() doesn't call __init__. Its a common use case that someone's class needs to be constructed differently when it is newly created vs. when it is re-populated from the DB. The usual method we have of modifying this behavior is to use a MapperExtension where you implement create_instance(). Then you can build the object any way you want, using __init__, whatever. Why is that not an option here ? ( or has it just not been mentioned ?) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to prevent select() to generate a FROM statement ?
It doesn't work because more than one row are returned by the subquery
used in the expression ...
On Fri, 2008-03-28 at 09:30 -0400, Michael Bayer wrote:
On Mar 28, 2008, at 8:06 AM, Julien wrote:
# Problem is here #
func.count(
select(
[sp.c.id],
sp.c.site_id.in_(
select(
[model.t_sites.c.id],
and_(
model.t_sites.c.latitude != None,
model.t_sites.c.longitude != None,
)
)
)
)
).label('specimen_filtered_georeferenced'),
##
I think you want to convert the select to a scalar, i.e.
count(myselect.as_scalar()).
--
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52
--~--~-~--~~~---~--~~
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: Multiple Levels of Inheritance
Thanks! That appears to have done it. Chris Guin At 05:28 PM 3/27/2008, you wrote: On Mar 27, 2008, at 3:54 PM, Chris Guin wrote: Does anyone know where I could find a working example of multiple levels of inheritance using joined table inheritance? Right now I have the following class hierarchy - an AspectDetection and an RFDetection are subclasses of Detection, which in turn is a subclass of Event. Each of the classes has its own DB table, and an object can be simply an Event or a Detection. I've tried mapping these classes together using the following code: detection_join = detection.outerjoin(aspect_detection).outerjoin(rf_detection) event_join = detection_join.outerjoin(event) if the ultimate base class is Event, then the event table's columns must be present in every result set. By outerjoining (where outerjoin is a LEFT OUTER JOIN) detection to event, you dont get Event objects that are not Detection objects. So event join should be: event_join=event.outerjoin(detection_join) Similarly, mapping select_table directly to detection_join for detection_mapper does not include any columns from the Event table, so those loads would be failing pretty badly. So for that mapper, assuming you want all subclasses in one big query, youd want to set select_table to: event .outerjoin (detection).outerjoin(aspect_detection).outerjoin(rf_detection) the good news is, 0.4.5 will deprecate select_table and you'll just be able to say with_polymorphic='*' on all your mappers where you want a join of all subtables constructed by default - it will do all this work for you. --~--~-~--~~~---~--~~ 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: Attribute error
That's fixed it. Thanks very much. On Mar 26, 7:03 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Mar 26, 2008, at 1:34 PM, pyplexed wrote: Hi all, I'm a complete newcomer to SA, and I've tried to adapt an example I found on-line to look a bit more like the problem I'm trying to solve. If I save an object to my session, and then flush it, I'm getting an error which Google can't help me with: AttributeError: 'MetaData' object has no attribute 'contextual_connect' I saw a post that said an attribute error can happen if the Metadata class name is mistakenly bound to an instance. I don't think that's the case in my script. I wondered if anyone here would be able to take a quick look at my (no doubt lousy) code and let me know what I'm doing wrong? The code is here: http://pubcat.org/alchemyTest.py sessionmaker needs to bind to the Engine, not the MetaData (this might be something we want to detect, its an understandable mistake): Session = sessionmaker(bind=engine, autoflush=True, transactional=False) --~--~-~--~~~---~--~~ 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: Use of new_instance() in the user-defined-state branch?
At 09:45 AM 3/28/2008 -0400, Michael Bayer wrote: On Mar 28, 2008, at 12:55 AM, Phillip J. Eby wrote: Sadly, about the only way for me to implement that without code duplication will be to temporarily change the item's __class__ to a subclass with an empty __init__ method. Unless there's a way to change the generated __init__ method to take an extra flag or check a per-thread variable to skip the bits you don't want? What are the bits you don't want run, anyway? That is, what specifically mustn't happen? an end-user's __init__ method is not run when the object is loaded from the DB since the ORM is going to populate its state explicitly. its for similar reasons that pickle.loads() doesn't call __init__. Its a common use case that someone's class needs to be constructed differently when it is newly created vs. when it is re-populated from the DB. The usual method we have of modifying this behavior is to use a MapperExtension where you implement create_instance(). Then you can build the object any way you want, using __init__, whatever. Why is that not an option here ? ( or has it just not been mentioned ?) Because Jason said this: At 06:08 PM 3/27/2008 -0700, jason kirtland wrote: Phillip J. Eby wrote: At 02:26 PM 3/27/2008 -0700, jason kirtland wrote: new_instance creates an instance without invoking __init__. The ORM uses it to recreate instances when loading from the database. new_instance can be added to InstrumentationManager as an extension method... The ORM doesn't care how empty instances are manufactured so long as they can be created without initialization arguments, e.g. a no-arg constructor. Does that mean that no attributes must be set from new_instance(), either? You should be able to set whatever you like there. So... new_instance() could literally just be a call to 'self.class_()', with no other behavior, as long as the constructor requires no arguments? The modified __init__ that SA inserts won't be a problem there? Sorry, I should have included more detail. You don't want to trigger the logic in SA's __init__ decorator or call the user's __init__. The ORM policy is not to __init__ on load, and will soon support a symmetric __on_load__ type of hook that the ORM can call post-__new__ when reconstituting instances. So, which one of you is right? :) --~--~-~--~~~---~--~~ 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] query on stdout
Hi, I just noticed that both 0.3.x and 0.4.x versions of SQLAlchemy print compiled query for MySQL without binded parameters, so typical printout for MySQL looks like SELECT DISTINCT block.`Path` AS `block_Path` FROM tier0.block WHERE block.`Path` LIKE %s while doing the same with ORACLE SELECT DISTINCT block.path AS block_path FROM block WHERE block.path LIKE :block_path Is there are any reason not to print binded parameters for MySQL or it is a bug? Thanks Valentin. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to prevent select() to generate a FROM statement ?
In the documentation I found
Note that from objects are automatically located within the columns
and whereclause ClauseElements
for the select() statement.
It is precisely the thing I do not want to.. no way to disable it .. ?
Thanks,
Julien
On Fri, 2008-03-28 at 09:30 -0400, Michael Bayer wrote:
On Mar 28, 2008, at 8:06 AM, Julien wrote:
# Problem is here #
func.count(
select(
[sp.c.id],
sp.c.site_id.in_(
select(
[model.t_sites.c.id],
and_(
model.t_sites.c.latitude != None,
model.t_sites.c.longitude != None,
)
)
)
)
).label('specimen_filtered_georeferenced'),
##
I think you want to convert the select to a scalar, i.e.
count(myselect.as_scalar()).
--
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: [EMAIL PROTECTED]
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to prevent select() to generate a FROM statement ?
On Mar 28, 2008, at 1:36 PM, Julien wrote:
in fact all the problem is that I can't generate the following query:
SELECT xx.yy, (SELECT xx.yy WHERE cond) FROM foobar xx;
where xx.yy are the same columns
SQLAlchemy generates :
SELECT xx.yy, (SELECT xx.yy FROM foobar xx WHERE cond) FROM foobar xx;
This feature has been added in r4366 (it really means we no longer
check for over correlation):
t = table('t', column('a'), column('b'))
s = select([t.c.a]).where(t.c.a==1).correlate(t).as_scalar()
s2 = select([t.c.a, s])
self.assert_compile(s2, SELECT t.a, (SELECT t.a WHERE t.a
= :t_a_1) AS anon_1 FROM t)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Use of new_instance() in the user-defined-state branch?
So you're still disagreeing with Jason, who's quite explicitly saying that SA's __init__ will blow up if it gets called. Which of you is right? :) At 11:38 AM 3/28/2008 -0400, Michael Bayer wrote: On Mar 28, 2008, at 10:58 AM, Phillip J. Eby wrote: Sorry, I should have included more detail. You don't want to trigger the logic in SA's __init__ decorator or call the user's __init__. The ORM policy is not to __init__ on load, and will soon support a symmetric __on_load__ type of hook that the ORM can call post-__new__ when reconstituting instances. So, which one of you is right? :) Well, I'm not entirely sure how your users will be using their objects. If they just want to take any old application and enable trellis + sqlalchemy, if they are accustomed to writing for SA then it would be a surprise for their __init__() method to be called. Like, if I wrote a class like this: class MyClass(object): def __init__(self, a, b): self.a = a self.b = b then I mapped it to Trellis + SQLA, we *can't* call MyClass() upon load from the database - we dont have the constructor arguments available and TypeError will be thrown. If OTOH, using Trellis implies that you must already have an __init__() that is compatible with a no-arg calling style and that they should expect population of attributes to occur after it's called, then theres no issue with configuring the SA mappings to call __init__(). I think you mentioned earlier that Trellis doesn't care what the user does with __init__()neither does SQLAlchemy, and thats why we never call it by default with no args, since we make no assumptions about what it expects or what it does. --~--~-~--~~~---~--~~ 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: Use of new_instance() in the user-defined-state branch?
On Mar 28, 2008, at 12:13 PM, Phillip J. Eby wrote: So you're still disagreeing with Jason, who's quite explicitly saying that SA's __init__ will blow up if it gets called. Which of you is right? :) SA's __init__ does not blow up if it gets called. It just checks that mappers are compiled and sets up InstanceState if not already present. I'm not sure if recent changes on the branch have changed this, though I doubt it, since we have a lot of users that do implement MapperExtension.create_instance() to call their __init__() method. --~--~-~--~~~---~--~~ 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: Use of new_instance() in the user-defined-state branch?
On Mar 28, 2008, at 12:28 PM, Michael Bayer wrote: On Mar 28, 2008, at 12:13 PM, Phillip J. Eby wrote: So you're still disagreeing with Jason, who's quite explicitly saying that SA's __init__ will blow up if it gets called. Which of you is right? :) SA's __init__ does not blow up if it gets called. It just checks that mappers are compiled and sets up InstanceState if not already present. I'm not sure if recent changes on the branch have changed this, though I doubt it, since we have a lot of users that do implement MapperExtension.create_instance() to call their __init__() method. I think I should summarize what is known about this: 1. being able to configure the ORM to call __init__() instead of __new__() has always been a supported use case. 2. there are general issues when you have an ORM or any other instrumentation layer call __init__(), which is that people like to define argument signatures and behaviors for their __init__() which may conflict with just being able to call it in a plain vanilla style. So you may not actually want to call __init__() across the board. 3. Our long-existing hook to change how an object is created is MapperExtension.create_instance(), which just expects an instance back, no opinion on how it's created. That isn't going anywhere. 4. Jason is working on new hooks in the branch that would be an alternative to using create_instance(). The new_instance hook specifically would *not* be where you'd just call __init__() from. But the management interface will allow you to define exactly how __init__ is decorated on mapped classes, including marking it with the reconsitute hook which means it would be called after new_instance is called. So an immediate workaround would be to play with MapperExtension.create_instance(), but our plan is that before the branch is merged, we will have more hooks at the instrumentation layer as well. --~--~-~--~~~---~--~~ 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] Elixir 0.5.2 released!
I am very pleased to announce that version 0.5.2 of Elixir (http://elixir.ematia.de) is now available. As always, feedback is very welcome, preferably on Elixir mailing list. This is a minor bug fixes release (mostly restoring python 2.3 compatibility). The full list of changes can be seen at: http://elixir.ematia.de/trac/browser/elixir/tags/0.5.2/CHANGES What is Elixir? - Elixir is a declarative layer on top of the SQLAlchemy library. It is a fairly thin wrapper, which provides the ability to create simple Python classes that map directly to relational database tables (this pattern is often referred to as the Active Record design pattern), providing many of the benefits of traditional databases without losing the convenience of Python objects. Elixir is intended to replace the ActiveMapper SQLAlchemy extension, and the TurboEntity project but does not intend to replace SQLAlchemy's core features, and instead focuses on providing a simpler syntax for defining model objects when you do not need the full expressiveness of SQLAlchemy's manual mapper definitions. Mailing list http://groups.google.com/group/sqlelixir/about -- Gaëtan de Menten http://openhex.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] Strange caching problem with Apache
I'm having a strange caching problem when using SQLAlchemy with Apache
and mod_python. Using the ORM, I have a class that maps to a simple
table with two columns and no foreign keys. When I get an HTTP
request, I get the desired object by primary key and return the value
of the attribute that maps to the other column. This works fine when
Apache first loads, but if the value stored in the column of the
desired object changes (e.g. by simply running an UPDATE in the MySQL
command line), the new value will not be returned in the code. Here's
a synopsis of the code:
engine = sa.create_engine('mysql://[EMAIL PROTECTED]/project',
encoding='utf-8', echo=False)
metadata = MetaData()
metadata.bind = get_engine()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('mojo', Integer, nullable=False,
default=0),
mysql_engine='InnoDB'
)
class User(object):
def __init__(self, id):
self.id = id
self.mojo = 0
def toResult(self):
return {'user_id': self.id,
'mojo': self.mojo,
'result_code': 0,
}
mapper(User, users_table)
Session = sessionmaker(bind=engine, autoflush=True,
transactional=True)
def log_debug(msg):
# custom logging function that logs to a file using the std lib
logging module
def get_user(user_id):
db = Session()
user = db.query(User).filter(User.id == user_id).first()
res = user.toResult()
log_debug(pelf_requests.balance, res)
db.close()
return res
The funny part is that if I run this very same code in a standalone
Python process (where the process is kept alive, like the Apache
process), there is no caching behavior; the correct value is returned
every single time. If it's run in Apache, I can see the incorrect
value get returned in the logs (in the 'get_user' function above). In
either case, I see the query getting logged in MySQL's query logs.
Any ideas at all? I've searched through both the mod_python and
sqlalchemy archives and haven't found anything appropriate.
The versions of all pertinent software are listed below:
SQLAlchemy: 0.4.2p3-1 (Ubuntu hardy package)
Apache: 2.2.4-3 (Ubuntu gutsy)
mod_python: 3.3.1-2 (Ubuntu gutsy)
Python: 2.5.1-5 (Ubuntu gutsy)
Thanks in advance for any insight or suggestions.
--~--~-~--~~~---~--~~
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: Strange caching problem with Apache
On Mar 28, 2008, at 6:42 PM, john spurling wrote: The funny part is that if I run this very same code in a standalone Python process (where the process is kept alive, like the Apache process), there is no caching behavior; the correct value is returned every single time. If it's run in Apache, I can see the incorrect value get returned in the logs (in the 'get_user' function above). In either case, I see the query getting logged in MySQL's query logs. Any ideas at all? I've searched through both the mod_python and sqlalchemy archives and haven't found anything appropriate. that is the symptom of a Session being reused - that the SQL is issued but it returns the existing identity map version. But the code you've illustrated should not have this problem since you are creating and closing a Session within the scope of a function call (but you said, that's only a synopsis of the code which I assume means it's not verbatim). I'd add logging which includes the in-memory identity of the Session in use as well as assertions that it's empty before use (assert len(list(session)) == 0). if a preceding session.clear() fixes the problem thats also a sign of that 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Strange caching problem with Apache
On Mar 28, 4:12 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Mar 28, 2008, at 6:42 PM, john spurling wrote: The funny part is that if I run this very same code in a standalone Python process (where the process is kept alive, like the Apache process), there is no caching behavior; the correct value is returned every single time. If it's run in Apache, I can see the incorrect value get returned in the logs (in the 'get_user' function above). In either case, I see the query getting logged in MySQL's query logs. Any ideas at all? I've searched through both the mod_python and sqlalchemy archives and haven't found anything appropriate. that is the symptom of a Session being reused - that the SQL is issued but it returns the existing identity map version. But the code you've illustrated should not have this problem since you are creating and closing a Session within the scope of a function call (but you said, that's only a synopsis of the code which I assume means it's not verbatim). I'd add logging which includes the in-memory identity of the Session in use as well as assertions that it's empty before use (assert len(list(session)) == 0). if a preceding session.clear() fixes the problem thats also a sign of that issue. I added debugging to get id(session) and len(list(session)). The session id is unique every time, and len(list(session)) is 0. I also called session.clear() before using it. Unfortunately, the caching behavior persists. Any other suggestions? Thank you very much for your time and help! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Strange caching problem with Apache
On Mar 28, 2008, at 7:56 PM, john spurling wrote: I added debugging to get id(session) and len(list(session)). The session id is unique every time, and len(list(session)) is 0. I also called session.clear() before using it. Unfortunately, the caching behavior persists. Any other suggestions? Thank you very much for your time and help! if its zero, then the Session isnt caching. Something is going on HTTP/process-wise. --~--~-~--~~~---~--~~ 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: Strange caching problem with Apache
On Mar 29, 11:02 am, Michael Bayer [EMAIL PROTECTED] wrote: On Mar 28, 2008, at 7:56 PM, john spurling wrote: I added debugging to get id(session) and len(list(session)). The session id is unique every time, and len(list(session)) is 0. I also called session.clear() before using it. Unfortunately, the caching behavior persists. Any other suggestions? Thank you very much for your time and help! if its zero, then the Session isnt caching. Something is going on HTTP/process-wise. Could it be that because Apache is a multi process web server (on UNIX), that OP is getting confused through subsequent requests actually hitting a different process. That said, sqlalchemy as I understood it was meant to deal with that, ie., change made from one process should be reflected in another process straight away upon a new query, ie., cached data should be replaced. Is it possible that what ever insures that has been disabled. OP should perhaps print out os.getpid() so they know which process is handling the request each time. This may help to explain what is going on. Graham --~--~-~--~~~---~--~~ 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: Strange caching problem with Apache
On Mar 28, 2008, at 11:54 PM, Graham Dumpleton wrote: On Mar 29, 11:02 am, Michael Bayer [EMAIL PROTECTED] wrote: On Mar 28, 2008, at 7:56 PM, john spurling wrote: I added debugging to get id(session) and len(list(session)). The session id is unique every time, and len(list(session)) is 0. I also called session.clear() before using it. Unfortunately, the caching behavior persists. Any other suggestions? Thank you very much for your time and help! if its zero, then the Session isnt caching. Something is going on HTTP/process-wise. Could it be that because Apache is a multi process web server (on UNIX), that OP is getting confused through subsequent requests actually hitting a different process. That said, sqlalchemy as I understood it was meant to deal with that, ie., change made from one process should be reflected in another process straight away upon a new query, ie., cached data should be replaced. Is it possible that what ever insures that has been disabled. OP should perhaps print out os.getpid() so they know which process is handling the request each time. This may help to explain what is going on. the only cache-like thing SA has is the session...and in this case we are starting from an empty session, so no caching is taking place. my next idea is that the UPDATE you're issuing is not being committed within its transaction so is not visible to other connections. Although thats unusual for MySQL since its commandline is usually in autocommit mode. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
