[sqlalchemy] Re: Selecting what properties of an object will be loaded
Cool, thanx
On 16 окт, 19:15, Ants Aasma <[EMAIL PROTECTED]> wrote:
> With
> session.query(User).options(undefer(User.column_a),
> defer(User.column_b), noload(User.column_c))
> column_a will be loaded with the query, column_b will be loaded on
> access and column_c will be None regardless of the value in the
> database.
>
> Ants
>
> On Oct 16, 12:56 pm, Alex K <[EMAIL PROTECTED]> wrote:
>
> > Hi All,
>
> > I wonder if there is a way to set what columns of the object will be
> > used during this particular query, to reduce the query in case if I
> > need the object, but I don't need all object properties.
>
> > is something like this: session.query(User).load('column_a') possible?
>
> > session.query([...]) - won't apply, since i need mapped object.
>
> > Thanks,
> > Alex
--~--~-~--~~~---~--~~
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: duplicated sql ? - why?, speeding up relations (slow)
you are right, the generated sql is fast, but the "fetching from results" is slow due the large ammount of columns (at least 100). I don't know now what to use :/ generaly i will use all of the columns that i fetch, including related objects, so everything is needed. any sugestions? On 16 Paź, 23:03, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Oct 16, 2008, at 1:23 PM, g00fy wrote: > > > > > I do have correct results, but this is veryslow. > > I assume that I have to query(Model) whitout any relations > > and then eagerload or join or whatever that will make this work for > > me. > > > Am i right that mapper's lazy=False is not good for this situation ? > > lazy=False specifically means, "join to related tables automatically, > to enable population of related elements and collections" - it means > you'd like eager loading to take place in all cases. So if you have > slowness which is due to many secondary SELECT statements being issued > each time a related attribute is first accessed, its very useful. I > would just suggest that using the eager load feature on a per-Query > basis, i.e. via query.options(eagerload(...)), would give you finer > grained control over when this feature is used. > > if OTOH the slowness is due to an enormous collection of elements > which load every time when eager loading is used, and you'd rather not > load this collection in, then you would *not* want to use eager > loading. If you do need access to elements of a very large > collection, there are other options which can help with this. > > what is common here is that you need to understand the source of your > application's slowness in order to determine the best action to take. --~--~-~--~~~---~--~~ 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] Mod_python problem
Hello.
I'm trying to use sqlalchemy with mod_python, but im getting a strange
behavior when i try to use the function sessionmaker. Here is the error:
AttributeError: 'module' object has no attribute 'sessionmaker'
on the line:
Session = sqlalchemy.orm.sessionmaker(autoflush=False, transactional=False)
I've tried various methods to import the function with no sucess. Works fine
on the console. Heres what i tried (changing the call to sessionmaker
acordingly)
import sqlalchemy, sqlalchemy.orm
from sqlalchemy.orm import *
from sqlalchemy.orm.session import *
orm = apache.import_module('sqlalchemy.orm')
orm = apache.import_module('sqlalchemy.orm.session')
Can someone please help me?
Thanks a lot in advance!
Tiago Becker
--~--~-~--~~~---~--~~
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: duplicated sql ? - why?, speeding up relations (slow)
On Oct 16, 2008, at 1:23 PM, g00fy wrote: > > I do have correct results, but this is very slow. > I assume that I have to query(Model) whitout any relations > and then eagerload or join or whatever that will make this work for > me. > > Am i right that mapper's lazy=False is not good for this situation ? lazy=False specifically means, "join to related tables automatically, to enable population of related elements and collections" - it means you'd like eager loading to take place in all cases. So if you have slowness which is due to many secondary SELECT statements being issued each time a related attribute is first accessed, its very useful. I would just suggest that using the eager load feature on a per-Query basis, i.e. via query.options(eagerload(...)), would give you finer grained control over when this feature is used. if OTOH the slowness is due to an enormous collection of elements which load every time when eager loading is used, and you'd rather not load this collection in, then you would *not* want to use eager loading. If you do need access to elements of a very large collection, there are other options which can help with this. what is common here is that you need to understand the source of your application's slowness in order to determine the best action to take. --~--~-~--~~~---~--~~ 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: duplicated sql ? - why?, speeding up relations (slow)
I do have correct results, but this is very slow. I assume that I have to query(Model) whitout any relations and then eagerload or join or whatever that will make this work for me. Am i right that mapper's lazy=False is not good for this situation ? On 16 Paź, 19:07, Michael Bayer <[EMAIL PROTECTED]> wrote: > first get your query to work properly without any eager loading, > meaning, you get the correct results. then, if your query happens to > JOIN to the table such that the returned rows can be used to populate > the collection or many-to-one reference, use the contains_eager() > option as described in the doc. This option allows you to hand > construct exactly the query you want without SQLA adding anything to > it, but you can still take advantage of eager loading. > > If the JOIN doesnt really represent the correct reference in all > cases, then you'd use automatic eager loading, which will make its own > OUTER JOIN that retrieves the correct data (thats what the anon_1 > thing is). I would advise only enabling this on a per-query basis, > using query.options(eagerload(MyClass.someproperty)). If you dont > want any joins at all, then the collections/references load themselves > through lazy loading. > > The subtlety here is that filtering results on multiple tables does > not necessarily represent the same kind of join that would properly > populate object references, which is why SQLA keeps these separate in > the default case. The query you're doing below is using an EXISTS on > a subquery so I dont see any opportunities for using contains_eager() > as it is. > > On Oct 16, 2008, at 12:45 PM, g00fy wrote: > > > > > So in simple words, how do I speed this up? > > > On 16 Paź, 18:42, Michael Bayer <[EMAIL PROTECTED]> wrote: > >> On Oct 16, 2008, at 12:31 PM, g00fy wrote: > > >>> I have loads of related fields in my model. > >>> I set up lazy = False where i had to and i don't know why SA keeps > >>> "duplicating" (aliasing) my tables, so i fetch my data twice (2 > >>> times > >>> more column that i realy need) > > >>> i send my model and mappings and the sql code SA is querying. > > >>> models: > >>>http://dpaste.com/84873/ > >>> tables: > >>>http://dpaste.com/84874/ > >>> mappings: > >>>http://dpaste.com/84875/ > >>> sql: > >>>http://dpaste.com/84876/ > > >>> notice in sql that anon_1 is realy the same as w_warehouse. > >>> this way i am stuck with 1000 objects having 2 languages and 2 > >>> currencies. > > >>> the code I do is : > >>> meta > >>> .Session > >>> .query(model.Warehouse).order_by(model.Warehouse.area_total.asc()) > >>> [0:10] > >>> .filter( > >>> model.Warehouse.price.has( > >>> model.Price.total<=price_total_max > >>> ) > >>> ) > > >>> how can I get rid of that nasty anon_1, and speed up this thing? > > >> look > >> intohttp://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOIN > >> ... > >> . an eager load of "Price" is not related to the filter > >> criterion > >> using "Price" - you need to join explicitly. > > >> To "dual purpose" your explicit join as an eager load as well, look > >> into : > >> http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relatio > >> ... > > --~--~-~--~~~---~--~~ 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: duplicated sql ? - why?, speeding up relations (slow)
first get your query to work properly without any eager loading, meaning, you get the correct results. then, if your query happens to JOIN to the table such that the returned rows can be used to populate the collection or many-to-one reference, use the contains_eager() option as described in the doc. This option allows you to hand construct exactly the query you want without SQLA adding anything to it, but you can still take advantage of eager loading. If the JOIN doesnt really represent the correct reference in all cases, then you'd use automatic eager loading, which will make its own OUTER JOIN that retrieves the correct data (thats what the anon_1 thing is).I would advise only enabling this on a per-query basis, using query.options(eagerload(MyClass.someproperty)). If you dont want any joins at all, then the collections/references load themselves through lazy loading. The subtlety here is that filtering results on multiple tables does not necessarily represent the same kind of join that would properly populate object references, which is why SQLA keeps these separate in the default case. The query you're doing below is using an EXISTS on a subquery so I dont see any opportunities for using contains_eager() as it is. On Oct 16, 2008, at 12:45 PM, g00fy wrote: > > So in simple words, how do I speed this up? > > On 16 Paź, 18:42, Michael Bayer <[EMAIL PROTECTED]> wrote: >> On Oct 16, 2008, at 12:31 PM, g00fy wrote: >> >> >> >> >> >>> I have loads of related fields in my model. >>> I set up lazy = False where i had to and i don't know why SA keeps >>> "duplicating" (aliasing) my tables, so i fetch my data twice (2 >>> times >>> more column that i realy need) >> >>> i send my model and mappings and the sql code SA is querying. >> >>> models: >>> http://dpaste.com/84873/ >>> tables: >>> http://dpaste.com/84874/ >>> mappings: >>> http://dpaste.com/84875/ >>> sql: >>> http://dpaste.com/84876/ >> >>> notice in sql that anon_1 is realy the same as w_warehouse. >>> this way i am stuck with 1000 objects having 2 languages and 2 >>> currencies. >> >>> the code I do is : >>> meta >>> .Session >>> .query(model.Warehouse).order_by(model.Warehouse.area_total.asc()) >>> [0:10] >>> .filter( >>>model.Warehouse.price.has( >>>model.Price.total<=price_total_max >>>) >>>) >> >>> how can I get rid of that nasty anon_1, and speed up this thing? >> >> look >> intohttp://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOIN >> ... >>. an eager load of "Price" is not related to the filter >> criterion >> using "Price" - you need to join explicitly. >> >> To "dual purpose" your explicit join as an eager load as well, look >> into : >> http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relatio >> ... >> >> >> >> > > --~--~-~--~~~---~--~~ 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: duplicated sql ? - why?, speeding up relations (slow)
So in simple words, how do I speed this up? On 16 Paź, 18:42, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Oct 16, 2008, at 12:31 PM, g00fy wrote: > > > > > > > I have loads of related fields in my model. > > I set up lazy = False where i had to and i don't know why SA keeps > > "duplicating" (aliasing) my tables, so i fetch my data twice (2 times > > more column that i realy need) > > > i send my model and mappings and the sql code SA is querying. > > > models: > >http://dpaste.com/84873/ > > tables: > >http://dpaste.com/84874/ > > mappings: > >http://dpaste.com/84875/ > > sql: > >http://dpaste.com/84876/ > > > notice in sql that anon_1 is realy the same as w_warehouse. > > this way i am stuck with 1000 objects having 2 languages and 2 > > currencies. > > > the code I do is : > > meta > > .Session > > .query(model.Warehouse).order_by(model.Warehouse.area_total.asc()) > > [0:10] > > .filter( > > model.Warehouse.price.has( > > model.Price.total<=price_total_max > > ) > > ) > > > how can I get rid of that nasty anon_1, and speed up this thing? > > look > intohttp://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOIN... > . an eager load of "Price" is not related to the filter criterion > using "Price" - you need to join explicitly. > > To "dual purpose" your explicit join as an eager load as well, look > into : > http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relatio... > > > > --~--~-~--~~~---~--~~ 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: duplicated sql ? - why?, speeding up relations (slow)
On Oct 16, 2008, at 12:31 PM, g00fy wrote: > > I have loads of related fields in my model. > I set up lazy = False where i had to and i don't know why SA keeps > "duplicating" (aliasing) my tables, so i fetch my data twice (2 times > more column that i realy need) > > i send my model and mappings and the sql code SA is querying. > > models: > http://dpaste.com/84873/ > tables: > http://dpaste.com/84874/ > mappings: > http://dpaste.com/84875/ > sql: > http://dpaste.com/84876/ > > notice in sql that anon_1 is realy the same as w_warehouse. > this way i am stuck with 1000 objects having 2 languages and 2 > currencies. > > the code I do is : > meta > .Session > .query(model.Warehouse).order_by(model.Warehouse.area_total.asc()) > [0:10] > .filter( >model.Warehouse.price.has( >model.Price.total<=price_total_max >) >) > > how can I get rid of that nasty anon_1, and speed up this thing? look into http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN . an eager load of "Price" is not related to the filter criterion using "Price" - you need to join explicitly. To "dual purpose" your explicit join as an eager load as well, look into : http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_relation_strategies_containseager > > > --~--~-~--~~~---~--~~ 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] duplicated sql ? - why?, speeding up relations (slow)
I have loads of related fields in my model. I set up lazy = False where i had to and i don't know why SA keeps "duplicating" (aliasing) my tables, so i fetch my data twice (2 times more column that i realy need) i send my model and mappings and the sql code SA is querying. models: http://dpaste.com/84873/ tables: http://dpaste.com/84874/ mappings: http://dpaste.com/84875/ sql: http://dpaste.com/84876/ notice in sql that anon_1 is realy the same as w_warehouse. this way i am stuck with 1000 objects having 2 languages and 2 currencies. the code I do is : meta.Session.query(model.Warehouse).order_by(model.Warehouse.area_total.asc()) [0:10] .filter( model.Warehouse.price.has( model.Price.total<=price_total_max ) ) how can I get rid of that nasty anon_1, and speed up this thing? --~--~-~--~~~---~--~~ 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] Problem with func and string concatenation
I'm using Postgres 8.3 and sqlalchemy 0.5.0rc2 - when I'm doing a
select, it seems I can't concatenate a function with another column -
rather that use the || operator, it tries to use the || operator.
Code to reproduce example:
import sqlalchemy as sa
from sqlalchemy import *
from sqlalchemy.sql import *
meta = MetaData()
def get_pg_statement(s):
return s.compile(bind=create_engine('postgres://'))
pt = Table('people', meta,
Column('id', Integer, primary_key=True),
Column('first_name', String(100)),
Column('last_name', String(100))
)
statements = (
select([func.lower(pt.c.first_name)]),
select([pt.c.first_name + pt.c.last_name]),
select([func.lower(pt.c.first_name) + pt.c.first_name +
pt.c.last_name]),
)
print sa.__version__
for s in statements:
print get_pg_statement(s)
Output of sample (line breaks removed):
0.5.0rc2
SELECT lower(people.first_name) AS lower_1 FROM people
SELECT people.first_name || people.last_name AS anon_1 FROM people
SELECT (lower(people.first_name) + people.first_name) ||
people.last_name AS anon_1 FROM people
Note on the third select, the + operator is used rather than ||.
The following error is thrown from PostGres:
ERROR: operator does not exist: text + character varying
LINE 1: SELECT (lower(people.first_name) + people.first_name) ||
peo...
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
When running "SELECT (lower(people.first_name) || people.first_name)
|| people.last_name AS anon_1 FROM people" the querey executes
properly.
Is there a workaround for this?
--~--~-~--~~~---~--~~
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: Selecting what properties of an object will be loaded
With
session.query(User).options(undefer(User.column_a),
defer(User.column_b), noload(User.column_c))
column_a will be loaded with the query, column_b will be loaded on
access and column_c will be None regardless of the value in the
database.
Ants
On Oct 16, 12:56 pm, Alex K <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I wonder if there is a way to set what columns of the object will be
> used during this particular query, to reduce the query in case if I
> need the object, but I don't need all object properties.
>
> is something like this: session.query(User).load('column_a') possible?
>
> session.query([...]) - won't apply, since i need mapped object.
>
> Thanks,
> Alex
--~--~-~--~~~---~--~~
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] How are connections managed?
Afternoon Guys, I have a suspicion that I'm leaving MySQL database connections open when I shouldn't be and I'm trying to understand how they are managed by SQLAlchemy. I currently create an engine instance and bind my session maker too it like this: # Create the engine to the database. engine = create_engine(connection_string, echo=False) # Connect the session. Session = sessionmaker(bind=engine) I then create sessions around my application by using: # Create a new session. session = Session() and once finished with it closing the session like this: # Close the session. session.close() When are new connections established to the database when using this method? And when are they closed again? The only reason I ask is that I've seen a few errors recently on high load instance of my application which struggle to connect to the database, I'm also seeing a few table corruptions and I think they're all related issues from me perhaps creating too many connections. Thanks for any information you can share chaps, Heston --~--~-~--~~~---~--~~ 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: Selecting what properties of an object will be loaded
That's fine, but I'd like to do the same for columns
On 16 окт, 13:50, [EMAIL PROTECTED] wrote:
> for relations, u can put deferred(name) and noload(name) in
> quety.options( ...).
> no idea for plain columns
>
> On Thursday 16 October 2008 12:56:19 Alex K wrote:
>
> > Hi All,
>
> > I wonder if there is a way to set what columns of the object will
> > be used during this particular query, to reduce the query in case
> > if I need the object, but I don't need all object properties.
>
> > is something like this: session.query(User).load('column_a')
> > possible?
>
> > session.query([...]) - won't apply, since i need mapped object.
>
> > Thanks,
> > Alex
--~--~-~--~~~---~--~~
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: connect data base
>Make sure you have these lines in pg_hba.conf: > hostall all 127.0.0.1/32 password > hostall all ::1/128 password thank you very much for help, with this change I can resolved a problem 1- after any thing we must change in the file "/var/lib/postgresql/ data/pg_hba.conf" "ident sameuser" is remplaced by "trust". # "local" is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: hostall all ::1/128 trust 2- after start postgres and create a password su - postgres psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'new password'" exit 3 - in the file " pg_hba.conf " remplace "trust" by "password". --~--~-~--~~~---~--~~ 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: Selecting what properties of an object will be loaded
for relations, u can put deferred(name) and noload(name) in
quety.options( ...).
no idea for plain columns
On Thursday 16 October 2008 12:56:19 Alex K wrote:
> Hi All,
>
> I wonder if there is a way to set what columns of the object will
> be used during this particular query, to reduce the query in case
> if I need the object, but I don't need all object properties.
>
> is something like this: session.query(User).load('column_a')
> possible?
>
> session.query([...]) - won't apply, since i need mapped object.
>
> Thanks,
> Alex
>
--~--~-~--~~~---~--~~
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] Selecting what properties of an object will be loaded
Hi All,
I wonder if there is a way to set what columns of the object will be
used during this particular query, to reduce the query in case if I
need the object, but I don't need all object properties.
is something like this: session.query(User).load('column_a') possible?
session.query([...]) - won't apply, since i need mapped object.
Thanks,
Alex
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
