from dateutil.parser import parse as date_parse
import csv
import datetime
import re
import sqlite3
#noise = re.compile(r' {3,}.*')
sqlite3.register_adapter(datetime.datetime,lambda dt: str(dt))
sqlite3.register_converter("DATE",date_parse)
def __init_db__(path):
    con = sqlite3.connect(path,detect_types=sqlite3.PARSE_DECLTYPES)
    cur = con.cursor()
    cur.execute('''
        CREATE TABLE IF NOT EXISTS `transactions` (
            `details` STRING,
            `posting_date` DATE,
            `amount` REAL,
            `balance` REAL,
            `type` STRING,
            UNIQUE (
            `details`,
            `posting_date`,
            `amount`,
            `balance`,
            `type`)
            )''')
    con.commit()
    return con

class browser:
    def __init__(self,db_path):

        self.db_path = db_path
        self.con = __init_db__(db_path)
        self.con.row_factory = sqlite3.Row
        self.cur = self.con.cursor()

    def update_from_csv(self,csv_file):
        with open(csv_file) as file:
            reader = csv.DictReader(file)
            l = list(reader)
        for line in l:
            date = line.pop('Posting Date')
            line['posting_date'] = date_parse(date)
            line['Amount'] = float(line['Amount'])
        self.cur.executemany('INSERT OR IGNORE INTO `transactions` VALUES (:Description,:posting_date,:Amount,:Balance,:Type);',l)
        self.con.commit()
if __name__ == "__main__":
    b = browser("test.db")
    b.update_from_csv('test.csv')