Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #77352 > unrolled thread

More questions on Python strings

Back to article view | Back to comp.lang.python

Contents

  More questions on Python strings "Dennis E. Evans" <dennisearlevans@gmail.com> - 2014-08-31 10:37 -0700
    Re: More questions on Python strings MRAB <python@mrabarnett.plus.com> - 2014-08-31 19:19 +0100
    Re: More questions on Python strings Peter Otten <__peter__@web.de> - 2014-09-01 01:03 +0200

#77352 — More questions on Python strings

  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 ..."

  thanks Dennis

[toc] | [next] | [standalone]

#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'

[toc] | [prev] | [next] | [standalone]

#77369

MRAB wrote:

> 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.

>>    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'

The same goes for the no-rows case:

>>> " and ".join([])
''

You might also introduce a helper function to reduce code redundancy:

    def ReadPkColumns(self) :
        rows = self.cursor.fetchall()

        def build_clause(sep, template):
            return sep.join(
                template.format(Ali=self.alias, ColLabel=row[KeyLabelPos])
                for row in rows)

        self.PkOrderBy = build_clause(CommaSpace, defaultColumn)
        self.PkWhereClause = build_clause(andConjunction,
                                         defaultColumnParaMarker)

General observations: don't microoptimize (always col = [] instead of 
col.clear()) and don't comment your code to death ;)
Also: CommaSpace instead of the literal ", " string? When you bind it to 
another value like

CommaSpace = ";\t"

you will also have to change the name to SemicolonTabulator anyway.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web