How I use Python to get Salesforce data

After writing about "How I built a Python script to read e-mails from an Exchange Server" and “How I access Microsoft SharePoint in my Python scripts” and getting great feedback on the articles, I decided to write a little more about accessing business software data from Python. This time I moved out of the Microsoft world and explored the Salesforce and Python combination.

Tech stack

This time we will build a script that accesses data from Salesforce from within a Python language script (version 3.7). We will achieve the data transfers via ODBC, using the pyodbc module (version 4.0.26).
Please note that my script uses Connect Bridge, a commercial product that takes care of the Python / Salesforce connection. With Connect Bridge, I put together this integration script with little code and little effort. Moreover, I did it in a way that could not possibly mess up the Salesforce installation (trust me, this is VERY important). You can try the whole thing yourself by getting a Connect Bridge free trial (see details below).
Connect Bridge is a middleware developed by Connecting Software. It works as an integration platform. You can write your code in Python or other programming languages, and it allows you to connect any software through ODBC drivers, JDBC drivers, or Web Services.
You can use Connect Bridge to access not only Salesforce data but also data from many other programs. These include Microsoft SharePoint and Microsoft Exchange (covered in my previous Codementor articles that I mentioned earlier). It is important to note that data flow can be bidirectional. You can get data from Salesforce as you will see, but you can also create, update or delete Salesforce accounts, cases, contacts or any relevant information.

Where do I start?

My goal is to get Salesforce data using a Python script in the simplest possible way. Please have your Salesforce login credentials at hand. You need a Salesforce Developer account/organization, and you will need a security token. 
Once you have all that, the procedure is simple:

1. Request a Connect Bridge free trial and then install Connect Bridge
2. Install Python for Windows version 3.7+, and pyodbc module 4.0.26+
3. Run Connect Bridge Management Studio. Once inside:
   3.1. Go to Accounts – Add account and add an account for Salesforce, using the credentials I mentioned at the beginning of this section.
   3.2. Open the option New Query and then Connection Browser. Look for the Salesforce connector and expand it until you see an item named DefaultConnection. Right-click it and choose the option Get Connection string. Copy the ODBC connection string. You will need it further on.
   3.3. Use the New Query option to test out a query that will collect the data you want in Salesforce. I will show you an example here, but this is definitely something you should change. Once you have clicked the "New Query" option, open the Connection Browser. Find the Salesforce connector and expand it until you see the Tables option. We can see that the schema contains a "table" called Contact. We can create our query as SELECT Salutation, FirstName, LastName, Email, Title, Department FROM Contact LIMIT 20;
   This query results are twenty entries from the Salesforce contacts. It might look like we are using Salesforce database directly, but that is not what is happening! Connect Bridge is using the API and then presenting the data as if it were a database. Once you have finished writing your query, copy it. You will also need to pass it on to the script together with the connection string.

Let the scripting start

Now comes the good part! Let's start scripting.
The whole solution is in one script file CBSalesforce.py. You can go through the complete source code below. If you focus on lines 58-85, you will see the core of the solution. The logic of the script is fully described below.

#!/usr/local/bin/python3.7 
# encoding: utf-8 
''' 
CBSalesforce -- Execute Stored Procedure using Connect Bridge and the Salesforce connector 
  
@author:    Ana Neto  
  
@copyright:  2020 
  
@contact:    ana@connecting-software.com 
@deffield    updated: 06.01.2020 
''' 
  
import sys 
import os 
import pyodbc 
  
__all__ = [] 
__version__ = 0.3 
__date__ = '2019-07-22' 
__updated__ = '2020-01-06' 
  
DEBUG = 1 
TESTRUN = 0 
PROFILE = 0 
  
class CLIError(Exception): 
    '''Generic exception to raise and log different fatal errors.''' 
    def __init__(self, msg): 
        super(CLIError).__init__(type(self)) 
        self.msg = "E: %s" % msg 
    def __str__(self): 
        return self.msg 
    def __unicode__(self): 
        return self.msg 
  
