Error in MySQL stored procedure - Python

Question

Error in MySQL stored procedure - Python

Navigation

#1 by (2 votes)
#2 by (2 votes)

3

I have this procedure in MySQL:

sql_procedure = """CALL INSERTAR_OPORTUNIDAD(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" % (
    CLIENTE,CLIENTEOCA,DENCLIENTE,AGRUPACION,DESAGRUPACION,CIUDAD,DESCIUDAD,
    PROVINCIA,DESPROVINCIA,DOMICILIO,TELEFONO1,TELEFONO2,FAX,EMAIL,CAMPOPERSO1,
    INACTIVO,TIPO,DESTIPO,SIGLAINFO,TIPOMOV,DEBCRE,LETRA,PUNTOVTA,NUMERO,FECHA_EMI,
    FECHA_VTO,PORDESCUENTO,DESCUENTO,NETO,PORPERIVA,PERIVA,PORPERIB,PERIB,
    PORPERMUNCBA,PERMUNCBA,TOTAL,TOTALSIMP,CANCELADO,SINAPLICAR,
    ESTADO,VENDEDOR,DENVENDEDOR,TRANSPORTISTA,DENTRANSPORTISTA,
    REDESPACHO,DENREDESPACHO,COMENTARIOS,REFERENCIAS,
    COD_ITEM,DES_ITEM,MEMO_ITEM,NRO_ITEM,FECHAENTREGA,CANTI,CANTI_COMPE,CANTI_SINCOMPE,PREUNI_NETO,
    PREUNI_FINAL,PREUNI_NETO_BONIFI,PREUNI_FINAL_BONIFI,BONIFI_POR,BONIFI,SUBTOTDESCUENTO,TOTPORPER,
    SUBTOTPER,IMPO_SUBTOT_SINIVA,IMPO_SUBTOT_CONIVA,IVA_GRAL,TIPOIMPUIN,PORCEIMPUIN,IMPU_IN,ESTADOITEM,
    CPTESDESTIASIGNACION,CODUNIDAD,NUMDOCU)
cursor_procedure= conMysql.cursor()
cursor_procedure.execute(sql_procedure)
cursor_procedure.commit()
cursor_procedure.close()

When I run it in Python I have this error:

Traceback (most recent call last):
  File "filemysql.py", line 303, in <module>
    CPTESDESTIASIGNACION,CODUNIDAD,NUMDOCU)
TypeError: not enough arguments for format string

How can I solve it?

mysql python stored-procedure

asked by Nahuel Jakobson 03.08.2017 в 22:18

source

2 answers

Problem filling object with node and mongodb Query to database

score 2 · Answer 1

The error says what the problem is, you are not passing the amount of %s needed to format the string. As I see you have one left, the code should be like this:

sql_procedure = """CALL INSERTAR_OPORTUNIDAD(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" % (
    CLIENTE,CLIENTEOCA,DENCLIENTE,AGRUPACION,DESAGRUPACION,CIUDAD,DESCIUDAD,
    PROVINCIA,DESPROVINCIA,DOMICILIO,TELEFONO1,TELEFONO2,FAX,EMAIL,CAMPOPERSO1,
    INACTIVO,TIPO,DESTIPO,SIGLAINFO,TIPOMOV,DEBCRE,LETRA,PUNTOVTA,NUMERO,FECHA_EMI,
    FECHA_VTO,PORDESCUENTO,DESCUENTO,NETO,PORPERIVA,PERIVA,PORPERIB,PERIB,
    PORPERMUNCBA,PERMUNCBA,TOTAL,TOTALSIMP,CANCELADO,SINAPLICAR,
    ESTADO,VENDEDOR,DENVENDEDOR,TRANSPORTISTA,DENTRANSPORTISTA,
    REDESPACHO,DENREDESPACHO,COMENTARIOS,REFERENCIAS,
    COD_ITEM,DES_ITEM,MEMO_ITEM,NRO_ITEM,FECHAENTREGA,CANTI,CANTI_COMPE,CANTI_SINCOMPE,PREUNI_NETO,
    PREUNI_FINAL,PREUNI_NETO_BONIFI,PREUNI_FINAL_BONIFI,BONIFI_POR,BONIFI,SUBTOTDESCUENTO,TOTPORPER,
    SUBTOTPER,IMPO_SUBTOT_SINIVA,IMPO_SUBTOT_CONIVA,IVA_GRAL,TIPOIMPUIN,PORCEIMPUIN,IMPU_IN,ESTADOITEM,
    CPTESDESTIASIGNACION,CODUNIDAD,NUMDOCU)
cursor_procedure= conMysql.cursor()
cursor_procedure.execute(sql_procedure)
cursor_procedure.commit()
cursor_procedure.close()

score 2 · Answer 2

There are a couple of things you could modify to improve your script a bit:

The safest thing is that instead of formatting the parameters directly in your variable sql_procedure , you pass them to the same function execute :

cursor_procedure.execute(sql_procedure, params)

This would imply that you have the parameter list in this way:

params = [
    CLIENTE,
    CLIENTEOCA,
    # ...
]

Instead of writing %s for each one, you can format it according to the total number of parameters. For example:

>>> sql = '''CALL INSERTAR_OPORTUNIDAD(%s)'''
>>> params = [10,20,30,40]
>>> sql % ','.join(['%s' for p in params])
'CALL INSERTAR_OPORTUNIDAD(%s,%s,%s,%s)'

If we put the above in your script, you would have:

params = [
    CLIENTE,
    CLIENTEOCA,
    # ...
]

# Por cada parámetro en params se crea su respectivo %s
sql_procedure = 'CALL INSERTAR_OPORTUNIDAD(%s)' % ','.join(['%s' for p in params])

cursor_procedure= conMysql.cursor()
cursor_procedure.execute(sql_procedure, params)
cursor_procedure.commit()
cursor_procedure.close()