You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
49 lines
1.5 KiB
49 lines
1.5 KiB
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')
|