heres two examples from the current unit tests: # tables:
users = Table('users', metadata, Column('user_id', Integer, Sequence('user_id_seq', optional=True), primary_key = True), Column('user_name', String(40)), ) addresses = Table('email_addresses', metadata, Column('address_id', Integer, Sequence('address_id_seq', optional=True), primary_key = True), Column('user_id', Integer, ForeignKey(users.c.user_id)), Column('email_address', String(40)), ) # classes: class User(object):pass class Address(object):pass # mappers: mapper(User, users, properties={ 'addresses':relation(Address, lazy=True) }) mapper(Address, addresses) sess = create_session() # build a query with Query, which will select "users" plus "count (addresses.address_id) AS count" q = sess.query(User) q = q.group_by([c for c in users.c]).order_by (User.c.user_id).outerjoin('addresses').add_column(func.count (addresses.c.address_id).label('count')) l = q.list() # build a query with a Select statement which will select "users" plus "count(addresses.address_id) AS count" s = select([users, func.count(addresses.c.address_id).label ('count')], from_obj=[users.outerjoin(addresses)], group_by=[c for c in users.c], order_by=[users.c.user_id]) q = sess.query(User) l = q.instances(s.execute(), "count") in both cases, the results look like: [(user7, 1), (user8, 3), (user9, 0)] where the first member of the tuple is a User object, the second member is the value of "count(addresses.address_id)". with the Query, add_column() and add_entity() can be used repeatedly. with instances, you just add any number of column names, Column instances, or mappers to the positional arguments of instances(). On Apr 2, 2007, at 12:36 PM, Glauco wrote: > I'm working over latest version and i appreciate a lot all news... > There is a lot of pretty think i'm implementing in our lib.. > > now some question: > > > ---------------------------------------------------------------------- > ----------------------------------------------------------------- > ---------------------------------------------------------------------- > ----------------------------------------------------------------- > - strings and columns can also be sent to the *args of instances() > where those exact result columns will be part of the result tuples. > What does it means? i've tryed in many way...but i cannot find any > way for do it. > > ---------------------------------------------------------------------- > ----------------------------------------------------------------- > ---------------------------------------------------------------------- > ----------------------------------------------------------------- > > Only way i found for retrieve some column from generative methods > is to use the select constructor at the end of my generated qry. > > ret = ret.select(select(columns)) > > but in this manner i found some problem, for example in a simple > table ( codice, descrizione) with a primary key > > > In [18]: t = TipoFigura() > > In [19]: t.c._data > Out[19]: > {'codice': Column('codice',PGChar > (length=1),primary_key=True,nullable=False), > 'descrizione': Column('descrizione',PGText > (length=None),nullable=False)} > > In [20]: t.select( select([t.c.codice]) ) > 2007-04-02 17:34:38,310 sqlalchemy.engine.base.Engine.0x..54 INFO > SELECT tipo_figura.codice AS tipo_figura_codice > FROM tipo_figura > 2007-04-02 17:34:38,311 sqlalchemy.engine.base.Engine.0x..54 INFO {} > > In [21]: t.select( select([t.c.descrizione]) ) > 2007-04-02 17:35:06,311 sqlalchemy.engine.base.Engine.0x..54 INFO > SELECT tipo_figura.descrizione AS tipo_figura_descrizione > FROM tipo_figura > 2007-04-02 17:35:06,312 sqlalchemy.engine.base.Engine.0x..54 INFO {} > ---------------------------------------------------------------------- > ----- > sqlalchemy.exceptions.NoSuchColumnError > Traceback (most recent call last) > /home/xxx/<ipython console> > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/ext/assignmapper.py > in do(self, *args, **kwargs) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/query.py in > select(self, arg, **kwargs) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/query.py in > select_statement(self, statement, **params) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/query.py in > _select_statement(self, statement, params, **kwargs) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/query.py in > execute(self, clauseelement, params, *args, **kwargs) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/query.py in > instances(self, cursor, *mappers_or_columns, **kwargs) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py in > _instance(self, context, row, result, skip_polymorphic) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py in > identity_key_from_row(self, row) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/engine/base.py in > __getitem__(self, key) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/engine/base.py in > _get_col(self, row, key) > /home/xxx/build/bdist.linux-i686/egg/sqlalchemy/engine/base.py in > _convert_key(self, key) > > NoSuchColumnError: "Could not locate column in row for column > 'tipo_figura.codice'" > > > > see that this query is exactly what i expect: > SELECT tipo_figura.descrizione AS tipo_figura_descrizione FROM > tipo_figura > > ---------------------------------------------------------------------- > ----------------------------------------------------------------- > ---------------------------------------------------------------------- > ----------------------------------------------------------------- > > What i try to do with new features is to unify all search fuction > in all mapper to a complete and exaustive function, for example: > > > > def search_m( self, select_clause=None, limit=None, > offset=None, order_by=[], use_labels=False, columns=[], **kw ): > """ > This is the simplest search function, over one table and > only 2 search column. > """ > # WHERE-CLAUSE > by_where_clause = {} # For generative methods > where_clause = [] # classical where Clause > for k,v in kw.items(): > > if k in ('codice',\ > ): > by_where_clause[ k ] = v > > elif k == 'descrizione': > where_clause.append( self.c.descrizione.op('ilike') > ('%'+v+'%') ) > > #else: > # raise ValueError, "XXX %s:%s"%(k, v) > > ret = session.query( TipoFigura ) > if by_where_clause: > ret = ret.filter_by( **by_where_clause ) > if where_clause: > ret = ret.filter( and_( *where_clause ) ) > if limit: > ret = ret.limit( limit ) > if offset: > ret = ret.offset( offset ) > if order_by: > ret = ret.order_by( order_by ) > if columns: > ret = ret.select(select(columns)) > return ret > > > i've done all for returnig a "<class > 'sqlalchemy.orm.query.Query'>" but with the last .select(select > (columns)) it return a list and this is different. > how can i use this features and returning the same orm.query obj? > > > > > thank all > > 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 -~----------~----~----~----~------~----~------~--~---