converting .csv to db in python withsqlite3

Question

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

Timestamp,title,genres 10/28/2019 15:03:45,Dynasty,"Drama, Family" 10/28/2019 15:04:48,The Office,Comedy 10/28/2019 15:09:00,Blindspot,"Action, Crime, Drama" 10/28/2019 15:09:02,24,Action 10/28/2019 15:09:02,Friends,Comedy 10/28/2019 15:09:05,psych,Comedy 10/28/2019 15:09:09,Veep,Comedy 10/28/2019 15:09:09,Survivor,Reality-TV ..... — AbuAminu
– AbuAminu, Commented Aug 7, 2022 at 13:17
db.executemany(stmnt2, show_id, genre) should be db.execute(stmnt2, (show_id, genre)). executemany is for when you want to insert / update / delete multiple sets of parameters in a single statement. — snakecharmerb
– snakecharmerb, Commented Aug 7, 2022 at 15:28
This also generates sqlite3.InterfaceError: error binding parameter 0 - probably unsupported type. — AbuAminu
– AbuAminu, Commented Aug 7, 2022 at 18:33

funnydman · Accepted Answer · 2022-08-07 19:10:18Z

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.

Thanks, this works as expected, though i have to comment the first conn.commit(). Thanks again.
@AbuAminu change to CREATE TABLE shows (id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT NULL) this is the correct way

Collectives™ on Stack Overflow

converting .csv to db in python withsqlite3

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related