How to load data from HubSpot to Postgres destination

First, log in to your HubSpot account. Navigate to "Settings" and find "Integrations" in the left sidebar. Under "API Key" tab, generate a new API key if you haven't already. Ensure to store this API key securely as it will be used for authentication in the API requests.

Determine which data you need to export from HubSpot. HubSpot's API allows access to various data types such as contacts, companies, deals, etc. Navigate to the HubSpot API documentation to understand the specific endpoints and fields relevant to your data requirements.

Install Python on your local machine or server if it’s not already available. Ensure you have `pip`, the Python package installer, then use it to install necessary libraries by running:
```bash
pip install requests psycopg2 pandas
```
`requests` will be used to make API calls to HubSpot, `psycopg2` to interact with PostgreSQL, and `pandas` to handle data manipulation.

Create a Python script to extract data from HubSpot using its API. Here’s a basic example:
```python
import requests
API_KEY = 'your_hubspot_api_key'
url = 'https://api.hubapi.com/contacts/v1/lists/all/contacts/all'
params = {'hapikey': API_KEY}
response = requests.get(url, params=params)
data = response.json()
contacts = data.get('contacts', [])
```

Once you have the data, transform it into a suitable format for PostgreSQL. Use `pandas` to create a DataFrame:
```python
import pandas as pd
df = pd.json_normalize(contacts)
# Perform any necessary data cleaning or transformation here
```

Use `psycopg2` to connect to your PostgreSQL database. Make sure you have the database credentials ready:
```python
import psycopg2
conn = psycopg2.connect(
dbname="your_dbname",
user="your_username",
password="your_password",
host="your_host",
port="your_port"
)
cursor = conn.cursor()
```

Prepare an SQL statement to insert data into your PostgreSQL table. Iterate over the DataFrame rows and execute the SQL command:
```python
for index, row in df.iterrows():
cursor.execute(
"INSERT INTO your_table (column1, column2) VALUES (%s, %s)",
(row['field1'], row['field2'])
)
conn.commit()
cursor.close()
conn.close()
```
Ensure your PostgreSQL table structure matches the DataFrame data format. Adjust the column names and data types as necessary.
By following these steps, you can effectively transfer your desired data from HubSpot to a PostgreSQL database using custom scripting, without relying on third-party connectors.