how to fill a database table using a for loop in postgresql?

Question

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?

Answer 1

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.

Answer 2

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}
        )