Trying to work with data from a query using Python.
Hello, I'm working with PostgreSQL and Python to obtain 2 columns froma database and need to print it in a specific format. Here is my current code. #!/usr/bin/python # -*- coding: utf-8 -*- import psycopg2 import sys con = None try: con = psycopg2.connect(database='DB', user='ME', password='1234') cur = con.cursor() cur.execute( select Account_Invoice.amount_untaxed, right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC from Account_Invoice inner join Res_Partner on Account_Invoice.partner_id = Res_Partner.id inner join Account_Invoice_Tax on Account_Invoice.id = Account_Invoice_Tax.invoice_id where account_invoice.journal_id=2 and account_invoice.date_invoice = '2013-01-01' and account_invoice.date_invoice = '2013-02-01' and account_invoice.reconciled is TRUE and account_invoice_tax.account_id = 3237 and account_invoice.amount_tax = 0;) rows = cur.fetchall() for row in rows: print row except psycopg2.DatabaseError, e: print 'Error %s' % e sys.exit(1) finally: if con: con.close() Now assume that fetchall would print the following: LOEL910624ND5 from the column vat as RFC. 227 from the column amount_untaxed. Now I would need to print that in the following format. 04|85|LOEL910624ND5|227||| 04 always goes in the first column and 85 always goes in the second, vat goes in the third and the amount_untaxed goes in the eight column but we still need to have 22 columns in total. -- http://mail.python.org/mailman/listinfo/python-list
Re: Trying to work with data from a query using Python.
On 06/07/2013 01:44 PM, ethereal_r...@hotmail.com wrote: SNIP rows = cur.fetchall() for row in rows: print row Now assume that fetchall would print the following: I doubt if fetchall() prints anything. presumably it returns something, extracted from the db. LOEL910624ND5 from the column vat as RFC. 227 from the column amount_untaxed. Now I would need to print that in the following format. 04|85|LOEL910624ND5|227||| 04 always goes in the first column and 85 always goes in the second, vat goes in the third and the amount_untaxed goes in the eight column but we still need to have 22 columns in total. I don't use psycopg2, and I'd suggest few others here do either. Since the problem has nothing to do with psycopg2, could you simplify the problem? Whatever fetchall() returns, it's presumably either a dict or list. Or is it a list of lists? Find out what kind of data it is, and stub it with something like: rows = [ab, 127] Then if you define what the items in that list (or whatever) are supposed to mean, we can tell you how to stick all those pipe-symbols between. One likely answer would be the csv module. -- DaveA -- http://mail.python.org/mailman/listinfo/python-list
Re: Trying to work with data from a query using Python.
ethereal_r...@hotmail.com wrote: Hello, I'm working with PostgreSQL and Python to obtain 2 columns froma database and need to print it in a specific format. Here is my current code. #!/usr/bin/python # -*- coding: utf-8 -*- import psycopg2 import sys con = None try: con = psycopg2.connect(database='DB', user='ME', password='1234') cur = con.cursor() cur.execute( select Account_Invoice.amount_untaxed, right (Res_Partner.vat,length(Res_Partner.vat)-2) as RFC from Account_Invoice inner join Res_Partner on Account_Invoice.partner_id = Res_Partner.id inner join Account_Invoice_Tax on Account_Invoice.id = Account_Invoice_Tax.invoice_id where account_invoice.journal_id=2 and account_invoice.date_invoice = '2013-01-01' and account_invoice.date_invoice = '2013-02-01' and account_invoice.reconciled is TRUE and account_invoice_tax.account_id = 3237 and account_invoice.amount_tax = 0;) rows = cur.fetchall() for row in rows: print row except psycopg2.DatabaseError, e: print 'Error %s' % e sys.exit(1) finally: if con: con.close() Now assume that fetchall would print the following: LOEL910624ND5 from the column vat as RFC. 227 from the column amount_untaxed. Now I would need to print that in the following format. 04|85|LOEL910624ND5|227||| 04 always goes in the first column and 85 always goes in the second, vat goes in the third and the amount_untaxed goes in the eight column but we still need to have 22 columns in total. Keep it simple: COLUMN_COUNT = 22 TEMPLATE = 04|85|{0}|{1}||| assert TEMPLATE.count(|) == COLUMN_COUNT -1, You cannot count ;) for row in cur.fetchall(): print TEMPLATE.format(*row) A bit more general: fill_rows(rows): out_row = [] * 22 out_row[0] = 04 out_row[1] = 85 for row in rows: out_row[2], out_row[7] = row # copying not necessary here, but let's play it safe yield out_row[:] writer = csv.writer(sys.stdout, delimiter=|) writer.writerows(fill_rows(cur.fetchall())) All untested code. -- http://mail.python.org/mailman/listinfo/python-list
Re: Trying to work with data from a query using Python.
On Fri, 07 Jun 2013 14:24:30 -0400, Dave Angel wrote: On 06/07/2013 01:44 PM, ethereal_r...@hotmail.com wrote: SNIP rows = cur.fetchall() for row in rows: print row Now assume that fetchall would print the following: I doubt if fetchall() prints anything. presumably it returns something, extracted from the db. LOEL910624ND5 from the column vat as RFC. 227 from the column amount_untaxed. Now I would need to print that in the following format. 04|85|LOEL910624ND5|227||| 04 always goes in the first column and 85 always goes in the second, vat goes in the third and the amount_untaxed goes in the eight column but we still need to have 22 columns in total. I don't use psycopg2, and I'd suggest few others here do either. Since the problem has nothing to do with psycopg2, could you simplify the problem? Whatever fetchall() returns, it's presumably either a dict or list. Or is it a list of lists? It actually returns a list of tuples. -- http://mail.python.org/mailman/listinfo/python-list