Federico Leoni Federico Leoni - 6 months ago 19
Python Question

Sorting data from a table

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: temporary solved with:

po_lines = '''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.order_id = %s
AND (product_template.pos_categ_id != 5 AND product_template.pos_categ_id != 6)
ORDER BY pos_order_line.id DESC'''

cur.execute(po_lines,[order]); fetch_lines = cur.fetchall()
instr = ''; newTable = []

for i, line in enumerate(fetch_lines):
if line[2].startswith('#') or line[2].startswith('----'):
if line[2].startswith('#'):
instr = instr + line[2][2:]
if line[2].startswith('----'):
line_in = fetch_lines[i-1]
extract_line = tuple([item[3] for item in newTable if line_in[0] in item])
newTable = [t for t in newTable if t[0] != line_in[0]]
instr = '!SERVIR DEPOIS!/' + extract_line[0]
newTable.append((line_in[0], line_in[1], line_in[2], instr))
instr = ''
else:
newTable.append((line[0], line[1], line[2], instr))
instr = ''

for i,l in enumerate(newTable[::-1]):
print i,l


Result:

0 (12811, 4419, 'Crudo GR', '')
1 (12812, 4419, 'Salame e Grana GR', '')
2 (12814, 4419, 'Nutella Ban GR', '!SERVIR DEPOIS!/Cortar em dois')

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 emnumerate(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
    elif i == len(fetch_lines) -1:
        newTable.append([line[0], ....., Intr ,  ...])
    elif 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