Home Services Partners Company
Python Connectivity to Salesforce, Eloqua or Google

Python Connectivity to Salesforce, Eloqua or Google

July 30, 2015 0 Comments

DataDirect Cloud® provides freedom of choice for developers to access a wide range of data sources from their favorite programming language, such as Python.

It’s exciting to see developers instantly connect to data sources and deliver unique innovation to these business applications. In response, a developer evangelist on my team, Saikrishna Teja Bobba, created the following tutorial to help get more data flowing through open standards and into the developer's code. This connectivity is available on both Windows and Linux, but the steps are a little different depending on your OS. I’ve split the tutorial into sections based on this, so once you have completed the common steps feel free to skip ahead to the section for your OS.

Getting Started

  1. If you do not already have a DataDirect Cloud account, register for a free trial before you begin.
  2. After registering, log in. You will be taken to the Progress Pacific dashboard. Once there, click “Connect Data” under the DataDirect Cloud section.

Opening the DataDirect Cloud Dashboard

Opening the DataDirect Cloud Dashboard

  1. You will arrive at the DataDirect Cloud dashboard. The first step here is to create a data source definition. Click the “Data Sources” tab on the left side of the screen. On the next screen, click “Data Stores.”

Choose your Data Source

Choose your Data Source

  1. You will be presented with list of cloud data sources that are currently supported by DataDirect Cloud. For this tutorial I chose Salesforce.com as my cloud data source.

    Create a Salesforce Data Source

    Create a Salesforce Data Source


  2. Once you select your data source, you will first have to give it a name. This name can be anything you want. You must then provide your Salesforce User ID, password and security token which can be obtained from your account settings in Salesforce.com. Once you have filled in those details, test the connection. If the test is successful, you will be able to save the data source.
  3. At this point, you can query your cloud data source under the ‘SQL Testing’ tab on the left side of the screen.
  4. Click the “Downloads” tab on the left side of the screen and download the DataDirect Cloud ODBC driver that matches your OS.

Connecting to DataDirect Cloud in Linux

Before going any further, please note that we support the following Linux distributions:

  • For 32-bit Drivers:
    • Red Hat Enterprise Linux 4.x, 5.x and 6.x
    • SUSE Linux Enterprise Server 10.x, 11 and 12
  • For 64-bit Drivers:
    • Red Hat Enterprise Linux AS, ES and WS version 4.x, 5.x and 6.x
    • SUSE Linux Enterprise Server 10.x, 11 and 12

If you are using a supported Linux distribution and have already completed the steps listed in the “Getting Started” section, you can proceed as follows:

  1. If you have not yet done so, install java using command yum install java.
  2. Install Python and module pyodbc. For more information on pyodbc and installation, check out this write-up on the project page.
  3. Navigate to the location of the DataDirect Cloud ODBC driver you downloaded earlier and install it using command:

This will install it in default directory /opt/Progress/DataDirect/Cloud_for_ODBC_20/

  • Tip: If you are having trouble with installation, change the permissions of the setup file to execute as a program.
  1. In the above location you will find a shell script ‘odbc.sh’ for bash and ‘odbc.csh’ for C shell. Run the appropriate script for your shell. This will install a couple of environment variables named ODBCINI pointing to the location of odbc.ini, ODBCINST pointing to the location of odbcinst.ini and LD_LIBRARY_PATH pointing to install_directory/lib. Please make sure that all these environment variables are set by checking them with command env.
  2. Set another environment variable DD_INSTALLDIR to installation directory. In my case it was /opt/Progress/DataDirect/Cloud_for_ODBC_20/.
  3. Now open the odbc.ini file and edit the file as follows. Note that ‘MySalesforce’ is the Data Source Name that I have given in DataDirect Cloud Dashboard, so yours might be different.
[ODBC Data Sources]
 MySalesforce=DataDirect Cloud 2.0

[ODBC] IANAAppCodePage=4 InstallDir=/opt/Progress/DataDirect/Cloud_for_ODBC_20 Trace=0 TraceFile=odbctrace.out TraceDll=/opt/Progress/DataDirect/Cloud_for_ODBC_20/lib/ddtrc27.so

[MySalesforce] Driver=/opt/Progress/DataDirect/Cloud_for_ODBC_20/lib/ddd2c01.so Description=DataDirect Cloud 2.0 Database= MySalesforce LogonID= ClientTimeZone= DataSourceUser= DataSourcePassword= ProxyHost= ProxyPort= ProxyUser= ProxyPassword= TransactionMode=0 WSRetryCount=3 WSTimeout=120 LogonDomain= LoginTimeout=30 QueryTimeout=0 ApplicationUsingThreads=1 ReportCodepageConversionErrors=0

  1. After making the changes, copy the whole text and open the file /etc/odbc.ini. This file holds all the DSN’s in a Linux machine. Paste the above configuration in the file /etc/odbc.ini and save it.
  2. To connect to Data Direct Cloud, I have written a python code through which we can fetch and update the data. This works on Windows and Linux. Currently this supports the following actions:
    • List all Tables
    • Execute SELECT Query
    • Execute UPDATE/DELETE Query.
