[sqlalchemy] Sharding and COUNT
Hi! I'm struggling to understand why the shard_chooser gets called for this query: SELECT count(users.uid) AS count_1 FROM users WHERE users.external_id = %s I would have thought this query_chooser should be called instead? shard_chooser gets set a mapper, a clause but the instance is None. Thanks for any help, Geoff --~--~-~--~~~---~--~~ 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: Sharding and COUNT
I've figured out why it doesn't work. The scalar method on Session doesn't deal with sharding. On Apr 11, 11:55 am, Geoff [EMAIL PROTECTED] wrote: Hi! I'm struggling to understand why the shard_chooser gets called for this query: SELECT count(users.uid) AS count_1 FROM users WHERE users.external_id = %s I would have thought this query_chooser should be called instead? shard_chooser gets set a mapper, a clause but the instance is None. Thanks for any help, Geoff --~--~-~--~~~---~--~~ 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] Using substring function
Hi,
I need to use substring function into sql statement. For it, I define
the next sql:
_sql = select([func.substring(planilladet.c.msg_debug, 1,
40).label('msg_debug'), func.sum(planilladet.c.cant).label('cant'),
func.sum(planilladet.c.cant * planilladet.c.importe).label('importe')],
\
from_obj=[planilladet.join(planilla)],
group_by=[1])
When I execute that sql, this error message appear:
(ProgrammingError) (-104, 'isc_dsql_prepare: \n Dynamic SQL Error\n
SQL error code = -104\n Token unknown - line 1, column 39\n ,')
'SELECT substring(planilladet.msg_debug, ?, ?) AS msg_debug,
sum(planilladet.cant) AS cant, sum(planilladet.cant *
planilladet.importe) AS importe \nFROM planilladet JOIN planilla ON
planilla.plan_id = planilladet.plan_id \nWHERE planilla.conv_id = ?
AND planilla.fec_imput = ? GROUP BY 1' [1, 40, 1, datetime.date(2008,
2, 1)]
What can be the problem here?
Thanks in advance.
--~--~-~--~~~---~--~~
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: Using substring function
(ProgrammingError) (-104, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -104\n Token unknown - line 1, column 39\n ,') 'SELECT substring(planilladet.msg_debug, ?, ?) AS msg_debug, sum(planilladet.cant) AS cant, sum(planilladet.cant * planilladet.importe) AS importe \nFROM planilladet JOIN planilla ON planilla.plan_id = planilladet.plan_id \nWHERE planilla.conv_id = ? AND planilla.fec_imput = ? GROUP BY 1' [1, 40, 1, datetime.date(2008, 2, 1)] What can be the problem here? Thanks in advance. Oopss, sorry, I respond to myself. The problem is with substring function, in SQL (I'm using Firebird 2.x) substring funcition sintax is: SUBSTRING(planilladet.msg_debug 1 FROM 40) How can do it in sqlalchemy ? (I use sqlalchemy 0.3.11) --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
Also what would be a syntax to make a same selection using
session.query(th)?
what would you like the ORM query to return ? Object instances ?
currently, using group_by() and such with Query implies you're getting
individual column tuples back using _values(), in which case its
sess.query(THsClass).group_by(whatever)._values(col expressions).
I would like to get similar result like I would via query.all, because
it allows me to look through it multiple times.
b=session.query(th).filter(th.RECORD_NO==5).all()
I will iterate through b multiple times, and getting it via select I
would have to get it from the database each time I loop. (correct?)
So I tried instead of doing:
s=sqlalchemy.select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO)],sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute()
do this:
a=session.query(th).add_column(sqlalchemy.func.count(th.c.RECORD_NO).label('MYGREAT_COUNT')).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).group_by(th.RECORD_NO).all()
or
a=session.query(th).group_by(th.RECORD_NO).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all()
but I get an error.
Am I using incorect syntax here?
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/orm/query.py, line 853, in all
return list(self)
File sqlalchemy/orm/query.py, line 914, in __iter__
return self._execute_and_instances(context)
File sqlalchemy/orm/query.py, line 917, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self.mapper,
instance=self._refresh_instance)
File sqlalchemy/orm/session.py, line 605, in execute
return self.__connection(engine,
close_with_result=True).execute(clause, params or {})
File sqlalchemy/engine/base.py, line 844, in execute
return Connection.executors[c](self, object, multiparams, params)
File sqlalchemy/engine/base.py, line 895, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File sqlalchemy/engine/base.py, line 907, in _execute_compiled
self.__execute_raw(context)
File sqlalchemy/engine/base.py, line 916, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File sqlalchemy/engine/base.py, line 958, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File sqlalchemy/databases/mssql.py, line 805, in do_execute
super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement,
parameters, context=context, **kwargs)
File sqlalchemy/databases/mssql.py, line 499, in do_execute
cursor.execute(SET IDENTITY_INSERT %s OFF %
self.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception
Lucas
--~--~-~--~~~---~--~~
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: Mapper issue with r4485
On Apr 10, 2008, at 8:46 PM, Steve Zatz wrote:
I am not sure when the change was introduced but the following mapper,
which previously worked:
mapper(Section, section_table, properties = {'items': relation(Item,
backref='section'), 'keywords':relation(Keyword,
primaryjoin
=and_(keyword_table.c.uuid==itemkeyword_table.c.keyword_uuid,
item_table.c.uuid==itemkeyword_table.c.item_uuid,
section_table.c.id==item_table.c.section_id), viewonly=True,
foreign_keys=[keyword_table.c.uuid],
remote_side=[item_table.c.section_id])})
now produces the following error:
sqlalchemy.exceptions.ArgumentError: Could not determine relation
direction for primaryjoin condition 'keyword.uuid =
item_keyword.keyword_uuid AND item.uuid = item_keyword.item_uuid AND
section.id = item.section_id', on relation Section.keywords (Keyword).
Specify the foreign_keys argument to indicate which columns on the
relation are foreign.
I am not sure what needs modification in the mapper to be compatible
with the current trunk.
I've added this example as a unit test and will have to relax the
viewonly rules a bit more to handle this - the primaryjoin along
many tables use case which is not that unusual. I'll get back to you
shortly.
--~--~-~--~~~---~--~~
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] Access to the attributes of a session object (newbie)
Hello All,
I have a session object, for instance:
my_object = session.query(Person).filter_by(name='MYSELF').first()
I know I can access to its attributes to modify the database:
my_object.name = 'YOURSELF'
my_object.town = 'PARIS'
Is there a way to access its attributes on another way ?
The point is that I have a dictionary that records all values of
several textcontrols (with wxPython):
my_dict = {'name':'YOURSELF', 'town':'PARIS'} etc
I would like to link (with a loop) the keys of this dictionary with
the attributes of the session object, without having to
write again all the attributes (my_object.name = my_dict['name'] etc )
since there are a lot.
I tried to loop like this without success:
for (key,value) in my_dict.iteritems():
my_object.key = value # my_object[key] = value doesn't
work either
Thanks in advance for any hints and sorry for the low level of my
question !
Dominique
--~--~-~--~~~---~--~~
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: Mapper issue with r4485
On Apr 10, 2008, at 8:46 PM, Steve Zatz wrote:
I am not sure when the change was introduced but the following mapper,
which previously worked:
mapper(Section, section_table, properties = {'items': relation(Item,
backref='section'), 'keywords':relation(Keyword,
primaryjoin
=and_(keyword_table.c.uuid==itemkeyword_table.c.keyword_uuid,
item_table.c.uuid==itemkeyword_table.c.item_uuid,
section_table.c.id==item_table.c.section_id), viewonly=True,
foreign_keys=[keyword_table.c.uuid],
remote_side=[item_table.c.section_id])})
now produces the following error:
sqlalchemy.exceptions.ArgumentError: Could not determine relation
direction for primaryjoin condition 'keyword.uuid =
item_keyword.keyword_uuid AND item.uuid = item_keyword.item_uuid AND
section.id = item.section_id', on relation Section.keywords (Keyword).
Specify the foreign_keys argument to indicate which columns on the
relation are foreign.
I am not sure what needs modification in the mapper to be compatible
with the current trunk.
my hat's off to you for coming up with that relation(), it works again
in rev 4486.
--~--~-~--~~~---~--~~
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: group_by, count, and name count field as ?
On Apr 11, 2008, at 10:41 AM, Lukasz Szybalski wrote:
So I tried instead of doing:
s
=
sqlalchemy
.select
([th
.RECORD_NO
,sqlalchemy
.func
.count
(th
.RECORD_NO
)],sqlalchemy
.and_
(th
.APPLIED_TEST
==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute()
do this:
a
=
session
.query
(th
).add_column
(sqlalchemy
.func
.count
(th
.c
.RECORD_NO
).label
('MYGREAT_COUNT
')).filter
(sqlalchemy
.and_
(th
.APPLIED_TEST
==1,th.CODING_DATE=='20080325')).group_by(th.RECORD_NO).all()
or
a
=
session
.query
(th
).group_by
(th
.RECORD_NO
).filter
(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all()
OK, you want a combination of entities and columns. The SQL being
issued above is still not what you want. From a SQL perspective,
saying session.query(MyClass) is equivalent to saying:
SELECT * from table
I.e., *all* columns from th are being added to the columns clause of
the select. According to the SQL standard, these names all need to
be added to the GROUP BY as well - if MS-SQL is allowing only a
partial GROUP BY list, thats just poor behavior on the part of MS-SQL
(MySQL has this bug as well). What this query really looks like is:
SELECT *, count(somecol) FROM table GROUP BY *
where the * includes the primary key column of the table, and
therefore it groups by the full set of columns on the table which
renders the count() meaningless.
So, the exact analogy to the select statement you're issuing is:
sess
.query
(th
).filter
(and_
(th
.APPLIED_TEST
=
=
1
,th
.CODING_DATE
=='20080325')).group_by(th.RECORD_NO)._values(th.RECORD_NO,
func.count(th.RECORD_NO))
where _values() will return an iterator of exactly the columns you
want.
in the next release of SA, you'll also be able to say
sess.query(th.RECORD_NO,
func.count(th.RECORD_NO)).filter(...).group_by(...).all() to get a
similar result (and _values() will be named values()).
File sqlalchemy/databases/mssql.py, line 499, in do_execute
cursor.execute(SET IDENTITY_INSERT %s OFF %
self
.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception
This seems to be some weird error either with pyodbc or with the MS-
SQL dialect, MS-SQL people we've seen this before, correct ?
--~--~-~--~~~---~--~~
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: Access to the attributes of a session object (newbie)
On Apr 11, 2008, at 11:45 AM, [EMAIL PROTECTED] wrote:
Hello All,
I have a session object, for instance:
my_object = session.query(Person).filter_by(name='MYSELF').first()
I know I can access to its attributes to modify the database:
my_object.name = 'YOURSELF'
my_object.town = 'PARIS'
Is there a way to access its attributes on another way ?
The point is that I have a dictionary that records all values of
several textcontrols (with wxPython):
my_dict = {'name':'YOURSELF', 'town':'PARIS'} etc
I would like to link (with a loop) the keys of this dictionary with
the attributes of the session object, without having to
write again all the attributes (my_object.name = my_dict['name'] etc )
since there are a lot.
I tried to loop like this without success:
for (key,value) in my_dict.iteritems():
my_object.key = value # my_object[key] = value doesn't
work either
Thanks in advance for any hints and sorry for the low level of my
question !
Python provides the setattr() function to assign object attributes
by string expression:
for k, v in dict.iteritems():
setattr(obj, k, v)
There is also the __dict__ attribute present on objects which can also
be written to in the general case, but in SQLAlchemy we need you to
use the attribute's public interface, i.e. setattr(), in order for
SQLAlchemy's change events to take place.
--~--~-~--~~~---~--~~
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: Access to the attributes of a session object (newbie)
Magic ! It works perfectly. Thanks a lot Michael for your help and all your work. Dominique --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
File sqlalchemy/databases/mssql.py, line 499, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self .identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception This seems to be some weird error either with pyodbc or with the MS- SQL dialect, MS-SQL people we've seen this before, correct ? That statement should only every be issued on an insert to a table with an auto-generated PK that contains an explicit value for the PK, not on any select() or query(). Even then it should be fine, unless pyodbc is having trouble with SQL statement sequence. A small testcase would be needed to track this one down. --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
I.e., *all* columns from th are being added to the columns clause of the select. According to the SQL standard, these names all need to be added to the GROUP BY as well - if MS-SQL is allowing only a partial GROUP BY list, thats just poor behavior on the part of MS-SQL (MySQL has this bug as well). What this query really looks like is: SELECT *, count(somecol) FROM table GROUP BY * where the * includes the primary key column of the table, and therefore it groups by the full set of columns on the table which renders the count() meaningless. so a=session.query(th).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).group_by(th.RECORD_NO)._values(th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO)).all() is just a slightly more complicated way of saying: s=sqlalchemy.select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO)],sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404')).group_by(th.RECORD_NO).execute().fetchall() If I had to pick I would just go with select. (unless there is other reason to use query) Is there a way to get this ? SELECT RECORD_NO, count(RECORD_NO) FROM table GROUP BY RECORD_NO (primary key is RECORD_ID) The only way I know how to do it is via sqlalchemy.select but I would like the query type of the return where my results are type(a) type 'list' type(a[0]) class '__main__.th' How would I do that? Lucas --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
On Apr 11, 2008, at 12:51 PM, Lukasz Szybalski wrote:
Is there a way to get this ?
SELECT RECORD_NO, count(RECORD_NO) FROM table GROUP BY RECORD_NO
(primary key is RECORD_ID)
The only way I know how to do it is via sqlalchemy.select
but I would like the query type of the return where my results are
type(a) type 'list'
type(a[0])
class '__main__.th'
If I understand correctly, you want the instances that correspond to
those record_no records, and the count. In this case:
s = select([th.RECORD_NO,
func.count(th.RECORD_NO).label('count')]).group_by(th.RECORD_NO)
sess.query(th).select_from(th_table.join(s,
th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all()
for some insight into this approach, see
http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx
.
also again, the next release will have a more concise method of
building that join 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] SQLAlchemy hangs with Pydev 1.3.14, works with 1.3.13
This is probably not a SQLAlchemy issue, but thought I should share with the group in case there were other Pydev+Eclipse users out there. I recently came across an issue that when debugging code using Pydev, the code would hang whenever I tried to step over a statement of SQLAlchemy code. I found out that this only occurs in the newest version of Pydev (1.3.14), and that reverting to the previous version fixed my problem. I submitted this as a Pydev bug on their sourceforge bug tracker: http://sourceforge.net/tracker/index.php?func=detailaid=1938768group_id=85796atid=577329 --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
ok, I don't think we are on a same page. So let me explain what I
want and maybe unconfused myself on what is a proper way to get data
out of database via sqlalchemy orm style.
Query class should not be confused with the Select class, which
defines database SELECT operations at the SQL (non-ORM) level. Query
differs from Select in that it returns ORM-mapped objects and
interacts with an ORM session, whereas the Select construct interacts
directly with the database to return iterable result sets
I need to return ORM-mapped object.
This is non-ORM object:
s=sqlalchemy.select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO).
label('RECORD_NO_COUNT')],sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404')).
group_by(th.RECORD_NO)
s.execute().fetchall()
Above statements should result in query like: 'select a,count(a) from
x where b=5,c=6 group by a'
In order to get ORM object I need to use query. (That is what I get
from reading the sentence I quoted)
By not knowing how sqlalchemy works and just using my intuition I
would assume that I should be able to do something like this:
sess.query(th).select([th.RECORD_NO,sqlalchemy.func.count(th.RECORD_NO).label('RECORD_NO_COUNT')],
sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080404'))
.group_by(th.RECORD_NO).all()
but obviously the above does not work. I guess what I am confused is
how to use non-ORM syntax (its easy to follow) in an ORM way?
What you emailed below is making a join if I read it correctly, and I
don't think that is what I want.
sess.query(th).select_from(th_table.join(s,
th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all()
Lucas
--~--~-~--~~~---~--~~
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: group_by, count, and name count field as ?
On Apr 11, 2008, at 2:51 PM, Lukasz Szybalski wrote: Above statements should result in query like: 'select a,count(a) from x where b=5,c=6 group by a' In order to get ORM object I need to use query. (That is what I get from reading the sentence I quoted) From the above, what ORM object would you like ? the one which has a as one of its columns ? if so, the recipe I gave you is the correct approach. What you emailed below is making a join if I read it correctly, and I don't think that is what I want. sess.query(th).select_from(th_table.join(s, th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all() if you want to select a full row from the table, including its primary key, and you'd like that row to correspond to a particular aggregate query which needs to GROUP BY only part of the columns which does not include the primary key column, you must use a join or subquery to create the aggregate. To illustrate, this is the query you think you want: select *, count(a) from table group by a Why * ? because you say that you want ORM objects. The ORM object represents the *full* row, including the primary key, of the table. but again, that query is invalid SQL.Most SQL-compliant databases will raise an error. Another article explaining this: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx . Therefore, the join or subquery is necessary if you wish to GROUP BY only particular columns. --~--~-~--~~~---~--~~ 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: Mapper issue with r4485
my hat's off to you for coming up with that relation(), it works again in rev 4486. Ah the irony ... check out http://tinyurl.com/6kqv94 And thanks as always for your remarkable responsiveness and for sqlalchemy. It is indispensible. Steve --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
Above statements should result in query like: 'select a,count(a) from x where b=5,c=6 group by a' In order to get ORM object I need to use query. (That is what I get from reading the sentence I quoted) From the above, what ORM object would you like ? the one which has a as one of its columns ? if so, the recipe I gave you is the correct approach. What you emailed below is making a join if I read it correctly, and I don't think that is what I want. sess.query(th).select_from(th_table.join(s, th.RECORD_NO==s.c.RECORD_NO)).add_column(s.c.count).all() if you want to select a full row from the table, including its primary key, and you'd like that row to correspond to a particular aggregate query which needs to GROUP BY only part of the columns which does not include the primary key column, you must use a join or subquery to create the aggregate. To illustrate, this is the query you think you want: select *, count(a) from table group by a Why * ? because you say that you want ORM objects. The ORM object represents the *full* row, including the primary key, of the table. It makes sense now. I assumed that ORM object will only return 1 column I group by, and a count. When it returns the full row then my group_by makes no sense. Thanks Lucas ps. The links were really helpful ! but again, that query is invalid SQL.Most SQL-compliant databases will raise an error. Another article explaining this: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx . Therefore, the join or subquery is necessary if you wish to GROUP BY only particular columns. -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ Install Broadcom wireless card on Linux: http://lucasmanual.com/mywiki/bcm43xx --~--~-~--~~~---~--~~ 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: group_by, count, and name count field as ?
On Apr 11, 2008, at 4:40 PM, Lukasz Szybalski wrote: It makes sense now. I assumed that ORM object will only return 1 column I group by, and a count. When it returns the full row then my group_by makes no sense. Thanks Lucas ps. The links were really helpful ! greatglad that worked out ! --~--~-~--~~~---~--~~ 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: Using substring function
On Fri, 11 Apr 2008 07:00:42 -0700 (PDT) maxi [EMAIL PROTECTED] wrote: The problem is with substring function, in SQL (I'm using Firebird 2.x) substring funcition sintax is: SUBSTRING(planilladet.msg_debug 1 FROM 40) How can do it in sqlalchemy ? (I use sqlalchemy 0.3.11) It's been fairly easy to implement it for 0.4.x, see revision 4490: dunno about 0.3.x but it shouldn't be any harder. ciao, lele. -- nickname: Lele Gaifax| Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas| comincerò ad aver paura di chi mi copia. [EMAIL PROTECTED] | -- Fortunato Depero, 1929. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
