Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!goblin2!goblin.stu.neva.ru!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Date: Sun, 31 Aug 2014 19:19:28 +0100
From: MRAB <python@mrabarnett.plus.com>
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: python-list@python.org
Subject: Re: More questions on Python strings
References: <e008fcef-6d95-4123-a2e1-eeac5fd54ba3@googlegroups.com>
In-Reply-To: <e008fcef-6d95-4123-a2e1-eeac5fd54ba3@googlegroups.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.13662.1409509178.18130.python-list@python.org>
Lines: 98
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:77353

On 2014-08-31 18:37, Dennis E. Evans wrote:
>
>    Hi
>
> I have a function that reads some meta data from a database and builds a default order by and where clause for a table.
>
> some details,
>
>    rows is a list of pyOdbc.Row and will look like this
>    [1, 'ColumnName', 3, 5]
> there will be one to n elements
>
> EmptyString, defaultColumn, defaultColumnParaMarker,
> KeyLabelPos and a couple others are all constants stings or intergers defined in another module.
>
>
> this is the function :
>
>    def ReadPkColumns(self) :
>
>      # fetch all the rows, this is the second result set in the call
>      # this will be a very small result set, typically a PK will have 1,2 or 3 columns
>      # on rare occasions a PK can have 4 or more
>      rows = self.cursor.fetchall()
>
>      # if there are no rows just assign an empty string
>      # this is probably an error in the db
>      if (len(rows) <= 0) :
>        self.PkOrderBy = EmptyString
>        self.PkWhereClause = EmptyString
>      elif (len(rows) == 1) :
>      # if there is one column then assign it no loops
>        self.PkOrderBy = defaultColumn.format(Ali=self.alias, ColLabel = rows[0][KeyLabelPos])
>        self.PkWhereClause = defaultColumnParaMarker.format(Ali=self.alias, ColLabel = rows[0][KeyLabelPos])
>      else :
>        # two or more columns build a list of strings formatted
>        # as needed for the order by and where clause
>
>        # create an empty list
>        cols = []
>        # add the formatted columns to the list
>        for oneRow in rows :
>          cols.append(defaultColumn.format(Ali=self.alias, ColLabel = oneRow[KeyLabelPos]))
>        # and build the order by clause form the formatted strings
>        self.PkOrderBy = CommaSpace.join(cols)
>
>        # clear the cols list and fill again for use with the where clause
>        cols.clear()
>        for oneRow in rows :
>          cols.append(defaultColumnParaMarker.format(Ali=self.alias, ColLabel = oneRow[KeyLabelPos]))
>        self.PkWhereClause = andConjunction.join(cols)
>
>      return
> # ------------------------------------------------------------------------------
>
>
> my question is about this block
>        # create an empty list
>        cols = []
>        # add the formatted columns to the list
>        for oneRow in rows :
>          cols.append(defaultColumn.format(Ali=self.alias, ColLabel = oneRow[KeyLabelPos]))
>        # and build the order by clause form the formatted strings
>        self.PkOrderBy = CommaSpace.join(cols)
>
>        # clear the cols list and fill again for use with the where clause
>        cols.clear()
>
>    I create an empty list and then fill it with some formatted strings and then assign to the order by instance member, clear the list and repeat for the where clause member.
>
>    I tried a different things using the join statement and the rows instance to avoid the need to create the list of string (cols) but that resulted in various syntax and run time errors.
>
>    Is the a way to build the strings with out using the intermediate list?
>
>    the end result needs to look like this,
>
>    self.OrderBy = "tableAlias.ColumnOne, tableAlias.ColumnTwo, ..."
>
>    self.WhereClause = "(tableAlias.ColumnOne = ?) and (tableAlias.ColumnTwo = ?) and ..."
>
You could use a generator comprehension:

        self.PkOrderBy = 
CommaSpace.join(defaultColumn.format(Ali=self.alias, 
ColLabel=oneRow[KeyLabelPos]) for oneRow in rows)

Does that make the code clearer? I don't think so. Or faster? Not
enough to be noticeable.

Incidentally, there's no need to treat the single-row case specially:

 >>> ' and '.join(['first', 'second', 'third'])
'first and second and third'
 >>> ' and '.join(['first', 'second'])
'first and second'
 >>> ' and '.join(['first'])
'first'