"""
You need a couple of packages to query the database and write a CSV file
"""

import psycopg2
import csv

"""
You will need this database name and host information to create a connection to the database
"""
database_config = {
    'dbname': 'wos',
    'host': 'idb1'
}

"""
This is a list of names we are searching for. Feel free to edit the names to find publications from researchers you are interested in
"""
author_names = [
    'Dearborn, Dylanne',
    'Fortin, Marcel',
    'Handren, Kara',
    'Schultz, Michelle Kelly',
    'Trimble, Leanne',
]

"""
This section of code uses the psycopg2 package to connect to the database
"""
con = psycopg2.connect(**database_config)
cur = con.cursor()

"""
This executes a SQL statement that creates a temporary table with our list of author names 
"""
cur.execute('CREATE TEMPORARY TABLE _author (name TEXT)')
for name in author_names:
    cur.execute('INSERT INTO _author VALUES (%s)', (name,))

"""
This SQL statement joins our list of names with the database author table to filter the results to only the authors we are looking for. This is a more efficient approach than looping through author names and running multiple SELECT statements
"""
cur.execute("SELECT wos_id, full_name FROM author INNER JOIN _author ON author.full_name ILIKE '%'||_author.name||'%'")

"""
This next section of code goes line by line through the results and adds them to a dictionary data type in python, where the publication id for the author is the key and the name of the author is the value. It also prints it out so you can see the data.
"""
mylist = dict()
while result := cur.fetchone():
    print(result)
    mylist[result[0]] = result[1]

"""
This next section sets up a CSV that we will use to store the results of our final query
"""
with open('myfirstpythonresults.csv', mode='w', encoding='UTF8', newline='') as csv_file:
    myheader = ['title']
    writer = csv.writer(csv_file)
    writer.writerow(myheader)

    """
    This section goes through each item in the dictionary that we created earlier. For each key (which is an author’s publication ID), it queries the database to find its title. Then it writes that title in the CSV file. It also prints it out so you can see the data.
    """
    for x in mylist.keys():
        cur.execute("SELECT title FROM publication WHERE publication.id=%s", (x,))
        while finalresult := cur.fetchone():
            print(finalresult)
            writer.writerow(finalresult)

"""
Finally, all the connections to the database are closed
"""
cur.close()
con.close()