-
Notifications
You must be signed in to change notification settings - Fork 0
/
run.py
158 lines (133 loc) · 4.53 KB
/
run.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
import mysql.connector
import os
import requests
import datetime
def hub(endpoint, payload, method="get"):
"""
make calls to hubspot
"""
base = "https://api.hubapi.com"
method = method.lower()
url = "{0}{1}".format(base, endpoint)
data = {"hapikey": os.environ.get('HAPIKEY', "demo")}
if method != "post":
data = {**data, **payload}
return requests.__getattribute__(method)(url,params=data)
else:
return requests.__getattribute__(method)(url, params=data, json=payload)
def initializeDatabase(cxr):
"""
Initialize the database. The database will be named `hubspot`.
It will also initialize the engagements table
.. note::
We will drop the engagements table if it already exists
:param cxr: A MySql.connector object connected to the MySql instance
"""
# stealing a lot of this from https://dev.mysql.com/doc/connector-python/en/connector-python-example-ddl.html
TABLES = {}
TABLES['engagements'] = (
"CREATE TABLE `engagements` ("
" `id` int NOT NULL,"
" `portalId` int,"
" `active` boolean,"
" `createdAt` bigint,"
" `lastUpdated` bigint,"
" `createdBy` bigint,"
" `modifiedBy` bigint,"
" `timestamp` bigint,"
" `ownerId` int,"
" `type` text,"
" `uid` text,"
" `source` text,"
" PRIMARY KEY (`id`)"
")")
# create database
cursor = cxr.cursor()
try:
cursor.execute(
"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format("hubspot")
)
except mysql.connector.Error as err:
if err.errno != 1007:
print("Failed creating table: {}".format(err))
exit(1)
else:
print("Warning database 'hubspot' already exists.")
cursor.close()
# create tables
for k, v in TABLES.items():
print("Creating table: {0}".format(k))
createTable(cxr, v, k)
def createTable(cxr, command, table):
"""
Create a table
:param cxr: A MySql.connector object connected to the MySql instance
:param command: A string representing the command to create a table
:param table: the name of the table
"""
cursor = cxr.cursor()
try:
cursor.execute("DROP TABLE IF EXISTS `{0}`".format(table))
except mysql.connector.Error as err:
print(err)
exit(1)
try:
cursor.execute(command)
except mysql.connector.Error as err:
if err.errno != 1050:
print("Failed creating table: {}".format(err))
exit(1)
else:
print("Warning table already exists.")
cxr.commit()
cursor.close()
def writeToTable(cxr, data, table):
"""
Write a list of data elements into a given table
:param cxr: A MySql.connector object connected to the MySql instance
:param data: A list of dictionaries containing the data to write to the table
:param table: Name of the table to write data to
"""
# write each data element to the table
# pull out the keys and list them out
# and then pull out the values and list them out
# this ensures that we are inserting the right values into the right columns
cursor = cxr.cursor()
print("Writing data")
for d in data:
# build the insertion string and specify what fields
# we are writing into
insertion_string = "INSERT INTO {0} ({1}) VALUES ({2})"
names = ", ".join(["`{0}`".format(k) for k in d.keys()])
values = ", ".join(["%s" for _ in d.keys()])
# actually insert the data
cursor.execute(insertion_string.format(table, names, values), list(d.values()))
# commit data
cxr.commit()
return cursor.close()
if __name__ == "__main__":
# assuming user is root and no password
connector = mysql.connector.connect(host='localhost',database='mysql',user='root',password='')
initializeDatabase(connector)
# we could multi-thread this if we wanted to
# but let's not
# we are going to ask the API to give us all recent engagements
# this only goes back 30 days, but that should be plenty
hasMore = True
i = 0
while hasMore:
r = hub("/engagements/v1/engagements/recent/modified", {"offset":i*100, "count":100})
d = r.json()
# format the data into a list of rows
l = [e['engagement'] for e in d['results']]
writeToTable(connector, l, "engagements")
i = i+1
hasMore = d['hasMore']
# write a query to pull out the results
cursor = connector.cursor()
cursor.execute("SELECT type, DATE(FROM_UNIXTIME(createdAt/1000)) as day, COUNT(*) as num_engagements FROM engagements GROUP BY type, day ORDER BY type, day ASC")
# print each result out on a newline
print("\n".join([str(x) for x in list(cursor)]))
# close the connection
cursor.close()
connector.close()