Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #47341 > unrolled thread

Trying to work with data from a query using Python.

Back to article view | Back to comp.lang.python

Contents

  Trying to work with data from a query using Python. ethereal_robe@hotmail.com - 2013-06-07 10:44 -0700
    Re: Trying to work with data from a query using Python. Dave Angel <davea@davea.name> - 2013-06-07 14:24 -0400
      Re: Trying to work with data from a query using Python. Walter Hurry <walterhurry@lavabit.com> - 2013-06-07 21:59 +0000
    Re: Trying to work with data from a query using Python. Peter Otten <__peter__@web.de> - 2013-06-07 20:59 +0200

#47341 — 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.

[toc] | [next] | [standalone]

#47345

On 06/07/2013 01:44 PM, ethereal_robe@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

[toc] | [prev] | [next] | [standalone]

#47355

On Fri, 07 Jun 2013 14:24:30 -0400, Dave Angel wrote:

> On 06/07/2013 01:44 PM, ethereal_robe@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.

[toc] | [prev] | [next] | [standalone]

#47348

ethereal_robe@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.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web