#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    1
    Rep Power
    0

    Creating reports in Python using data retrieved from a postgresql DB


    I am trying to code in python in order to get a report on prices depending on their taxes. the template is as follows:

    04|85|%s|||||%s|||%s||||||||%s||||
    22 columns total, first two columns always have 04 and 85 set, the third column is where the data from vat goes in while the other 3 columns (8th, 11th, and 19th). vat codes cannot be repeated and thus I added the SUM function to the query command but the hard part for me is juggling the price_subtotal and setting it in the correct column depending on the tax.

    3236 = 0%tax
    3237 = 16%tax
    3238 = 11% tax
    For example

    if the query finds price_subtotal is 2,1,3 where the account_paid_id is 3237 and then finds another price_subtotal with 1,4 wherethe account_paid_id is 3738, the resu˝lting report would be like this, assuming vat is dgfsasf.

    04|85|dgfsasf|||||6|||5||||||||||||
    vat went into the third column and all the values for the 8th column were added together as well asthe ones for the 11th, the 19th column was left empty as there wereno values with an account_paid_id of 3236 associated with the vat code "dgfsasf".

    My question is, how to write code in python that defines that logic, set the data on that template using these rules and provide a proper report after it does so.


    The code is as follows:



    import psycopg2
    import sys

    con = None

    try:

    con = psycopg2.connect(database='MyDb', user='me', password='ppassedd')

    cur = con.cursor()
    cur.execute("

    Select Account_Invoice_Line.price_subtotal,
    right (Res_Partner.vat,length(Res_Partner.vat)-2)
    from Account_Invoice_Line
    inner join Res_Partner on Account_Invoice_Line.partner_id = Res_Partner.id
    inner join Account_Invoice on Account_Invoice_Line.invoice_id = Account_Invoice.id
    join Account_Invoice_Line_Tax
    join Account_Tax on Account_Invoice_Line_Tax.tax_id = Account_Tax.id
    on Account_Invoice_Line.id = Account_Invoice_Line_Tax.invoice_line_id
    where account_invoice.journal_id=2
    and account_invoice.date_invoice >= '2013-01-01'
    and account_invoice.date_invoice <= '2013-12-31'
    and account_invoice.reconciled is TRUE
    and account_invoice.amount_tax >= 0
    and Account_Tax.account_paid_id in (3236, 3238, 3237)
    ")

    rows = cur.fetchall()

    f=file("newfile.txt","w")
    for row in rows: f.write("04|85|%s|||||%s|||%s||||||||%s||||\n" % (row[1],row[0]))
    f.close()

    for row in rows:
    print row

    except psycopg2.DatabaseError, e:
    print 'Error %s' % e
    sys.exit(1)

    finally:

    if con:
    con.close()
  2. #2
  3. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,851
    Rep Power
    481
    With a format string containing 4 conversions you'll need to supply 4 values. Precompute them as strings. Part of this precomputation logic will include "if the value is '0' change it to '' (the empty string)". For further assistance you'll need to show example rows that might be returned from your database query, using code such as
    for row in rows: pprint.pprint(row)
    I see that the sum of 2,1,3 is 6 and that the sum of 1,4 is 5. I don't know why price_subtotal is a list other than to conclude that you transposed the rows and didn't tell us, or equivalently that instead of a list the numbers form a column vector.

    Difficult to follow your writing
    ...the third column is where the data from vat goes in while the other 3 columns (8th, 11th, and 19th).
    end of sentence.
    Code:
    # I am trying to code in python in order to get a report on prices
    # depending on their taxes. the template is as follows:
    
    # 04|85|%s|||||%s|||%s||||||||%s||||
    # 22 columns total, first two columns always have 04 and 85 set, the
    # third column is where the data from vat goes in while the other
    # 3 columns (8th, 11th, and 19th). vat codes cannot be repeated and thus
    # I added the SUM function to the query command but the hard part for me
    # is juggling the price_subtotal and setting it in the correct column
    # depending on the tax.
    
    # 3236 = 0%tax
    # 3237 = 16%tax
    # 3238 = 11% tax
    # For example
    
    # if the query finds price_subtotal is 2,1,3 where the account_paid_id
    # is 3237 and then finds another price_subtotal with 1,4 where the
    # account_paid_id is 3738, the resu˝lting report would be like this,
    # assuming vat is dgfsasf.
    
    # 04|85|dgfsasf|||||6|||5||||||||||||
    # vat went into the third column and all the values for the 8th column
    # were added together as well as the ones for the 11th, the 19th column
    # was left empty as there were no values with an account_paid_id of 3236
    # associated with the vat code "dgfsasf".
    
    # My question is, how to write code in python that defines that logic,
    # set the data on that template using these rules and provide a proper
    # report after it does so.
    
    # The code is as follows:
    
    import psycopg2
    import sys
    
    con = None
    
    try:
        con = psycopg2.connect(database='MyDb', user='me', password='ppassedd') 
        cur = con.cursor() 
        cur.execute('''
            Select Account_Invoice_Line.price_subtotal,
            right (Res_Partner.vat,length(Res_Partner.vat)-2)
            from Account_Invoice_Line
            inner join Res_Partner on Account_Invoice_Line.partner_id = Res_Partner.id
            inner join Account_Invoice on Account_Invoice_Line.invoice_id = Account_Invoice.id
            join Account_Invoice_Line_Tax
            join Account_Tax on Account_Invoice_Line_Tax.tax_id = Account_Tax.id
            on Account_Invoice_Line.id = Account_Invoice_Line_Tax.invoice_line_id
            where account_invoice.journal_id=2 
            and account_invoice.date_invoice >= '2013-01-01' 
            and account_invoice.date_invoice <= '2013-12-31' 
            and account_invoice.reconciled is TRUE 
            and account_invoice.amount_tax >= 0
            and Account_Tax.account_paid_id in (3236, 3238, 3237)
        ''')
    
        rows = cur.fetchall()
    
    except psycopg2.DatabaseError, e:
        print 'Error %s' % e 
        sys.exit(1)
    
    else:
        with file("newfile.txt","w") as f
        for row in rows:
            f.write("04|85|%s|||||%s|||%s||||||||%s||||\n" % (row[1],row[0]))
            print row
    
    finally:
        if con:
            con.close()
    [code]Code tags[/code] are essential for python code and Makefiles!

IMN logo majestic logo threadwatch logo seochat tools logo