How to put a $ before a value taken from a database through SQL in Python

Question

I'm trying to display values in HTML that have a "$" at the beginning, but the way I print out the values in HTML makes it so that with the justification I can only add it at the end of the previous value or at the end of the value.

I'm thinking I have to somehow incorporate the "$" into the for loop, but I'm not sure how to do that.

BODY['html'] += '<br>Total shipped this month:..............Orders........Qty...........Value<br>'

SQL5 = '''
select count(*) as CNT, sum(A.USER_SHIPPED_QTY) as QTY, sum(( A.USER_SHIPPED_QTY) * A.UNIT_PRICE) as VALUE
                  from SHIPPER_LINE A, SHIPPER B
                  where  B.PACKLIST_ID = A.PACKLIST_ID
                  and A.CUST_ORDER_ID like ('CO%')
                  and B.SHIPPED_DATE between ('{}') and ('{}')
                  '''.format(RP.get_first_of_cur_month_ora(), RP.get_rep_date_ora())
        ## {} and .format get around the issue of using %s with CO%

print SQL5
curs.execute(SQL5)
for line in curs:  ##used to print database lines in HTML
    print line
    i=0
    for c in line:
        if i==0:
            BODY['html'] += '<pre>' + str(c).rjust(60,' ')
        elif i == 1:
            BODY['html'] += str(c).rjust(15,' ')
        else:
            BODY['html'] += str(c).rjust(22,' ') + '</pre>'
        i+=1

The "pre" in HTML is used to keep the whitespace and the ' ' after rjust is used to space the numbers properly to go under the column headings. The values that are printed out are generated from the database using the SQL.

Here is what displays in HTML for this code:

 Total shipped this month:..............Orders........Qty...........Value                                                        
                                          3968      16996      1153525.96

This is what I want it to look like:

 Total shipped this month:..............Orders........Qty...........Value                                                        
                                          3968      16996     $1153525.96

Possible duplicate of [Currency formatting in Python](http://stackoverflow.com/questions/320929/currency-formatting-in-python) — CodeLikeBeaker, May 15 '17 at 20:34
@Jason Heine I don't think I can use that code in my case. I need to add something in the SQL or in the for loop. The values that are printed out are generated from the database using the SQL — Rowan A. Boat, May 15 '17 at 21:01

score 0 · Accepted Answer · answered May 15 '17 at 20:45

0

You could apply the format in the DB by wrapping your sum with a to_char and a currency/numeric format model ...

select to_char(12345.67, 'FML999,999.99') FROM DUAL;

answered May 15 '17 at 20:45

unleashed

771
3
9

The values aren't absolute. They change day by day. Also, I don't believe I can change the database values like this in my case. – Rowan A. Boat May 15 '17 at 20:52
this was an example, you would use to_char(sum(( A.USER_SHIPPED_QTY) * A.UNIT_PRICE), 'FML999,999.99') – unleashed May 15 '17 at 21:10
SQL. It would return your value in a formatted string with the currency symbol as the leading character. – unleashed May 16 '17 at 02:21
It works in the database when using SQL, but when I put that in the Python SQL it just prints out "#####################" for that value in the HTML – Rowan A. Boat May 16 '17 at 13:31
Are your values more than 6 digits? If so, extend the format to cover the necessary number of digits. – unleashed May 16 '17 at 13:37
Yes, so I changed it to 'FML999,999,999.99' and it works now. Thank you very much! – Rowan A. Boat May 16 '17 at 13:44
The only issue is that it displays a decimal at the end of the numbers if the decimals are 0 and it doesn't show cents properly. For example, it displays "123,456." or "123,456.2". Any idea how to fix this? – Rowan A. Boat May 16 '17 at 13:50
Never mind, it looks like I fixed it by changing it to 'FML999,999,999.00' – Rowan A. Boat May 16 '17 at 13:54
Glad it worked. More information on character format models in the documentation here -> https://docs.oracle.com/database/122/SQLRF/Format-Models.htm#SQLRF00211 – unleashed May 16 '17 at 14:27

How to put a $ before a value taken from a database through SQL in Python

1 Answers1