Python insert Dict into sqlite3

December 19, 2021

I have a sqlite3 database where the first column is the id and set as primary key with auto increment. I’m trying to insert the values from my python dictionary as such:

value = {'host': [], 'drive': [], 'percent': []}
soup = bs(contents, 'html.parser')
for name in soup.find_all("td", class_="qqp0_c0"):
    hostname = name.parent.find('td').get_text()
    drive = name.parent.find('td', class_="qqp0_c1").get_text()
    used_percent = name.parent.find('td', class_="qqp0_c5").get_text()
    value['host'].append(hostname)
    value['drive'].append(drive)
    value['percent'].append(used_percent)
    #cur.executemany("INSERT INTO scrap VALUES (?, ?, ?)", hostname, drive, used_percent)
    cur.execute("INSERT INTO scrap VALUES (?, ?, ?);", value)

I keep getting errors, my latest error seems to imply it needs an id value:

cur.execute("INSERT INTO scrap VALUES (?, ?, ?);", value)
sqlite3.OperationalError: table scrap has 4 columns but 3 values were supplied

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

Do I need to supply an id number?

This is the db schema:

CREATE TABLE scrap (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hostname VARCHAR(255),
    drive VARCHAR(255),
    perc VARCHAR(255)
       );

>Solution :

If the id column is auto-incrementing you don’t need to supply a value for it, but you do need to "tell" the database that you aren’t inserting it. Note that in order to bind a dictionary, you need to specify the placeholders by name:

cur.execute("INSERT INTO scrap (hostname, drive, perc) VALUES (:host, :drive, :percent);", value)

EDIT:
Following up on the discussion from the comments – the value dictionary should map placeholder names to their intended values, not list containing them:

soup = bs(contents, 'html.parser')
for name in soup.find_all("td", class_="qqp0_c0"):
    hostname = name.parent.find('td').get_text()
    drive = name.parent.find('td', class_="qqp0_c1").get_text()
    used_percent = name.parent.find('td', class_="qqp0_c5").get_text()
    value = {'host': hostname, 'drive': drive, 'percent': used_percent}  
    cur.execute("INSERT INTO scrap (hostname, drive, perc) VALUES (:host, :drive, :percent);", value)