· 6 years ago · May 17, 2019, 04:02 AM
1import json
2import os
3import sys
4
5packages_path = os.path.join(os.path.split(__file__)[0], "packages")
6sys.path.append(packages_path)
7
8
9#----------------------------------
10# Handler functions
11#
12#
13
14def postSubscription(event, context):
15 """
16 Writes a new subscriber to the `registrations` table
17 """
18
19 data = sqlInserRegistrations(event)
20
21 response = {
22 "statusCode": 201,
23 "headers": {
24 "Access-Control-Allow-Origin": "*"
25 }
26 }
27
28 return response
29
30
31def getSubscriptions(event, context):
32 """
33 Returns a page of subscribers from the `registrations`.
34
35 Paging done by optional query string args 'page' and 'size'.
36 Default 'page' is 1, default 'size' is 10.
37 """
38
39 data = sqlSelectRegistrations(event)
40
41 body = {
42 "data": data
43 }
44
45 response = {
46 "statusCode": 200,
47 "headers": {
48 "Content-Type": "application/json",
49 "Access-Control-Allow-Origin": "*"
50 },
51 "body": json.dumps(body)
52 }
53
54 return response
55
56
57
58#----------------------------------
59# SQL functions
60#
61#
62
63def sqlSelectRegistrations(event):
64 """
65 Select from `registrations` table with optional paging
66 """
67
68 size = int(getQueryStringElement("size", event, 10))
69 page = int(getQueryStringElement("page", event, 1))
70
71 limit = size
72 offset = (size * page) - size
73
74 sql = """
75 SELECT first_name,
76 last_name,
77 email
78 FROM registrations
79 LIMIT {}
80 OFFSET {}
81 """.format(limit, offset)
82
83 data = executeRdsDataSql(sql)
84 return data
85
86
87def sqlInserRegistrations(event):
88 """
89 Inserts a record into the `registrations` table
90 """
91
92 first_name = event.get('first_name')
93 last_name = event.get('last_name')
94 email = event.get('email')
95
96 sql = """
97 INSERT
98 INTO registrations
99 (first_name, last_name, email)
100 VALUES ('{}', '{}', '{}')
101 """.format(first_name, last_name, email)
102
103 data = executeRdsDataSql(sql)
104
105
106
107#----------------------------------
108# Private functions
109#
110#
111
112def executeRdsDataSql(sql):
113 """
114 Calls aurora via rds-data data api and executes passed sql, returning munged result
115
116 Creates client connection.
117
118 Parameters:
119 sql (string): The sql to execute
120
121 Returns: string
122 """
123 # get the client
124 client = getRdsDataClient()
125
126 # call data-api to execute sql
127 response = client.execute_sql(
128 awsSecretStoreArn=getDotEnv("AWSSECRETSTOREARN"),
129 database=getDotEnv("DATABASE"),
130 dbClusterOrInstanceArn=getDotEnv("DBCLUSTERORINSTANCEARN"),
131 sqlStatements=sql,
132 )
133
134
135 # turn the rds response json into key/val json
136 return mungeBoto3Rds(response)
137
138
139def getRdsDataClient():
140 """
141 Creates a boto3 rds-data client for use by executeRdsDataSql()
142
143 Note: You must bundle boto3 and botocore with your app as the AWS default boto3 does not include rds-data
144
145 Returns: client
146 """
147
148 import botocore
149 import boto3
150
151 # valuable info stored in .env
152 aws_access_key_id = str(getDotEnv("ACCESS_KEY_ID"))
153 aws_secret_access_key = str(getDotEnv("SECRET_ACCESS_KEY"))
154 region_name = str(getDotEnv("REGION_NAME"))
155
156 # create the client
157 client = boto3.client(
158 'rds-data',
159 aws_access_key_id=aws_access_key_id,
160 aws_secret_access_key=aws_secret_access_key,
161 region_name=region_name,
162 )
163
164 return client
165
166
167def getDotEnv(element):
168 """
169 Gets a value from the .env by its key
170
171 Parameters:
172 element (string): The name of the element to get from the .env file
173
174 Returns: string or None
175 """
176 from dotenv import load_dotenv, find_dotenv
177 from os.path import join, dirname
178
179 dotenv_path = join(dirname(__file__), '.env')
180 load_dotenv(dotenv_path)
181 return os.getenv(element)
182
183
184def getQueryStringElement(element, event, default=None):
185 """
186 Gets value of element from the query string or default if not exists
187
188 Parameters:
189 element (string): The name of the element to get from the query string
190 event (dict): The event
191 default : The default value to return if no value is on the query string, or None if default not set
192
193 Returns: string or None
194 """
195
196 if event is None:
197 return default
198 elif not('queryStringParameters' in event):
199 return default
200 if event['queryStringParameters'] is None:
201 return default
202 elif element in event['queryStringParameters']:
203 return event['queryStringParameters'].get(element)
204 else:
205 return default
206
207
208def mungeBoto3Rds(response):
209 """
210 Converts the json returned by rds-data execute_sql() into key/val json
211
212 Parameters:
213 response (dict): The response from the call to execute_sql()
214
215 Returns: string
216 """
217
218 records = response.get('sqlStatementResults')[0].get('resultFrame').get('records')
219
220 keysList = list(map(lambda i: str(i.get('name')), response.get('sqlStatementResults')[0].get('resultFrame').get('resultSetMetadata').get('columnMetadata')))
221
222 data = []
223 for record in records:
224 tmp = {}
225 for i in range(0, len(record.get('values')) ):
226 dataType = list(record.get('values')[i])[0]
227 dataValue = None
228 if dataType == "stringValue" or dataType == "blobValue":
229 dataValue = str(record.get('values')[i][dataType])
230 if dataType == "intValue" or dataType == "bigIntValue":
231 dataValue = int(record.get('values')[i][dataType])
232 if dataType == "bitValue" or dataType == "isNull":
233 dataValue = bool(record.get('values')[i][dataType])
234 if dataType == "doubleValue" or dataType == "realValue":
235 dataValue = float(record.get('values')[i][dataType])
236 tmp[keysList[i]] = dataValue
237 data.append(tmp)
238
239 return data