CODEWITHSUNDEEP
pythonmysqlmysql-python
TMac
TMac

Reputation: 117

MySQLdb._exceptions.ProgrammingError: not enough arguments for format string

I am trying to take a list of dictionaries, which is the return from a job's board API, and insert that data into a SQL table for one of my teammates to access. The issue that I am running into is that when I run my script to read through the dictionaries one at a time and insert them into the MySQLDb Table I get through some number of runs before I hit an error about not enough arguments for the format string. This doesn't make much sense to me, both due to my lack of experience but also, because I set up the number of arguments based on len(dict). Any ideas where I got off track?

My code:

#!/usr/bin/env python3


def refresh_remote(jobs=""):
    import MySQLdb

    dB = MySQLdb.connect("localhost","******", "******", "BoomTown")
    cursor = dB.cursor()
    cursor.execute("TRUNCATE TABLE remote_jobs");

    for job in jobs:
        placeholders = ', '.join(['%s'] * len(job))
        columns = ', '.join(job.keys())
        sql = "INSERT INTO remote_jobs ( %s ) VALUES ( %s )" % (columns, placeholders)
        vls = []
        for v in job.values():
            try:
                str(v).encode(encoding='utf-8').decode('ascii')
                if str(v) == "":
                    v = 'NULL'
                else:
                    v = str(v)
                vls.append(v)
            except UnicodeDecodeError:
                pass
        print(vls)
        print(sql)
        cursor.execute(sql, vls)

    dB.close()

    print("All done with remote jobs table")

if __name__ == '__main__':
    """
    This will be our main guard which will import
    gaurd our methods for dropping and refreshing the
    tables in the SQL DB
    """
    from getRemoteJobs import remote_jobs_get

    """
    First we'll need to get remote jobs via the API
    call to remotework, then we will send the data
    as a list of dicts to refresh_remote to be put
    into a SQL table
    """
    remote_jobs = remote_jobs_get()
    refresh_remote(remote_jobs)

The error:

Traceback (most recent call last):
  File "/home/tulsaboomtown/.local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 201, in execute
    query = query % args
TypeError: not enough arguments for format string

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./refresh_jobs_db.py", line 49, in <module>
    refresh_remote(remote_jobs)
  File "./refresh_jobs_db.py", line 28, in refresh_remote
    cursor.execute(sql, vls)
  File "/home/tulsaboomtown/.local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 203, in execute
    raise ProgrammingError(str(m))
MySQLdb._exceptions.ProgrammingError: not enough arguments for format string

I am seeing what I think is good output from those print statements I have:

['Business Development Representative (BDR)', 'Brightback', 'full_time', '2021-03-09T21:42:29', 'USA Only', 'https://remotive.io/remote-jobs/business/business-development-representative-bdr-483643', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Customer Success Champion', 'Geckoboard', 'NULL', '2021-03-09T19:05:49', 'Pacific Timezone', 'https://remotive.io/remote-jobs/customer-support/customer-success-champion-516576', 'full_time']
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Translations Clinical Quality Auditors (Italian/English)', 'International SOS', 'full_time', '2021-03-09T13:40:12', 'Italy only', 'https://remotive.io/remote-jobs/all-others/translations-clinical-quality-auditors-italian-english-487628', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Managing Editor', 'Testlio', 'full_time', '2021-03-09T13:40:07', 'USA Only', 'https://remotive.io/remote-jobs/writing/managing-editor-512640', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Copy Editor', 'Raspberry Pi Foundation', 'full_time', '2021-03-09T13:40:06', 'UK Only', 'https://remotive.io/remote-jobs/writing/copy-editor-515941', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Lead Software QA Engineer', 'Swapcard', '$75K - $95K', '2021-03-09T09:14:27', 'CET Time Zone', 'https://remotive.io/remote-jobs/qa/lead-software-qa-engineer-515914', 'full_time']
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Senior C++ Developer', 'Flightradar24', 'NULL', '2021-03-09T05:58:10', 'https://remotive.io/remote-jobs/software-dev/senior-c-developer-515106', 'full_time']
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )

But when I check remote_jobs in the sqldb after a run there is nothing in the table (empty set).

Upvotes: 1

Views: 1325

Answers (1)

JD2775
JD2775

Reputation: 3801

Take a look at one example from your output:

INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Senior C++ Developer', 'Flightradar24', 'NULL', '2021-03-09T05:58:10', 'https://remotive.io/remote-jobs/software-dev/senior-c-developer-515106', 'full_time']

Your INSERT is expecting 7 values to be inserted, but you are only providing 6 in this instance. Looks like you are missing values for 'candidate_required_location'

Upvotes: 0

Related Questions