Trying to work with data from a query using Python.

E

ethereal_robe

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 whereaccount_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.
 
D

Dave Angel

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.
 
P

Peter Otten

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,931
Messages
2,570,085
Members
46,536
Latest member
keelop

Latest Threads

Top