Skip to content

Instantly share code, notes, and snippets.

@jackcoldrick90
Last active June 25, 2022 11:41
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jackcoldrick90/282850cb9a1b3d71056f56cd761b4148 to your computer and use it in GitHub Desktop.
Save jackcoldrick90/282850cb9a1b3d71056f56cd761b4148 to your computer and use it in GitHub Desktop.
OPERATIONS HUB WORKSHOP #4: Connecting to and Querying an external MySQL Datbase
NOTE: You will need to consult with your hosting provider if you run into any issues in terms of configuring the database. Alternatively, for the purposes of this workshop if you'd like you can use a free hosting service like db4free.net.
These code snippets can be used within a HubSpot custom coded workflow action (https://developers.hubspot.com/docs/api/workflows/custom-code-actions) to query the Clearbit Enrichment API (https://dashboard.clearbit.com/docs#enrichment-api-company-api).
In this workshop you will learn how to connect and query an external MySQL Database using custom coded workflow actions, a feature of HubSpots Operations Hub Professional.
We will focus on 2 examples and share sample code in both Javascript and Python.
Example 1: Setup a standard contact workflow to add any new contacts to a database if they do not exist already.
Example 2: Setup a scheduled deal workflow to add any deals created in the last 24 hours to an external database table.
Please note that custom coded workflow actions are a feature of Operations Hub Professional.
You can setup a free HubSpot Developer Account by vising https://developers.hubspot.com/
You can find much more code snippets by visting https://www.hubspot.com/programmable-automation-use-cases
/*
Example 1: Connect to a MySQL Database using NodeJS. Check to see if a record exists that matches the email address of the currently enrolled contact.
If they do exist, we do nothing. If they do not exist we add a record to the database.
*/
//1. Include the mysql node library
const mysql = require('mysql');
exports.main = async (event, callback) => {
//2. Define variables using CRM data as an input
const email = event.inputFields['email'];
const firstName = event.inputFields['firstname'];
const lastName = event.inputFields['lastname'];
var sql, resultFound;
//3. Configure our connection to mySQL database, using secret values
var con = mysql.createConnection({
host: process.env.MYSQL_HOST,
user: process.env.MYSQL_USER,
password: process.env.MYSQL_PASSWORD,
database: process.env.MYSQL_DB
});
//4. Connect to the mySQL database
con.connect(function(err) {
if (err) throw err;
//5. Query database
sql = "SELECT * FROM customer_info WHERE emailAddress ='" + email + "'";
con.query(sql, function(err, result) {
if (err) throw err;
//6. Check if there was any results, if no results add new record to the table
if (result.length > 0) {
//Results found
var results = JSON.parse(JSON.stringify(result));
console.log(results[0]);
resultFound = true;
} else {
//No Results found, create record in database
sql = "INSERT INTO customer_info (firstName, lastName, emailAddress) VALUES ('" + firstName + "', '" + lastName + "', '" + email + "')";
resultFound = false;
con.query(sql, function(err, result) {
if (err) throw err;
});
}
con.end(); // terminate connection
// 7. Pass data back to worklfow using outputFields - make sure to also define data output types
callback({
outputFields: {
resultFound: resultFound
}
})
})
});
}
# Example 1: Connect to a MySQL database using Python. Check to see if a record exists that matches the email address of the currently enrolled contact.
# if they do exist, we do nothing. If they do not exist we add a record to the database.
# 1. Import required libraries
import os
import mysql.connector
def main(event):
# 2. Store input fields in variables
email = event.get("inputFields").get("email")
fname = event.get("inputFields").get("firstname")
lname = event.get("inputFields").get("lastname")
# 3. Connect to MySQL Database
mydb = mysql.connector.connect(
host=os.getenv("MYSQL_HOST"),
user=os.getenv("MYSQL_USER"),
password=os.getenv("MYSQL_PASSWORD"),
database=os.getenv("MYSQL_DB"),
)
# 4. Query MySQL Database
mycursor = mydb.cursor()
sql = "SELECT * FROM customer_info WHERE emailAddress = %s"
adr = (email,)
mycursor.execute(sql, adr)
myresult = mycursor.fetchall() # Get all results
print(mycursor.rowcount) # Debugging rowcount 0 if no results
# 5. Check if there was any results, if no results add new record
if mycursor.rowcount > 0:
print("results found") # RESULTS FOUND
result = True
for x in myresult:
print(x)
else:
print("no results found") # NO RESULTS FOUND
sql = "INSERT INTO customer_info (firstName, lastName, emailAddress) VALUES (%s, %s, %s)"
val = (fname, lname, email)
mycursor.execute(sql, val)
result = False
mydb.commit()
# 6. Pass data back to worklfow using outputFields - make sure to also define data output types
return {"outputFields": {"resultFound": result}}
/*
Example 2: Connect to a MySQL Database using NodeJS. Add deal information to a table. This could be used in a scheduled workflow and run at a specific cadence.
*/
//1. Include the mysql node library
const mysql = require('mysql');
exports.main = async (event, callback) => {
//2. Define variables using CRM data as an input
const name = event.inputFields['dealname'];
const amount = event.inputFields['amount'];
const closedate = parseInt(event.inputFields['closedate']);
var sql;
// Convert unix timestamp to a human readable date
const dateObj = new Date(closedate);
const humanReadableDate = dateObj.toLocaleString();
console.log('humanReadableDate' + humanReadableDate);
/*
// FORMAT WE WANT IS YYYY-MM-DD H:M:S
console.log("YYYY: " + dateObj.toLocaleString("en-US", {year: "numeric"})); // YYYY
console.log("MM: " + dateObj.toLocaleString("en-US", {month: "numeric"})); // MM
console.log("DD: " + dateObj.toLocaleString("en-US", {day: "numeric"})); // DD
console.log("H: " + dateObj.toLocaleString("en-US", {hour: "numeric"}).replace("AM", "").replace("PM", "")); // H
console.log("M: " + dateObj.toLocaleString("en-US", {minute: "numeric"})); // M
console.log("S: " + dateObj.toLocaleString("en-US", {second: "numeric"})); // S
*/
var yyyy = dateObj.toLocaleString("en-US", {
year: "numeric"
}); // YYYY
var mm = dateObj.toLocaleString("en-US", {
month: "numeric"
}); // MM
var dd = dateObj.toLocaleString("en-US", {
day: "numeric"
}); // DD
var h = dateObj.toLocaleString("en-US", {
hour: "numeric"
}).replace(" AM", "").replace(" PM", ""); // H
var m = dateObj.toLocaleString("en-US", {
minute: "numeric"
}); // M
var s = dateObj.toLocaleString("en-US", {
second: "numeric"
}); // S
var sqlDate = yyyy + "-" + mm + "-" + dd + " " + h + ":" + m + ":" + s;
console.log(sqlDate)
//3. Configure our connection to mySQL database, using secret values
var con = mysql.createConnection({
host: process.env.MYSQL_HOST,
user: process.env.MYSQL_USER,
password: process.env.MYSQL_PASSWORD,
database: process.env.MYSQL_DB
});
//4. Connect and insert data to the mySQL database
con.connect(function(err) {
sql = "INSERT INTO order_info (name, amount, closeDate) VALUES ('" + name + "', '" + amount + "', '" + sqlDate + "')";
console.log(sql);
con.query(sql, function(err, result) {
if (err) throw err;
});
con.end(); // terminate connection
});
}
# Example 2: Connect to a MySQL Database using Python. Add deal information to a table. This could be used in a scheduled workflow and run at a specific cadence.
# 1. Import required libraries
import os
import mysql.connector
import datetime
def main(event):
# 2. Store inputs in variables
dealname = event.get("inputFields").get("dealname")
amount = event.get("inputFields").get("amount")
closedate = int(event.get("inputFields").get("closedate"))
# 3. Convert Unix Timestamp to valid format
# print(datetime.datetime.fromtimestamp(closedate/1000).strftime('%Y-%m-%d %H:%M:%S'))
sqlDate = datetime.datetime.fromtimestamp(closedate / 1000).strftime(
"%Y-%m-%d %H:%M:%S"
)
# 4. Connect to MySQL Database
mydb = mysql.connector.connect(
host=os.getenv("MYSQL_HOST"),
user=os.getenv("MYSQL_USER"),
password=os.getenv("MYSQL_PASSWORD"),
database=os.getenv("MYSQL_DB"),
)
# 5. Query MySQL Database
mycursor = mydb.cursor()
sql = "INSERT INTO order_info (name, amount, closeDate) VALUES (%s, %s, %s)"
val = (dealname, amount, sqlDate)
mycursor.execute(sql, val)
mydb.commit()
/* use the database "YOUR_DATABASE_NAME" */
USE YOUR_DATABASE_NAME;
/*
Create table in the "customers" database called "customer_info".
This will have columns to hold data relating to our customers namely
their name and email address. CustomerID is the primary key and is an auto-generaged
unique number.
*/
CREATE TABLE customer_info(
customerId int AUTO_INCREMENT,
firstName varchar(255),
lastName varchar(255),
emailAddress varchar(255),
PRIMARY KEY (customerId)
);
/*
Create table in the "customers" database called "order_info".
This will have columns to hold data relating to our orders namely
their name, amount and close date. orderId is the primary key and is an auto-generaged
unique number.
*/
CREATE TABLE order_info(
orderId int AUTO_INCREMENT,
name varchar(255),
amount decimal(15,2),
closedate DATETIME,
PRIMARY KEY (orderId)
);
/*
Retrieve all of the rows within the "customer_info" table.
Initially this should return nothing but over time it will populate when you
run your custom coded worklfow actions in HubSpot
*/
SELECT * FROM customer_info;
/*
Retrieve all of the rows within the "order_info" table.
Initially this should return nothing but over time it will populate when you
run your custom coded worklfow actions in HubSpot
*/
SELECT * FROM order_info;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment