cursor.fetchall() brings only one row in Python with PostgreSQL

Question

I am trying to create a training app in python to work with a database of movies, adding movie details via a text menu prompting user input for all fields (movie name, actors, company, etc.). I am using PostgreSQL as the database and import psycopg2 in Python.

From user input, I am collecting data which I then want to store in my database tables 'movies' and 'actors'. For one movie, there are several actors. I have this code:

def insert_movie(name, actors, company, year):
    connection = psycopg2.connect(user='postgres', password='postgres', database='movie')
    cursor = connection.cursor()

    query1 = "INSERT INTO movies (name, company, year) VALUES (%s, %s, %s);"
    cursor.execute(query1, (name, company, year))
    movie_id = cursor.fetchone[0]
    print(movie_id)
    query2 = 'INSERT INTO actors (last_name, first_name, actor_ordinal) VALUES (%s, %s, %s);'
    for actor in actors:
        cursor.execute(query2, (tuple(actor)))
    rows = cursor.fetchall()
    actor_id1 = [row[0] for row in rows]
    actor_id2 = [row[1] for row in rows]
    print(actor_id1)
    print(actor_id2)

    connection.commit()
    connection.close()

This works great for printing movie_id after query1. However for printing actor_id2, I get IndexError: list index out of range.

If I leave only actor_id1 in query3 like this:

query2 = 'INSERT INTO actors (last_name, first_name, actor_ordinal) VALUES (%s, %s, %s);'
for actor in actors:
    cursor.execute(query2, (tuple(actor)))
rows = cursor.fetchall()
actor_id1 = [row[0] for row in rows]
print(actor_id1)

, I get printed the following result:

movie_id --> 112
actor2_id --> 155

The problem that I cannot retrieve actor1_id with this code, which is 154.

Can anyone help with using fetchall correctly here?

this can help you "http://initd.org/psycopg/docs/cursor.html#cursor.fetchall". but why are you inserting two id's "actor_id1" "actor_id2" in one table "actors" — M_x, Jan 21 '20 at 08:58

score 2 · Accepted Answer · answered Jan 21 '20 at 16:20

OK, I have found out the answer. The fetch should be used inside the loop as we should execute fetch for every row and not after the whole query for all rows altogether:

query2 = 'INSERT INTO actors (last_name, first_name, actor_ordinal) VALUES (%s, %s, %s);'
actor_ids = []
for actor in actors:
    cursor.execute(query2, (tuple(actor)))
    actor_id = cursor.fetchone()[0]
    actor_ids.append(actor_id)
print(actor_ids)

cursor.fetchall() brings only one row in Python with PostgreSQL

1 Answers1