artist_name = ['Madonna', 'Slayer', 'Disturbed', 'Michael Jackson', 'Katty Parry']
with conn.cursor() as cur:
for artists in artist_name:
id_num = 0
id_num += 1
cur.execute(f"""INSERT INTO Artist (Id, Name)
VALUES ('{id_num}', '{artists}')
ON CONFLICT DO NOTHING""");
The loop adds only the first element of the list to the database, assigning it id = 1. How to add the entire list to the database?
The problem with your code is here:
for artists in artist_name:
id_num = 0
id_num += 1
Note how id_num gets reset on every iteration of the loop. That means that, for every entry in your list, id_num will always be 1 and triggers the ON CONFLICT clause of your query, leaving the first record untouched.
Instead, pull the counter outside of the loop:
id_num = 0
for artists in artist_name:
id_num += 1
In addition, you should note that using string interpolation (f-strings) here is not a safe way to build queries. This is open to SQL Injection, which is a serious issue. Even though you're not exposed to outside data sources here, it's best to use parameterization from the start:
artist_name = ['Madonna', 'Slayer', 'Disturbed', 'Michael Jackson', 'Katty Parry']
with conn.cursor() as cur:
id_num = 0
for artists in artist_name:
id_num += 1
cur.execute(
"""
INSERT INTO Artist (Id, Name)
VALUES (:id_num, :artists)
ON CONFLICT DO NOTHING
""",
{'id_num': id_num, 'artists': artists}
)
I'd try this:
artist_name = ['Madonna', 'Slayer', 'Disturbed', 'Michael Jackson', 'Katty Parry']
with conn.cursor() as cur:
id_num = 0
for artists in artist_name:
id_num += 1
cur.execute(f"""INSERT INTO Artist (Id, Name)
VALUES ('{id_num}', '{artists}')
ON CONFLICT DO NOTHING""");
Let me spell out the difference for you: I moved the initialization of id_num outside the loop.
Some responders feel like SQL injection is key here. I agree that it's important, but your first problem is to solve your INSERT issue. Once you've done so, perhaps you could read this to understand the problem better.
