How to update columns of a CSV file if row exists, else how to append to same CSV, using temporary file

I am stuck at trying to build a database using a CSV file.

I am using input of symbols (stock market tickers), and I am able to generate website links for each symbol, corresponding to the company's website. I would like to save that database to a CSV file named BiotechDatabase.csv

The Database look

Every time I input a new symbol in Python, I would like to verify the first column of the CSV file to see if the symbol exists. If it does, I need to overwrite the Web column to make sure it is updated.

If the symbol does not exist, a row will need to be appended containing the symbol and the Web.

Since I need to expand the columns to add more information in the future, I need to use DictWriter as some columns might have missing information and need to be skipped. I have been able to update information for a symbol if the symbol is in the database using the code below:

from csv import DictWriter
import shutil
import csv

#Replacing the symbol below with the any stock symbol I want to get the website for
symbol = 'PAVM'

#running the code web(symbol) generates the website I need for PAVM and that is http://www.pavmed.com which I converted to a string below
web(symbol)


filename = 'BiotechDatabase.csv'
tempfile = NamedTemporaryFile('w', newline='', delete=False)

fields = ['symbol','Web']

#I was able to replace any symbol row using the code below:
with open(filename, 'r', newline='') as csvfile, tempfile:
    reader = csv.DictReader(csvfile, fieldnames=fields)
    writer = csv.DictWriter(tempfile, fieldnames=fields)
    for row in reader:   
        if row['symbol'] == symbol:
            print('adding row', row['symbol'])
            row['symbol'], row['Web']= symbol, str(web(symbol))  
        row = {'symbol': row['symbol'], 'Web': row['Web']} 
        writer.writerow(row)
shutil.move(tempfile.name, filename)

If the symbol I entered in Python doesn't exist however in the CSV file, how can I append a new row in the CSV file at the bottom of the list, without messing with the header, and while still using a temporary file? Since the tempfile I defined above uses mode 'w', do I need to create another temporary file that allows mode 'a' in order to append rows?

Solution

You can simplify your code dramatically using the Pandas python library.

Note: I do not know how the raw data looks like so you might need to do some tweaking in order to get it to work, please feel free to ask me more about the solution in the comments.

import pandas as pd

symbol = 'PAVM'
web(symbol)

filename = 'BiotechDatabase.csv'
fields = ['symbol', 'Web']

# Reading csv from file with names as fields
df = pd.read_csv(filename, names=fields)
# Pandas uses the first column automatically as index
df.loc[symbol, 'Web'] = web(symbol)
# Saving back to filename and overwrites it - Be careful!
pd.to_csv(filename)

There might be some faster ways to do that but this one is very elegant.