[sqlalchemy] Re: count function problem
Glauco wrote:
Michael Bayer ha scritto:
On Mar 14, 2007, at 12:49 PM, Glauco wrote:
This is perfect but when i try to use count function the SQL
composer try to do an expensive sql.
In [63]: print select([tbl['azienda'].c.id], tbl['azienda']).count()
*SELECT count(id) AS tbl_row_count
FROM (SELECT azienda.id AS id
FROM azienda)*
what makes you think that query is expensive ? anyway, more succinct
to just say table.count().
because i expect that engine do a SELECT COUNT FROM BLABLA and no a
SELECT COUNT FROM ( SELECT BLABLA)
i think this is expensive for my DataBase.
in this example i've used a simple table but this is a complex qry so
i cannot use tbl.count()
Another question:
Does anyone know how to use this object ?
select([tbl['azienda'].c.id], tbl['azienda']).count()
sqlalchemy.sql.Select object at 0xb6c803ac
select(...).count().execute()
or
engine.connect().execute(select(...).count())
I've done a lot of try this don't work
print select([tbl['azienda'].c.id], tbl['azienda']).execute()
*In [8]: print select([tbl['azienda'].c.id],
tbl['azienda']).count().execute()*
2007-03-15 09:51:29,870 sqlalchemy.engine.base.Engine.0x..d4 INFO
SELECT count(id) AS tbl_row_count
FROM (SELECT azienda.id AS id
FROM azienda)
2007-03-15 09:51:29,870 sqlalchemy.engine.base.Engine.0x..d4 INFO {}
2007-03-15 09:51:29,871 sqlalchemy.engine.base.Engine.0x..d4 INFO ROLLBACK
---
_SQLError: (ProgrammingError) ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.
_
SELECT count(id) AS tbl_row_count
FROM (SELECT azienda.id AS id
FROM azienda) 'SELECT count(id) AS tbl_row_count \nFROM (SELECT
azienda.id AS id \nFROM azienda)' {}
When i use count() function on a mapper - SelectResult , this return
exactly rowcount, but the same on a select seems to go in error.
for Marco Mariani:
Credo che sia
select([ sa.func.count(tbl['azienda'].c.id ])
almeno io, con la max(), faccio cosi'..
I cannot redefine select_clause because this is a feature for
returning paginate result and rowcount from the same funtion.
Example.
i intend
Azienda.my_generic_search( bla, bla, limit, offset
).execute().fetchall() -- give paginated result
Azienda.my_generic_search( bla, bla,
).execute().fetchone() -- give rowcount
For the second line, I suppose you want to say...
Azienda.my_generic_search( bla, bla).count().execute().fetchone() --
give rowcount
Thank you
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software® [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
++
--~--~-~--~~~---~--~~
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: count function problem
On Mar 15, 2007, at 5:09 AM, Glauco wrote: because i expect that engine do a SELECT COUNT FROM BLABLA and no a SELECT COUNT FROM ( SELECT BLABLA) i think this is expensive for my DataBase. its not. optimizers can usually figure things like that out. I've done a lot of try this don't work print select([tbl['azienda'].c.id], tbl['azienda']).execute() what happened to table.count() ? if you want to count manually: select([func.count(table.c.whatevercolumn)]).execute() --~--~-~--~~~---~--~~ 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: count function problem
On Mar 14, 2007, at 12:49 PM, Glauco wrote: This is perfect but when i try to use count function the SQL composer try to do an expensive sql. In [63]: print select([tbl['azienda'].c.id], tbl['azienda']).count() SELECT count(id) AS tbl_row_count FROM (SELECT azienda.id AS id FROM azienda) what makes you think that query is expensive ? anyway, more succinct to just say table.count(). Another question: Does anyone know how to use this object ? select([tbl['azienda'].c.id], tbl['azienda']).count() sqlalchemy.sql.Select object at 0xb6c803ac select(...).count().execute() or engine.connect().execute(select(...).count()) --~--~-~--~~~---~--~~ 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: count(*) function
It worked. Thanks! On Jan 15, 11:37 am, Marco Mariani [EMAIL PROTECTED] wrote: milenawrote: I have tried select([func.count(*)], from_obj=[table_name]).execute() but it didn't workI suppore you're not using mappers, so this is the fastest method: number_of_rows = table.count().execute().fetchone()[0] where table is the table object --~--~-~--~~~---~--~~ 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: count(*) function
milena wrote: I have tried select([func.count(*)], from_obj=[table_name]).execute() but it didn't work I suppore you're not using mappers, so this is the fastest method: number_of_rows = table.count().execute().fetchone()[0] where table is the table object --~--~-~--~~~---~--~~ 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: count(*) function
I have tried select([func.count(*)], from_obj=[table_name]).execute() but it didn't work I think you should try to specify a column in your count or leave it empty (didn't try). If you're using mapped objects, you can use the SelectResults extension: from sqlalchemy.ext.selectresults import SelectResults SelectResults(your_session.query(YourMappedClass)).count() Cheers, Seb -- Sébastien LELONG sebastien.lelong[at]sirloon.net --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
