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