Python and sqlite3 : insert matrix into table
Question: Let's say i have this matrix:

    
    
    [[[u'artist1'], [u'song1']], [[u'artist2'], [u'song2']], [[u'artist3'], [u'song3']]]
    

I was wondering how can i insert it into a sql table so it will look like
this:

    
    
    ID | ARTIST |SONG
    -----------------
    1 | artist1 | song1
    2 | artist2 | song2
    3 | artist3 | song3
    

I have about 1 million artists and 1 million songs i have to insert so it's
just an example :P

Thanks in advance!

Source code:

    
    
    import sqlite3
    import numpy as np
    print('Opening SQL Database')
    sql = sqlite3.connect('mblite_post.db')
    cur = sql.cursor()
    cursor=cur.execute("SELECT entity0 from r_artist_release LIMIT 500000000000000 ")
    result_author = [row[0] for row in cur.fetchall()]
    
    cursor=cur.execute("SELECT entity1 from r_artist_release LIMIT 500000000000000 ")
    result_song = [row[0] for row in cur.fetchall()]
    
    
    artistlist=[]
    for x in result_author:
        y=cur.execute("SELECT name FROM artist_name where id='%s'" % x)
        artistname=[row[0] for row in cur.fetchall()]
        artistlist.append(artistname)
    songlist=[]
    for y in result_song:
        z=cur.execute("SELECT name FROM release_name where id='%s'" % y)
        songname=[row[0] for row in cur.fetchall()]
        songlist.append(songname)
    
    
    
    matrix2 = [[artistlist[i], songlist[i]] for i in range(len(artistlist))]
    
    print(len(matrix2))
    sql2 = sqlite3.connect('itaidb.db')
    cur2 = sql2.cursor()
    cur2.execute('CREATE TABLE IF NOT EXISTS main (artist TEXT,song TEXT )')
    
    
    for i, v in enumerate(matrix2):
        cur2.execute('INSERT INTO main VALUES (?,?)', (v[0][0], v[1][0]))
        sql2.commit()
    
    
    
    #
    #
    # for ton in songlist:
    #     cur2.execute("UPDATE main SET (song) values (?) WHERE id='%r'", [''.join(ton)] % integer)
    #     sql2.commit()
    #     integer=integer+1
    #
    sql2.close()
    sql.close()
    
    # artistname=[]
    # for x in author:
    #     y=cur.execute("SELECT name FROM artist_name where id='%s'" % x)
    #     artistname=artistname.append(list(y.fetchall()))
    #
    # print artistname
    


Answer: I suggest you create your list like this:

    
    
    yourlist = [(artistlist[i], songlist[i]) for i, v in enumerate(artistlist)]
    

Then you can use a for loop to execute the `INSERT` statements:

    
    
    for i, v in enumerate(yourlist, start=1):
        cursor.execute('INSERT INTO yourtable VALUES (?,?,?)', (i, v[0], v[1]))
    

And don't forget to commit afterwards:

    
    
    db.commit()
    

