Re: [sqlalchemy] per-host pooling vs per-engine pooling
So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 - the url is passed along the ConnectionFairy checkout method, so I can decide if I have to switch the database - I keep a few globals to decide lazily if a database should be initialized - I keep one engine and one pool *per server* it seems to work, here's an example of usage with the query() function: http://tarek.pastebin.mozilla.org/1372476 So, now I am pretty sure most of this code is crappy, and there's a better way to do this. I still need to make sure all of this is thread-safe. Thoughts ? Feedback ? Cheers Tarek -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Thu, Nov 3, 2011 at 2:28 PM, Tarek Ziadé ziade.ta...@gmail.com wrote: So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 ooops, I missed a piece : def _raw(self): return self.pool.unique_connection(self.url) -- Tarek Ziadé | http://ziade.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] query returns a non-result?
Hi All,
Any idea what this traceback is about?
This query normally works fine and is run a few hundred times a day ;-)
cheers,
Chris
Original Message
session.query(PasswordRequest).with_lockmode('update').all():
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/query.py, line 1579,
in all
return list(self)
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/query.py, line 1791,
in instances
fetch = cursor.fetchall()
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py, line
2498, in fetchall
l = self.process_rows(self._fetchall_impl())
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py, line
2467, in _fetchall_impl
self._non_result()
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py, line
2472, in _non_result
This result object does not return rows.
sqlalchemy.exc.ResourceClosedError: This result object does not return
rows. It has been closed automatically.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] select count(*)
Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Thanks, Mark -- 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] select count(*)
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
require a field object. Is there a way to generate a query that essentially
becomes 'select count(*) from table' using the ORM, not the SQL generator?
Thanks,
Mark
Did you try func.count('*')?
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web: www.asidev.com
Skype: stefanofontanelli
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Foreign key reflection error?
Hi,
I'm getting the following error with SQLAlchemy 0.7.3:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship Survey.bossSpectrumHeaders. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed
as well.
I am relating two tables named Survey and BOSSSpectrumHeader. The former is
in a schema called platedb and the latter in another schema called boss.
The latter table has numerous foreign keys from the platedb schema, but and
each fail if I comment out the previous one. The search path is platedb,
shared, boss, photofield, twomass, public. The python code is:
class BOSSSpectrumHeader(Base):
__tablename__ = 'spectrum_header'
__table_args__ = {'autoload' : True, 'schema' : 'boss', 'extend_existing' :
True}
class Survey(Base):
__tablename__ = 'survey'
__table_args__ = {'autoload' : True, 'schema' : 'platedb'}
Survey.bossSpectrumHeaders = relationship(BOSSSpectrumHeader, backref=survey)
Finally, the SQL definitions of the tables are pasted below. Is there something
I am missing? Why is the foreign key not being retrieved via reflection?
Virtually everything else (including cross-schema relationships) is working
fine.
Thanks for any help!
Cheers,
Demitri
---
CREATE TABLE boss.spectrum_header
(
pk integer NOT NULL DEFAULT nextval('spectrum_header_pk_seq'::regclass),
...
survey_pk integer NOT NULL,
CONSTRAINT boss_spectrum_header_pk PRIMARY KEY (pk ),
CONSTRAINT survey_fk FOREIGN KEY (survey_pk)
REFERENCES survey (pk) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
CREATE TABLE survey
(
pk serial NOT NULL,
label text,
CONSTRAINT survey_pk PRIMARY KEY (pk ),
CONSTRAINT survey_label_uniq UNIQUE (label )
)
--
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] select count(*)
Mark, On 11/03/2011 07:18 PM, Mark Erbaugh wrote: Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Just the other day I thought I needed the same, initially I just used the id column which all my tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the id values of that table (to build a virtual listctrl in wxPython), so instead of doing the count and starting feeling the list I got the id and did a len(onresult) to get my count. Point I am trying to make with a lot of words, maybe there is a solution which doesn't need count() at all:-) . Werner -- 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] Foreign key reflection error?
On Nov 3, 2011, at 3:12 PM, thatsanicehatyouh...@mac.com wrote:
Hi,
I'm getting the following error with SQLAlchemy 0.7.3:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship Survey.bossSpectrumHeaders. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
needed as well.
I am relating two tables named Survey and BOSSSpectrumHeader. The former
is in a schema called platedb and the latter in another schema called
boss. The latter table has numerous foreign keys from the platedb schema,
but and each fail if I comment out the previous one. The search path is
platedb, shared, boss, photofield, twomass, public. The python code is:
two things I notice, first why using extend_existing - suggests theres more
going on here. Also are you certain the foreign key from boss.spectrum_header
points to the platedb.schema table and not another schema table elsewhere ?
Yet another thing, when you reflect the foreign key from spectrum_header, it
may not be coming back with platedb as the schema since you appear to be
referring to the remote table using the implicit search path. SQLAlchemy may
not be matching that up like you expect. There was an issue regarding this
which was fixed in 0.7.3, another user relying upon a long search path. Do
you get different results using 0.7.2 ? can you try defining your foreign key
constraints in PG consistently with regards to how you're using schemas in your
model ? (i.e. either the FK is to platedb.schema in PG, or remove the
platedb schema from Survey).
class BOSSSpectrumHeader(Base):
__tablename__ = 'spectrum_header'
__table_args__ = {'autoload' : True, 'schema' : 'boss', 'extend_existing'
: True}
class Survey(Base):
__tablename__ = 'survey'
__table_args__ = {'autoload' : True, 'schema' : 'platedb'}
Survey.bossSpectrumHeaders = relationship(BOSSSpectrumHeader,
backref=survey)
Finally, the SQL definitions of the tables are pasted below. Is there
something I am missing? Why is the foreign key not being retrieved via
reflection? Virtually everything else (including cross-schema relationships)
is working fine.
Thanks for any help!
Cheers,
Demitri
---
CREATE TABLE boss.spectrum_header
(
pk integer NOT NULL DEFAULT nextval('spectrum_header_pk_seq'::regclass),
...
survey_pk integer NOT NULL,
CONSTRAINT boss_spectrum_header_pk PRIMARY KEY (pk ),
CONSTRAINT survey_fk FOREIGN KEY (survey_pk)
REFERENCES survey (pk) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
CREATE TABLE survey
(
pk serial NOT NULL,
label text,
CONSTRAINT survey_pk PRIMARY KEY (pk ),
CONSTRAINT survey_label_uniq UNIQUE (label )
)
--
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.
--
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] per-host pooling vs per-engine pooling
On Nov 3, 2011, at 9:28 AM, Tarek Ziadé wrote: So, I have worked on a first prototype, and was unable to use the event system as-is, because of a lack of context to know which database the user is trying to access. So, after a bit of hacking, here's what I have done: http://tarek.pastebin.mozilla.org/1372473 - the url is passed along the ConnectionFairy checkout method, so I can decide if I have to switch the database - I keep a few globals to decide lazily if a database should be initialized - I keep one engine and one pool *per server* it seems to work, here's an example of usage with the query() function: http://tarek.pastebin.mozilla.org/1372476 So, now I am pretty sure most of this code is crappy, and there's a better way to do this. I still need to make sure all of this is thread-safe. Thoughts ? Feedback ? I should look at this more closely, took a brief glance. One thought I had was why not do the switch the schema thing within Engine.connect(), at least there you know which engine you're dealing with. Though I don't really understand how this is organized anyway, the query() function for example seems a little weird, wouldn't you want this to be transparent at the Engine level ? There should be a simple way to make two engines talk to one pool and switch the schema based on each engine. Maybe some context to be provided to the checkout event- possibly a small API change. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] query.only_load_relationships() API
There are certain properties that lazy=False or 'joined' makes some sense. But sometimes you don't want to load relationships. In those cases query.enable_eagerloads(False) is very useful. However, often you do want to load *some* relationships, but the rest you want turned off without having to enumerate them manually. You can't use enable_eagerloads(False) because there are a couple you want to specify. Have you considered a public query API for what is similar to the framework's query._only_load_props, except that it would work off just relationship properties (and leave column properties alone)? I've come across this several times now and I find it awkward to need to enumerate all the joined_loads and turn them off manually with query.options(lazyload()). Suggestions? Thanks, Kent -- 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] select count(*)
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the
docs). The docs say to use func.count to avoid the subquery. func.count
seems to require a field object. Is there a way to generate a query that
essentially becomes 'select count(*) from table' using the ORM, not the SQL
generator?
Thanks,
Mark
Did you try func.count('*')?
How would you specify the table you want counted? I trued
func.count('table.*') and that didn't work.
Mark
--
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] select count(*)
On Nov 3, 2011, at 3:31 PM, werner wrote: Mark, On 11/03/2011 07:18 PM, Mark Erbaugh wrote: Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Just the other day I thought I needed the same, initially I just used the id column which all my tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the id values of that table (to build a virtual listctrl in wxPython), so instead of doing the count and starting feeling the list I got the id and did a len(onresult) to get my count. Point I am trying to make with a lot of words, maybe there is a solution which doesn't need count() at all:-) . I never considered that a count(*) was that expensive especially if there is no where clause. I would think that it would be less expensive than actually retrieving all the rows and counting them. What if there are millions of rows? The result set could fill up memory. In my case, I just need to know how many rows. I don't care about any other details. In one case, I'm checking to see if there are zero rows, in which case, I populate the table with initial rows. In another case, I'm just unittesting some code and I want to make sure that there are the proper number of rows in the table as one of the test conditions. Mark -- 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] query.only_load_relationships() API
On Nov 3, 2011, at 6:03 PM, Kent wrote: There are certain properties that lazy=False or 'joined' makes some sense. But sometimes you don't want to load relationships. In those cases query.enable_eagerloads(False) is very useful. However, often you do want to load *some* relationships, but the rest you want turned off without having to enumerate them manually. You can't use enable_eagerloads(False) because there are a couple you want to specify. Have you considered a public query API for what is similar to the framework's query._only_load_props, except that it would work off just relationship properties (and leave column properties alone)? I've come across this several times now and I find it awkward to need to enumerate all the joined_loads and turn them off manually with query.options(lazyload()). Suggestions? I leave lazy=True in almost all cases, except for some that i use subqueryload, then use options(whatever) to joinedload whatever i want to load that way. But I only use joinedload for many-to-ones, which in an equal number of cases I'm already joining to via join(), in which case I use contains_eager(). So there's virtually no case i want joinedload() on by default. joins are expensive when there's too many of them, so these days I never want to specify joinedload() in any other way other than within options(). that said, if there's a set of six different relationships you like to joinedload() a lot, you can build a function that just spits them outquery.options(*standard_joined_loads().minus(i_dont_want_these)) type of thingI've used that approach for setting up caching options in the past. Thanks, Kent -- 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. -- 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.
