Re: [Modeling-users] Aggregations with fetchSQL

[Sebastien B. <sbi...@us...>]

        Hi,

<luk...@po...> wrote:
[...]
SB>   Your voice has been heard. However, this won't happen in the coming
SB>   days, for sure. Would a integrated fetchSQL() make you happy in the
SB>   meantime?
[...]
>=20
> Yes, but I've got the latest version (I think so) 0.9-pre-15 and there is=
n't
> anything like "fetchSQL". Is this "meantime", you've mentioned is present=
 or
> a rather near or far future?
> Is there a possibility to run now any query (with aggregations, group by's
> etc.) in Modeling framework? If is, could you suggest how to do it? The
> nicest way would be a simple piece of Python code, but some links to manu=
als
> or another sources of information are also good.

  Sorry for not having been any clearer: yes, I meant that this could be
  added in the next release. The next release is planned at the end of
  the week, probably. Now that I'm back and convalescing I guess it's
  time to make things happen again ;)

Back on the subject, yes, there is a possibility to run any query you
want w/ the current release, and previous ones as well. The way it can
be done is exposed in tests' test_EditingContext_Global.py, method
test_999_customSQLQuery().  However, this is unfinished --I've realized
that something more needs to be done. I'm including at the end of the
mail method fetchSQL() which demonstrates how this can be done, and that
you can try as-is with the following examples.

  Example of use (using test-model AuthorBooks):

>>> from Modeling.EditingContext import EditingContext
>>> from Modeling import ModelSet
>>> import AuthorBooks, pprint
>>> ec=3DEditingContext()
>>> model=3DModelSet.defaultModelSet().modelNamed('AuthorBooks')

>>> sql=3D"select avg(age), sum(age) from WRITER"
>>> attrs=3D["average_age", "sum_age"]
>>> pprint.pprint(fetchSQL(ec, model, sql, attrs))
[{'average_age': 204.66666666666666, 'sum_age': 614L}]

>>> sql=3D"select avg(age), sum(age), last_name from WRITER GROUP BY(last_n=
ame)"
>>> attrs=3D["average(age)", "sum(age)", "Last name"]
>>> pprint.pprint(fetchSQL(ec, model, sql, attrs))
[{'Last name': 'Cleese', 'average(age)': 24.0, 'sum(age)': 24L},
 {'Last name': 'Dard', 'average(age)': 82.0, 'sum(age)': 82L},
 {'Last name': 'Rabelais', 'average(age)': 508.0, 'sum(age)': 508L}]


Why do we need a model? To be sure that we get through the right
DBContext down to the database.

  Last comment: when aggregating, sometimes you just get aggregates,
  sometimes you also get informations on real objects that you might be
  interested in retrieving as such (I mean, as real objects). Here is an
  example: let's fetch those writers whose books have an average price
  less the 4.5:

    [ NB: I changed the default test db with:
            update book set price=3D2.3 where id=3D1;
            update book set price=3D3.3 where id=3D2;
            update book set price=3D8.7 where id=3D3;
            update book set price=3D1.1 where id=3D4;    ]

>>> sql=3D"SELECT t0.id, t0.last_name, AVG(t1.price) FROM WRITER t0 INNER J=
OIN BOOK t1 ON t0.id=3Dt1.fk_writer_id GROUP BY t0.id, t0.last_name HAVING =
AVG(t1.price)<4.5"
>>> attrs=3D["id", "Author's last name", "Average price"]
>>> result=3DfetchSQL(ec, model, sql, attrs)
>>> pprint.pprint(result)
[{"Author's last name": 'Rabelais',
  'Average price': 2.2999999999999998,
  'id': 2},
 {"Author's last name": 'Dard', 'Average price': 4.3666666666666671, 'id': =
3}]

--> Now, if you need the real objects --the corresponding writers--,
    simply get them from the raw row (this topic is also discussed here,
    from a different point of view however:
    http://modeling.sf.net/UserGuide/ec-fetch-raw-rows.html)

>>> authors=3D[ec.faultForRawRow(r, "Writer") for r in result]
>>> pprint.pprint([{a.getLastName():a} for a in authors])
[{'Rabelais': <Writer.Writer instance at 0x84f3114>},
 {'Dard': <Writer.Writer instance at 0x8545a6c>}]

  In fact, you can turn any dictionary into real objects using
  ec.faultForRawRow() at the only condition that the PKs for the entity
  is included in the dictionary --that's why WRITER.id was included in
  the result set here.

I hope this could fit your requirements. I'll probably add such a method
to EditingContext for the next release, or at least add something liek
this in the documentation. I'll also be pleased to hear from your own
application, at least of the aggregations you're thinking about when
asking this (if you can disclose): it always help to learn about the
context in which feature requests raises.

  And to all: if you have any specific requirements, comments etc. about
  this, it's probably the time to speak ;)

-- S=E9bastien.



------------------------------------------------------------------------
def fetchSQL(ec, model, sql, attributeNames):
  """A method demonstrating how to fetch using any sql statement within
  the MDL framework.

  Parameters:

    model -- the model related to the db one wants to fetch data from

    sql -- a valid sql expression

    attributeNames -- the name to give to each column in the result set,
      supplied in the order in which they are fetched

  Returns: a sequence of dictionaries, mapping attributes'names to the
           fetched values
  """
  class FakeAttr:
    def __init__(self, name):
      self._name=3Dname
    def name(self):
      return self._name
=20=20
  attrs=3D[FakeAttr(n) for n in attributeNames]
  from Modeling import DatabaseContext
  dbContext=3DDatabaseContext.registeredDatabaseContextForModel(model, ec)
  adaptorChannel=3DdbContext.availableChannel().adaptorChannel()
  sqlExpr=3DadaptorChannel.adaptorContext().adaptor().expressionClass()()
  sqlExpr.setStatement(sql)
  adaptorChannel.setAttributesToFetch(attrs)
  adaptorChannel.adaptorContext().beginTransaction()
  res=3D[]
  try:
    adaptorChannel.evaluateExpression(sqlExpr)
    row=3DadaptorChannel.fetchRow()
    while row:
      res.append(row)
      row=3DadaptorChannel.fetchRow()
=20=20=20=20=20=20
    adaptorChannel.cancelFetch()
  finally:
    adaptorChannel.adaptorContext().rollbackTransaction()
  return res
------------------------------------------------------------------------