Reputation: 111
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: 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')
Upvotes: 0
Views: 419
Answers (2)
Reputation: 111
Temporary solved with the HUGE help of @Merlin, he drove me to the right direction but I needed to arrange the code. The big trick was to select the lines in DESC from table.
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')
I'm sure my code could be better, but in the old version I needed more than 80 lines of code to archive that. That's a huge step forward for my Python knowledge.
Upvotes: 0
Reputation: 25629
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', ''
]
Upvotes: 1
Related Questions
- Sorting an array of objects by property values
- Delete a column from a Pandas DataFrame
- How can I remove a key from a Python dictionary?
- How can I pair socks from a pile efficiently?
- Importing files from different folder
- How do I select rows from a DataFrame based on column values?
- Sort (order) data frame rows by multiple columns
- Sorting object property by values
- Delete an element from a dictionary
- How to remove an element from a list by index