Extracting Data from Salesforce with Python

You don’t need to rely on apex developers or a middleware to get data out of Salesforce. If you have some python skills you can extract all the data you need in a few lines of code! Today we will look at extracting public groups and their users.

Photo by C Dustin on Unsplash

Getting Started

We have a couple of small requirements to fulfill to get started. The Salesforce API is quite robust, as a result the authentication process can be quite tedious. Luckily, there is a library called simple-salesforce. This will handle all of the heavy lifting of authentication and make the querying of data quite easy. We will also utilize pandas to handle some data manipulation.

pip install simple-salesforce
pip install pandas

After you’ve got those two packages installed, we’re ready to get logged in!

from simple_salesforce import Salesforce
import pandas as pd

sf = Salesforce(username="yourSFusername",password="youSFpassword", security_token="yourSFsecuritytoken")

That’s it! Now we can reference sf everywhere we are calling functions from the simple-salesforce library (how to create your security token).

Salesforce Queries

To get the public groups and their users we need to query three different objects (tables) in Salesforce. User, GroupMember, and Group. These three objects will get us all the users, groups, and the junction object that relates the two. For this example, we are only pulling actively licensed Salesforce users.

Group_Members = sf.query_all("Select GroupId,UserOrGroupId from GroupMember")

Active_Users = sf.query_all("Select Username,Name,Id from User where isActive = True")

Groups = sf.query_all("Select Id,Name,Type from Group")

So now we have three sets of data to load into some dataframes to play with. In the code below, we load into a dataframe and then we are dropping a bunch of data that isn’t relevant for our process today.

Group_Members_DF = pd.DataFrame(Group_Members['records'])

Users_DF = pd.DataFrame(Active_Users['records'])
Users_DF = users.drop(columns=['attributes'])

Groups_DF = pd.DataFrame(Groups['records'])'
Groups_DF = groups.drop(columns=['attributes'])

With our dataframes we can loop over the data extracting the Id’s of the users, the groups, and reference the junction object to throw it together in a human readable view. Below, we have a couple of functions to make our processing reusable.

Data Processing Functions

def get_user_name(user_id):
    for index, name in Users_DF.iterrows():
        if user_id == name['Id']:
            print("\t"+name['Name'])

def get_members(group_id):
    result = Group_Members_DF.loc[Group_Members_DF['GroupId'] == group_id]
    for index, member in result.iterrows():
        get_user_name(member["UserOrGroupId"])

def get_groups_with_users():
    for index, group in Groups_DF.iterrows():
        group_id = group['Id']
        group_name = group['Name']
        print(str(group_name)+":")
        get_members(group_id)

To add some color

get_user_name() accepts a user_id this is converting a Salesforce ID to a human readable name.

get_members() accepts a group_id which then pulls the group from the Group_Members_DF then loops through for the related Salesforce Ids, then calls the above function to print it out the name.

get_groups_with_users() will be the main function we call to run the whole process. It loops through our dataframe of groups, throws the groupid and name in variables, prints the group name and then passes the groupid to our function above to find the users who are part of the group!

Final Output

We just need to call this function at the end of our file

get_groups_with_users()

So then we can run our python file from our terminal or command prompt! If everything worked as we expect, we should see an output similar to what I have below:

Conclusion

Extracting data from Salesforce using python is quite easy as long as you’re taking advantage of the simple-salesforce library. Given what you’ve learned here, you can query data from any object, custom or standard. Using the query_all function you can pass any SOQL query that you want.

The specific example I’ve gone over today also solves for a quick and dirty way of finding out all your public groups in Salesforce and the users that are in them. This is useful for audits, or if you want to go through some general spring cleaning of public groups.

Finally, if you’re more of a copy and paste existing code kind of person, well, I’ve got the full file here on github and here’s a preview down below!

sfpublicgroups.py:

from simple_salesforce import Salesforce
import pandas as pd
sf = Salesforce(username="yourSFusername",password="youSFpassword", security_token="yourSFsecuritytoken")

Group_Members = sf.query_all("Select GroupId,UserOrGroupId from GroupMember")
Active_Users = sf.query_all("Select Username,Name,Id from User where isActive = True")
Groups = sf.query_all("Select Id,Name,Type from Group")

Group_Members_DF = pd.DataFrame(Group_Members['records'])
Users_DF = pd.DataFrame(Active_Users['records'])
Users_DF = Users_DF.drop(columns=['attributes'])
Groups_DF = pd.DataFrame(Groups['records'])
Groups_DF = Groups_DF.drop(columns=['attributes'])

def get_user_name(user_id):
    for index, name in Users_DF.iterrows():
        if user_id == name['Id']:
            print("\t"+name['Name'])

def get_members(group_id):
    result = Group_Members_DF.loc[Group_Members_DF['GroupId'] == group_id]
    for index, member in result.iterrows():
        get_user_name(member["UserOrGroupId"])

def get_groups_with_users():
    for index, group in Groups_DF.iterrows():
        group_id = group['Id']
        group_name = group['Name']
        print(str(group_name)+":")
        get_members(group_id)


get_groups_with_users()

That's it for this topic. Thank you for reading!