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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
