How to connect Python with Hubspot ODBC

Introduction

This guide will walk you through connecting Python with the HubSpot API driver using the HubSpot API. Integrating HubSpot with Python allows you to automate file storage tasks like uploading, downloading, sharing, and organizing files directly from your Python code. This connection provides seamless interaction with HubSpot, enabling you to manage cloud files efficiently and streamline workflows.

Prerequisites

  • ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.

  • HubSpot account

  • pyodbc: An open-source Python module that makes accessing ODBC databases simple.

Steps

Install pyodbc in Python

You must install pyodbc in Python to establish connections to databases that support ODBC (Open Database Connectivity). This module facilitates communication between Python applications and various database management systems, enabling you to perform operations such as querying, retrieving data, and managing databases.

  1. Ensure you have Python installed on your system. If not, download it from the official Python website and follow the installation instructions.

  2. Open your terminal or command prompt.

  3. Use the following command to install pyodbc using pip, the Python package installer:

python -m pip install pyodbc

Ensure you have a stable internet connection and the necessary permissions to install Python packages.

Reasons to Install:

  • If pyodbc is not installed, your Python script will generate the following error:

ModuleNotFoundError: No module named 'pyodbc'

  • Database Connectivity: pyodbc allows Python to connect to various databases that support ODBC, such as Microsoft SQL Server, PostgreSQL, MySQL, and more.

  • Data Operations: It facilitates the execution of SQL queries, data retrieval, and other database operations within Python scripts.

  • Cross-Platform Support: pyodbc works with various operating systems, including Windows, macOS, and Linux distributions.

  • Simplicity and Efficiency: The module provides an intuitive interface for managing database transactions and connections, simplifying the process of working with databases in Python.

By installing pyodbc, you can seamlessly integrate your Python applications with a wide range of ODBC-supported databases, enabling efficient and effective data management and analysis.

Create/Select an App from the HubSpot Developers Page

  1. Log in to your HubSpot account and use the account with Super Admin.

  2. Click the settings icon in the main navigation bar and go to Integrations > Private Apps.

  3. Select Create private app.

  4. Configure your app details in the Basic Info tab: Enter the app’s name, add a description (optional), and upload an image (optional).

  5. Navigate to the Scopes tab and select the appropriate read/write permissions for each scope.

  6. Click Create app in the top right corner to save your app configuration and obtain the token.

Create a New Driver

  1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
    Open ODBC Data Source

  2. To gain access for yourself or other users, go to the User DSN or System DSN. For SQL Server integration, go to the System tab and add a new System DSN by clicking the “Add” button.
    ZappySys ODBC Driver - Open UI

  3. From the driver list, select ZappySys API Driver, then select Hubspot in the connector list and click Continue.

Hubspot Connector Configuration

  1. Enter the Token you generated earlier and test the connection.

  2. Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.

Read Data in Python Using ODBC DSN

  1. Create a Python file with this code, for example HubSpotExample.py:
import pyodbc

conn = pyodbc.connect('DSN=API Driver - Hubspot')
cursor = conn.cursor()

# Execute query to fetch data from API service
cursor.execute("SELECT * FROM Owners")

row = cursor.fetchone()
print("------------HubSpot & Python----------------")
while row:
    print(row)
    row = cursor.fetchone()
  1. When you run the code, it will make the API call and read the data:

Using an Entire ODBC Connection String

You can use a fully qualified connection string to avoid creating multiple DSNs for each platform (x86, x64). Go to your DSN and copy the connection string:

  1. Open ODBC data source configuration and click Copy Settings.

  2. The window opens, confirming the connection string was successfully copied to the clipboard.
    Connection String Copied

  3. Then, in your Python code, use the connection string when initializing the OdbcConnection object, for example:

conn = pyodbc.connect('DRIVER={ZappySys API Driver};ServiceUrl=https://yourservice.provider.com/api/xxxx....;AuthName=Http;')

Conclusion

Following this guide, you’ve learned to connect Python with HubSpot using the HubSpot API. This integration allows you to automate tasks such as uploading, downloading, and organizing files directly from your Python scripts. With Python’s flexibility and HubSpot’s cloud storage capabilities, you can create efficient workflows and handle file management operations seamlessly. Contact the ZappySys support team.

References