HubSpot Deals data to MySQL Database

In this article, we're following the below steps for copying the data to MySQL.

Prerequisites:

• Python Environment

• MySQL, either in a Local or Server Environment

• HubSpot account admin access and Private App token

Once you logged into HubSpot, create the Private app located under Settings -> Integration -> Private App in your HubSpot(HS) account.

Get the Access token and have it securely.

Now the code Part coming into the blog,

Install the required packages below in Python Shell using the terminal,

pip3 install hubspot-api-client
pip3 install hubspot
pip3 install mysql-connector-python

Next, let's create a Credentials file named `MySQLCredentials.py` and add the below code.

user = USER_NAME
password = PASSWORD
host = "localhost"
database = DATABASE_NAME

Let's explore the developer documentation from HubSpot here.

As we are fetching the Deals data from HS, Below is the class for getting the Deal info,

client.crm.deals.basic_api.get_page(limit=100,after=after, archived=False, properties = {})

The above line returns the Json response. Hence we're looping all the desired properties every single record using for loop as below.

for deal_dict in deal_json["results"]:
    amount = deal_dict['properties']['amount']
    ......
    ......

And here's the full source code of how it's solved.

import hubspot
from hubspot.crm.pipelines import ApiException
import mysql.connector
import config.MySQLCredentials as mc

client = hubspot.Client.create(access_token="ACCESS_TOKEN")

mydb = mysql.connector.connect(
  host=mc.host,
  user=mc.user,
  password=mc.password,
  database=mc.database
)
mycursor = mydb.cursor()

after = 0
counter = 1
try:
    while after or after == 0:
        try:
            api_response = client.crm.deals.basic_api.get_page(limit=100,after=after, archived=False, properties=["amount", "amount_in_home_currency", "city"])

            deal_json = api_response.to_dict()
            for deal_dict in deal_json["results"]:
                amount = deal_dict['properties']['amount']
                amount_in_home_currency = deal_dict['properties']['amount_in_home_currency']
                city = str(deal_dict['properties']['city'])

### Database section Start ###
                values = (amount, amount_in_home_currency, city)
                query = "INSERT INTO Deals (`Amount`, `Amount in company currency`, `City`) VALUES (%s, %s, %s)"
                cur.execute(query, values)
            try:
                mydb.commit()
            except Exception as e:
                print(e)
### Database section END ###

            after = deal_json["paging"]["next"]["after"] if "paging" in deal_json else ''
            counter = counter + 1
            if after is None:
                after = False
                break
        except Exception as e:
            print("Exception : ",e)

except ApiException as e:
    print("Exception when calling basic_api->get_page: %s\n" % e)

Finally, you got all the Deals Info in SQL table.