Extract Large Datasets from Salesforce using Python

Have you ever encountered challenges when trying to extract large datasets from Salesforce? Many businesses face difficulties due to Salesforce's limitations on the amount of data that can be retrieved in a single query. Extracting large volumes of data efficiently and effectively becomes crucial for comprehensive analysis, reporting, and other data-driven processes.

To overcome these challenges, businesses require a solution that allows them to extract large datasets from Salesforce using Python without hitting limitations and compromising performance.

Set up the Python package and API access

To get started, you'll need to install an open-source package called Simple Salesforce. It's like a special tool that acts as a client for the Salesforce REST API. This API allows us to interact with Salesforce and retrieve data in different ways.

You can install it by running the below command in your terminal or command prompt.

pip install simple-salesforce        

Once installed, you can import the Simple Salesforce package and all the related packages in your Python script.

from simple_salesforce import Salesforce, SalesforceLogin
import json
import csv
import pandas as pd?        

The package provides a convenient way to interact with Salesforce's data without the need for a connected app setup. Instead, you can directly authenticate and access Salesforce's API using your Salesforce username, password, and security token.?

login = json.load(open('login.json'))
username = login['username']
password = login['password']
token = login['token']

session_id, instance = SalesforceLogin(username= username , password= password , security_token=token, domain = 'login' )
sf = Salesforce(instance = instance, session_id= session_id)        

NOTE : Here we are using a JSON file (login.json) to store the credentials, which can be read by the Python script. Alternatively, you can use environment variables specific to your operating system to store and retrieve the credentials, to ensure that the credentials are kept confidential and not exposed directly in the code.

Fetching the data with SOQL

We have to frame a query to specify the data we want to retrieve for which query method provided by the Simple Salesforce package can be used. When working with large datasets, Salesforce limits the number of records returned in a single query.

To overcome this limitation, the query method provides a feature called pagination. After executing the initial query, Salesforce includes a nextRecordsUrl in the response. We can use this URL to fetch the next set of records. By iterating through the results and making subsequent requests using the nextRecordsUrl, we can retrieve the entire dataset in smaller chunks.

This approach ensures that you can efficiently handle large datasets without overwhelming Salesforce's limitations.

queryRecords = 'SELECT ID, Name, Industry, OwnerId FROM Account'

records = sf.query(queryRecords)
list_records = records.get('records')
nextRecordsUrl = records.get('nextRecordsUrl')


while not records.get('done') :
? ? records = sf.query_more(nextRecordsUrl, identifier_is_url = True)
? ? list_records.extend(records.get('records'))
? ? nextRecordsUrl = records.get('nextRecordsUrl')        

Constructing a DataFrame from the dictionary

The query results are typically returned in the form of a list of dictionaries. To further analyse or export the data, you can convert the query results into a DataFrame using the pandas library.

?df_records = pd.DataFrame(list_records)        

Once you have the data in DataFrame format, you can leverage the powerful functionalities of pandas to perform tasks such as filtering, sorting, aggregating, or generating visualizations. Additionally, if you need to export the data as a CSV file, pandas provides a simple method called "to_csv" that allows you to save the DataFrame as a CSV file.

?df_records=df_records.apply(pd.Series).drop(labels = 'attributes', axis = 1,inplace = False)
 df_records.to_csv("OUTPUT.csv", index = False)        

Conclusion

Extracting large datasets from Salesforce using Python can be a complex task, but with the help of the Simple Salesforce package, it becomes more streamlined and efficient. By framing a query and utilizing the query method, we can retrieve the desired data from Salesforce's API, even when working with large datasets. The package's built-in pagination support allows us to fetch data in smaller chunks, overcoming Salesforce's limitations and ensuring efficient data extraction.

Once the data is fetched, converting the query results into a DataFrame using the pandas library enables us to work with the data in a tabular format. This opens up a wide range of possibilities for data manipulation, analysis, and visualization. Additionally, pandas provides an easy way to export the DataFrame as a CSV file, allowing for seamless sharing and integration with other tools and applications.

You can refer the code here.

References :