def main(argv=None): # IGNORE:C0111 
    '''Command line options.''' 
  
    program_name = os.path.basename(sys.argv[0]) 
    program_version = "v%s" % __version__ 
    program_build_date = str(__updated__) 
    program_version_message = '%%(prog)s %s (%s)' % (program_version, program_build_date) 
    program_shortdesc = __import__('__main__').__doc__.split("n")[1] 
    program_license = '''%s 
  
  Created by Ana Neto on %s. 
  
  Licensed under the Apache License 2.0 
  http://www.apache.org/licenses/LICENSE-2.0 
  
  Distributed on an "AS IS" basis without warranties 
  or conditions of any kind, either express or implied. 
  
USAGE 
''' % (program_shortdesc, str(__date__)) 
  
    try: 
        query = "SELECT Salutation, FirstName, LastName, Email, Title, Department FROM Contact;" 
        connstr = "Driver={Media Gateway ODBC Driver};impl='CORBA';host='CNSF-NB14.cns.local';port='8087';acc='SalesforceAna';uid='administrator';pwd='beatriz44';ssl='false'" '''COPY FROM CONNECT BRIDGE MANAGEMENT STUDIO''' 
  
        conn = pyodbc.connect(connstr) 
        cursor = conn.cursor() 
        cursor.execute(query) 
        while 1: 
            row = None 
            try: 
                row = cursor.fetchone() 
            except:  
                print(sys.exc_info()[1]) 
                break 
            if not row: 
                break                     
            print(row) 
                         
             
    except KeyboardInterrupt: 
        ### handle keyboard interrupt ### 
        return 0 
    except: 
        print(sys.exc_info()[1]) 
        #indent = len(program_name) * " "         
        #sys.stderr.write(program_name + ": " + repr(e) + "n") 
        #sys.stderr.write(indent + "  for help use --help") 
        return 2 
  
if __name__ == "__main__": 
          
    if TESTRUN: 
        import doctest 
        doctest.testmod() 
    if PROFILE: 
        import cProfile 
        import pstats 
        profile_filename = 'CBSalesforce_profile.txt' 
        cProfile.run('main()', profile_filename) 
        statsfile = open("profile_stats.txt", "wb") 
        p = pstats.Stats(profile_filename, stream=statsfile) 
        stats = p.strip_dirs().sort_stats('cumulative') 
        stats.print_stats() 
        statsfile.close() 
        sys.exit(0) 
    sys.exit(main()) 

Here is what the script does:
• The variable connstr holds the ODBC connection string that I pass into the pyodbc module to build the ODBC connection
• I then use that ODBC connection (stored in conn) to open a database cursor
• I execute the query
• I do a while loop to read results from the cursor row by row. When cursor.fetchone returns None, I break the loop
• I also break the loop if an exception occurs during the ODBC fetch (the problem is printed to output)
• If the fetchone method returns a data row, I print that raw data directly to output. Please note that I could have formatted it as JSON, XML, CSV, or any other kind of format that could be of use for data interchange. I could also pick the raw data row object and perform custom tasks in further code.
When you run the script, don't forget the CBSalesforce.py script has two variables that you need to change, connstr and query (lines 59 and 60). You'll need to copy these from Connect Bridge Management Studio like I explained above (point 3).

Constraints

There is no ODBC Linux client library available (and I am not even sure If there could be). For this reason, the use of the Connect Bridge tool is limited to Windows machines.

Conclusion

We have seen how accessing Salesforce data in Python can be quickly done using the Connect Bridge integration platform. You can also use the same Connect Bridge tool to access other Microsoft software, such as Dynamics or Exchange, or different kinds of software like SAP or Gmail.
If you have an integration challenge, Connect Bridge can be a useful tool that can save you a lot of headaches. With it you need to write less code, your code has fewer errors, and once it is done, no maintenance will be needed from your side (Connect Bridge comes with a forward compatibility warranty).
If you want to know if you can use it to get some specific information you want, try it out using a free trial or leave a comment below and I will try to help you out.