CS50 SQL Library use outside of CS50 IDE

Question

I am trying to create a SQlite database following Dr Malan's class example. The difference is that I am doing it outside of the CS50 IDE. For some reason the below code does actually create the .db file, it does not create any content in the database. I suspect the issue is with line;

db = SQL(r"sqlite:///shows3.db")

I have tried many other versions as well, but can not get the code to do anything, except for creating the empty shows.db file. In the above line, do I need to specify the location of where sqlite is installed on my machine?

Any help would be appreciated.

Regards

Merwe

import csv
from cs50 import SQL

open(r"C:\sqlite\shows3.db","w").close()
db = SQL(r"sqlite:///shows3.db")
db.execute("CREATE TABLE shows_2 (id INTEGER, title TEXT, PRIMARY KEY(id))")
# db.execute('CREATE TABLE genres (show_id INTEGER, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id)')
# db.execute('CREATE TABLE genres (genre TEXT)')


with open (r'C:\Users\Admin\Desktop\Python\Repository\5_SQL Interaction\csv list & dict\shows.csv') as file:
    reader = csv.DictReader(file, delimiter = ";")
    for row in reader:
        title = row['show'].upper().strip()
        id = db.execute('INSERT INTO shows_2 (title) VALUES(?)', title)
        
#         for genre in row['genre'].split(","):
#             db.execute('INSERT INTO genres (show_id, genre) VALUES (?, ?)', id, genre)
            
            
    

Answer 1

Managed to find the solution. You need to use the absolute reference as per below; db = SQL("sqlite:///C:/sqlite/shows1.db")

import csv
from cs50 import SQL

open(r"C:\sqlite\shows1.db","w").close()
db = SQL("sqlite:///C:/sqlite/shows1.db")
db.execute("CREATE TABLE shows_1 (id INTEGER, title TEXT, PRIMARY KEY(id));")
db.execute('CREATE TABLE genres_1 (show_id INTEGER, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows_1(id))')


with open (r'C:\Users\Admin\Desktop\Python\Repository\5_SQL Interaction\csv list & dict\shows.csv') as file:
    reader = csv.DictReader(file, delimiter = ";")
    for row in reader:
        title = row['show'].upper().strip()
        id = db.execute('INSERT INTO shows_1 (title) VALUES(?)', title)
        
        for genre in row['genre'].split(","):
            print(genre)
            db.execute("INSERT INTO genres_1 (show_id, genre) VALUES (?, ?)", id, genre)