CODEWITHSUNDEEP
pythonmysql
Yerbol
Yerbol

Reputation: 39

not enough arguments for format string from excel to mysql

I'm trying to put all information from excel to mysql, while processing it have these problem. struggling to solve it! counted all %s, seems like didn't miss any of them. 

query = """INSERT INTO sanction (id, organization_type, organization, date, decision_number, penalty_type, penalty_way
                       penalty, violation, execution_period, article, note, type_npa, department, uploaded_date) 
                        VALUES(null, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""


for r in range(1, sheet.nrows):
    organization_type = sheet.cell(r,1).value
    organization = sheet.cell(r,2).value
    date = sheet.cell(r,3).value
    decision_number = sheet.cell(r,4).value
    penalty_type = sheet.cell(r,5).value
    penalty_way = sheet.cell(r,6).value
    penalty = sheet.cell(r,7).value
    violation = sheet.cell(r,8).value
    execution_period = sheet.cell(r,9).value
    article =sheet.cell(r,10).value
    note =sheet.cell(r,11).value
    type_npa =sheet.cell(r,12).value
    department =sheet.cell(r,13).value
    uploaded_date =datetime.now().strftime("%Y-%m-%d %H:%M") 


    values = (organization_type, organization, date, decision_number, penalty_type, 
                  penalty_way,penalty, violation, execution_period,article, note, type_npa, department,uploaded_date)


    mycursor.execute(query, [values])

Upvotes: 1

Views: 57

Answers (1)

Ralf
Ralf

Reputation: 16505

I notice 2 things that could cause this error:

  1. Your variable values is a tuple already, so you dont need to wrap it inside a new list.

    That means, change this line

    mycursor.execute(query, [values])

    to

    mycursor.execute(query, values)

  2. You are also missing a comma in your query in the part where you list the target column names, between penalty_way and penalty.

In case of this many arguments, I would suggest to restructure your code so that you can more easily see if you missed anything.

For example, here is a version that groups the 15 parameters in a 1-3-3-3-3-2 formation in 3 parts: the first part of the query, the second part of the query and also when building the values tuple.

query = """
    INSERT INTO sanction (
        id,
        organization_type, organization, date,
        decision_number, penalty_type, penalty_way,
        penalty, violation, execution_period,
        article, note, type_npa,
        department, uploaded_date)
    VALUES (
        null,
        %s, %s, %s,
        %s, %s, %s,
        %s, %s, %s,
        %s, %s, %s,
        %s, %s)
"""

for r in range(1, sheet.nrows):
    organization_type = sheet.cell(r, 1).value
    organization = sheet.cell(r, 2).value
    date = sheet.cell(r, 3).value
    decision_number = sheet.cell(r, 4).value
    penalty_type = sheet.cell(r, 5).value
    penalty_way = sheet.cell(r, 6).value
    penalty = sheet.cell(r, 7).value
    violation = sheet.cell(r, 8).value
    execution_period = sheet.cell(r, 9).value
    article = sheet.cell(r, 10).value
    note = sheet.cell(r, 11).value
    type_npa = sheet.cell(r, 12).value
    department = sheet.cell(r, 13).value
    uploaded_date = datetime.now().strftime("%Y-%m-%d %H:%M")

    values = (
        # the first value of the INSERT statement will be NULL
        organization_type, organization, date,              # 3 elements
        decision_number, penalty_type, penalty_way,         # 3 elements
        penalty, violation, execution_period,               # 3 elements
        article, note, type_npa,                            # 3 elements
        department, uploaded_date,                          # 2 elements
    )
    mycursor.execute(query, values)

Upvotes: 1

Related Questions