Data Operations
The DealCloud
object provides methods to Create, Read, Update and Delete data in bulk. As its default behaviour, when working with site data, the library uses DataFrames with
the Pandas (opens in a new tab) library, however the option is available for it to work with just python standard data types.
List Configured Views
DealCloud.list_configured_views()
returns a Rows
object, containing all configured views in the site.
The argument is_private
can be passed as a boolean, where if true, only private views to (including views shared with) the authenticated user will be returned.
Example:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
views = dc.list_configured_views()
views
will contain a summary of configured views.
Read Data
DealCloud.read_data()
allows users to read data from a DealCloud object into a pandas.DataFrame
or a list of python dictionaries.
Only one of object_id or view_id can be populated
Reading Data from an Object
Example:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data("Company")
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName | CompanyType |
---|---|---|
12345 | Company1 | [{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}] |
12346 | Company2 | [{'seqNumber': 1, 'isAutoPdf': False, 'id': 3197782, 'name': 'Operating Company', 'entryListId': -6}] |
12347 | Company3 | [{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}] |
To return a python list
instead of a pandas.DataFrame
:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data("Company", output="list")
data
will be a list
of dict
s as below:
[
{
'EntryId': 12345,
'CompanyName': 'Company1',
'CompanyType': [
{
'seqNumber': 3,
'isAutoPdf': False,
'id': 3197780,
'name': 'Limited Partner',
'entryListId': -6
}
],
},
{
'EntryId': 12346,
'CompanyName': 'Company2',
'CompanyType': [
{
'seqNumber': 1,
'isAutoPdf': False,
'id': 3197782,
'name': 'Operating Company',
'entryListId': -6
}
]
},
{
'EntryId': 12347,
'CompanyName': 'Company3',
'CompanyType': [
{
'seqNumber': 4,
'isAutoPdf': False,
'id': 3197779,
'name': 'Service Provider',
'entryListId': -6
}
]
},
]
Resolving to Name or to ID
The following methods only apply to where a pandas.DataFrame
is returned.
Choice, Reference and User fields appear as lists of dictionaries as seen in the above example by default.
To resolve these fields to a more readable name
value:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data("Company", resolve="name")
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName | CompanyType |
---|---|---|
12345 | Company1 | Limited Partner |
12346 | Company2 | Operating Company |
12347 | Company3 | Limited Partner |
To resolve the fields to an id
value:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data("Company", resolve="id")
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName | CompanyType |
---|---|---|
12345 | Company1 | 3197780 |
12346 | Company2 | 3197782 |
12347 | Company3 | 3197780 |
Reading a Subset of Fields
To read a specific set of fields from an object, pass a list of DealCloud field API names to the argument fields
:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data("Company", fields=["CompanyName"])
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName |
---|---|
12345 | Company1 |
12346 | Company2 |
12347 | Company3 |
Using Queries to Read Data
A query can be passed to the DealCloud request for data to return more specific information, and reduce the volume of incoming data.
To use a query, pass the query string to the query
argument.
The available query operations are below:
Name | Query Operation |
---|---|
Equals | $eq |
Contains | $contains |
Greater | $gt |
GreaterOrEqual | $gte |
Less | $lt |
LessOrEquals | $lte |
StartsWith | $startswith |
In | $in |
Between | $between |
NotIn | $nin |
NotEqualTo | $not |
EndsWith | $endswith |
Or | $or |
And | $and |
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
dc.read_data("Company", query="{CompanyName: {$contains: \"1\"}}")
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName | CompanyType |
---|---|---|
12345 | Company1 | 3197780 |
Some other example query strings are below: Filter on excluding records with a specific reference value:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
dc.read_data("Company", query="{CoveragePerson: {$nin: [5785]}")
Filter using Or:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
dc.read_data("Company", query="{$or: [{CompanyName: \"DealCloud\"},{CompanyName: \"API Entry\"}]}")
Reading Data from a View
To read data from a view with ID: 12345
Example:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data(view_id=12345)
Alternatively, if the view is named: "My Company View":
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data(view_id="My Company View")
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName | CompanyType |
---|---|---|
12345 | Company1 | [{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}] |
12346 | Company2 | [{'seqNumber': 1, 'isAutoPdf': False, 'id': 3197782, 'name': 'Operating Company', 'entryListId': -6}] |
12347 | Company3 | [{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}] |
Using a Filter When Reading View Data
When reading data from a view, which has "Supply Value Later" filters configured, the filter values can be provided by passing them to the view_filter
argument in a list
of dict
s. For more information on their format, please see the API Documentation (opens in a new tab).
For Example, supplying a "CompanyType" filter value to filter for only "Limited Partner" companies:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
data = dc.read_data(
view_id="My Company View",
view_filter=[
{
"column": "CompanyType",
"value": [3197780]
}
]
)
data
will be a pandas.DataFrame
as below:
EntryId | CompanyName | CompanyType |
---|---|---|
12345 | Company1 | [{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}] |
12347 | Company3 | [{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}] |
Create, Update and Upsert Data
DealCloud.insert_data()
, DealCloud.update_data()
, DealCloud.upsert_data()
are methods which provide the ability to create, update and upsert data respectively.
When not using use_dealcloud_ids
, for Reference fields, reference by record EntryId. For choice fields, use the choice value ID. For user fields, use the user ID (from DealCloud.get_users()
).
When using use_dealcloud_ids
, for Reference fields, reference by the lookup column on the referenced object. For choice fields, use the choice field display name. For user fields, use the user email address.
Field Mapping
All columns passed to the create, update and upsert methods must match by API Name. If a column passed to the method does not exist in the site by API name, then a KeyError
will be raised:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{
"CompanyName": "Test Company 1",
"UnmappableColumn": "Foo",
}
]
responses = dc.insert_data("Company", to_send)
Will Return
Traceback...
KeyError: "mapping error, could not map: ['UnmappableColumn']"
Create Data
Example, to insert data into the "Company" object from a list:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{
"CompanyName": "Test Company 1",
"CompanyType": 12345,
"BusinessDescription": "Here is a business description",
"Sector": 14321,
}
]
responses = dc.insert_data("Company", to_send)
If successful, responses
will contain the below - note that EntryId
is now included:
[
{
"EntryId": 234567,
"CompanyName": "Test Company 1",
"CompanyType": 12345,
"BusinessDescription": "Here is a business description",
"Sector": 14321,
}
]
Example, to insert data into the "Company" object from a CSV, using pandas
:
CSV File (company.csv
):
CompanyName | CompanyType |
---|---|
Company1 | 3197780 |
Company2 | 3197782 |
Company3 | 3197780 |
import pandas as pd
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = pd.read_csv("company.csv")
responses = dc.insert_data("Company", to_send)
Example, to insert data into the "Company" object using "ExternalSystemID" as a lookup column:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{
"CompanyName": "Test Company 1",
"CompanyType": "Operating Company",
"BusinessDescription": "Here is a business description",
"Sector": "612E9FF4-1D8C-41B7-B8ED-2D1C15240434",
}
]
responses = dc.insert_data(
object_api_name="Company",
data=to_send,
use_dealcloud_ids=False,
lookup_column="ExternalSystemID"
)
Update Data
To update existing site data, the process is similar, except objects must include EntryId
, or a valid primary key by lookup_column
.
Example, to update data in the "Company" object from a list:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{
"EntryId": 234567,
"BusinessDescription": "Here is an updated business description!",
}
]
responses = dc.insert_data("Company", to_send)
If successful, responses
will contain the below:
[
{
"EntryId": 234567,
"CompanyName": "Test Company 1",
"CompanyType": 12345,
"BusinessDescription": "Here is an updated business description!",
"Sector": 14321,
}
]
Example, to update data in the "Company" object from a list, using the lookup_column
, ExternalSystemId
:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{
"ExternalSystemId": "F628AFC8-532C-406C-BDE6-A782F03508D5",
"BusinessDescription": "Here is an updated business description!",
}
]
responses = dc.insert_data(
object_api_name="Company",
data=to_send,
use_dealcloud_ids=False,
lookup_column="ExternalSystemId"
)
Upsert Data
Upserting data is a method that combines insert and update. If a record exists and can be found by EntryId
or a lookup_column
then it will be updated, if not, it will be created new.
Example, to upsert data in the "Company" object from a list:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{
"EntryId": 234567,
"CompanyName": "Test Company 1",
"CompanyType": 12345,
"BusinessDescription": "Here is an updated business description",
"Sector": 14321,
},
{
"CompanyName": "Test Company 2",
"CompanyType": 12345,
"BusinessDescription": "Here is a business description for a new company",
"Sector": 14321,
},
]
responses = dc.insert_data("Company", to_send)
If successful, responses
will contain the below, note that the "Test Company 2" now has an EntryId
as it has been created:
[
{
"EntryId": 234567,
"CompanyName": "Test Company 1",
"CompanyType": 12345,
"BusinessDescription": "Here is an updated business description",
"Sector": 14321,
},
{
"EntryId": 234568,
"CompanyName": "Test Company 2",
"CompanyType": 12345,
"BusinessDescription": "Here is a business description for a new company",
"Sector": 14321,
},
]
Understanding Errors
The data operation methods raise errors in a number of ways, when use_dealcloud_ids
is False
, errors are fed back from the API into the data returned from the function.
Example, if the sector 12345 does not exist:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
to_send = [
{'CompanyName': 'TEST_UPDATED', "Sector": 12345}
]
responses = dc.insert_data("WJ_Company", to_send)
responses
will contain.
[{'EntryId': -1,
'CompanyName': 'TEST_UPDATED',
'Sector': None,
'Errors': [{'field': 'Sector',
'code': 5006,
'description': 'One or more referenced entries are not valid for this field.'}]}]
!!! info
In this case, the record has not been created in the site. When multiple records are trying to be made and any one fails in this way, none of the records in that group will be created. When a large volume of records are being created, DealCloud
handles breaking the large volume of data into multiple pages. In this case, where there is an error on a record in a page, all of the records in that page will not be created/updated.
When use_dealcloud_ids
is True
, and lookup values are used, the create/update/upsert methods are less prone to "failing" errors. This is because, when a value is being resolved through a lookup_column
, a value that cannot be found will result in an error being logged to the configured logger. However, as the record progresses, the un-resolvable value will simply be blank. The logs appear as below:
For choice fields:
ERROR: Choice mapping error on: {object}, {field}, could not find value: {value}
For reference fields:
ERROR: Reference mapping error on: {object}, {field}, could not find value: {value}
For user fields:
ERROR: User mapping error on: {object}, {field}, could not find value: {value}
When using DealCloud.update_data()
, if an EntryId
cannot be located, you will see the following error:
ERROR: Primary Key error on object: {object}, record found without 'EntryId' field.
Delete Data
DealCloud.delete_data()
provides the ability to delete data from an object by the EntryId
. To use it, simply pass the object api name to the object_api_name
argument
and a list
of EntryId
s to the records
argument.
Example to delete two records with the EntryIds 123451
and 12346
:
from dealcloud_sdk import DealCloud
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
response = dc.delete_data(
object_api_name="Company",
records=[12345, 12346]
)
response
will contain:
[
{'entryId': 12345, 'fieldId': 0, 'rowId': 4935650, 'isNoData': False},
{'entryId': 12346, 'fieldId': 0, 'rowId': 4935650, 'isNoData': False},
]