import sqlite3
conn = sqlite3.connect('academic_papers_populated.db')

def apaReference(ArticleId):
    SQL = '''SELECT AuthorNumber, LastName, Initials, Year, Title, Name  
             FROM Article as p, Journal as j, Author as a, Article_Authors as b
             WHERE p.JournalID = j.JournalID
             AND p.ArticleID = b.ArticleID
             AND b.AuthorID = a.AuthorID
             AND p.Articleid = :id
             ORDER BY AuthorNumber;'''

    cursor = conn.cursor()
    record = cursor.execute(SQL,{'id':ArticleId}).fetchall()
    cursor.close()
    if len(record) ==0:
        raise Exception("Invalid Article")
    else:
        ref = ''
        count = 0
        for row in record:
            ref = ref + row[1]+', '+row[2]
            count += 1
            if count < len(record):
                if count + 1 < len(record):
                    ref = ref +', '
                else:
                    ref = ref +', & '
        ref = ref + ' (' + str(record[0][3]) + ') '+ record[0][4]+ '. ' +record[0][5]+'.'
        return ref