how to format mysql float into sytem local values or decimal comma for a reportlab billing system

Question

I have a Reportlab form for a billing system and it works good. But what I want to get are the values in sytem local values, with decimal comma, or separator; something as next instead as shown in the picture:

1,350,000.00

I'd like something like FORMAT in MySQL, but the problem with this one is that it does'nt let me to sum the values to get the total because it returns the values as string.

SELECT FORMAT (columna, 'es_CO') FROM tabla;

How my form looks like

And my Python code:

Just retefuente, ivajuridico, rteiva and i_vlrenta are float in mysql

#!/usr/bin/python
#-*- coding:utf-8 -*-
from Tkinter import*
from tkMessageBox import*
import MySQLdb
from controller import *
import analisis_arrendatarios
import os
import datetime
import time
import locale
locale.setlocale(locale.LC_ALL, "")
#LIBRERÍA PLATYPUS DE REPORTLAB PARA CREAR TABLAS
from reportlab.platypus import (SimpleDocTemplate, PageBreak, Image, Spacer,
Paragraph, Table, TableStyle)
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib.pagesizes import letter
from reportlab.lib import colors
styleSheet = getSampleStyleSheet()
style = styleSheet['BodyText']

class Proceso_Fact_Auto_Arre(Frame):
        def __init__(self, parent, controller):
                Frame.__init__(self, parent)

        global opt

        #VARIABLES
        opt = IntVar()

        #WIDGETS
        header = Label(self, text="TENANTS AUTO BILLING PROCESS", font="bold")
        header.pack(pady=20, side=TOP)

        wrapper = Frame (self)
        wrapper.pack()

        r1 = Radiobutton(wrapper, text="Generate Analysis", variable=opt, value=0).pack(pady=5, anchor=W)
        r2 = Radiobutton(wrapper, text="Generate Billings", variable=opt, value=1).pack(pady=5, anchor=W)
        Button(wrapper, text="Iniciar Proceso", bg='navy', foreground='white', activebackground='red3', activeforeground='white', command=operacion).pack(pady=5, anchor=W)