import pyodbc
import getpass
import sys

def show_odbc():
    sources = pyodbc.dataSources()
    dsns = sources.keys()
    sl = []
    i = 1
    for dsn in dsns:
        sl.append( str(i) + '. %s' % (dsn))
        i= i+1
    return dsns
def listTables(cursor):
        for row in cursor.tables():
                print row.table_name

def executeSelectQuery(cursor, cnxn):
        query = raw_input('Enter the SELECT Query:')
        rows = cursor.fetchall()
        for row in rows:
                print row

def executeUpdateQuery(cursor, cnxn):
        query = raw_input('Enter the UPDATE/DELETE Query:')
        print('Update/Delete Successful!')

def connect_odbc(dsn):
        print('Select DSN you wish to connect:')
        dsn_num = raw_input()
        print(str(dsn[int(dsn_num)- 1]))
        loginID = raw_input('Enter Data Direct UserName:')
        password = getpass.getpass('Enter Data Direct Password:')
        print('Attempting to Connect....')
        cnxn = pyodbc.connect('DSN='+str(dsn[int(dsn_num)- 1])+';UID='+loginID+';PWD='+password+'', autocommit=True)
        cursor = cnxn.cursor()
        print('Successfully Connected!')
                        print('Available Actions:')
                        print(' 1. List all Tables')
                        print(' 2. Execute SELECT Query')
                        print(' 3. Execute UPDATE/DELETE Query')
                        print(' 4. Exit')
                        action = raw_input('Select your action:')
                        if(action == '1'):
                        elif(action == '2'):
                                executeSelectQuery(cursor, cnxn)
                        elif(action == '3'):
                                executeUpdateQuery(cursor, cnxn)
                        elif(action == '4'):
                print('Exception Occured:' + str(sys.exc_info()[1][1]))

if __name__ == '__main__':
    dsn = show_odbc()


  1. Once you run the code, it will List all the DSN’s in the machine as shown below:

    Selecting a DSN

    Selecting a DSN


  2. Once you select a DSN, you will be prompted for your DataDirect Cloud Username and Password:

    Provide DataDirect Cloud login details

    Provide DataDirect Cloud login details


  3. After you are authenticated, you will be shown the available actions and you can fetch/update data from your cloud data source. Figure below for reference:

    Choose an action once connected

    Choose an action once connected


Connecting to DataDirect Cloud in Windows

The process for connecting in a Windows environment makes use of the ODBC Data Source Administrator GUI. Before you begin, make sure you have completed the steps in the “Getting Started” section above. After you’ve done that, proceed as follows:

  1. Install the DataDirect Cloud ODBC Drivers for Windows. This should have been done already in the “Getting Started” section.
  2. From the Start Menu, open the ODBC Administrator under Progress DataDirect Cloud ODBC Drivers Folder.
  3. Once the ODBC Administrator is open, click the User DSN tab, then click the button marked, “Add…”

    Click 'Add' in the ODBC Data Source Administrator

    Click "Add" in the ODBC Data Source Administrator


  4. A new window will appear titled, “Create New Data Source.” Select “DataDirect Cloud 2.0” and click “Finish.”

    Create the new data source in the next window.

    Create the new data source in the next window.


  5. Another window will appear letting you set up the driver. In the fields titled “Data Source Name” and “Database Name,” enter the Data Source Name you provided during the DataDirect Cloud setup. Click “Test Connect” to check if connection is successful.

    Set up driver based on details from the DataDirect Cloud setup.

    Set up driver based on details from the DataDirect Cloud setup.


  6. Now you can run the Python code from the Linux section of this tutorial to fetch or update data from any cloud data source.

Get Connected with DataDirect Cloud

Though I chose to work in Python, similar steps can be followed with a language of your own choosing. Hopefully, this shows just how easy it is to establish a connection to any cloud data source using DataDirect Cloud and our ODBC/JDBC drivers.

Go ahead and grab a free trial of DataDirect Cloud and try it yourself! If you have any questions or run into any trouble, don’t hesitate to leave a comment below and we will help you out any way we can. Also, keep your eyes peeled for more posts from Saikrishna and myself, as we plan to share more exciting connectivity tricks in the future.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next Building Hybrid Mobile Apps on Existing Systems via OData
Comments are disabled in preview mode.