Sorting data from a table
Question: I'm working on a new version of a module and I need to create a new table for
that, but I'm facing a little issue that is driving me crazy.

Here is my relevant python code:

    
    
    import psycopg2, sys, psycopg2.extras, time
    
    order = 4419
    
    try:
        con = psycopg2.connect(host='localhost', database='DB01', user='odoo', password='******')
    cur = con.cursor()
    po_lines = 'SELECT pos_order_line.id FROM public.pos_order_line, public.product_template ' \
               'WHERE pos_order_line.product_id = product_template.id AND  pos_order_line.order_id = %s '\
               'AND (product_template.pos_categ_id != 5 AND product_template.pos_categ_id != 6)' \
               'ORDER BY pos_order_line.id ASC'
    po_lines2 = 'SELECT pos_order_line.id, pos_order_line.order_id, product_template.name, pos_order_line.qty, product_template.pos_categ_id ' \
                'FROM public.pos_order_line, public.product_template  ' \
                'WHERE pos_order_line.product_id = product_template.id AND  pos_order_line.id = %s ' \
                'ORDER BY pos_order_line.id ASC'
    
    cur.execute(po_lines,[order]); fetch_lines = cur.fetchall()
    dish = ''; instr = []; kot = 0; dp = 0
    print fetch_lines
    for line in fetch_lines:
        cur.execute(po_lines2, [line]); pos_lines = cur.fetchone()
        if pos_lines[2].startswith('#'):
            instr.insert(1, pos_lines[2][2:]); kot = 1
        elif pos_lines[2].startswith('----'):
            dp = 1
        else:
            dish = pos_lines[2]
            kot = 0; instr = []
            if dp == 1:
                instr.insert(0, '!SERVIR DEPOIS!'); dp = 0
        if dish != pos_lines[2]:
            print 'Ordem: ', order, ' - Prato:', dish, ' - Instr:', instr, 'qt: ', pos_lines[3],'kot: ', kot, 'dp status:', dp
    
    except psycopg2.DatabaseError, e:
    print 'Error %s' % e
    sys.exit(1)
    
    finally:
        if con:
            con.close()
    

Starting from a query I have:

    
    
    ID     ORDER PRODUCT                    QTY     CAT
    12811  4419  "Crudo GR"                 1.0       1
    12812  4419  "Salame e Grana GR"        1.0       1
    12813  4419  "---- servir depois ----"  1.0       7
    12814  4419  "Nutella Ban GR"           1.0       3
    12815  4419  "# Cortar em dois"         1.0       7
    

Resuming all product line (pos_lines[2]) not starting with '#' or with '----'
need to be placed on a variable 'instr' until the var 'dish' changes. All the
line are correctly read because if I put a print statement at the end of all
IF cycles I can see how the variables are filled:

