import psycopg2
import random
import MySQLdb


try:
    conn = psycopg2.connect("dbname='musicbrainz' user='musicbrainz' host='localhost' password='musicbrainz'")
    conn1 = MySQLdb.connect(host = "localhost", user = "root", passwd = "40OZlike", db = "plalyst")
    conn2 = MySQLdb.connect(host = "localhost", user = "root", passwd = "40OZlike", db = "plalyst")
    print("connections made...")
    cur = conn.cursor()
    cur1 = conn1.cursor()
    cur2 = conn2.cursor()
    print("cursors created....")
    rnumbers = random.sample(range(1, 22660511), 100000)
    print("Random numbers generated...")
    for eachnum in rnumbers:
        print(eachnum)
        songName=""
        while(songName==""):
            cur.execute("""select name from track where id = %s """, (eachnum,))
            rows = cur.fetchall()
            print(rows)
            if not len(rows)==0:
                songName = rows[0][0]
            eachnum+=1
        print("Got the track name:")
        print(songName)
        sql = "INSERT into Song (name) values ( '"+songName+"')"
        print(sql)
        cur1.execute(sql)
        cur1.execute('commit')
        print("inserted into the song table....")
        cur.execute("""select distinct t.name from track tr
            join recording r
            on tr.recording = r.id
            join recording_tag rt
            on rt.recording = r.id
            join tag t
            on
            rt.tag= t.id
            where tr.name = %s
            order by t.name""", (rows[0][0],))
        print("got the tag names....")
        rows = cur.fetchall()
        print(rows)
        for row in rows:
            print(row)
            qry = "select * from Tag where name='"+row[0]+"'"
            print(qry)
            cur2.execute(qry)
            row2 = cur2.fetchall()
            print("getting if the tag is already in")
            if (len(row2)==0):
                print("taag not present...")
                qry1 = "INSERT INTO Tag (name) VALUES ('"+row[0]+"')"
                print(qry1)
                cur1.execute(qry1)
                cur1.execute('commit')
                print("inserted into the tag table..")
            print("getting song id...")
            qry2 = "select id from Song where name='"+songName+"'"
            print(qry2)
            cur2.execute(qry2)
            songId = cur2.fetchall()[0][0]
            print(row[0])
            qry3 = "select id from Tag where name='"+row[0]+"'"
            print(qry3)
            #done till here problem with no no tag
            cur2.execute(qry3)
            print(cur2.fetchall())
            tagId = cur2.fetchall()[0][0]
            qry1 = "INSERT INTO SongTag VALUES ('"+songId+"','"+tagId+"')"
            cur2.execute(qry4)
            print("inserted into the songtag table..")
            gc.collect()
    cur.close()
    cur1.close()
    cur2.close()
    conn.close()
    conn1.close()
    conn2.close()
except:
    conn1 = MySQLdb.connect(host = "localhost", user = "root", passwd = "40OZlike", db = "plalyst")
    cur = conn1.cursor()
    cur.execute("SET FOREIGN_KEY_CHECKS = 0;")
    cur.execute("Truncate table Song")
    cur.execute("Truncate table Tag")
    cur.execute("Truncate table SongTag")
    cur.execute("SET FOREIGN_KEY_CHECKS = 1;")
    cur.close()
    conn1.close()
    print(e)