#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ @author: pmctaggart """ import pymysql from pymysql import OperationalError from pymysql import Error import OpenSSL.crypto as crypto import requests import base64 import time ############################################################################### ########################## Saltedge Functions ############################### ############################################################################### def get_app(clientID, serviceSECRET): return { 'Accept': 'application/json', 'Content-type': 'application/json', 'Expires-at': str(time.time() + 60), 'App-id': clientID, 'Secret': serviceSECRET } ################################################################# ## Step one of customer setup, send user email and get back customer_id. def fetch_leads(uuid): payload = '{"data":{"email":"' + str(uuid) + '@MMJ.com"}}' post_url = 'https://www.saltedge.com/api/partners/v1/leads' data = post_response(post_url, payload) return data ################################################################# ## Step one of customer setup, send user email and get back customer_id. def fetch_connections(customer_id, connection_id): data_out = get_response('https://www.saltedge.com/api/partners/v1/connections?customer_id=' + str(customer_id)) return data_out ################################################################# ## Step one of customer setup, send user email and get back customer_id. def fetch_reconnect(customer_id, connection_id): payload = '{"data": {"customer_id": "222222222222222222", \ "connection_id": "111111111111111111", \ "consent": { \ "from_date": "2020-01-01", \ "period_days": 90, \ "scopes": [ \ "account_details", \ "transactions_details" \ ] \ }, \ "attempt": { \ "from_date": "2020-02-01", \ "fetch_scopes": [ \ "accounts", \ "transactions" \ ], \ "custom_fields": { \ "test": true \ }}}}' data_out = get_response('https://www.saltedge.com/api/partners/v1/connections?customer_id=' + str(customer_id)) return data_out ################################################################# ## Step one of customer setup, send user email and get back customer_id. def fetch_refresh(customer_id): payload = '{"data\": {"customer_id": "222222222222222222", \ "connection_id": "111111111111111111", \ "attempt": { \ "from_date": "2020-02-01", \ "fetch_scopes": [ \ "accounts", \ "transactions" \ ], \ "custom_fields": { \ "test": true \ }}}}' data_out = get_response('https://www.saltedge.com/api/partners/v1/connections?customer_id=' + str(customer_id)) return data_out ################################################################# ## Step one of customer setup, send user email and get back customer_id. def fetch_consent(customer_id): data_out = get_response('https://www.saltedge.com/api/partners/v1/partner_consents?customer_id=' + str(customer_id)) return data_out ################################################################# ## Send data for individual user and get their request URL def create_lead_session(customer_id, from_date, period_days): #### Check dates here payload = '{"data":{"customer_id":"' + str(customer_id) + '",\ "consent":{"from_date":"' + str(from_date) + '",\ "period_days":' + str(period_days) + ',\ "scopes":["account_details","transactions_details"]},\ "attempt":{"from_date":"2020-02-01",\ "fetch_scopes":["accounts","transactions"]}}}' post_url = 'https://www.saltedge.com/api/partners/v1/lead_sessions/create' data = post_response(post_url, payload) return data ################################################################# ## ADD SALTEDGE DELETE USER ################################################################# ## ADD SALTEDGE refresh USER ################################################################# ## ADD SALTEDGE reconnect USER ################################################################# ## ADD SALTEDGE revoke USER ############################################################################### ############################# MYSQL Queries ################################# ############################################################################### def get_status_update(uuid, customer_id, status, connections): status_query = "INSERT INTO aispDB.user_status values (\ '" + uuid + "',\ '" + customer_id + "',\ '" + status + "',\ '" + str(connections) + "') \ ON DUPLICATE KEY UPDATE \ uuid = uuid,\ customer_id = customer_id,\ status = '" + status + "',\ connections = connections;" return status_query ################################################################# def get_status_full_update(uuid, customer_id, status, connections): status_query = "INSERT INTO aispDB.user_status values (\ '" + uuid + "',\ '" + customer_id + "',\ '" + status + "', \ '" + str(connections) + "') \ ON DUPLICATE KEY UPDATE \ uuid = '" + uuid + "',\ customer_id = '" + customer_id + "',\ status = '" + status + "',\ connections = '" + str(connections) + "';" return status_query ################################################################# def get_callback_update(type_1, payload, customer_id, connection_id, error_class, created_at): callback_query = "INSERT INTO aispDB.callbacks values (\ DEFAULT, \ '" + type_1 + "',\ '" + payload + "',\ '" + customer_id + "',\ '" + connection_id + "',\ '" + error_class + "',\ '" + created_at + "');" return callback_query ################################################################# def get_status_pull(uuid, columns): callback_query = "Select " + str(columns) + " from aispDB.user_status where uuid = '" + str(uuid) + "';" return callback_query ############################################################################### ############################ Production DB ################################## ############################################################################### #ssh_host = '52.30.45.126' ## ssh_hostname #ssh_user = 'www-data' ## ssh_username #ssh_password = '' ## # #sql_hostname= 'production-tracker.cwfqsdukfwmb.eu-west-1.rds.amazonaws.com' ## sql_hostname #sql_port = 3306 ## #sql_username = 'admin' ## sql_username #sql_password = '5vj689W97pff8nn' ## sql_password Xg9sV4bE4AoEJKUf1nd2 # #sql_main_database = 'aispDB' ## db_name #ssh_port = 22 ## ############################################################################### ############################## Staging DB ################################### ############################################################################### ssh_host = '54.220.72.82' ## ssh_hostname ssh_user = 'www-data' ## ssh_username ssh_password = '' ## sql_hostname= 'staging-tracker.cwfqsdukfwmb.eu-west-1.rds.amazonaws.com' ## sql_hostname sql_port = 3306 ## sql_username = 'admin' ## sql_username sql_password = 'mY0lY2kK3uY0sF7b' ## sql_password Xg9sV4bE4AoEJKUf1nd2 sql_main_database = 'aispDB' ## db_name ssh_port = 22 ## ############################################################################### ############################## Journey DB ################################### ############################################################################### #ssh_host = '52.30.45.126' ## ssh_hostname #ssh_user = 'www-data' ## ssh_username #ssh_password = '' ## # #sql_hostname= '127.0.0.1' ## sql_hostname #sql_port = 3306 ## #sql_username = 'pmctaggart' ## sql_username #sql_password = 'eSF3jj4g9jZ827Q' ## sql_password Xg9sV4bE4AoEJKUf1nd2 # #sql_main_database = 'aispDB' ## db_name #ssh_port = 22 ## ############################################################################### ########################### Database Queries ################################ ############################################################################### import os import paramiko from paramiko import SSHClient from sshtunnel import SSHTunnelForwarder local_dir = os.path.dirname(os.path.realpath(__file__)) + '/' #local_dir = '/Users/pmctaggart/Work/AISP/Python2/Working/Test_Version_v1/' mypkey = paramiko.RSAKey.from_private_key_file(local_dir + 'mmj-www-data.pem') ## Query data base with single execution def aisp_query(query): i = 1 while i <= 10: print('Running query A, attempt: ' + str(i)) try: conn = pymysql.connect(host=sql_hostname, user=sql_username, passwd=sql_password, db=sql_main_database, port=sql_port) cur = conn.cursor() cur.execute(query) conn.commit() cur.close() conn.close() print(' - Complete') return None except: time.sleep(1) i += 1 try: cur.close() except: pass try: conn.close() except: pass print('Failed after ' + str(i - 1) + ' attempts') ################################################################# def aisp_query_one(query): i = 1 while i <= 10: # print('Running query B, attempt: ' + str(i)) try: conn = pymysql.connect(host=sql_hostname, user=sql_username, passwd=sql_password, db=sql_main_database, port=sql_port) cur = conn.cursor() cur.execute(query) result = cur.fetchone() output = result[0] # conn.commit() cur.close() conn.close() print('Complete after ' + str(i) + ' attempts') return output except: time.sleep(1) i += 1 try: cur.close() except: pass try: conn.close() except: pass print('Failed after ' + str(i - 1) + ' attempts') ################################################################# def aisp_query_safe(query): # Simple version of below, seems to work better aisp_query("SET SQL_SAFE_UPDATES = 0;") aisp_query(query) aisp_query("SET SQL_SAFE_UPDATES = 1;") ################################################################# def aisp_query_all(query): i = 1 while i <= 10: print('Running query C, attempt: ' + str(i)) try: conn = pymysql.connect(host=sql_hostname, user=sql_username, passwd=sql_password, db=sql_main_database, port=sql_port) cur = conn.cursor() cur.execute(query) result=cur.fetchall() output = result#[0] # conn.commit() cur.close() conn.close() print(' - Complete') return output except: time.sleep(1) i += 1 try: cur.close() except: pass try: conn.close() except: pass print('Failed after ' + str(i - 1) + ' attempts') ############################################################################### ################## Saltedge GET & POST - with Signiture ####################### ############################################################################### clientID = 'GzrhYdPiBqNNA86EB2raWw7UmgcpUQc7By9xOCmCsyQ' serviceSECRET = 'cRTPXyIOOkC2iwVb1LCarrVmRiEFkhJ5QJCq4J0EzFE' pem_path = local_dir + 'saltedge_private.pem' digest = 'sha256' ################################################################# def sign_message(method, expire, some_url, payload=""): with open(pem_path, "rb") as private_key: keydata = private_key.read() private_key_1 = crypto.load_privatekey(crypto.FILETYPE_PEM, keydata) message = "{expire}|{method}|{some_url}|{payload}".format(**locals()) signiture = base64.b64encode(crypto.sign(private_key_1, message, digest)) return signiture ################################################################# def get_response(get_url): app_id = clientID secret = serviceSECRET expire = str(time.time() + 60) headers = { "Accept": "application/json", "Content-type": "application/json", "Expires-at": expire, "App-id": app_id, "Secret": secret } headers["Signature"] = sign_message("GET", expire, get_url) response = requests.get(get_url, headers=headers) data = response.json() return data ################################################################# def post_response(post_url, payload): app_id = clientID secret = serviceSECRET expire = str(time.time() + 60) headers = { "Accept": "application/json", "Content-type": "application/json", "Expires-at": expire, "App-id": app_id, "Secret": secret } headers["Signature"] = sign_message("POST", expire, post_url, payload) response = requests.post(post_url, data=payload, headers=headers) data = response.json() return data ############################################################################### ############################################################################### ###############################################################################