def operacion():
    if opt.get()==0:
        showinfo('Operation', "Generate Analysis")
        try:
            connect.commit()
            #Consulta de códigos y valores de la tabla 'configuracion'
            cursor.execute("SELECT cod_canon, cod_subtotalarrend, cod_ivaarrend,
                      retefuente, ivajuridico, rteiva, resolucion 
                    FROM configuracion;")
            dato1 = cursor.fetchall()
            #Consulta de datos generales del propietario y el arrendatario para la factura
            cursor.execute("SELECT p_cc, dueño, r_carpeta, relacionip.i_cod, i_dir, i_vlrenta, i_tel, contratos.a_cc, inquilino, a_tpersona, a_contribuyente
                  FROM contratos
                  INNER JOIN relacionip ON contratos.r_id = relacionip.r_id
                  INNER JOIN inmuebles ON relacionip.i_cod = inmuebles.i_cod
                  INNER JOIN arrendatarios ON contratos.a_cc = arrendatarios.a_cc;")
            #cursor.execute("SELECT p_cc, dueño, r_carpeta, relacionip.i_cod, i_dir, FORMAT(SUM(i_vlrenta), 2,'es'), i_tel, contratos.a_cc, inquilino, a_tpersona, a_contribuyente 
                  #FROM contratos 
                  #INNER JOIN relacionip ON contratos.r_id = relacionip.r_id 
                  #INNER JOIN inmuebles ON relacionip.i_cod = inmuebles.i_cod 
                  #INNER JOIN arrendatarios ON contratos.a_cc = arrendatarios.a_cc;")
            dato2 = cursor.fetchall()
        except:
            pass
        for c in dato1:
            canon = c[0]
            subtotal = c[1]
            iva = c[2]
            retef = c[3]
            ivajuridi = c[4]
            retei = c[5]
            resolucion = c[6]

        doc = SimpleDocTemplate("facturas/factura_auto_inquilino.pdf", pagesize = (595.27,400.00), rightMargin=5, leftMargin=5, topMargin=10, bottomMargin=0)
        story=[]

        for i in dato2:
            nit = i[0]
            prop = i[1]
            folder = i[2]
            inm = i[3]
            loc = i[4]
            renta = i[5]
            tel = i[6]
            cc = i[7]
            arrend = i[8]
            tipo = i[9]
            contri = i[10]
            #SI ARREND ES NATURAL(1)
            if tipo == 1:
                tipo = 0
            #SI ARREND ES JURÍDICO(2)
            if tipo == 2:
                tipo = renta*ivajuridi/100

            total = renta+tipo

            tiempo = datetime.date.today()
            anio = time.strftime("%Y")
            mes = time.strftime("%B")

        #-------------------------------------------- CABECERA DEL DOCUMENTO

            #VARIABLES
            logo = Image("img/logo.gif", width=150, height=45) #LOGO
            logo.hAlign ='LEFT' #Posicion de la img en la hoja

            info = Paragraph('''<para align=center leading=8><font size=6>CALLE 11A N°42-68 LOC,195 ED. EL DORADO<br/>TELEFONO: 3110513 FAX:2664154<br/>AFILIADO A FENALCO<br/>M.A.V.U N°000078</font></para>''', styleSheet["BodyText"])

            tipoDoc = Paragraph ('''<para align=right><b>FACTURA DE VENTA<br/>N°</b></para>''', styleSheet["BodyText"])

            #TABLA 1
            tabla1 = Table([[logo, info, tipoDoc]], colWidths=[200,150,140], rowHeights=None)
            tabla1.setStyle([
                ('VALIGN', (1,0), (2,0), 'TOP'),
                ('ALIGN', (2,0), (2,0), 'RIGHT')#ALINEAR A LA DER
                ])

            story.append(tabla1) #Construye la tabla 't' definida anteriormente
            story.append(Spacer(0,-10)) #Espacio del salto de línea con el siguiente Ejemplo

        #-------------------------------------------- DATOS GENERALES DEL DOCUMENTO

            #VARIABLES
            inquilino = Paragraph ('''<font size=6><b>Nombre Arrendatario:</b><br/></font>%s'''%arrend, styleSheet["BodyText"])
            docID = Paragraph ('''<font size=6><b>CC/Nit: </b></font>   %s''' %nit, styleSheet["BodyText"])
            locImn = Paragraph ('''<font size=6><b>Dirección Inmueble:</b><br/></font>%s'''%loc, styleSheet["BodyText"])
            telefono = Paragraph ('''<font size=6><b>Teléfono:</b><br/></font>%s'''%tel, styleSheet["BodyText"])
            IDpropietario = Paragraph ('''<font size=6><b>CC/Nit:</b><br/></font>%s'''%cc, styleSheet["BodyText"])
            propietario = Paragraph ('''<font size=6><b>Propietario: </b></font>%s'''%prop, styleSheet["BodyText"])
            fechaFormato = Paragraph ('''<para align=center fontSize=6>Día Mes Año</para>''', styleSheet["BodyText"])
            hoy = time.strftime("%d/%m/%Y")
            fecha = Paragraph ('''<para align=center spaceBefore=0>%s</para>''' %hoy, styleSheet["BodyText"])
            codigoImn = Paragraph ('''<font size=6><b>Código Inmueble:</b><br/></font>%s'''%inm, styleSheet["BodyText"])

            #TABLA 2
            datos = [[inquilino,'','','','',[fechaFormato,fecha]],
                    [docID,'','',propietario,'',''],
                    [locImn,'',telefono,IDpropietario,'',codigoImn]]

            tabla2 = Table(datos, 
                           style=[('BOX',(0,0),(2,2),0.5,colors.black),
                                  ('VALIGN', (0,0),(2,0),'TOP'),
                                  ('SPAN',(0,0),(2,0)),#Combinar 3 filas (col0,row0) hasta (col2,row0) Arrendatario #0
                                  ('SPAN',(0,1),(2,1)),#Combinar 3 filas CC/Nit #1
                                  ('SPAN',(0,2),(1,2)),#Combinar 2 filas Dirección #2
                                  ('SPAN',(3,1),(5,1)),#Combinar 3 filas Nombre Propietario #
                                  ('SPAN',(3,2),(4,2)),#Combinar 2 filas CC/Nit Propietario #
                                  ('GRID',(3,1),(4,2),0.5,colors.black),
                                  ('GRID',(5,0),(5,2),0.5,colors.black)
                                 ],colWidths=[100,90,90,90,80,70], rowHeights=None)

            #Constructor y espaciado
            story.append(Spacer(0,15)) #Espacio del salto de línea con el siguiente Ejemplo
            story.append(tabla2) #Construye la tabla 't' definida anteriormente

        #-------------------------------------------- DETALLES DEL DOCUMENTO

            #VARIABLES
            desc = Paragraph('''<para align=center><b>DESCRIPCION</b></para>''', styleSheet["BodyText"])
            vlr = Paragraph('''<para align=center><b>VALOR</b></para>''', styleSheet["BodyText"])
            concepto = Paragraph('''Valor Arrendamiento Mes: %s/%s''' % (mes,anio), styleSheet["BodyText"])

            resol = "Resolucion Dian N°110000658514 de Diciembre de 2015 Consectivo Facturacion 33001 al 36000. P"

            #TABLA 3
            data=[[desc, '', vlr],      #0
                  [concepto, '', renta], #1
                  ['', '', ''],         #2
                  ['', '', ''],         #3
                  ['', '', ''],         #4
                  ['', '', ''],         #5
                  ['', '', ''],         #6
                  ['Observaciones', 'SUBTOTAL', renta], #7
                  ['', 'IVA', tipo], #8
                  [resolucion, 'TOTAL', total]] #9

            #Formato de la tabla
            tabla3 = Table(data,
                        style=[('GRID',(0,0),(2,0),0.5,colors.black),#Color regilla de DESCRIPCION & VALOR
                               ('BOX',(2,1),(2,9),0.5,colors.black), #Color & grosor de la tabla/marco externo de los VALORES
                               #('BACKGROUND',(0,0),(2,0), colors.pink), #Color de fondo de DESCRIPCION & VALOR #0
                               ('SPAN',(0,0),(1,0)), #Combinar filas DESCRIPCION #0
                               ('BOX',(0,1),(2,6),0.5,colors.black), #Color & grosor de la tabla o marco externo de los DETALLES
                               ('ALIGN', (2,1), (2,1), 'RIGHT'),#Centrar renta #1
                               ('ALIGN', (2,7), (2,7), 'RIGHT'),#Centrar renta #7
                               ('ALIGN', (2,8), (2,8), 'RIGHT'),#Centrar tipo #8
                               ('ALIGN', (2,9), (2,9), 'RIGHT'),#Centrar total #9
                               #('ALIGN', (2,9), (2,9), 'CENTER'),#Centrar total #9
                               ('SPAN',(0,1),(1,1)), #Combinar filas de Detalle #1
                               ('SPAN',(0,2),(1,2)), #Combinar filas de Detalle #2
                               ('SPAN',(0,3),(1,3)), #Combinar filas de Detalle #3
                               ('SPAN',(0,4),(1,4)), #Combinar filas de Detalle #4
                               ('SPAN',(0,5),(1,5)), #Combinar filas de Detalle #5
                               ('SPAN',(0,6),(1,6)), #Combinar filas de Detalle #6
                               ('GRID',(1,7),(2,9),0.5,colors.black),#Color regilla de SUBTOTAL, IVA, TOTAL
                               ('BOX',(0,7),(0,9),0.5,colors.black), #Color & grosor de la tabla o marco externo de los OBSERVACIONES Y RESOLUCION
                               ('FONTSIZE', (0,9),(0,9),7), #Tamaño de la Resolucion
                               #('BACKGROUND',(1,9),(1,9),colors.black),#Color de fondo de TOTAL
                               ('TEXTCOLOR',(1,9),(1,9),colors.black), #Color de letra de TOTAL
                               #('BACKGROUND',(2,9),(2,9),colors.grey)#Color de fondo de VALOR TOTAL
                              ],colWidths=[340,80,100], rowHeights=None)

            story.append(Spacer(0,15)) #Espacio del salto de línea con el siguiente Ejemplo
            story.append(tabla3) #Construye la tabla 't' definida anteriormente

        #-------------------------------------------- FIN PDF

        doc.build(story) #Constructor del documento

        if sys.platform == 'linux2':
            os.system("xdg-open ~/Project/facturas/factura_auto_inquilino.pdf")#DEBIAN
        elif sys.platform == 'linux2':
            os.system("/usr/bin/gnome-open facturas/factura_auto_inquilino.pdf")#UBUNTU
        else:
            os.startfile("Project/facturas/factura_auto_inquilino.pdf")#WINDOWS

    else:
        #Code to insert data into a billinf table)

Have you tried doing the sum first, and then using the format function on it ? — Florian Humblot, Jun 06 '17 at 09:51
I think here is a response to your problem: https://stackoverflow.com/questions/320929/currency-formatting-in-python — BDS, Jun 06 '17 at 10:17
If you do the formatting in MySQL, see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_format — Rick James, Jun 06 '17 at 18:32
@BDS I've tried import locale, locale.setlocale(locale.LC_ALL, "") -as you can see in the code-- but i doesn't return me data with decimal separator. I'm good at this point, plus I really don't know how and where to add someting like '{:20,.2f}'.format(). I don't get the right way. — Einnerlink, Jun 07 '17 at 00:38
@RickJames I'm trying to add what you suggest in my code, but maybe I'm not writing it properly. — Einnerlink, Jun 07 '17 at 00:40
Yeah, the MySQL code and the Python code will look different. Which place do you want to do it? — Rick James, Jun 07 '17 at 01:08
@RickJames maybe near the second MySQL cursor.execute to know how it works, or near Reportlab variables (where '#---CABECERA DEL DOCUMENTO' is. — Einnerlink, Jun 07 '17 at 01:19

score 0 · Answer 1 · answered Jun 07 '17 at 02:10

0

SELECT FORMAT(SUM(columna), 2, 'es_CO') FROM tabla;

For example:

mysql> SELECT FORMAT(SUM(population), 3, 'es_CO') FROM canada;
+-------------------------------------+
| FORMAT(SUM(population), 3, 'es_CO') |
+-------------------------------------+
| 23584718,000                        |
+-------------------------------------+
1 row in set (0.01 sec)

I suspect you wanted something like

| 23.584.718,000                      |

Then I suggest you file a bug with bugs.mysql.com . It seems that the 'grouping separator' is incorrect?

Meanwhile, will 'es' (instead if 'es_CO') work correctly enough?

answered Jun 07 '17 at 02:10

Rick James

135,179
13
127
222

OK! I tried that and I know that it works, but as I wrote it does'nt let me to sum the values to get the total because it returns the values as string. Some kind of sum but I don't know how to do it. – Einnerlink Jun 07 '17 at 05:19
You must do the `SUM` _inside_ the `FORMAT` call, as I did. – Rick James Jun 07 '17 at 23:51
I have about 8 tenants to whom I have to give them the bills, that equals 8 bills for each one. As seen in the image link, my code throws me 8 bills (1/8 bills) with the values of the lease , tax (iva), subtotal and total without decimal separator in each, but the total sum the subtotal and the tax correctly. I tried your solutio —and it's commented in my code— but in the console of MySQL but I got the data of a single tenant with the sum of the lease of all 8. In my GUI I got this: – Einnerlink Jun 08 '17 at 05:07
Exception in Tkinter callback Traceback (most recent call last): File "c:\python27\lib\lib-tk\Tkinter.py", line 1536, in __call__ return self.func(*args) File "F:\SBIENES\mod\proceso_fact_auto_arre.py", line 88, in operacion tipo = renta*ivajuridi/100 TypeError: can't multiply sequence by non-int of type 'float' – Einnerlink Jun 08 '17 at 05:08
`FORMAT` delivers a string; do the arithmetic inside. Let's see your SQL. – Rick James Jun 08 '17 at 05:09
That file is not on my machine. May I look over your shoulder? – Rick James Jun 08 '17 at 05:10

Einnerlink · Accepted Answer · 2017-06-09T07:39:05.303

As some guys suggested, what I had to do was: sum the values, which was already done in my code, add a new variable appling a format with 'locale.format', and then just place that variable in the corresponding place in the reportlab form. For the total value it was not necessary to create a new variable, but just apply locate.

So, I added to my code something like this:

for i in dato2:
            #...
            renta = i[5]
            #New var to show value with thousands separator in the form
            renta1 = locale.format("%d", renta, grouping=True)
            tipo = i[9]
            #SI ARREND ES NATURAL(1)
            if tipo == 1:
                tipo = 0
                #New var to show value with thousands separator in the form
                tipo1 = locale.format("%d", tipo, grouping=True)
            #SI ARREND ES JURÍDICO(2)
            if tipo == 2:
                tipo = renta*ivajuridi/100
                #New var to show value with thousands separator in the form
                tipo1 = locale.format("%d", tipo, grouping=True)

            total = locale.format("%d", renta+tipo, grouping=True)

            #REPORTLAB FORM TABLA 3
            data=[[desc, '', vlr],      #0
                  [concepto, '', renta], #1
                  ['', '', ''],         #2
                  ['', '', ''],         #3
                  ['', '', ''],         #4
                  ['', '', ''],         #5
                  ['', '', ''],         #6
                  ['Observaciones', 'SUBTOTAL', renta], #7
                  ['', 'IVA', tipo], #8
                  [resolucion, 'TOTAL', total]] #9

how to format mysql float into sytem local values or decimal comma for a reportlab billing system

2 Answers2