Re: [sqlalchemy] Unexplainable SQL queries / Performance issues
Hmm maybe I'm missing something. Perhaps someone else can jump in,
and show me what I'm missing?
When I take your code, and execute the following 5 cases:
CASE A: no 'subquery' on relationship, 'subqueryload' on query
CASE B: 'subquery' on relationship, no 'subqueryload' on query
CASE C: no 'joined' on relationship, 'joinedload' on query
CASE D: 'joined' on relationship, no 'joinedload' on query
CASE E: no 'joined' or 'subquery' on relationship, no 'joinedload'
or 'subqueryload' on query
1) The queries in cases A B are equal (exactly 2 queries each)
2) The queries in cases C D are equal (exactly 1 query each)
3) Case E is completely lazy, there are exactly 1 + N queries
Here are the notes I kept, as I tested those cases:
http://pastebin.com/hx0Kj4An
Martin: perhaps create a new pastebin that shows exactly what you're
doing (including table/engine/session creation, data population, etc),
so that I have a better chance of seeing what you're seeing?
http://pastebin.com/mcum0c7Q
--diana
On Thu, Nov 22, 2012 at 6:09 AM, Martin84 steko...@googlemail.com wrote:
Hi Diana,
thank you for your help, but unfortunately my problem still exists.
In my case the lazy attribute for the myChildren relationship has absolutely
no effect!
At this point one more information is important: I build and populate the
database with one session, and then open a new session and call
showDatabase.
If you populate the database with one session, and then call showDatabase
with the same (now not empty) session,
then the instances are already in the session and sqlalchemy don't fire new
queries for access to the myChildren attribute of human.
So, it is important to call showDatabase with an empty session to reproduce
my issue.
One more information could be important:
If I load a woman instance and access myChildren with:
woman = session.query(Woman).one()
print woman.myChildren
then sqlalchemy emits an extra sql query on the access to myChildren and
ignore my lazy='subquery' or lazy='joined' parameter for the mychildren
relationship configuration.
But if I modify the query like this:
woman = session.query(Woman).options(subqueryload('myChildren')).one()
print woman.myChildren
then sqlalchemy load the woman and all children at once! Exactly what I
need.
But this makes completely no sense to me, i thought that subqueryload() just
overload the load strategie for a relationship.
So myChildren = relationship('Child', secondary=link_table, lazy='subquery')
and subqueryload('myChildren') should be equivalent.
Why is there a difference?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/GponYdm2PLsJ.
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.
--
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.
Re: [sqlalchemy] Unexplainable SQL queries / Performance issues
Morning Martin:
I could be wrong, but I think what you're looking for is lazy='joined'
rather than lazy='subquery'.
When I change the following, I see one query per showDatabase() call
rather than two.
class Men(Human):
myChildren = relationship('Child', secondary=link_table, lazy='joined')
class Woman(Human):
myChildren = relationship('Child', secondary=link_table, lazy='joined')
Here's how I think of it, with examples from:
http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html?highlight=subqueryload#using-loader-strategies-lazy-loading-eager-loading
* Case 1 (1 + N queries):
# set children to load lazily
session.query(Parent).options(lazyload('children')).all()
* Case 2 (1 query):
# set children to load eagerly with a join
session.query(Parent).options(joinedload('children')).all()
* Case 3 (2 queries):
# set children to load eagerly with a second statement
session.query(Parent).options(subqueryload('children')).all()
The subqueryload() and lazy='subquery' options emit an *additional*
SQL statement for each collection requested, but at least it's not N
queries (one for each child).
Thanks for including isolated code to easily reproduce the question.
Cheers,
--diana
On Wed, Nov 21, 2012 at 3:25 AM, Martin84 steko...@googlemail.com wrote:
Hi,
I use SQLalchemy 0.7.9 with SQLite and have a performance issue.
I have a simple database with joined table inheritance and some
relationships.
In short: I have a base class Human, and 3 subclasses (Men, Woman and
Child), and I have a class House.
The house class have a many to many relationship to the human class (a house
have residents, and every human have houses).
And there are two more relationship, the Woman and the Men class have a many
to many relationship to the Child class (a men/woman can have many
children).
Here you can see my model , query code: http://pastebin.com/mcum0c7Q
The issue is: if I load a house from the database with house =
session.query(House).first() and then access the residents of this house
with house.residents,
and iterate over the residents and access the children of every resident
then sqlalchemy emits a new sqlquery on every access:
for resident in house.residentes:
print resident.myChildren # emits new sql-query
This is very bad for my performance, what I need is a solution, that load
with a single session.query(House)-Command all residents AND all children
of the residents at once!
Is this possible?
For the many to many relationship between the Women/Men and the Child class
is use lazy='subquery', but sqlalchemy ignore this! Why?
I hope someone could help me.
--
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.
Re: [sqlalchemy] Re: cherrypy SA 0.7 scoped_session
Hi John:
The following import is causing that error.
from sqlalchemy.orm import session, Session
In the all-in-one example you provided, you later go on to reassign
'session' and 'Session', replacing those imported values with your own
values.
Session = scoped_session(sessionmaker(bind=engine)) session = Session()
In the two-file version, restresource.py isn't getting the 'Session'
you created in two_file_server.py, but rather a new blank one from the
import statement.
Hope that helps,
--diana
On Thu, Dec 8, 2011 at 8:41 PM, John Hufnagle johnjhufna...@gmail.com wrote:
Thanks Michael,
Could not find a hidden Session object.
So I broke it down into a problem that works/doesn't work based on
files separation
1. If I place all of the code into one file all_in_one.py which
contains the cherrypy startup, the SA init code and ORM object and the
cherrypy REST resource then it works when trying to use a session
during request handling
2. If I divide up the code into 2 files...the cherrypy startup code
SA init code in one file and then the REST class, and the ORM class in
the other then it fails when trying to use a session during request
handling.
In both cases the code is 'essentially' the same.
I'm sure I'm missing a python based problem...I'm new to it as well as
SA.
Working single file case all_in_one.py
import cherrypy
from sqlalchemy import *
import MySQLdb
from sqlalchemy.orm import session, Session, scoped_session,
sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy import Column, Integer, String, DateTime, func
engine = create_engine('mysql://:x@localhost/test', echo=True)
meta = MetaData()
Session = scoped_session(sessionmaker(bind=engine))
class RESTResource(object):
orm_class = None
def __init__(self, ormcls):
self.orm_class = ormcls
@cherrypy.expose
def default(self, *vpath, **params):
method = getattr(self, handle_ + cherrypy.request.method,
None)
if not method:
methods = [x.replace(handle_, )
for x in dir(self) if x.startswith(handle_)]
cherrypy.response.headers[Allow] = ,.join(methods)
raise cherrypy.HTTPError(405, Method not implemented.)
return method(*vpath, **params);
def handle_GET(self, *vpath, **params):
try:
session = Session()
session.query(ProjectORM).filter(ProjectORM.id ==
12).one()
except:
print got exception!
raise
print all ok!
return done GET
def handle_PUT(self, *vpath, **params):
return done PUT
Base = declarative_base()
class ProjectORM(Base):
__tablename__ = 'project'
id = Column(Integer, primary_key=True)
name = Column(String(256))
about = Column(String(1024))
url = Column(String(512))
version = Column(Integer)
mbd_metadata = Column('metadata',MEDIUMTEXT)
creation = Column(DateTime)
def __init__(self, name, about, url, version, mbd_metadata):
self.name = name
self.about = about
self.url = url
self.version = version
self.mbd_metadata = mbd_metadata
self.creation = func.now()
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.about,
self.url, self.version, self.mbd_metadata, self.creation)
class Root(object):
project = RESTResource(ProjectORM)
@cherrypy.expose
def index(self):
return JBPC VAMPS REST Service
cherrypy.quickstart(Root())
-
-
Failing two file case
first file
two_file_server.py as follows:
import cherrypy
from sqlalchemy import *
import MySQLdb
from sqlalchemy.orm import session, Session, scoped_session,
sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from restresource import *
engine = create_engine('mysql://:x@localhost/test', echo=True)
meta = MetaData()
Session = scoped_session(sessionmaker(bind=engine))
class Root(object):
project = RESTResource(ProjectORM)
@cherrypy.expose
def index(self):
return JBPC VAMPS REST Service
cherrypy.quickstart(Root())
-
-
the second file of the two file set:
restresource.py
import cherrypy
from sqlalchemy import *
import MySQLdb
from sqlalchemy.orm import session, Session, scoped_session,
sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy import Column, Integer, String, DateTime, func
Base = declarative_base()
class ProjectORM(Base):
__tablename__ = 'project'
id = Column(Integer, primary_key=True)
name =
Re: [sqlalchemy] Re: cherrypy SA 0.7 scoped_session
[hmmm, let's try that again... not sure why gmail mangled my email]
Hi John:
The following import is causing that error.
from sqlalchemy.orm import session, Session
In the all-in-one example you provided, you later go on to reassign
'session' and 'Session', replacing those imported values with your own
values.
Session = scoped_session(sessionmaker(bind=engine))session = Session()
In the two-file version, restresource.py isn't getting the 'Session'
you created in two_file_server.py, but rather a new blank one from the
import statement.
Hope that helps,
--diana
On Thu, Dec 8, 2011 at 8:41 PM, John Hufnagle johnjhufna...@gmail.com wrote:
Thanks Michael,
Could not find a hidden Session object.
So I broke it down into a problem that works/doesn't work based on
files separation
1. If I place all of the code into one file all_in_one.py which
contains the cherrypy startup, the SA init code and ORM object and the
cherrypy REST resource then it works when trying to use a session
during request handling
2. If I divide up the code into 2 files...the cherrypy startup code
SA init code in one file and then the REST class, and the ORM class in
the other then it fails when trying to use a session during request
handling.
In both cases the code is 'essentially' the same.
I'm sure I'm missing a python based problem...I'm new to it as well as
SA.
Working single file case all_in_one.py
import cherrypy
from sqlalchemy import *
import MySQLdb
from sqlalchemy.orm import session, Session, scoped_session,
sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy import Column, Integer, String, DateTime, func
engine = create_engine('mysql://:x@localhost/test', echo=True)
meta = MetaData()
Session = scoped_session(sessionmaker(bind=engine))
class RESTResource(object):
orm_class = None
def __init__(self, ormcls):
self.orm_class = ormcls
@cherrypy.expose
def default(self, *vpath, **params):
method = getattr(self, handle_ + cherrypy.request.method,
None)
if not method:
methods = [x.replace(handle_, )
for x in dir(self) if x.startswith(handle_)]
cherrypy.response.headers[Allow] = ,.join(methods)
raise cherrypy.HTTPError(405, Method not implemented.)
return method(*vpath, **params);
def handle_GET(self, *vpath, **params):
try:
session = Session()
session.query(ProjectORM).filter(ProjectORM.id ==
12).one()
except:
print got exception!
raise
print all ok!
return done GET
def handle_PUT(self, *vpath, **params):
return done PUT
Base = declarative_base()
class ProjectORM(Base):
__tablename__ = 'project'
id = Column(Integer, primary_key=True)
name = Column(String(256))
about = Column(String(1024))
url = Column(String(512))
version = Column(Integer)
mbd_metadata = Column('metadata',MEDIUMTEXT)
creation = Column(DateTime)
def __init__(self, name, about, url, version, mbd_metadata):
self.name = name
self.about = about
self.url = url
self.version = version
self.mbd_metadata = mbd_metadata
self.creation = func.now()
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.about,
self.url, self.version, self.mbd_metadata, self.creation)
class Root(object):
project = RESTResource(ProjectORM)
@cherrypy.expose
def index(self):
return JBPC VAMPS REST Service
cherrypy.quickstart(Root())
-
-
Failing two file case
first file
two_file_server.py as follows:
import cherrypy
from sqlalchemy import *
import MySQLdb
from sqlalchemy.orm import session, Session, scoped_session,
sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from restresource import *
engine = create_engine('mysql://:x@localhost/test', echo=True)
meta = MetaData()
Session = scoped_session(sessionmaker(bind=engine))
class Root(object):
project = RESTResource(ProjectORM)
@cherrypy.expose
def index(self):
return JBPC VAMPS REST Service
cherrypy.quickstart(Root())
-
-
the second file of the two file set:
restresource.py
import cherrypy
from sqlalchemy import *
import MySQLdb
from sqlalchemy.orm import session, Session, scoped_session,
sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import MEDIUMTEXT
from sqlalchemy import Column, Integer, String, DateTime, func
Base = declarative_base()
class ProjectORM(Base):
__tablename__ =
[sqlalchemy] IS NULL filter, non-NULL row returned
Any ideas why I'm getting one row back with an id of 5 when I filtered
by id IS NULL?
[SQLAlchemy-0.6.4, MySQL 5, MyISAM]
sqlalchemy.engine.base.Engine.0x...eb2c: INFO: SELECT user.id AS
user_id, user.username AS user_username, user.level AS user_level
FROM user
WHERE user.level = %s AND user.id IS NULL
sqlalchemy.engine.base.Engine.0x...eb2c: INFO: (1,)
sqlalchemy.engine.base.Engine.0x...eb2c: DEBUG: Col ('user_id',
'user_username', 'user_level')
sqlalchemy.engine.base.Engine.0x...eb2c: DEBUG: Row (5L, u'jane', 1)
Thanks,
--diana
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] IS NULL filter, non-NULL row returned
Wow, I want to unlearn this.
For the benefit of some ODBC applications, the following query can be
used to find a newly inserted row: ... SELECT * FROM tbl_name WHERE
auto IS NULL;
All further executions of the same statement provide the expected result
Thanks, Michael!
--diana
On Tue, Oct 26, 2010 at 11:27 PM, Michael Bayer
mike...@zzzcomputing.com wrote:
yeah you'd have to search around MySQL's bugtracker for that one, I've seen
it before, the only record I can find at the moment is #4 here:
http://sql-info.de/mysql/gotchas.html#1_1
On Oct 26, 2010, at 11:16 PM, Diana Clarke wrote:
Any ideas why I'm getting one row back with an id of 5 when I filtered
by id IS NULL?
[SQLAlchemy-0.6.4, MySQL 5, MyISAM]
sqlalchemy.engine.base.Engine.0x...eb2c: INFO: SELECT user.id AS
user_id, user.username AS user_username, user.level AS user_level
FROM user
WHERE user.level = %s AND user.id IS NULL
sqlalchemy.engine.base.Engine.0x...eb2c: INFO: (1,)
sqlalchemy.engine.base.Engine.0x...eb2c: DEBUG: Col ('user_id',
'user_username', 'user_level')
sqlalchemy.engine.base.Engine.0x...eb2c: DEBUG: Row (5L, u'jane', 1)
Thanks,
--diana
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] session lifecycle and wsgi
Hi Chris: I'm a bit hesitant to share what I've done, b/c it's still a work in progress etc, but here goes: MySQL MyISAM, wait_timeout=28800 SQLAlchemy 0.5.6, pool_recycle=3600 I've written a few decorators (mostly stolen from SQLAlchemy docs examples): def with_query_write(fn): def go(self, *args, **kw): try: result = fn(self, *args, **kw) self.session.commit() return result except: self.session.rollback() raise return go def with_query_read(fn): def go(self, *args, **kw): try: return fn(self, *args, **kw) except: self.session.rollback() raise return go def with_session_write(fn): def go(*args, **kw): try: result = fn(*args, **kw) session.commit() return result except: session.rollback() raise return go All session writes go through base_dao.py, and are decorated with @with_session_write. This way the code isn't littered with session.add(), session.commit(), session.rollback() etc. @with_session_write def save(self, instance): session.add(instance) ... We've also extended Query (for reasons other than framing -- code omitted) and added @with_query_write and @with_query_read decorators. I don't love how I extended Query, and as of PyCon I know a better way to do this, but I haven't had a chance to re-implement it. Anyhoo, we pass the custom query class to the sessionmaker: session = orm.scoped_session(orm.sessionmaker(query_cls=FooQuery)) The custom query class: class FooQuery(Query): def __init__(self, *arg, **kw): Query.__init__(self, *arg, **kw) ... @with_query_read def all(self): return Query.all(self) @with_query_write def delete(self): return Query.delete(self) ... Finally, we're using pylons and are removing the contextual session in the finally clause of the base controller's __call__ method. class BaseController(WSGIController): def __call__(self, environ, start_response): try: ... finally: session.remove() We only ever see 'MySQL server has gone away' on our idle failover app instances, as the only traffic they get are occasional pings from nagios. I would have thought a combination of wait_timeout pool_recycle would prevent this... but I'm probably missing some piece of the big picture. OperationalError: (OperationalError) (2006, 'MySQL server has gone away') I do wonder how possible it would be wrt the open session in view etc to implement a reconnect on is_dissconect() in _handle_dbapi_exception()... I also wonder if there's some app out there using SQLAlchemy with an exemplary data access layer that we could all learn from. I should try a few code search engines... I went with DAOs (one per mapped table) which extend a base DAO that know how to do pagination, saves, deletes, etc. I dunno... Thanks fro asking Chris. I'm watching the answers to these threads too. --diana On Wed, Apr 28, 2010 at 9:37 AM, Chris Withers ch...@simplistix.co.ukwrote: Hi All, I'm still trying to get an answer on this... Am I right in understanding that the basic session lifecycle should be: try: use session session.commit() except: log() session.rollback() finally: session.remove() The structure I've traditionally used with transactions has been: try: use session except: log() session.rollback() else: session.commit() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] session lifecycle and wsgi
Yup, no transactions (legacy, can't switch anytime soon) which is why I didn't originally write any rollback framing... but I was still getting the following error after MySQL raised a 2006 (until app restart), and a quick peek at _handle_dbapi_exception seemed to suggest that I needed to issue rollbacks even for non-transactional database engines. InvalidRequestError: Can't reconnect until invalid transaction is rolled back It goes without saying, but perhaps I've misunderstood something... --diana On Wed, Apr 28, 2010 at 10:52 AM, Chris Withers ch...@simplistix.co.uk wrote: MySQL MyISAM, wait_timeout=28800 You have no transactions, so I'm not sure why you're worrying about them... Switch to InnoDB if you want transactions... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.6.0 released
Yes, Congratulations Thank-you! --diana On Sun, Apr 18, 2010 at 8:02 PM, Michael Bayer mike...@zzzcomputing.com wrote: The first official 0.6 release of SQLAlchemy is now available. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Sharding - same table definitions yet different (but predictable) table names... thousands of them
Thanks for the quick response, Michael. We are mapping the classes dynamically, and we patched an in-house copy of SQLAlchemy 0.5.6 with the stricter mutex from SQLAlchemy 0.6 to fix the sporadic mappers failed to compile errors we were seeing under production load. We also added a Least Recently Used cache for these dynamically mapped tables because we needed the memory usage to max out at something reasonable. When I get a chance, I'll play with UsageRecipes/EntityName to see if differs from our current approach in any significant ways. In the mean time, I'm stuck on another sharding question... which I might ask shortly ;) Thanks again for your time, --diana On Fri, Jan 8, 2010 at 10:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: how does your application want to reference these tables ? while this might be a little memory consuming I'd probably look into generating mappers, Tables, and classes dynamically ala the recipe http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName . If the table is chosen based on the attributes of the class, I'd create new instances of the class using a factory function: for i in range(1, M): table = Table(circle_%d % i, ...) my_registry_of_classes[Circle_%d % i] = \ map_class_to_some_table(CircleBase, table, Circle_%d % i) def myobject(circle): return my_registry_of_classes[Circle_%d% circle]() x = myobject(circle=5) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] why query_chooser rather than shard_chooser in this case?
A, deferred (new to me), thanks!
in sqlalchemy/test/orm/sharding/test_shard.py:
mapper(WeatherLocation, weather_locations, properties={
'reports':relation(Report, backref='location'),
'city': deferred(weather_locations.c.city),
})
When I comment out the deferred property, it behaves as I would
suspect (one query_chooser call).
Ok, that answers Question #1.
Question #2 similar, but w/ session.add(). I'll send a new email for
Question #2.
Thanks,
--diana
On Mon, Jan 11, 2010 at 3:25 PM, Michael Bayer mike...@zzzcomputing.com wrote:
of course the details of the mapping might say something totally different
(i.e. deferred(), joined table inhertance, etc.)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] why query_chooser rather than shard_chooser in this case?
Again, this investigative test is loosely based on SQLAlchemy's sharding test: sqlalchemy/test/orm/sharding/test_shard.py def test_update(self): print \n session = create_session() query = session.query(WeatherLocation) # query_chooser returns: ['asia'] print get tokyo: tokyo = query.filter_by(city='Tokyo').filter_by(continent='Asia').first() # no new SQL print access tokyo: assert tokyo.city == Tokyo # no new SQL print change tokyo: tokyo.city = Tokyo_city_name_changed # uses shard_chooser by instance print save tokyo: session.add(tokyo) session.commit() # query_chooser returns: ['north_america', 'asia', 'europe', 'south_america'] print access tokyo 2: assert tokyo.city == Tokyo_city_name_changed My question #2: If we already have an instance of tokyo from the 'save tokyo' code snippet, why is a new query_cls being instantiated to refresh the tokyo object in 'access tokyo 2' (thus having to traverse all 4 shards) rather than using shard_chooser and the got instance to compute the shard based on its continent value? Is there some way I can optimize this case, perhaps by setting the shard_id somewhere, so that 4 queries aren't executed in this case? Thanks, --diana On Mon, Jan 11, 2010 at 3:38 PM, Diana Clarke diana.joan.cla...@gmail.com wrote: Question #2 similar, but w/ session.add(). I'll send a new email for Question #2. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] why query_chooser rather than shard_chooser in this case?
Thanks, Michael. This will take me a bit to digest, and I'm about to start the second shift as wife and mother... tomorrow maybe. Thanks again for the quick responses -- greatly exceeding expectations! Cheers, --diana On Mon, Jan 11, 2010 at 5:14 PM, Michael Bayer mike...@zzzcomputing.com wrote: well there's two things, one left over from previous. one is that commit() expires all attributes in the session. that is why new SQL is emitted. check the docs for rationale there. but also, the loading of deferred attributes as earlier and expired attributes here does have the primary key, so its a bug that shard_chooser is being run here, since the internal function doing that is calling query._get(), whereas ShardedQuery is being simple and only overriding get(). You might want to change ShardedQuery to override _get() instead (which leads me further towards pulling the trigger of moving shard.py out to examples altogether for 06, since it really is not supportable as a core element, just FYI). Its also possibly worth it to get your ShardChooser to the point where it can recognize what is effectively a get() based on filtering criterion. You can do this by imitating the approach in the example FindContinent chooser in examples/sharding/attribute_shard.py. Thanks, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pylons SQLAlchemy memory
On Mon, Jan 11, 2010 at 7:07 PM, Antoine Pitrou solip...@pitrou.net wrote: Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : Well if you only want to count entries, use Query.count(), not Query.all(). Yup, I don't actually do this in a real app. I was just doing this (in a hello world app) as an exercise to illustrate a point, and to better understand pylons (and, as I'm just learning, the Python VM). Thanks Antoine, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pylons SQLAlchemy memory
I never really suspected that this was a SQLAlchemy issue, which was why I didn't originally post this question to the SQLAlchemy group. I apologize if it came across that way. Time for me to do my python VM homework... My apologies, --diana On Mon, Jan 11, 2010 at 8:15 PM, Michael Bayer mike...@zzzcomputing.com wrote: we have a full set of tests that ensure SQLA itself has no unreleased memory issues or excessive cycles and they've been in our trunk for several years, and we also nailed a few remaining corner cases over the past year which correspond to highly unusual usage patterns, so I'm very confident that there's no issues within SQLA itself. On Jan 11, 2010, at 7:17 PM, Diana Clarke wrote: On Mon, Jan 11, 2010 at 7:07 PM, Antoine Pitrou solip...@pitrou.net wrote: Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : Well if you only want to count entries, use Query.count(), not Query.all(). Yup, I don't actually do this in a real app. I was just doing this (in a hello world app) as an exercise to illustrate a point, and to better understand pylons (and, as I'm just learning, the Python VM). Thanks Antoine, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
