Federico Leoni Federico Leoni - 6 months ago 20
Python Question

Missing line sorting data from a table

I'm working on a new module for Odoo POS 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 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.

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 lists, then compare the two tables

for line in fetch_lines:
    # dont do this -- rather compare with table one vs table two. 
    #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, ' - Pra  


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

newTable = [] 
Intr = ''
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