1.#!/usr/bin/python
2.# -- coding: utf-8 --
3."""
4.This file is part of KMyMoney, A Personal Finance Manager for KDE
5.Copyright (C) 2014 Christian Dávid
7.This program is free software; you can redistribute it and/or
8.modify it under the terms of the GNU General Public License
9.as published by the Free Software Foundation; either version 2
10.of the License, or (at your option) any later version.
12.This program is distributed in the hope that it will be useful,
13.but WITHOUT ANY WARRANTY; without even the implied warranty of
14.MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15.GNU General Public License for more details.
17.You should have received a copy of the GNU General Public License
18.along with this program. If not, see http://ift.tt/ytFmsU.
19."""
21."""
22.Uses the " BIC-lijstvanNederlandsebankenvoorSEPA " of the dutch Betaalvereniging Nederland bank to create a bic lookup table for KMyMoney .
24.@author: Christian David
25."""
26.# importing important packages
27.import sqlite3
28.import argparse
29.import xlrd
31.def createTable():
""" Create table structure
"""
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS institutions")
cursor.execute(
"CREATE TABLE institutions ("
" country CHAR(2) DEFAULT 'NL' CONSTRAINT dutchCountryCode NOT NULL CHECK(country == 'NL'),"
" bankcode CHAR(4) NOT NULL PRIMARY KEY CHECK(length(bankcode) = 4),"
" bic CHAR(11),"
" name VARCHAR(60)"
" )"
)
db.commit()
45.# Have Some Doubt In Line 39 . If No Error , Please remove This Comment :)
46.def processFile(fileName):
""" Fills the database with institutions saved in fileName
"""
rowsInserted = 0
cursor = db.cursor()
cursor.execute("BEGIN")
def submitInstitute(bankCode, bankName, bic):
try:
cursor.execute("INSERT INTO institutions (bankCode, bic, name) VALUES(?,?,?)", (bankCode, bic, bankName))
except sqlite3.Error, e:
print "Sorry , Error: {0} while inserting {1} ({2})".format(e.args[0], bankCode, bic)
institutesFile = xlrd.open(fileName, "r", encoding=args.encoding)
for institute in institutesFile:
submitInstitute(institute[0:8], institute[9:67].strip(), institute[39:50])
rowsInserted += 1
db.commit();
return rowsInserted
70.
71.if name == 'main':
parser = argparse.ArgumentParser(description="We Create an SQLite database for KMyMoney with information about IBAN and BICs based on an 'Excel 2007 sheet' from the Dutch Betaalvereniging Nederland bank."
" You can go and download the source (.xlsx) file at http://ift.tt/1yevWEG "
)
parser.add_argument(dest='file', help='File to load')
parser.add_argument('-o', '--output', default="dutchbankdata.nl.db", help='SQLite database to open/generate')
args = parser.parse_args()
print "Read data from \"{0}\"".format(args.file)
db = sqlite3.connect(args.output)
createTable()
institutions = processFile( args.file )
print "Inserted {0} institutions into database \"{1}\"".format(institutions, args.output)
cursor = db.cursor();
cursor.execute("ANALYZE institutions");
cursor.execute("CREATE INDEX bic_index ON institutions (bic)");
cursor.execute("REINDEX");
cursor.execute("VACUUM");
db.commit();
db.close()
Aucun commentaire:
Enregistrer un commentaire