[Tutor] Code to download credit card statement and add to DB
Kuan Lu
klu at talize.com
Fri Apr 20 16:59:52 EDT 2018
Hello Mr. Tutor:
I constructed a pieced of code utilizing requests and pyodbc to download creditcard statement transactions and insert them into a DB table based on the code published by Louis Millette here: https://github.com/louismillette/Banking/tree/master.
Now I am a total newbie to Python even though I had some experience writing in other languages. The code works like a charm until where it comes to inserting the transactions into the table. As you can see in my code, in order to avoid duplicates being inserted into the table I’m trying to compared the downloaded statement line to each existing line in the table through nested loop and insert only when there’s no match. I kind of intuitively expect that there is another much simpler way to compare each downloaded line to all the inserted lines and find out if it is already in there. Obviously the approach I’m using doesn’t really work as I want it to. Can you please give me some hints as to how to get the loops to work as I explained or, if there is another better way to do it, what would that look like?
Thanks a lot for your guidance.
Kuan
The code is listed as below:
import os
import requests
import datetime
import re
import json
import pyodbc
from datetime import date, timedelta
pyodbc.lowercase = False
#connect to the server through pyodbc
server = 'XXXX\\XXX'
database = 'XXXXXX'
username = 'XXX'
password = 'XXXXX'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=XXXX\\XXX;DATABASE=XXXXXXX;UID=XXX;PWD=XXXXX', autocommit=True)
cursor = cnxn.cursor()
#connect to the Utilities DB and determin the latest date of statement line(s) imported
cursor.execute("SELECT MAX(TransDate) AS TransDate FROM Visa.CreditCardStatement")
Latest_Statement_Date=cursor.fetchone()
#login and date range parameters
_username = "XXXXXXXXXXXXX"
_password = "XXXXXX"
dateFrom = Latest_Statement_Date.TransDate - timedelta(2)
dateUntil = date.today()
#Firstly, you'll need to create an authentication request to acquire an X Auth Token.
# The headers are pulled from a request on my computer, feel free to change them to headers acquired from yours or another header.
# Note that the URL is an encrypted HTTPS request, as are all calls made to the API.
authenticate_request = requests.post(
url="https://www.cibconline.cibc.com/ebm-anp/api/v1/json/sessions",
json={"card": {"value": "{}".format(_username), "description": "", "encrypted": False, "encrypt": True},
"password": "{}".format(_password)},
headers={
"Host": "www.cibconline.cibc.com",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0",
"Accept": "application/vnd.api+json",
"Accept-Language": "en",
"Accept-Encoding": "gzip, deflate, br",
"Referer":"https://www.cibconline.cibc.com/ebm-resources/public/banking/cibc/client/web/index.html",
"Content-Type": "application/vnd.api+json",
"Client-Type": "default_web",
"X-Auth-Token": "",
"brand": "cibc",
"WWW-Authenticate": "CardAndPassword",
"X-Requested-With": "XMLHttpRequest",
"Content-Length": "112",
"Connection": "keep-alive",
"Pragma": "no-cache",
"Cache-Control": "no-cache"
}
)
#Next you'll need to save the cookies, response header, and X Auth Token
cookies = dict(authenticate_request.cookies)
#self.cookies = cookies
authenticate_response_headers = authenticate_request.headers
X_Auth_Token = authenticate_response_headers['X-Auth-Token']
#self.X_Auth_Token = X_Auth_Token
#Make a login request like below. Again the headers are not rigid, however, the "X-Auth-Token" filed must be the X Auth Token from earlier.
login_request = requests.get(
url="https://www.cibconline.cibc.com/ebm-anp/api/v1/profile/json/userPreferences",
headers={
"Host": "www.cibconline.cibc.com",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0",
"Accept": "application/vnd.api+json",
"Accept-Language": "en",
"Accept-Encoding": "gzip, deflate, br",
"Referer":"https://www.cibconline.cibc.com/ebm-resources/public/banking/cibc/client/web/index.html",
"Content-Type": "application/vnd.api+json",
"Client-Type": "default_web",
"brand": "cibc",
"X-Auth-Token": X_Auth_Token,
"X-Requested-With": "XMLHttpRequest",
"Connection": "keep-alive",
},
cookies=cookies
)
#after logging in as yourself, go ahead and pull your default account id from the response
login_request_response = login_request.json()
#The Visa Account ID
defaultAccountId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
#the dateFrom and dateUntil arguments are python datetimes representing from when until when you want to pull credit and debit entries
account_requests = requests.get(
url="https://www.cibconline.cibc.com/ebm-ai/api/v1/json/transactions?accountId={}&filterBy=range&fromDate={}&lastFilterBy=range&limit=150&lowerLimitAmount=&offset=0&sortAsc=true&sortByField=date&toDate={}&transactionLocation=&transactionType=&upperLimitAmount=".format(
defaultAccountId,
dateFrom.strftime("%Y-%m-%d"),
dateUntil.strftime("%Y-%m-%d")
),
headers={
"Host": "www.cibconline.cibc.com",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0",
"Accept": "application/vnd.api+json",
"Accept-Language": "en",
"Accept-Encoding": "gzip, deflate, br",
"Referer":"https://www.cibconline.cibc.com/ebm-resources/public/banking/cibc/client/web/index.html",
"Content-Type": "application/vnd.api+json",
"Client-Type": "default_web",
"brand": "cibc",
"X-Auth-Token": X_Auth_Token,
"X-Requested-With": "XMLHttpRequest",
"Connection": "keep-alive",
},
cookies=cookies
)
transactions = account_requests.json()['transactions']
#for T in transactions:
# ListOfRows=[r for r in T]
# print(ListOfRows[0],ListOfRows[1],ListOfRows[2],ListOfRows[3])
#The function to load the statement dataset
def Generate_List():
#There are a couple of ways to do this one, I set it up as an iterator. This is just to format the output as a nice dictionary.
for transaction in transactions:
transaction_type = 'Debit' if transaction['debit'] else 'Credit'
date_datetime = datetime.datetime.strptime(transaction['date'].split('T')[0],"%Y-%m-%d")
amount = transaction['debit'] if transaction_type == 'Debit' else -transaction['credit']
yield {
'transaction': transaction_type, # 'Debit' or 'Credit'
'TransDate': date_datetime,
'TransDescription': transaction['transactionDescription'],
'CreditCardNumber': transaction['creditCardNumber'],
'Amount': amount,
'balance': transaction['runningBalance']
}
#call the function to populate the generator
Statement=Generate_List()
#read the credit card list from the DB
Card_Dict = dict(cursor.execute("SELECT * FROM dbo.CardNameLookup"))
#read the transactions already loaded from the latest date existing in the statement table
Loaded_Trans = cursor.execute("SELECT * FROM Visa.CreditCardStatement WHERE TransDate=(SELECT MAX(TransDate) FROM Visa.CreditCardStatement)")
#gather the column name info for the dictionary
columns = [column[0] for column in Loaded_Trans.description]
#create the empty dictionary to hold the loaded transactions
Card_Trans = []
#iterate through the cursor and load the dictionary with transactions
for line in Loaded_Trans.fetchall():
Card_Trans.append(dict(zip(columns,line)))
Trans_Count = 0
#loop through the generator and insert the new visa statement lines
for Statement_Line in Statement:
for trans in Card_Trans:
Trans_Count += 1
print(Statement_Line['TransDate'],Statement_Line['TransDescription'],Card_Dict.get(Statement_Line['CreditCardNumber']),\
Statement_Line['Amount'],Statement_Line['balance'])
if trans['TransDate'] == Statement_Line['TransDate'].date() and trans['TransDescription'] == Statement_Line['TransDescription'] and \
trans['CreditCardID'] == Card_Dict.get(Statement_Line['CreditCardNumber']) and float(trans.Amount) == float(Statement_Line['Amount']):
pass
elif Trans_Count==len(Card_Trans):
cursor.execute('INSERT INTO Visa.CreditCardStatement (TransDate, TransDescription, CreditCardID, Amount) VALUES (?, ?, ?, ?)', \
Statement_Line['TransDate'], Statement_Line['TransDescription'], Card_Dict.get(Statement_Line['CreditCardNumber']), Statement_Line['Amount'])
KUAN LU, CPA, CGA | FINANCE TEAM LEAD
________________________________
Email: klu at talize.com |
Tel.:416-757-7008,212 | Fax.:416-757-9656
67 Alexdon Rd. Unit 1, North York, M3J 2B5 [cid:image9ae555.PNG at 558c93ca.4a9482d2] <http://www.talize.com>
[cid:image0b1cd4.PNG at 048ae104.4a9e50c4] <http://www.linkedin.com/company/talize/> [cid:image65149d.PNG at b0ad0da3.40867d32] <http://www.instagram.com/talizethrift/> [cid:image7e7083.PNG at 0fcf5a63.4cb71f2c] <http://www.facebook.com/TalizeThrift> [cid:imagea0af02.PNG at 3584bf2f.4697ad6a] <https://plus.google.com/+TalizeThriftStoreTalizeThriftStore> [cid:imagef55df3.PNG at ff566e87.4281f912] <https://twitter.com/TalizeThrift>
The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error please contact the sender and destroy any copies of this information.
More information about the Tutor
mailing list