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: [email protected] |
Tel.:416-757-7008,212 | Fax.:416-757-9656
67 Alexdon Rd. Unit 1, North York, M3J 2B5
[cid:[email protected]] <http://www.talize.com>
[cid:[email protected]]
<http://www.linkedin.com/company/talize/>
[cid:[email protected]]
<http://www.instagram.com/talizethrift/>
[cid:[email protected]] <http://www.facebook.com/TalizeThrift>
[cid:[email protected]]
<https://plus.google.com/+TalizeThriftStoreTalizeThriftStore>
[cid:[email protected]] <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.
_______________________________________________
Tutor maillist - [email protected]
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
