CSV file INPUT:

seq_name1,ACTACTACT
seq_name2,CGTCGTCGTCGT

FASTA file OUTPUT:

> seq_name1
ACTACTACT
> seq_name2
CGTCGTCGTCGT

METHOD with no packages

# OPEN IN & OUT files
in_handle = open(in_path, "r")
out_handle = open(out_path, "w")

# read in the file
for line in in_handle:
    line_tuple = line.strip("\n").split(',')  # for TSV just change this to split('\t')
    header = line_tuple[0] # column 1
    seq = line_tuple[1] # column 2

    # WRITE out the edited TSV
    out_handle.write("> {}\n{}\n".format(header, seq))

in_handle.close()
out_handle.close()

Be aware of any spaces before or after the comma in a CSV and within the seq name if you want to remove them.