IndexError: tuple index out of range connecting Python to PostgreSQL

Question

I know this question has been asked a number of times, but i am stuck here unable to proceed further. I am executing a for loop in python to load data to fact table.

I am executing the below code

for index, row in df.iterrows():

# get songid and artistid from song and artist tables
cur.execute(song_select, (row.song, row.artist, row.length))
results = cur.fetchone()

if results:
    song_id, artist_id = results
else:
    song_id, artist_id = None, None

# insert songplay record

songplay_data = (pd.to_datetime(row.ts, unit='ms'),row.userId,row.level,song_id,artist_id,row.sessionId,row.location,row.userAgent)
cur.execute(songplay_table_insert, songplay_data)
conn.commit()

and getting the error

<ipython-input-22-b8b0e27022de> in <module>()
 13 
 14     songplay_data = (pd.to_datetime(row.ts, unit='ms'),row.userId,row.level,song_id,artist_id,row.sessionId,row.location,row.userAgent)
 15     cur.execute(songplay_table_insert, songplay_data)
 16     conn.commit()
 IndexError: tuple index out of range

My table i am trying to insert is

songplay_table_insert = ("""INSERT INTO songplays (songplay_id, start_time, 
user_id, level, song_id, artist_id, session_id, location, user_agent )
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)

I am really stuck, any help appreciated.

Alex Hall · Accepted Answer · 2019-04-14 19:19:11Z

1

You have one too many %s markers.

VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)

has 9 markers, while

songplay_data = (pd.to_datetime(row.ts, unit='ms'),row.userId,row.level,song_id,artist_id,row.sessionId,row.location,row.userAgent)

has 8 elements. When it tries to evaluate the last marker, it looks for the 9th element, i.e. songplay_data[8], and that raises the error.

You will also need to remove songplay_id from the SQL to make the INSERT statement valid. The database should be generating the primary key for you if you don't have a value to provide, if not we should take a look at your table definition.

answered Apr 14, 2019 at 19:19

Alex Hall

36.2k5 gold badges63 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arun.K Over a year ago

Thank you Alex Hall. I changed the INSERT statement and it worked.

Collectives™ on Stack Overflow

IndexError: tuple index out of range connecting Python to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related