> 1 Ordem: 4419 - Prato: Crudo GR - Instr: [] qt: 1.0 kot: 0 dp status: 0
>
> 2 Ordem: 4419 - Prato: Salame e Grana GR - Instr: [] qt: 1.0 kot: 0 dp
> status: 0
>
> 3 Ordem: 4419 - Prato: Salame e Grana GR - Instr: [] qt: 1.0 kot: 0 dp
> status: 1
>
> 4 Ordem: 4419 - Prato: Nutella Ban GR - Instr: ['!SERVIR DEPOIS!'] qt: 1.0
> kot: 0 dp status: 0
>
> 5 Ordem: 4419 - Prato: Nutella Ban GR - Instr: ['!SERVIR DEPOIS!', 'Cortar
> em dois'] qt: 1.0 kot: 0 dp status: 0

I've numerated the lines just to show how the problem is: lines 2 and 4 should
be hidden because are just intermediate steps. Then the results I need should
be:

    
    
    ID     ORDER PRODUCT              INSTR                              QTY     
    12811  4419  "Crudo GR"                                               1.0       
    12812  4419  "Salame e Grana GR"                                    1.0       
    12814  4419  "Nutella Ban GR"     "!SERVIR DEPOIS! Cortar em dois"          1.0       
    

Could someone gently tell me where is the error in my code and how to put the
correct print statement? Please note I'm relatively new on Python, have mercy.

Thanks.

**Edit: solved with a more simple approach based on the hints of Merlin.**

The code wrote by Merlin is complicated to setup for me due to the many
variant I have. I rewrote part of my script in a more basic way. In this
version I've reverted the lines of the fetch to grab and add to the respective
line all the instructions (#) I need after the product in a temp table. Then I
reversed again the lines to check if there is a line '----' before a product
and add to the respective product, last I wrote to the final table. This
script is much simple to read (for a newbie like me) and avoid the use of
'operator' module simply reversing a table with [::-1].

    
    
    TableTemp = []; newTable = []; Instr = ''
    
    for line in fetch_lines[::-1]:
        if line[2].startswith('#') or line[2].startswith('----'):
            if line[2].startswith('#'):
                Instr = line[2][2:]+' | '+ Instr
            if line[2].startswith('----'):
                TableTemp.append((line[0], line[1], line[2], '', line[3], line[4]))
        else:
            TableTemp.append((line[0],line[1],line[2], Instr, line[3], line[4]))
            Instr = ''
    
    for line in TableTemp[::-1]:
        if line[2].startswith('----'):
            Instr = '!SERVIR DEPOIS! | '
        else:
            newTable.append((line[0],line[1],line[2], Instr+line[3][:-3], line[4], line[5]))
            Instr = ''
    

Result:

Inner fetch:

    
    
    (13264, 4558, 'Funghi GR', Decimal('1.0'), 'Mesa 11')
    (13265, 4558, '# + Champinhons', Decimal('1.0'), 'Mesa 11')
    (13266, 4558, '# + Alface', Decimal('1.0'), 'Mesa 11')
    (13267, 4558, '# - R\xc3\xbacola', Decimal('1.0'), 'Mesa 11')
    (13268, 4558, 'Formaggi GR', Decimal('1.0'), 'Mesa 11')
    (13269, 4558, '# Cortar em dois', Decimal('1.0'), 'Mesa 11')
    (13270, 4558, '---- servir depois ----', Decimal('1.0'), 'Mesa 11')
    (13271, 4558, 'Nutella GR', Decimal('1.0'), 'Mesa 11')
    (13272, 4558, '# Cortar em dois', Decimal('1.0'), 'Mesa 11')
    (13273, 4558, '---- servir depois ----', Decimal('1.0'), 'Mesa 11')
    (13274, 4558, 'Nutella Mor MD', Decimal('1.0'), 'Mesa 11')
    (13275, 4558, '# Para Levar', Decimal('1.0'), 'Mesa 11')
    

Output table:

    
    
    (13264, 4558, 'Funghi GR', '+ Champinhons | + Alface | - R\xc3\xbacola', Decimal('1.0'), 'Mesa 11')
    (13268, 4558, 'Formaggi GR', 'Cortar em dois', Decimal('1.0'), 'Mesa 11')
    (13271, 4558, 'Nutella GR', '!SERVIR DEPOIS! | Cortar em dois', Decimal('1.0'), 'Mesa 11')
    (13274, 4558, 'Nutella Mor MD', '!SERVIR DEPOIS! | Para Levar', Decimal('1.0'), 'Mesa 11')
    


Answer: Its seems you have two tables. This way you only making 2 trips to the
database vs many.

Do a fetches -- to get data from tables

Table one

    
    
    ID     ORDER PRODUCT                    QTY     CAT
    12811  4419  "Crudo GR"                 1.0       1
    12812  4419  "Salame e Grana GR"        1.0       1
    12813  4419  "---- servir depois ----"  1.0       7
    12814  4419  "Nutella Ban GR"           1.0       3
    12815  4419  "# Cortar em dois"         1.0       7
    

get the data as table,

    
    
    newTable = [] 
    Intr = ''
    
        #Added something like
        #http://www.saltycrane.com/blog/2007/12/how-to-sort-table-by-columns-in-python/
            #import operator
           #fetch_lines = sorted(fetch_lines, key=operator.itemgetter(col))
    
    #Sort table so looks like this
    
        # ID     ORDER PRODUCT                    QTY     CAT
       # 12815  4419  "# Cortar em dois"         1.0       7
       # 12813  4419  "---- servir depois ----"  1.0       7
       # 12811  4419  "Crudo GR"                 1.0       1
       # 12812  4419  "Salame e Grana GR"        1.0       1
       # 12814  4419  "Nutella Ban GR"           1.0       3
    
    
    for i,line in enumerate(fetch_lines):
        if line[2].startswith('#') or line[2].startswith('----'): 
            # Within this if statement you can make adjustment to text item
            if line[2].startswith('#')
               Intr =  Intr + " Cortar em dois"
            if line[2].startswith('----')
               Intr =  '!SERVIR DEPOIS!' + Intr
        if i == len(fetch_lines) -1:
            newTable.append([line[0], ....., Intr ,  ...])
        if i < len(fetch_lines)
            newTable.append([line[0], ....., '',  ...])
    print table
    
    
       #Then sort by first column  so table look right 
               #table = sorted( newTable, key=operator.itemgetter(col))
    
    #ID     ORDER PRODUCT              INSTR                              QTY     
    #12811  4419  "Crudo GR"                                               1.0       
    #12812  4419  "Salame e Grana GR"                                    1.0       
    #12814  4419  "Nutella Ban GR"     "!SERVIR DEPOIS! Cortar em dois"          1.0   
    

Try This to place Intr in last row:

    
    
    newTable = [] 
    Intr     = ''
    LineCt   = 0
    
    
    for line in fetch_lines:
        if line[2].startswith('#') or line[2].startswith('----'): 
            # Within this if statement you can make adjustment to text item
            if line[2].startswith('#'):
               Intr =  Intr + " Cortar em dois"
               LineCt +=1
            if line[2].startswith('----'):
               Intr =  '!SERVIR DEPOIS!' + Intr
               LineCt +=1   
    
    for i,line in enumerate(fetch_lines):
        if line[2].startswith('#') or line[2].startswith('----'): pass        
        elif i == len(fetch_lines) - LineCt:
            newTable.append([line[0],line[1], line[2], Intr , "" ])
            Intr     = ''
        elif i < len(fetch_lines):
            newTable.append([line[0],line[1],line[2], '', "" ])        
    
    print Intr        
    for e in newTable: print e
    

Output:

    
    
      [12811, 4419, 'Crudo GR', '', '']
        [12812, 4419, 'Salame e Grana GR', '', '']
        [12814, 4419, 'Nutella Ban GR', '!SERVIR DEPOIS! Cortar em dois', ''
    

]

