Create a Service User in PrecisionLender Administration tab
import http.client
import getpass
import json
import urllib.parse
import pandas as pd
from io import StringIO
import os
clientid = "a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1"
username = "L3_Workbench_Service_User"
password = getpass.getpass()
········
Connect to L3 Workbench as Service User
conn = http.client.HTTPSConnection("api.precisionlender.com")
headers = {
'clientid': clientid,
'username': username,
'password': password
}
conn.request("POST", "/L3/v1/workbench/auth", headers=headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))
{"token_type":"Bearer","expires_in":3599,"ext_expires_in":3599,"access_token":"eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCIsImtpZCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCJ9.eyJhdWQiOiJodHRwczovL3N0b3JhZ2UuYXp1cmUuY29tIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvZjA2ZDQ1OWItZDkzNS00YWQ3LWE5ZDMtYTgyMzQzYzRjOWRhLyIsImlhdCI6MTYwNjkyNTUyNSwibmJmIjoxNjA2OTI1NTI1LCJleHAiOjE2MDY5Mjk0MjUsImFpbyI6IkUyUmdZSGpGWjFvNnY0c2wrMy81dXNBdjhla3NBQT09IiwiYXBwaWQiOiIwN2JlNDJlYS0xY2Y2LTQ5YTYtYmIzYy1lMTVmOGU2Y2U2MTciLCJhcHBpZGFjciI6IjEiLCJpZHAiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9mMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEvIiwib2lkIjoiNDhlYzMyMzgtYTZjZi00NTYwLWE4MjAtYzBiNjgzOGE1NGI5IiwicmgiOiIwLkFBQUFtMFZ0OERYWjEwcXAwNmdqUThUSjJ1cEN2Z2YySEtaSnV6emhYNDVzNWhkYkFBQS4iLCJzdWIiOiI0OGVjMzIzOC1hNmNmLTQ1NjAtYTgyMC1jMGI2ODM4YTU0YjkiLCJ0aWQiOiJmMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEiLCJ1dGkiOiJ3ZzBvbmtKNzBFZTJ3WlN0ODdVVkFBIiwidmVyIjoiMS4wIn0.CCBc-Q8ixP1GFCHvF3__sKKKy-VTn7u6XU0ODiRxuhD9X-upbJdJpBh8RjBAc9hbtagLysrCRnNmwFE5vRsnnSniDNRpFowd4YlB9hTvSwR_tVhrkH5k6lBCkCawOfUHcun1gx_MAy2RPgJ5xy_Qwe2dZs39c0nWpKFYM1ph5Ryna33Zc5O8HhSgNDobIC5Dx1oIubWYl6jc5mR78DAM5nLNBv3QAUPRvn8MHJpLJY3Cannswbilu3uG3zEQunCr6C7ieBTd_nyWSPEBaZ-NNrQZTnnegKNhnd0kKGLSgrJIVl5JcN87SyYAsorynX4rqDal52h7FlbAHioe9cPGfQ"}
access_token = eval(data.decode("utf-8"))["access_token"]
print(access_token)
eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCIsImtpZCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCJ9.eyJhdWQiOiJodHRwczovL3N0b3JhZ2UuYXp1cmUuY29tIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvZjA2ZDQ1OWItZDkzNS00YWQ3LWE5ZDMtYTgyMzQzYzRjOWRhLyIsImlhdCI6MTYwNjkyNTUyNSwibmJmIjoxNjA2OTI1NTI1LCJleHAiOjE2MDY5Mjk0MjUsImFpbyI6IkUyUmdZSGpGWjFvNnY0c2wrMy81dXNBdjhla3NBQT09IiwiYXBwaWQiOiIwN2JlNDJlYS0xY2Y2LTQ5YTYtYmIzYy1lMTVmOGU2Y2U2MTciLCJhcHBpZGFjciI6IjEiLCJpZHAiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9mMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEvIiwib2lkIjoiNDhlYzMyMzgtYTZjZi00NTYwLWE4MjAtYzBiNjgzOGE1NGI5IiwicmgiOiIwLkFBQUFtMFZ0OERYWjEwcXAwNmdqUThUSjJ1cEN2Z2YySEtaSnV6emhYNDVzNWhkYkFBQS4iLCJzdWIiOiI0OGVjMzIzOC1hNmNmLTQ1NjAtYTgyMC1jMGI2ODM4YTU0YjkiLCJ0aWQiOiJmMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEiLCJ1dGkiOiJ3ZzBvbmtKNzBFZTJ3WlN0ODdVVkFBIiwidmVyIjoiMS4wIn0.CCBc-Q8ixP1GFCHvF3__sKKKy-VTn7u6XU0ODiRxuhD9X-upbJdJpBh8RjBAc9hbtagLysrCRnNmwFE5vRsnnSniDNRpFowd4YlB9hTvSwR_tVhrkH5k6lBCkCawOfUHcun1gx_MAy2RPgJ5xy_Qwe2dZs39c0nWpKFYM1ph5Ryna33Zc5O8HhSgNDobIC5Dx1oIubWYl6jc5mR78DAM5nLNBv3QAUPRvn8MHJpLJY3Cannswbilu3uG3zEQunCr6C7ieBTd_nyWSPEBaZ-NNrQZTnnegKNhnd0kKGLSgrJIVl5JcN87SyYAsorynX4rqDal52h7FlbAHioe9cPGfQ
Do not forgot to URL encode the directory path!
# Path parameters
filesystem = 'datamart'
# Query Parameters
recursive = False
# Headers
headers = {
'clientid': clientid,
'authorization': "Bearer " + access_token
}
# directory
l3_primary = '/L3/v1.0/Primary/'
dataset = '/PL_RelationshipAwareness/Core/CoreCommercialLoanAccounts/'
instance_partition = "/InstancePartition=7d8cd6f2-0dbe-4c10-9e05-9beccee6614a/"
version_partition = '/VersionPartition=v1.0/'
date_partition = '/DatePartition=20180802/'
directory = l3_primary + dataset + instance_partition + version_partition + date_partition
encoded_directory = urllib.parse.quote_plus(directory)
conn.request("GET", "/L3/v1/workbench/" + filesystem + "?directory=" + encoded_directory + "&recursive=" + str(recursive) , headers=headers)
res = conn.getresponse()
data = res.read()
l3_paths = json.loads(data.decode("utf-8"))
l3_paths["paths"]
[{'contentLength': '39061974',
'etag': '0x8D890083302DDFA',
'lastModified': 'Mon, 23 Nov 2020 23:33:36 GMT',
'name': 'L3/v1.0/Primary/PL_RelationshipAwareness/Core/CoreCommercialLoanAccounts/InstancePartition=7d8cd6f2-0dbe-4c10-9e05-9beccee6614a/VersionPartition=v1.0/DatePartition=20180802/00000-dbd1dbb4-35f0-4f63-b9a0-7951905956d4.csv'},
{'contentLength': '39087526',
'etag': '0x8D8900825A7C94B',
'lastModified': 'Mon, 23 Nov 2020 23:33:13 GMT',
'name': 'L3/v1.0/Primary/PL_RelationshipAwareness/Core/CoreCommercialLoanAccounts/InstancePartition=7d8cd6f2-0dbe-4c10-9e05-9beccee6614a/VersionPartition=v1.0/DatePartition=20180802/00001-dbd1dbb4-35f0-4f63-b9a0-7951905956d4.csv'}]
filesystem = "datamart"
# directory
l3_primary = '/L3/v1.0/Primary/'
dataset = '/PL_RelationshipAwareness/Core/CoreCommercialLoanAccounts/'
# Modify for
instance_partition = "/InstancePartition=7d8cd6f2-0dbe-4c10-9e05-9beccee6614a/"
version_partition = '/VersionPartition=v1.0/'
date_partition = '/DatePartition=20180802/'
filename = '00000-dbd1dbb4-35f0-4f63-b9a0-7951905956d4.csv'
path = l3_primary + dataset + instance_partition + version_partition + date_partition + filename
encoded_path = urllib.parse.quote_plus(path)
conn.request("GET", "/L3/v1/workbench/" + filesystem + path, headers=headers)
res = conn.getresponse()
core_commercial_loan_accounts = res.read()
data_string = StringIO(core_commercial_loan_accounts.decode("utf-8"))
df = pd.read_csv(data_string)
df.head(n=5)
| RaRunId | TopLevelParent | Id | ProductFamilyId | Name | Amount | Balance | Payment | Rate | PaymentTypeEnum | ... | SpreadResetFrequency | LetterOfCreditType | CurrencyId | CommittedCommitment | AdvisedCommitment | GuidanceCommitment | LOCExpectedRenewalRetention | ExpansionStrategy | LastModified | FeesInitialPercentOfAmount | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 07f1d46f-5c8c-4e9a-aa4f-10fad791abbd | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 183114.0 | 183114.0 | 8965.0 | 0.03 | 1 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 08f701c7-d7bf-48d7-8750-82eb8e98d9ca | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 76487.0 | 76487.0 | 3745.0 | 0.03 | 1 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 12907454-a560-46bb-a9be-a66ae48dfaa8 | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 2a4f830b-0b89-498a-8f0d-0f84aa886e5f | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 2f00b406-3cc2-42f9-85c5-49e6cdb6792f | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 rows × 88 columns
The Workspace folder is the only folder in L3 that has write access. All other folders are read only. This provides a guardrail to not accidentally delete your raw production data!
Create the file or directory name. In this case, we are creating a file name.
# directory
workspace = '/Workspace/Derived/'
instance_partition = "/InstancePartition=" + clientid + "/"
filename = 'test.csv'
path = workspace + instance_partition + filename
resource = "file"
conn.request("PUT", "/L3/v1/workbench/" + filesystem + path + "?resource=" + resource, headers=headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))
Modify the Pandas DataFrame
df.head(n=5)
| RaRunId | TopLevelParent | Id | ProductFamilyId | Name | Amount | Balance | Payment | Rate | PaymentTypeEnum | ... | SpreadResetFrequency | LetterOfCreditType | CurrencyId | CommittedCommitment | AdvisedCommitment | GuidanceCommitment | LOCExpectedRenewalRetention | ExpansionStrategy | LastModified | FeesInitialPercentOfAmount | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 07f1d46f-5c8c-4e9a-aa4f-10fad791abbd | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 183114.0 | 183114.0 | 8965.0 | 0.03 | 1 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 08f701c7-d7bf-48d7-8750-82eb8e98d9ca | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 76487.0 | 76487.0 | 3745.0 | 0.03 | 1 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 12907454-a560-46bb-a9be-a66ae48dfaa8 | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 2a4f830b-0b89-498a-8f0d-0f84aa886e5f | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | 9e1b3e06-aa95-426f-b45d-2528162f989a | 00055784-c56e-4814-bd65-727c58513700 | 2f00b406-3cc2-42f9-85c5-49e6cdb6792f | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 rows × 88 columns
df = df.drop('RaRunId', axis = 1)
df.head(n=5)
| TopLevelParent | Id | ProductFamilyId | Name | Amount | Balance | Payment | Rate | PaymentTypeEnum | PaymentFrequencyEnum | ... | SpreadResetFrequency | LetterOfCreditType | CurrencyId | CommittedCommitment | AdvisedCommitment | GuidanceCommitment | LOCExpectedRenewalRetention | ExpansionStrategy | LastModified | FeesInitialPercentOfAmount | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 00055784-c56e-4814-bd65-727c58513700 | 07f1d46f-5c8c-4e9a-aa4f-10fad791abbd | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 183114.0 | 183114.0 | 8965.0 | 0.03 | 1 | 1 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 00055784-c56e-4814-bd65-727c58513700 | 08f701c7-d7bf-48d7-8750-82eb8e98d9ca | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 76487.0 | 76487.0 | 3745.0 | 0.03 | 1 | 1 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | 00055784-c56e-4814-bd65-727c58513700 | 12907454-a560-46bb-a9be-a66ae48dfaa8 | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | 2147483647 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 00055784-c56e-4814-bd65-727c58513700 | 2a4f830b-0b89-498a-8f0d-0f84aa886e5f | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | 2147483647 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | 00055784-c56e-4814-bd65-727c58513700 | 2f00b406-3cc2-42f9-85c5-49e6cdb6792f | df11bdbd-312e-4356-88f1-06457e8fdc0c | NaN | 0.0 | 0.0 | 0.0 | 0.00 | 4 | 2147483647 | ... | NaN | 2 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
5 rows × 87 columns
Update the file with our Pandas DataFrame
# Convert Pandas DataFrame to string before API Upload
payload = df.to_csv(index=False)
position = str(0)
headers = {
'clientid': clientid,
'content-type': "application/octet-stream",
'authorization': "Bearer " + access_token}
conn.request("PATCH", "/L3/v1/workbench/" + filesystem + path + "?position=" + position + "&action=append", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))
position = str(len(payload))
payload = ""
headers = {
'clientid': clientid,
'content-type': "application/octet-stream",
'authorization': "Bearer " + access_token}
conn.request("PATCH", "/L3/v1/workbench/" + filesystem + path + "?position=" + position + "&action=flush", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))
Check if file successfully uploaded
path
'/Workspace/Derived//InstancePartition=a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1/test.csv'
# Headers
headers = {
'clientid': clientid,
'authorization': "Bearer " + access_token
}
directory = workspace + instance_partition
encoded_directory = urllib.parse.quote_plus(directory)
conn.request("GET", "/L3/v1/workbench/" + filesystem + "?directory=" + encoded_directory + "&recursive=" + str(recursive) , headers=headers)
res = conn.getresponse()
data = res.read()
l3_paths = json.loads(data.decode("utf-8"))
l3_paths["paths"]
[{'contentLength': '0',
'etag': '0x8D808C9874BB51B',
'isDirectory': 'true',
'lastModified': 'Thu, 04 Jun 2020 20:54:52 GMT',
'name': 'Workspace/Derived/InstancePartition=a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1/CoreAccountDetails'},
{'contentLength': '0',
'etag': '0x8D7BB9382C021E1',
'isDirectory': 'true',
'lastModified': 'Thu, 27 Feb 2020 14:44:12 GMT',
'name': 'Workspace/Derived/InstancePartition=a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1/Pipeline-Quality'},
{'contentLength': '38698464',
'etag': '0x8D896DE6D5501D0',
'lastModified': 'Wed, 02 Dec 2020 16:22:13 GMT',
'name': 'Workspace/Derived/InstancePartition=a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1/test.csv'}]