def get_data_frame_from_salesforce(sf_instance: Salesforce, table_name: str) -> pd.DataFrame:
"""
Queries for data in SalesForce for a given table.
Parameters
----------
sf_instance
Instance of SalesForce connector
table_name
Name of table in SalesForce
Returns
-------
Table rows as DataFrame
"""
desc = sf_instance.__getattr__(table_name).describe()
field_names = [field['name'] for field in desc['fields']]
soql = "SELECT {} FROM {}".format(','.join(field_names), table_name)
results = sf_instance.query_all(soql)
return pd.DataFrame().from_dict(results['records'])
class SF:
def __init__(self, connectionData):
if (connectionData['isQA'] == 'true'):
self.sf = Salesforce(
domain='test',
username=connectionData['username'],
password=connectionData['password'],
organizationId=connectionData['org'],
security_token=connectionData['security_token'])
else:
self.sf = Salesforce(username=connectionData['username'],
password=connectionData['password'],
organizationId=connectionData['org'])
self.customFunc = CustomFunc()
def send(self, objectName, data):
config = self.getObjectConfig(objectName)
primaryKey = config['primaryKey']['salesforceFieldName']
externalKey = config['externalId']['salesforceFieldName']
hasMultipleIds = config['multipleIds']
ids = config['salesforceIds']
relatedObject = config['relatedObject']
salesForceSubObject = config['subObject']
salesForceObject = self.sf.__getattr__(objectName)
print("RWT: Config")
print(config)
for datum in data:
parsedData = self.parse(datum, config)
alreadyInSF, results = self.isAlreadyInSF(ids, parsedData,
hasMultipleIds,
primaryKey, objectName)
if alreadyInSF:
objectId = results['records'][0]['Id']
print("RWT: Update " +
str(salesForceObject.update(objectId, parsedData)))
if (salesForceSubObject != ""):
self.subObject('Validation__c', datum, objectId)
return objectId
elif (hasMultipleIds):
if relatedObject:
results = self.getRequiredFields(objectName, parsedData,
externalKey, primaryKey)
reponse_id = salesForceObject.create(results)
print("RWT: Create Has Multiple Ids RESULT " +
str(reponse_id))
return reponse_id
else:
reponse_id = salesForceObject.create(parsedData)
print("RWT: Create Has Multiple Ids RESULT " +
str(reponse_id))
return reponse_id
elif (self.hasExternalId(config)):
externalId = config['externalId'][
'salesforceFieldName'] + '/' + str(
datum[config['externalId']['erpFieldName']])
parsedData.pop(primaryKey, None)
print("RWT: Upsert Has External Id " + str(externalId))
print(salesForceObject.upsert(externalId, parsedData))
reponse_id = self.sf.query(
"SELECT Id FROM " + config['salesForceTableName'] +
" WHERE " + config['externalId']['salesforceFieldName'] +
" = '" + str(datum[config['externalId']['erpFieldName']]) +
"'")
print("RWT: Response" + str(reponse_id['records'][0]['Id']))
if (salesForceSubObject != ""):
self.subObject('Validation__c', datum,
reponse_id['records'][0]['Id'])
return reponse_id['records'][0]['Id']
else:
reponse_id = salesForceObject.create(parsedData)
print("RWT: Create " + str(reponse_id))
return reponse_id
def sendBT(self, objectName, data, checkExisting):
config = self.getObjectConfig(objectName)
primaryKey = config['primaryKey']['salesforceFieldName']
externalKey = config['externalId']['salesforceFieldName']
hasMultipleIds = config['multipleIds']
ids = config['salesforceIds']
relatedObject = config['relatedObject']
salesForceObject = self.sf.__getattr__(objectName)
parsedData = self.parse(data, config)
alreadyInSF, results = self.isAlreadyInSF(ids, parsedData,
hasMultipleIds, primaryKey,
objectName)
if alreadyInSF and checkExisting:
objectId = results['records'][0]['Id']
print("RWT: Update")
if (self.hasExternalId(config)):
externalId = config['externalId'][
'salesforceFieldName'] + '/' + str(
data[config['externalId']['erpFieldName']])
parsedData.pop(primaryKey, None)
print("RWT: Upsert Has External Id " + str(externalId))
try:
reponse_id = salesForceObject.upsert(
externalId, parsedData)
print("RWT: Response" + str(reponse_id))
return reponse_id['records'][0]['Id']
except:
print("RWT: Falla actualización de registro")
else:
try:
reponse_id = salesForceObject.create(parsedData)
print("RWT: Create " + str(reponse_id))
return reponse_id
except:
print("RWT: Falla creación de nuevo registro")
def sendApprove(self, objectName, id, resp):
config = self.getObjectConfig(objectName)
primaryKey = "Id"
hasMultipleIds = config['multipleIds']
ids = config['salesforceIds']
relatedObject = config['relatedObject']
salesForceSubObject = config['subObject']
salesForceObject = self.sf.__getattr__(objectName)
parsedData = {}
parsedData["Id"] = str(id)
if (str(resp) == "true"):
parsedData["StageName__c"] = "Ingreso a SAP"
else:
parsedData["Status"] = "Cancelado"
print("RWT: alreadyInSF?")
alreadyInSF, results = self.isAlreadyInSF(ids, parsedData,
hasMultipleIds, primaryKey,
objectName)
if alreadyInSF:
objectId = results['records'][0]['Id']
print("RWT: Update")
if (self.hasExternalId(config)):
externalId = primaryKey + '/' + str(id)
parsedData.pop(primaryKey, None)
print("RWT: Upsert Has External Id " + str(externalId))
try:
reponse_id = salesForceObject.upsert(
externalId, parsedData)
print("RWT: Response" + str(reponse_id))
return reponse_id
except:
print("RWT: Falla actualización de registro")
def closeApprove(self, objectName, id):
config = self.getObjectConfig(objectName)
primaryKey = "Order_ExternalId__c"
hasMultipleIds = config['multipleIds']
ids = config['salesforceIds']
relatedObject = config['relatedObject']
salesForceSubObject = config['subObject']
salesForceObject = self.sf.__getattr__(objectName)
parsedData = {}
parsedData["Order_ExternalId__c"] = str(id)
parsedData["Status"] = "Cerrado"
print("RWT: Close alreadyInSF?")
alreadyInSF, results = self.isAlreadyInSF(ids, parsedData,
hasMultipleIds, primaryKey,
objectName)
if alreadyInSF:
objectId = results['records'][0]['Id']
print("RWT: Update")
if (self.hasExternalId(config)):
externalId = 'Id/' + objectId
parsedData.pop(primaryKey, None)
print("RWT: Upsert Has External Id " + str(externalId))
try:
reponse_id = salesForceObject.upsert(
externalId, parsedData)
print("RWT: Response" + str(reponse_id))
return reponse_id
except:
print("RWT: Falla actualización de registro")
def is_int(self, string):
try:
int(string)
return True
except ValueError:
return False
def is_date(self, string, fuzzy=False):
try:
parse(string, fuzzy=False)
return True
except ValueError:
return False
def getObjectConfig(self, objectName):
path = "./config/{}.json".format(objectName)
config = json.load(open(path))
return config
def parse(self, data, config):
print("RWT: Parse " + str(config['hasCustomParsing']))
toSalesforce = {}
if (config['hasCustomParsing']):
data = self.customFunc.callOne(config['salesForceTableName'], data)
for field in config['fields']:
value = data[field['erpFieldName']]
if (type(value).__name__ == 'Decimal'):
value = float(value)
if (type(value).__name__ == 'LOB'):
value = value.read()
if (type(value).__name__ == 'str' and not self.is_int(value)
and self.is_date(value)):
value = parse(
value,
fuzzy=True).astimezone(UTC).strftime("%Y-%m-%dT%H:%M:%SZ")
toSalesforce[field['salesforceFieldName']] = value
print('RWT Parsing Result ')
print(toSalesforce)
return toSalesforce
def whereSalesforceQuery(self, primaryKeys, obj):
objKeys = []
for pk in primaryKeys:
print('whereSalesforceQuery: ' + pk)
tempString = "{}='{}'".format(pk, str(obj[pk]))
print('whereSalesforceQuery: ' + tempString)
objKeys.append(tempString)
return " WHERE {}".format(" AND ".join(objKeys))
def isAlreadyInSF(self, ids, toSales, hasMultipleIds, primaryKey,
salesForceName):
print('RWT: isAlreadyInSF??' + str(toSales) + str(primaryKey))
primaryKeyValue = str(toSales[primaryKey])
if (isinstance(toSales[primaryKey], str)):
primaryKeyValue = "'" + primaryKeyValue + "'"
whereClause = self.whereSalesforceQuery(ids, toSales) if (
hasMultipleIds
) else " WHERE " + primaryKey + " = " + primaryKeyValue
query = "SELECT ID FROM {} {}".format(salesForceName, whereClause)
print(query)
objectInfo = self.sf.query(query)
if (len(objectInfo['records']) > 0):
return True, objectInfo
return False, objectInfo
def hasExternalId(self, config):
if ('externalId' in config.keys()):
return True
return False
### NOT IN USE:
def getRequiredFields(self, salesforceObjectName, objectData, externalKey,
primaryKey):
parentObject = None
externalId = str(objectData[externalKey])
productCode = str(objectData[primaryKey])
if salesforceObjectName == 'PricebookEntry':
parentObject = "Pricebook2"
whereClause = " WHERE ExternalId__c = '" + externalId + "'"
query = "SELECT ID FROM {} {} ".format(parentObject, whereClause)
query2 = "SELECT ID FROM Product2 WHERE ProductCode = '" + productCode + "'"
print(query)
print(query2)
objectInfo = self.sf.query(query)
print(objectData)
print('RWT: A VER QUE SALE DE ESTO YA ACTUALIZADO ')
objectData['Pricebook2Id'] = objectInfo['records'][0]['Id']
print(objectData)
objectInfo = self.sf.query(query2)
print('RWT: A VER QUE SALE DE ESTO YA ACTUALIZADO ')
objectData['Product2Id'] = objectInfo['records'][0]['Id']
print(objectData)
return objectData
def subObject(self, salesforceSubObjectName, objectData, productId):
salesforceSubObject = self.sf.__getattr__(salesforceSubObjectName)
accountData = self.getAccount()
for key, value in accountData.items():
alreadyInSF = False
createFlag = False
toSalesforce = {}
query = "SELECT ID FROM Validation__c WHERE ProductId__c='" + productId + "' AND AccountId__c='" + value + "'"
objInfo = self.sf.query(query)
toSalesforce['ProductId__c'] = productId
toSalesforce['AccountId__c'] = value
if (len(objInfo['records']) > 0):
print('ESTO ES LO QUE HAY')
alreadyInSF = True
if (key == 'NAFIN'
and (objectData['QryGroup10'] or objectData['QryGroup14']
or objectData['QryGroup15'])):
toSalesforce['SelloFlag__c'] = objectData['QryGroup10']
toSalesforce['NAFlag__c'] = objectData['QryGroup14']
toSalesforce['CATFlag__c'] = objectData['QryGroup15']
createFlag = True
if (key == 'HSBC' and
(objectData['QryGroup11'] or objectData['QryGroup12'])):
toSalesforce['NAFlag__c'] = objectData['QryGroup11']
toSalesforce['CATFlag__c'] = objectData['QryGroup12']
createFlag = True
if (key == 'BANCOMEXT' and
(objectData['QryGroup13'] or objectData['QryGroup23'])):
toSalesforce['NAFlag__c'] = objectData['QryGroup13']
toSalesforce['CATFlag__c'] = objectData['QryGroup23']
createFlag = True
#if(key == 'CONDUSEF' and (objectData['QryGroup16'] or objectData['QryGroup17'])):
# toSalesforce['NAFlag__c'] = objectData['QryGroup16']
# toSalesforce['CATFlag__c'] = objectData['QryGroup17']
# createFlag = True
if (key == 'Santander' and
(objectData['QryGroup18'] or objectData['QryGroup19'])):
toSalesforce['NAFlag__c'] = objectData['QryGroup18']
toSalesforce['CATFlag__c'] = objectData['QryGroup19']
createFlag = True
if (key == 'Fonatur' and
(objectData['QryGroup20'] or objectData['QryGroup21'])):
toSalesforce['NAFlag__c'] = objectData['QryGroup20']
toSalesforce['CATFlag__c'] = objectData['QryGroup21']
createFlag = True
if (key == 'Banorte' and objectData['QryGroup22']):
toSalesforce['NAFlag__c'] = objectData['QryGroup22']
createFlag = True
if (key == 'Banobras' and
(objectData['QryGroup24'] or objectData['QryGroup25'])):
toSalesforce['NAFlag__c'] = objectData['QryGroup24']
toSalesforce['CATFlag__c'] = objectData['QryGroup25']
createFlag = True
#if(key == 'SAE' and (objectData['QryGroup26'] or objectData['QryGroup27'])):
# toSalesforce['NAFlag__c'] = objectData['QryGroup26']
# toSalesforce['CATFlag__c'] = objectData['QryGroup27']
# createFlag = True
if ((key == 'AME_BANK' or key == 'AME_SERVICIOS'
or key == 'AME_COMPANY') and objectData['QryGroup28']):
toSalesforce['NAFlag__c'] = objectData['QryGroup28']
createFlag = True
if (key == 'Vitamedica' and objectData['QryGroup29']):
toSalesforce['NAFlag__c'] = objectData['QryGroup29']
createFlag = True
#if(key == 'Metlife' and (objectData['QryGroup34'] or objectData['QryGroup35'])):
# toSalesforce['NAFlag__c'] = objectData['QryGroup39']
# toSalesforce['CATFlag__c'] = objectData['QryGroup40']
# createFlag = True
#if(key == 'Metlife' and (objectData['QryGroup36'] or objectData['QryGroup37'] or objectData['QryGroup38'])):
# toSalesforce['NAFlag__c'] = objectData['QryGroup36']
# toSalesforce['CATFlag__c'] = objectData['QryGroup37']
# toSalesforce['SelloFlag__c'] = objectData['QryGroup38']
# createFlag = True
if (key == 'Metlife' and
(objectData['QryGroup39'] or objectData['QryGroup40'])):
toSalesforce['NAFlag__c'] = objectData['QryGroup39']
toSalesforce['CATFlag__c'] = objectData['QryGroup40']
createFlag = True
if (alreadyInSF):
print('VA A ACTUALIZAR')
print(toSalesforce)
print("RWT: Update " + str(
salesforceSubObject.update(objInfo['records'][0]['Id'],
toSalesforce)))
elif (createFlag):
print('VA A CREAR REGISTRO')
print(toSalesforce)
reponse_id = salesforceSubObject.create(toSalesforce)
print("RWT: Create Has Multiple Ids RESULT " + str(reponse_id))
#flag,data = self.customFunc.callOne("Validation__c", objectData)
#print (key)
#print (data)
#print (flag)
def getAccount(self):
accountIds = {}
query = "SELECT ID FROM ACCOUNT WHERE NAME= '"
queryInst = query + "NAFIN'"
objInfo = self.sf.query(queryInst)
accountIds['NAFIN'] = objInfo['records'][0]['Id']
queryInst = query + "HSBC'"
objInfo = self.sf.query(queryInst)
accountIds['HSBC'] = objInfo['records'][0]['Id']
queryInst = query + "BANCOMEXT'"
objInfo = self.sf.query(queryInst)
accountIds['BANCOMEXT'] = objInfo['records'][0]['Id']
queryInst = query + "BANCOMEXT'"
objInfo = self.sf.query(queryInst)
accountIds['BANCOMEXT'] = objInfo['records'][0]['Id']
#queryInst = query + "CONDUSEF'"
#objInfo=self.sf.query(queryInst)
#accountIds['CONDUSEF']=objInfo['records'][0]['Id']
queryInst = query + "Santander'"
objInfo = self.sf.query(queryInst)
accountIds['Santander'] = objInfo['records'][0]['Id']
queryInst = query + "Banorte'"
objInfo = self.sf.query(queryInst)
accountIds['Banorte'] = objInfo['records'][0]['Id']
queryInst = query + "Banobras'"
objInfo = self.sf.query(queryInst)
accountIds['Banobras'] = objInfo['records'][0]['Id']
queryInst = query + "Fonatur'"
objInfo = self.sf.query(queryInst)
accountIds['Fonatur'] = objInfo['records'][0]['Id']
#queryInst = query + "SAE'"
#objInfo=self.sf.query(queryInst)
#accountIds['SAE']=objInfo['records'][0]['Id']
queryInst = query + "AME_BANK'"
objInfo = self.sf.query(queryInst)
accountIds['AME_BANK'] = objInfo['records'][0]['Id']
queryInst = query + "AME_SERVICIOS'"
objInfo = self.sf.query(queryInst)
accountIds['AME_SERVICIOS'] = objInfo['records'][0]['Id']
queryInst = query + "AME_COMPANY'"
objInfo = self.sf.query(queryInst)
accountIds['AME_COMPANY'] = objInfo['records'][0]['Id']
queryInst = query + "Vitamedica'"
objInfo = self.sf.query(queryInst)
accountIds['Vitamedica'] = objInfo['records'][0]['Id']
#queryInst = query + "Banjercito'"
#objInfo=self.sf.query(queryInst)
#accountIds['Banjercito']=objInfo['records'][0]['Id']
#queryInst = query + "CNBV'"
#objInfo=self.sf.query(queryInst)
#accountIds['CNBV']=objInfo['records'][0]['Id']
queryInst = query + "Metlife'"
objInfo = self.sf.query(queryInst)
accountIds['Metlife'] = objInfo['records'][0]['Id']
print(accountIds)
return accountIds
def getAllFields(self, obj):
fields = []
for x in obj:
fields.append(x['name'])
return fields, ','.join(fields)
def getFullData(self, modifided, fieldsString, salesForceName):
objectInfo = None
data = []
for objectId in modifided['ids']:
query = "SELECT " + fieldsString + " FROM " + salesForceName + " WHERE ID = '" + objectId + "'"
objectInfo = self.sf.query(query)
objectInfo = objectInfo['records'][0]
data.append(objectInfo)
return data
def getRecordsToUpdate(self, objectName):
queryTime = 864000
salesForceObject = self.sf.__getattr__(objectName)
fields, fieldsString = self.getAllFields(
salesForceObject.describe()['fields'])
end = datetime.datetime.now(pytz.UTC)
modifided = salesForceObject.updated(
end - datetime.timedelta(seconds=queryTime), end)
modifided = self.getFullData(modifided, fieldsString, objectName)
return modifided
class SFInteraction(object):
"""Simple class that interacts with Salesforce"""
def __init__(self,
username,
password,
token,
uat,
project_id='datacoco3.db',
session_id=None,
instance=None,
version=None):
"""Instantiate a Salesforce interaction manager.
UAT mode is explicitly set to a boolean value in case a string is provided.
If Salesforce session credentials do not exist, attempt to retrieve.
:param username: Username
:param password: Password
:param token: API token
:param uat: Whether or not in UAT mode
:param project_id: to identify project source api calls
:param session_id: Access token for existing session
:param instance: Domain of Salesforce instance
"""
if not username or not password or not token or uat is None:
raise RuntimeError("%s request all __init__ arguments" % __name__)
self.username = username
self.password = password
self.token = token
self.session_id = session_id
self.instance = instance
self.project_id = project_id
self.version = version
self.conn = None
self.bulk = None
self.job_id = None
self.batch_max_attempts = None
self.batch_timeout = None
self.batch_sleep_interval = None
self.temp_file = "sf_temp_results.txt"
self.redis_conn = None
self.session_credentials_key = "sf_session_credentials"
# Handle string uat which should be boolean
if isinstance(uat, str) and uat.lower() in ("true", "t"):
self.uat = True
else:
self.uat = False
# Retrieve session_id and/or instance if they do not exist
if not self.session_id or not self.instance:
self._get_session_credentials()
def connect(self):
"""Connect to the Salesforce API client.
Only executes if there is not an existing open Salesforce connection.
If there are a session_id and an instance, attempt to connect to
the existing session. The existing session connection is verified with a Salesforce API describe call.
If that fails, create a new connection.
There are 3 retry attempts
"""
if self.session_id and self.instance:
retry_count = 1
while True:
if retry_count > 3:
LOG.l(
"Could not connect to Salesforce in the specified number of retries."
)
LOG.l("Starting a new connection...")
break
else:
LOG.l(
f"Connecting to Salesforce: attempt {retry_count} of 3..."
)
try:
self.conn = Salesforce(session_id=self.session_id,
instance=self.instance,
client_id=self.project_id,
version=self.version)
self.conn.describe() # Connection health check
return #Sucess, leave this function
except SalesforceError as sfe:
LOG.l(
f"Encountered error connecting to Salesforce:\n{sfe}")
retry_count += 1
sleep(5)
continue
#If reconnecting didn't work or session_id is not set, then start a new connection
try:
self._create_new_connection()
except:
raise Exception("Could not initiate connection to Salesforce!")
def fetch_soql(
self,
db_table,
soql,
batch=True,
batch_timeout=600,
batch_sleep_int=10,
batch_max_attempts=1,
):
"""Fetch results from Salesforce soql queries.
Batch Salesforce queries results saved to a file and retrieved because they are in CSV format
and to avoid bulk queries timeouts.
:param db_table: Database table name
:param soql: Soql queries
:param batch: Whether to use Salesforce Batch or Simple API
:param batch_sleep_int: Salesforce Bulk query sleep interval
:param batch_timeout: Batch job timeout in seconds
:param batch_max_attempts: Maximum number of batch query creation attempts
:return: If success, List of result dictionaries; Else empty list
"""
try:
if batch:
# Set batch operation attributes
self.batch_timeout = batch_timeout
self.batch_sleep_interval = batch_sleep_int
self.batch_max_attempts = batch_max_attempts
results = self.get_query_records_dict(db_table, soql)
# save to and read from file to avoid connection timeout
self._save_results_to_file(results)
records = self._get_results_from_file()
else:
result = self.conn.query(soql)
# if there isn't a result return an empty list
if result["records"]:
salesforce_records = json.loads(
json.dumps(result["records"][0]))
parsed_records = parse_sf_records(salesforce_records)
records = [parsed_records
] # put result in a list object for consistency
else:
records = []
except BulkApiError as e:
self.bulk.abort_job(self.job_id)
# TODO Handle failed bulk API transaction better
raise e
return records
def get(self, object_name: str, object_id: str):
"""
To get a dictionary with all the information regarding that record
"""
return self.conn.__getattr__(object_name).get(object_id)
def get_by_custom_id(self, object_name: str, field: str, id: str):
"""
To get a dictionary with all the information regarding that record
using a **custom** field that was defined as External ID:
"""
return self.conn.__getattr__(object_name).get_by_custom_id(field, id)
def upsert(self, object_name: str, field: str, id: str, data: dict):
"""
To insert or update (upsert) a record using an external ID
"""
return self.conn.__getattr__(object_name).upsert(f'{field}/{id}', data)
def get_query_records_dict(self, db_table, soql_query):
"""Execute bulk Salesforce soql queries and return results as generator of dictionaries.
:param db_table: Database table name
:param soql_query: Soql queries
:return: If success, List of result record dictionaries; Else empty list
"""
self.bulk = SalesforceBulk(sessionId=self.session_id,
host=self.instance)
job = self.bulk.create_query_job(db_table, contentType="JSON")
batch = self.bulk.query(job, soql_query)
self.bulk.close_job(job)
while not self.bulk.is_batch_done(batch):
print("Waiting for batch query to complete")
sleep(10)
dict_records = []
rec_count = 0
print("Iterating through batch result set")
for result in self.bulk.get_all_results_for_query_batch(batch):
result = json.load(IteratorBytesIO(result))
for row in result:
rec_count += 1
dict_records.append(row)
print("Current fetched record count: ", rec_count)
return dict_records
def batch_query_records_dict(self,
db_table,
soql_query,
concurrency='Serial'):
"""Execute bulk Salesforce soql queries and return results as generator of dictionaries.
works only for PK CHUNKING enabled SF tables.
Allows millions of record read.
:param db_table: Database table name
:param soql_query: Soql queries
:return: If success, List of result record dictionaries; Else empty list
"""
self.bulk = SalesforceBulk(sessionId=self.session_id,
host=self.instance)
job = self.bulk.create_query_job(db_table,
contentType="JSON",
pk_chunking=True,
concurrency=concurrency)
try:
batch = self.bulk.query(job, soql_query)
batch_list = self.bulk.get_batch_list(job)
print('first batch', batch_list[0])
batch_id = batch_list[0]['id']
job_id = batch_list[0]['jobId']
state = batch_list[0]['state']
while state == 'Queued' or state == 'InProgress':
print(
"Waiting for batch state Queued or InProgress to change " +
state)
sleep(10)
state = self.bulk.batch_state(batch_id, job_id)
batch_list = self.bulk.get_batch_list(job)
print(f'number of batches: {len(batch_list)}')
for item in batch_list:
print('item', item)
batch_id = item['id']
job_id = item['jobId']
state = item['state']
if state == 'NotProcessed':
continue
while not self.bulk.is_batch_done(batch_id, job_id):
print(
f"Waiting for batch query to complete batch_id:{batch_id}, job_id: {job_id}, state: {state}"
)
sleep(10)
state = self.bulk.batch_state(batch_id, job_id)
total_retry_count = len(batch_list)
retry = len(batch_list)
lastIndex = 0
while retry > 0:
print(f'retry {retry} times left')
try:
for result in list(
self.bulk.get_all_results_for_query_batch(
batch_id, job_id))[lastIndex:]:
result = json.load(IteratorBytesIO(result))
lastIndex += 1
yield result
break
except requests.exceptions.ChunkedEncodingError as e:
print('Chunking failed')
retry -= 1
self.connect()
self.bulk = SalesforceBulk(sessionId=self.session_id,
host=self.instance)
pass
except Exception as e:
print('There was an error')
traceback.print_exc()
retry -= 1
self.connect()
self.bulk = SalesforceBulk(sessionId=self.session_id,
host=self.instance)
pass
if retry <= 0:
raise Exception(
f'Retried {total_retry_count} times and it still failed'
)
except BulkApiError as e:
self.bulk.abort_job(self.job_id)
raise e
def upload_records_to_s3(self, records, s3_bucket, s3_key, aws_access_key,
aws_secret_key):
"""Upload records to s3.
:param records: Records filename
"""
self._save_results_to_file(records)
datetime_today = datetime.today().strftime("%Y-%m-%d-%X")
s3_dest_key = s3_key + datetime_today
s3_interaction = S3Interaction(aws_access_key, aws_secret_key)
s3_interaction.put_file_to_s3(s3_bucket, s3_dest_key, self.temp_file)
return s3_dest_key
def get_description(self, object_name):
"""Retrieves object description
:param object_name: Salesforce object/table name
"""
retry = True
while retry:
try:
return self.conn.__getattr__(object_name).describe()
except SalesforceError as sfe:
retry = self._sf_except_reconnect(sfe)
def _sf_except_reconnect(self, e):
""" Used in try/catch blocks to reinit the connection
returns true if the code should be retried, false if no connection could be made
"""
LOG.l(f"Encountered error:\n{e}")
try:
self.connect()
return True
except Exception:
return False
def _create_new_connection(self):
"""Create a new Salesforce API client connection.
After the connection is created, the Salesforce session credentials are stored externally.
"""
self.conn = Salesforce(username=self.username,
password=self.password,
security_token=self.token,
sandbox=self.uat,
client_id=self.project_id)
self.session_id = str(self.conn.session_id)
self.instance = str(self.conn.sf_instance)
self._set_session_credentials()
def _save_results_to_file(self, records):
"""Save Salesforce Bulk API results to a temp file.
:param records: Records to save
"""
with open(self.temp_file, "w") as f:
for r in records:
f.write("\n")
f.write(str(str(r).encode("utf-8")))
def _get_results_from_file(self):
"""Get Salesforce Bulk API results from a temp file.
The records must be parsed. After the results are retrieved. The file is deleted.
:return: Iterator with records.
"""
results = []
with open(self.temp_file, "r") as f:
records = f.read()[1:].splitlines()
for r in records:
r = ast.literal_eval(r)
results.append(r)
os.remove(self.temp_file)
return results
def _get_session_credentials(self):
"""Get Salesforce session credentials stored in Redis.
If the credentials variables do not exist, set the credentials as None.
"""
# Establish connection to Redis
self._connect_to_redis()
# Get salesforce credentials if exists
if self.redis_conn.conn.exists(self.session_credentials_key):
self.session_id = self.redis_conn.fetch_by_key_name(
self.session_credentials_key, "session_id")
self.instance = self.redis_conn.fetch_by_key_name(
self.session_credentials_key, "instance")
else:
self.session_id = None
self.instance = None
def _set_session_credentials(self):
"""Set Salesforce session credentials in Redis.
"""
sf_session_credentials = {
"session_id": self.session_id,
"instance": self.instance,
}
self.redis_conn.set_key(self.session_credentials_key,
sf_session_credentials)
def _connect_to_redis(self):
"""Connect to Redis.
"""
CONF = config()
host = CONF["redis"]["server"]
port = CONF["redis"]["port"]
db = CONF["redis"]["db"]
self.redis_conn = RedisInteraction(host, port, db)
self.redis_conn.connect()
sf = Salesforce(username=username,password=password, security_token=security_token)
#get a list of queryable object names we will need to backup
if args.objectNames:
names = args.objectNames
else:
#get a description of our global salesforce instance, see layout:
# https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_describeGlobal.htm
description = sf.describe()
names = [obj['name'] for obj in description['sobjects'] if obj['queryable']]
#for every object we'll need all the fields it has that are exportable.
for name in names:
salesforceObject = sf.__getattr__(name)
# so get a list of the object fields for this object.
fieldNames = [field['name'] for field in salesforceObject.describe()['fields']]
# then build a SOQL query against that object and do a query_all
try:
results = sf.query_all( "SELECT " + ", ".join(fieldNames) + " FROM " + name )['records']
except SalesforceMalformedRequest as e:
# ignore objects with rules about getting queried.
continue
outputfile = datapath / (name+".csv")
with outputfile.open(mode='w', encoding='utf_8_sig) as csvfile:
writer = DictWriter(csvfile,fieldnames=fieldNames)
writer.writeheader()
for row in results:
# each row has a strange attributes key we don't want
row.pop('attributes',None)
password = '******'
security_token = '3ojna9b4VIY7grIPp0ZmNhlD'
sf = Salesforce(username=username,password=password, security_token=security_token)
#Declare vaiables
localCSVDir = 'c:/kenandy/python/localCSV/'
sourceCSVDir = 'c:/kenandy/python/sourceCSV/'
stageCSVDir = 'c:/kenandy/python/stageCSV/'
configDir = 'c:/kenandy/python/Configuration/'
myObject = "KSPRING17__Customer__c"
#myObject = "KSPRING17__Supplier__c"
salesforceObject = sf.__getattr__(myObject)
fieldNames = [field['name'] for field in salesforceObject.describe()['fields']]
print(fieldNames)
#soqlQuery = "SELECT " + ", ".join(fieldNames) + " FROM " + myObject "WHERE RecordTypeId = '0121I0000009IBkQAM'"
recordTypeName = "'Sold To'"
recordTypeName = recordTypeName.replace('Name=','')
#subquery recordtype
#SELECT Id, name FROM KNDY4__Customer__c WHERE recordtypeID IN (SELECT Id FROM RecordType WHERE Name = 'Remit To')
soqlQuery = "SELECT " + ", ".join(fieldNames) + " FROM " + myObject + " WHERE " + "recordtypeid IN (SELECT Id FROM RecordType WHERE Name = " + recordTypeName + ")"
print (soqlQuery)
records = sf.query_all(soqlQuery)['records']
for row in records:
class SalesforceQ:
def __init__(self,
instance=None,
instance_url=None,
username=None,
password=None,
security_token=None,
organizationId=None,
domain='login'):
self.username = escape(username) if username else None
self.password = escape(password) if password else None
self.instance = escape(instance) if instance else None
self.instance_url = escape(instance_url) if instance_url else None
self.sf = None
if self.username is not None and self.password is not None and self.instance is not None or self.instance_url is not None or self.instance is not None:
if security_token is not None:
self.security_token = security_token
self.sf = Salesforce(instance=self.instance,
username=self.username,
password=self.password,
security_token=self.security_token,
domain=domain)
self.Account = Account(self.sf)
self.Contact = Contact(self.sf)
self.Case = Case(self.sf)
self.Opportunity = Opportunity(self.sf)
self.Contract = Contract(self.sf)
elif organizationId is not None:
self.organizationId = organizationId
self.sf = Salesforce(instance=self.instance,
username=self.username,
password=self.password,
organizationId=self.organizationId,
domain=domain)
self.Account = Account(self.sf)
self.Contact = Contact(self.sf)
self.Case = Case(self.sf)
self.Opportunity = Opportunity(self.sf)
self.Contract = Contract(self.sf)
if self.sf is None:
raise SalesforceAuthenticationFailed(
'INVALID AUTH',
'You must submit username and password either a security token or '
'organizationId for authentication')
else:
"""SOQL queries:
query: #Equivalent to .get(path='query', params='q=SELECT Id, Name FROM Contact WHERE LastName = 'Adam'')
.query("SELECT Id, Name FROM Contact WHERE LastName = 'Adam'")
query_more:
If, due to an especially large result, Salesforce adds a nextRecordsUrl to your query result,
such as "nextRecordsUrl" : "/services/data/v26.0/query/01gD0000002HU6KIAW-2000", you can pull the
additional results with either the ID or the full URL (if using the full URL, you must pass 'True' as
your second argument)
.query_more("01gD0000002HU6KIAW-2000")
.query_more("/services/data/v26.0/query/01gD0000002HU6KIAW-2000", True)
query_all:
A convenience of query_more, to retrieve all of the results in a single local method call use
.query_all("SELECT Id, Email FROM Contact WHERE LastName = 'Jones'")
"""
self.query = self.sf.query
self.query_more = self.sf.query_more
self.query_all = self.sf.query_all
self.search = self.sf.search
else:
raise SalesforceAuthenticationFailed(
'INVALID AUTH',
'You must submit username and password either a security token or '
'organizationId for authentication')
def get(self, path, params=None, **kwargs):
"""Allows you to make a direct GET REST call if you know the path
EXAMPLE: .get(path='sobjects/Account/0017j00000VLkZtAAL', params={"fields" : "Name"}))
Arguments:
* path: The path of the request
Example: sobjects/User/ABC123/password'
* params: dict of parameters to pass to the path
* method: HTTP request method, default GET
* other arguments supported by requests.request (e.g. json, timeout)
:return JSON / False if issue has occurred
"""
try:
res = self.sf.restful(path=path,
params=params,
method='GET',
**kwargs)
return res
except SalesforceResourceNotFound as e:
print("[GET]{errorCode}: Resource {name} not found. {message}".
format(message=e.content[0]['message'],
name=e.resource_name,
errorCode=e.content[0]['errorCode']))
return False
except SalesforceMalformedRequest as e: # Deletion failed (could be due account being associated to existing cases)
print(
"[GET]{errorCode}: Malformed request {url}. {message}".format(
message=e.content[0]['message'],
url=e.url,
errorCode=e.content[0]['errorCode']))
return False
except Exception as e:
print("Something went wrong!")
print(e)
return False
def get_sobject(self, sobject=None, sobject_id=None):
"""Allows you to make a direct GET REST call if you know the path
EXAMPLE: .get(path='sobjects/Account/0017j00000VLkZtAAL', params={"fields" : "Name"}))
Arguments:
* path: The path of the request
Example: sobjects/User/ABC123/password'
* params: dict of parameters to pass to the path
* method: HTTP request method, default GET
* other arguments supported by requests.request (e.g. json, timeout)
:return JSON objects list / False if issue has occurred
"""
try:
if isinstance(sobject_id, str):
sobject_data = self.__getattr__(sobject).get(sobject_id)
return [Parser.parse(sobject_data)]
elif isinstance(sobject_id, list):
sobjects_data = []
for sobject_sid in sobject_id:
sobject_data = self.__getattr__(sobject).get(sobject_sid)
sobjects_data.append(Parser.parse(sobject_data))
return sobjects_data
except SalesforceResourceNotFound as e:
print("[GET]{errorCode}: Resource {name} not found. {message}".
format(message=e.content[0]['message'],
name=e.resource_name,
errorCode=e.content[0]['errorCode']))
return False
except SalesforceMalformedRequest as e: # Deletion failed (could be due account being associated to existing cases)
print(
"[GET]{errorCode}: Malformed request {url}. {message}".format(
message=e.content[0]['message'],
url=e.url,
errorCode=e.content[0]['errorCode']))
return False
except Exception as e:
print("Something went wrong!")
print(e)
return False
def get_sobject_type(self, sobject_id):
"""Get sobject type by ID"""
try:
res = self.get(path=f'ui-api/record-ui/{sobject_id}')
od = collections.OrderedDict(
sorted(res['layouts'].items(), key=lambda x: x[1]))
return list(od.keys())[0]
except SalesforceResourceNotFound as e:
print("[GET]{errorCode}: Resource {name} not found. {message}".
format(message=e.content[0]['message'],
name=e.resource_name,
errorCode=e.content[0]['errorCode']))
return False
except SalesforceMalformedRequest as e: # Deletion failed (could be due account being associated to existing cases)
print(
"[GET]{errorCode}: Malformed request {url}. {message}".format(
message=e.content[0]['message'],
url=e.url,
errorCode=e.content[0]['errorCode']))
return False
except Exception as e:
print("Something went wrong!")
print(e)
return False
def post(self, path, params=None, **kwargs):
"""Allows you to make a direct POST REST call if you know the path
EXAMPLE: .post(path='sobjects/Account',params=None,json={"Name" : "MyREST Test account"})
Arguments:
* path: The path of the request
Example: sobjects/User/ABC123/password'
* params: dict of parameters to pass to the path
* method: HTTP request method, default GET
* other arguments supported by requests.request (e.g. json, timeout)
:return JSON
"""
try:
res = self.sf.restful(path=path,
params=params,
method='POST',
**kwargs)
return res
except SalesforceResourceNotFound as e:
print("[POST]{errorCode}: Resource {name} not found. {message}".
format(message=e.content[0]['message'],
name=e.resource_name,
errorCode=e.content[0]['errorCode']))
return False
except SalesforceMalformedRequest as e:
print(
"[POST]{errorCode}: Malformed request {url}. {message}".format(
message=e.content[0]['message'],
url=e.url,
errorCode=e.content[0]['errorCode']))
return False
except Exception as e:
print("Something went wrong!")
print(e)
return False
def patch(self, path, params=None, **kwargs):
"""Allows you to make a direct POST REST call if you know the path
EXAMPLE: .patch(path='sobjects/Account',params=None,json={"Name" : "MyREST Test account"})
Arguments:
* path: The path of the request
Example: sobjects/User/ABC123/password'
* params: dict of parameters to pass to the path
* method: HTTP request method, PATCH
* other arguments supported by requests.request (e.g. json, timeout)
:return JSON
"""
try:
res = self.sf.restful(path=path,
params=params,
method='PATCH',
**kwargs)
return res
except SalesforceResourceNotFound as e:
print("[PATCH]{errorCode}: Resource {name} not found. {message}".
format(message=e.content[0]['message'],
name=e.resource_name,
errorCode=e.content[0]['errorCode']))
return False
except SalesforceMalformedRequest as e:
print("[PATCH]{errorCode}: Malformed request {url}. {message}".
format(message=e.content[0]['message'],
url=e.url,
errorCode=e.content[0]['errorCode']))
return False
except Exception as e:
print("Something went wrong!")
print(e)
return False
def delete(self, path, params=None, **kwargs):
"""Allows you to make a direct DELETE REST call if you know the path
EXAMPLE: .delete(path='sobjects/Account/recordId')
Arguments:
* path: The path of the request
Example: sobjects/User/ABC123/password'
* params: dict of parameters to pass to the path
* method: HTTP request method, DELETE
* other arguments supported by requests.request (e.g. json, timeout)
:return JSON
"""
try:
res = self.sf.restful(path=path,
params=params,
method='DELETE',
**kwargs)
return res
except SalesforceResourceNotFound as e:
print("[DELETE]{errorCode}: Resource {name} not found. {message}".
format(message=e.content[0]['message'],
name=e.resource_name,
errorCode=e.content[0]['errorCode']))
return False
except SalesforceMalformedRequest as e:
print("[DELETE]{errorCode}: Malformed request {url}. {message}".
format(message=e.content[0]['message'],
url=e.url,
errorCode=e.content[0]['errorCode']))
return False
except Exception as e:
print("Something went wrong!")
print(e)
return False
def __getattr__(self, name):
"""
Every salesforce object without a written class can still access the following functions:
metadata / describe / describe_layout / get / get_by_custom_id / create / upsert / update / delete / deleted / updated
More information at SFType Class:
https://github.com/simple-salesforce/simple-salesforce/blob/5d921f3dd32a69472b31d435544ce9c5a1d5eba3/simple_salesforce/api.py#L638
:param name: sobject name
:return: SFType object
"""
return self.sf.__getattr__(name)
ktc_tables = sf.describe()
ktc_tables = ktc_tables[u'sobjects']
table_names = []
for i in ktc_tables:
table_names.append(i[u'name'])
#the ones we want
desired_tables = ['Account', 'Contact', 'College_Persistence__c', 'Contact', 'Contact_Note__c', 'Enrollment__c']
all_tables = defaultdict()
for i in desired_tables:
print 'Retrieving fields for table %s from Salesforce.' % i
#this gets us the attributes of the table
this_table = sf.__getattr__(i).describe()
#this has the list of ordered dictionaries that describe the fields
table_fields = this_table['fields']
#list comprehension style
field_names = []
[field_names.append(item['name']) for item in table_fields]
all_tables[i] = field_names
# #non list comprehension style, fro reference
# field_names = []
# for item in table_fields:
# field_names.append(item['name'])
class SF2MySQLCopy:
def __init__(self):
v = mysql.connector.version.VERSION
if v[0] < 2 or v[1] < 1 or v[2] < 3:
raise Exception('Minimum required MySQL driver version is 2.1.3')
self.salesforce = Salesforce(username=sf_credentials.SF_USER, password=sf_credentials.SF_PASSWD, security_token=sf_credentials.SF_SEC_TOKEN, sandbox=False, sf_version='34.0')
self.salesforce.headers['Accept-Encoding'] = 'gzip'
# https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/headers_queryoptions.htm
self.salesforce.headers['Sforce-Query-Options'] = 'batchSize=2000'
self.mysql_connection = mysql.connector.connect(user='******', password='******',
host=mysql_config.MYSQL_HOST,
port=mysql_config.MYSQL_PORT,
database=mysql_config.MYSQL_SCHEMA,
charset=mysql_config.MYSQL_CHARSET,
use_unicode=True)
def copy_sobjects(self, sobjects_names):
for sobject in sobjects_names:
self._actually_copy_sobject(sobject);
def copy_sobject(self, sobject_name):
self._actually_copy_sobject(self.mysql_connection, sobject_name)
def _actually_copy_sobject(self, mysql_connection, sobject_name):
# Step 1: Obtain SObject's metadata and instantiate helper objects
stype = self.salesforce.__getattr__(sobject_name)
print 'Retrieving metadata for SObject ' + sobject_name + '...'
sobject_desc = stype.describe()
sobject_fields_map = self._sobject_fields_map(sobject_desc['fields'])
table_creator = MySQLTableCreator(sobject_desc)
soql_helper = SOQLHelper(sobject_desc)
cursor = mysql_connection.cursor()
# Step 2: Drop table if table already exists
drop_table_statement = table_creator.generate_drop_table()
try:
cursor.execute(drop_table_statement)
print 'Dropped MySQL table: ' + table_creator.mysql_table_name
except mysql.connector.Error as err:
print 'MySQL Error when executing: ' + drop_table_statement
# Step 3: Create table
create_table_statement = table_creator.generate_create_table()
create_index_statements = table_creator.generate_indexes_for_references()
table_name = table_creator.mysql_table_name
try:
cursor.execute(create_table_statement)
for create_index_statement in create_index_statements:
cursor.execute(create_index_statement)
print 'Created MySQL table: ' + table_name
except mysql.connector.Error as err:
print 'MySQL Error when executing: ' + create_table_statement
print 'Skipping SObject copy ...'
return
# Step 4: Start to pump data from Salesforce
soql_query = soql_helper.generate_soql_select()
print 'Querying SObject ' + sobject_desc['name']
timer = Timer()
timer.start()
results = self.salesforce.query(soql_query)
total_size = results['totalSize'] # Int
print 'Salesforce query retrieved ' + str(total_size) + ' records for SObject ' + sobject_desc['name']
done = False
paged = False
page_number = 0
pages_count = 0
insert_statement = table_creator.insert_statement()
while not done:
done = results['done']
if not done and not paged:
paged = True
sf_records = results['records']
page_size = len(sf_records)
if pages_count == 0:
pages_count = total_size / page_size
pages_count = int(math.ceil(pages_count))
if not done:
print 'Query result is paged'
if paged:
page_number = page_number + 1
print 'Page #' + str(page_number) + ': ' + str(page_size) + ' records'
mysql_records = []
for sf_record in sf_records:
mysql_record = table_creator.sf_record2mysql_record(sf_record)
mysql_records.append(mysql_record)
# Step 5: Insert records into MySQL
print 'Inserting ' + str(len(mysql_records)) + ' records into the ' + table_name + ' table'
try:
cursor.executemany(insert_statement, mysql_records)
mysql_connection.commit()
except mysql.connector.Error as err:
print 'MySQL Error when executing: ' + insert_statement
print 'Aborting ...'
return
if not done:
if 'nextRecordsUrl' in results:
next_records = results['nextRecordsUrl']
print 'Retrieving page ' + str(page_number + 1) + ' of ' + str(pages_count) + ' ...'
print 'ETC: ' + timer.formatted_etc(page_number, pages_count)
results = self.salesforce.query_more(next_records, True)
else:
done = True
cursor.close()
def _sobject_fields_map(self, sobject_fields):
map = {}
for field in sobject_fields:
map[field['name']] = field
return map
