i get TypeError: function takes exactly 2 arguments (3 given), when i convert show_id and genre into list of tuples, i get ValueError: paramenter are of unsupported type.
import sqlite3
import csv
# create database
conn = sqlite3.connect("favorites8.db")
open("favorites8.db", "w")
db = conn.cursor()
# create table
db.execute("CREATE TABLE shows (id INTEGER AUTO INCREMENT, title TEXT NOT NULL, PRIMARY KEY (id))")
db.execute("CREATE TABLE genres (shows_id INTEGER, genre TEXT NOT NULL, FOREIGN KEY (shows_id) REFERENCES shows(id))")
# open csv file
with open("favorites.csv", "r") as file:
# create dictreader
reader = csv.DictReader(file)
# iterate over csv file
for row in reader:
# canonicalize title
title = row["title"].strip().upper()
# insert title
stmnt1 = "INSERT INTO shows (title) VALUES(?)"
show_id = db.execute(stmnt1, (title,))
# Insert genres
for genre in row["genres"].split(", "):
stmnt2 = "INSERT INTO genres (shows_id, genre) VALUES(?, ?)"
db.executemany(stmnt2, show_id, genre)
# commit changes
conn.commit()
conn.close()
# end with
CodePudding user response:
I've fixed some mistakes and cleaned this up a bit:
conn = sqlite3.connect("favorites8.db")
db = conn.cursor()
db.execute("CREATE TABLE shows (id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT NULL)")
db.execute("CREATE TABLE genres (shows_id INTEGER, genre TEXT NOT NULL, FOREIGN KEY (shows_id) REFERENCES shows(id))")
with open("favorites.csv", "r") as file:
reader = csv.DictReader(file)
for row in reader:
title = row["title"].strip().upper()
stmnt1 = "INSERT INTO shows (title) VALUES(?)"
db.execute(stmnt1, (title,))
show_id = db.lastrowid
# Insert genres
data = []
stmnt2 = "INSERT INTO genres (shows_id, genre) VALUES(?, ?)"
for genre in row["genres"].split(", "):
data.append((show_id, genre))
db.executemany(stmnt2, data)
# commit changes
conn.commit()
conn.close()
There were a bunch of issues:
executemany
accepts iterable as a second argument.- First execute statement does not return id, but cursor object, you need to retrieve it manually.