import csv
from sqlalchemy import bindparam, create_engine, MetaData
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql://dawn_user@debian/dawndb')
meta = MetaData()
meta.reflect(bind=engine)
counterparties = meta.tables['counterparties']
ins = counterparties.insert().values(code = bindparam('CODE'),
                                     name = bindparam('FIRM'),
                                     dtc_number = bindparam('DTC Number'),
                                     sales_contact = bindparam('Sales Contact'),
                                     sales_email = bindparam('Email1'),
                                     sales_phone = bindparam('Phone'),
                                     valuation_contact1 = bindparam('Valuation Contact1'),
                                     valuation_email1 = bindparam('Valuation Contact1 Email'),
                                     valuation_contact2 = bindparam('Valuation Contact2'),
                                     valuation_email2 = bindparam('Valuation Contact2 Email'),
                                     valuation_contact3 = bindparam('Valuation Contact3'),
                                     valuation_email3 = bindparam('Valuation Contact3 Email'),
                                     notes = bindparam('Valuation Note'))


with open("/home/share/CorpCDOs/data/counterparties.csv") as fh:
    csvreader = csv.DictReader(fh)
    to_insert = []
    for line in csvreader:
        for k, v in line.items():
            if v=="":
                line[k] = None
        if any(line.values()):
            line['city'] = line['Location'].split(", ")[0]
            line['state'] = line['Location'].split(", ")[1]
            to_insert.append(line)
            conn = engine.connect()
conn.execute(ins, to_insert)