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

153
March 12, 2021, at 11:30 AM

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).

Answer 1

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'

READ ALSO
Is there a way to draw data from only business dates in dygraphs?

Is there a way to draw data from only business dates in dygraphs?

I am trying to draw candlestick plots with dygraph in RThe problem is the holidays and weekends leave blank spaces in the plot

103
How to make sub navbar two to three columns

How to make sub navbar two to three columns

Hi there I am trying to clone the navbar of cointelegraphcom, however, I'm not good at CSS this is the photo of What I'm trying to accomplishcointelegraph

90
Hide or remove user&#39;s sensitive information in pdf file using NodeJS [closed]

Hide or remove user's sensitive information in pdf file using NodeJS [closed]

Want to improve this question? Update the question so it focuses on one problem only by editing this post

97
how to import Jquery and Popper js in vue

how to import Jquery and Popper js in vue

i tried to install Bootstrap, popper js and jquery on my project using clii did

134