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
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)
);
CodePudding user response:
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)