sanjeevai

query = "SELECT firstName, lastName FROM user_info WHERE song = 'All Hands \
    Against His Own'"
rows = session.execute(query)
for row in rows:
    print(row.firstname, row.lastname)

### OUTPUT
### firstName, lastName
Jacqueline, Lynch
Tegan, Levine

sanjeevai

# transformation and loading part of the ETL
file = 'event_datafile_new.csv'

with open(file, encoding='utf8') as f:
    csvreader = csv.reader(f)
    next(csvreader)  # skip header

    for line in csvreader:

        # Assign the INSERT statements into the `query` variable

sanjeevai

query3 = "CREATE TABLE IF NOT EXISTS user_info "
query3 = query3 + "(userId int, firstName text, lastName text, song text, \
    PRIMARY KEY (song, userId))"
session.execute(query3)

sanjeevai

query = "SELECT * FROM user_artist_relation WHERE userId = 10 AND sessionId = \
    182"
rows = session.execute(query)
for row in rows:
    print(row.artist, row.song, row.firstname, row.lastname, row.iteminsession)

### OUTPUT
### artist, song, firstname, .lastname, iteminsession
>>>
Down To The Bone, Keep On Keepin' On, Sylvie, Cruz, 0

sanjeevai

file = 'event_datafile_new.csv'

with open(file, encoding='utf8') as f:

    csvreader = csv.reader(f)
    next(csvreader)  # skip header

    for line in csvreader:

        # Assign the INSERT statements into the `query` variable

sanjeevai

query2 = "CREATE TABLE IF NOT EXISTS user_artist_relation "
query2 = query2 + "(artist text, song text, firstName text, lastName text, \
    userId int, sessionId int, itemInSession int, \
    PRIMARY KEY ((userId, sessionId), itemInSession))"
session.execute(query2)

sanjeevai

query = "SELECT * FROM discography WHERE sessionId = 338 AND itemInSession = 4"
rows = session.execute(query)
for row in rows:
    print(row.artist, row.song, row.length, row.sessionid, row.iteminsession)

### OUTPUT
### artist, song, length, sessionId, itemInSession
>>> Faithless, Music Matters (Mark Knight Dub), 495.30731201171875, 338       4

sanjeevai

file = 'event_datafile_new.csv'

with open(file, encoding = 'utf8') as f:

    csvreader = csv.reader(f)
    next(csvreader) # skip header
    for line in csvreader:
        
        # Assign the INSERT statements into the `query` variable
        query = "INSERT INTO discography (artist, song, length, sessionId, \

sanjeevai

query1 = "CREATE TABLE IF NOT EXISTS discography "
query1 = query1 + "(artist text, song text, length float, sessionId int, \
    itemInSession int, PRIMARY KEY (sessionId, itemInSession))"
session.execute(query1)

sanjeevai

conn = psycopg2.connect("host=127.0.0.1 dbname=studentdb user=#### password=####")
cur = conn.cursor()