Getting error on inserting tuple values in postgreSQL table using python

Question

Hello, I want to keep last.fm's user recent music tracks list to postgresql database table using pylast interface.But when I tried to insert values to the table it shows errors.Code example:

import pylast
import psycopg2
import re
from md5 import md5
import sys
import codecs
import psycopg2.extensions

psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
user_name = raw_input("Enter last.fm username: ")
user_password = raw_input("Enter last.fm password: ")

api_key = '*********'
api_secret = '********'

#Lastfm network authentication
md5_user_password = md5(user_password).hexdigest()
network = pylast.get_lastfm_network(api_key, api_secret,user_name,md5_user_password)

used=pylast.User(user_name, network)

recent_tracks=used.get_recent_tracks(10)

# Database connection 
try:
    conn=psycopg2.connect("dbname='**' user='postgres' host='localhost'   password='*'")
    conn.set_client_encoding('UNICODE')
except:
    print "I am unable to connect to the database, exiting."
    sys.exit()
cur=conn.cursor()

for i, artist in enumerate(recent_tracks):
    for key in sorted(artist):

        cur.execute("""
        INSERT INTO u_recent_track(Playback_date,Time_stamp,Track) 
        VALUES (%s,%s,%s)""",  (key, artist[key]))

    conn.commit()
cur.execute("SELECT * FROM u_recent_track;")
cur.fetchone()
for row in cur:
    print '   '.join(row[1:])

cur.close()
conn.close()

Here "recent_tracks" tuple have the values for example:

artist 0
  - playback_date : 5 May 2010, 11:14
  - timestamp : 1273058099
  - track         : Brian Eno - Web

I want to store these value under u_recent_track(Tid,Playback_date,Time_stamp,Track).Can anybody have idea how to sort out this problem? when I tried to run, it shows error:

Traceback (most recent call last):
  File "F:\JavaWorkspace\Test\src\recent_track_database.py", line 50, in <module>
    VALUES (%s,%s,%s)""",  (key, artist[key]))
IndexError: tuple index out of range

Thanks in advanced!

Answer 1

(Playback_date,Time_stamp,Track) indicates you want to insert three values into a row. VALUES (%s,%s) should therefore be VALUES (%s,%s,%s) and (key, artist[key]) should be a tuple with 3 elements, not 2.

Try:

for track in recent_tracks:
    cur.execute("""
        INSERT INTO u_recent_track(Playback_date,Time_stamp,Track) 
        VALUES (%s,%s,%s)""",  (track.get_date(), track.get_timestamp(), track.get_track()))
    conn.commit()

PS. This is where I'm getting my information about the pylast API.

PPS. If my reading of the documentation is correct, track.get_track() will return a Track object. It has methods like get_album, get_artist, get_id and get_title. Exactly what do you want stored in the Track column of the u_recent_track database table?

Answer 2

sorted(artist) returns a ordered list of artist, when you're iterating over it it returns still elements of artist. So when you're trying to access artist[key] it is actually trying to access an element of artist indexed by the index, which is an element of artist itself. Tuples do not work this way.

It seems you're using python2.5 or lower and therefore you could do:

cur.executemany("""
        INSERT INTO u_recent_track(Playback_date,Time_stamp,Track) 
        VALUES (%(playback_date)s,%(timestamp)s,%(track)s)""",  recent_tracks)
conn.commit()

This should work.

Answer 3

This error isn't anything to do with Postgres, but with the artist variable. You're firstly saying:

for key in sorted(artist):

implying that it's a list, but then you're accessing it as if it were a dictionary, which is raising an error. Which is it? Can you show an example of the full contents?