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