Last active
June 25, 2022 11:41
-
-
Save jackcoldrick90/282850cb9a1b3d71056f56cd761b4148 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
} | |
}) | |
}) | |
}); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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}} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
}); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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