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()

In this case fetchall gets 2 columns (vat with the RFC alias and amount_untaxed)

Now I need to format the data in the following way

Colums = 22
first 2 columns always have 04 and 85 respectively.
Third colum has the Vat as RFC code
Eight column has the amount untaxed

Assuming this code finds the following data form the DB
LOEL910624ND5 from the column vat as RFC.
227 from the column amount_untaxed.

Then the correct final format would be the following.

04|85|LOEL910624ND5|||||227|||||||||||||||

I would like to also do something like

writer = csv.writer(sys.stdout, delimiter="|")

Hope I explained myself well enough.

I'm guessing you already found this good tutorial on PostgreSQL and Python: Click Here

What is your problem with your code (except the fact that it isn't even inserted in a code block...). Do you get a specific error message, or you just don't get any result at all.
I'm guessing this is your 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()

Edited 3 Years Ago by Lucaci Andrew

Yup i was going with that tutorial and this code works up till returning the values that are stored in the columns of the database, in this case it is vat and amount_untaxed.

But I do not know how to actually work with said data and format it in this way.

04|85|LOEL910624ND5|||||227|||||||||||||||

04 and 85 are static, vat goes into the third column and amount_untaxed goes into the 8th column, every otehr column is empty and tehre are 22 in total.

This article has been dead for over six months. Start a new discussion instead.