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: >> > >> >> 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
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 06/07/2013 01:44 PM, ethereal_r...@hotmail.com wrote: 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
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
