Newbie Q: modifying SQL statements

[Mike Meyer]

On Thu, 10 Jan 2008 20:32:06 -0500 "Faber J. Fedor" <faber at linuxnj.com> wrote:

> Hi all,
> 
> I'm in the process of learning Python by writing a job queue program.
> Nothing fancy, mind you, just read from a table, shell out to a program,
> write back to the table.
> 
> I'm working off of the tutorial listed here (amongst many places):
> http://www.devx.com/dbzone/Article/22093
> 
> In my Jobs class I have:
> 
> def __iter__(self):
>    "creates a data set, and returns an iterator (self)"
>    q = "select * from %s" % (self.name)
>    self._query(q)
>    return self  # an Iterator is an object 
>    # with a next() method
> 
> def next(self):
>    "returns the next item in the data set, 
>       or tells Python to stop"
>    r = self.dbc.fetchone()
>    if not r:
>       raise StopIteration
>    return r
> 
> which works well, but what if I want to modify the __iter__ query?  I
> want to be able to do something like this (and I know this is not the
> right syntax but you'll get my drift):
> 
> 
> for job in jobs: print job # which the above code does
> for job in jobs("status = running"): print job
> for job in jobs("jobid = 4"): print job
> 
> What's the pythonic way of doing this?

Part of evaluating a for loop is that the expression in the for loop
is evaluated, and it's __iter__ method is called. In your first line,
the initial evaluation returns the jobs object, whose __iter__ method
is then called, giving you the effect you want.

For the second and third lines, the first evaluation tries to call the
jobs object with the query as an argument. This fails, unless your
jobs object is callable. If that returns the jobs object, __iter__
will be called - and thus use the query you want to avoid. So you need
to have a __call__ method that leaves a token for __iter__ so it uses
the proper query.

This will do that, but I'm not sure I'd call it pythonic:

_where = ""
def __iter__(self):
   if not self._working:
     q = "select * from %s" % (self.name + where,)
     self._query(q)
   return self

def __call__(self, where):
   self._where = " where %s" % (where,)
   return self

def next(self):
   ...
      self._where = self.__class__._where	# Put back class default
      raise StopIteration
   return r


Personally, I think it would be more pythonic to not try and use two
different APIs to walk the list of jobs (... One Way To Do it):

def __call__(self, where=None):
    q = "select * from %s" % (self.name,) + ("" if not where else (" where %s" % where))
    self._query(q)
    for r in self.dbc.iterresults()	# I assume it has something like this
       yield r


This should cause your first line to fail (possibly, depends on the
exact nature of the class); it needs to be "for job in jobs(): print job".

      <mike
-- 
Mike Meyer <mwm at mired.org>		http://www.mired.org/consulting.html
Independent Network/Unix/Perforce consultant, email for more information.