def add_sku():
try:
json = request.json
PARTNUMBER = json['PARTNUMBER']
CURRENCYCODE = json['CURRENCYCODE']
DISPLAYNAME = json['DISPLAYNAME']
METRICDISPLAYNAME = json['METRICDISPLAYNAME']
PAY_AS_YOU_GO = json['PAY_AS_YOU_GO']
MONTHLY_COMMIT = json['MONTHLY_COMMIT']
# validate the received values
if PARTNUMBER and CURRENCYCODE and DISPLAYNAME and METRICDISPLAYNAME and PAY_AS_YOU_GO and MONTHLY_COMMIT and request.method == 'POST':
#do not save password as a plain text
#_hashed_password = generate_password_hash(_password)
#data = (_name, _email, _hashed_password,)
# save edits
sql = "INSERT INTO ociprice (PARTNUMBER, CURRENCYCODE, DISPLAYNAME, METRICDISPLAYNAME, PAY_AS_YOU_GO, MONTHLY_COMMIT) values (%s, %s, %s, %s, %s, %s)"
data = (PARTNUMBER, CURRENCYCODE, DISPLAYNAME, METRICDISPLAYNAME, PAY_AS_YOU_GO, MONTHLY_COMMIT)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('new SKU has been added successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def cleanup_mysql():
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("DELETE FROM ociprice")
conn.commit()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def getprice(sku):
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("select * from ociprice where PARTNUMBER=%s", sku)
row = cursor.fetchone()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
def emp():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT id, name, email, phone FROM users")
usrRows = cursor.fetchall()
respone = jsonify(usrRows)
respone.status_code = 200
return respone
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def ociprice():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM ociprice")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def load_to_mysql(oci_price_list):
try:
data = []
# search display name, metric(PAYG or monthly or requests), Model(PAY_AS_YOU_GO or MONTHLY_COMMIT)
for oci_price in oci_price_list['items']:
#print(oci_price)
if 'prices' in oci_price:
#some SKUs has no metricDisplayName
if "metricDisplayName" in oci_price:
#some SKUs are free up to certain capacity, hence there are 2 PAYG, 2 Monthly Flex in a single SKU. first two prices will be 0. please go with 3rd 4th price
if len(oci_price['prices']) == 2:
# create array for bulk insert
row_data = (oci_price['partNumber'],
oci_price['currencyCode'],
oci_price['displayName'],
oci_price['metricDisplayName'],
oci_price['prices'][0]['value'],
oci_price['prices'][1]['value'])
elif len(oci_price['prices']) == 4:
row_data = (oci_price['partNumber'],
oci_price['currencyCode'],
oci_price['displayName'],
oci_price['metricDisplayName'],
oci_price['prices'][2]['value'],
oci_price['prices'][3]['value'])
else:
print("{} has more than 4 prices".format(
oci_price['partNumber']))
else:
row_data = (oci_price['partNumber'],
oci_price['currencyCode'],
oci_price['displayName'], "N/A",
oci_price['prices'][0]['value'],
oci_price['prices'][1]['value'])
data.append(row_data)
else:
print("{} has no pricing".format(oci_price['partNumber']))
#print(data)
# Oracle DB => :1, :2...
# MySQL => %s, %s...
sql = "INSERT INTO ociprice (PARTNUMBER, CURRENCYCODE, DISPLAYNAME, METRICDISPLAYNAME, PAY_AS_YOU_GO, MONTHLY_COMMIT) values ("
#sql += ":1, :2, :3, :4, :5, :6"
sql += "%s, %s, %s, %s, %s, %s"
sql += ") "
#print(sql)
conn = mysql.connect()
cursor = conn.cursor()
cursor.executemany(sql, data)
#cursor.execute(sql, data)
conn.commit()
selectAll = "select * from ociprice"
cursor.execute(selectAll)
print(cursor.fetchall())
print("ociprice - " + str(len(data)) + " Rows Inserted")
except Exception as e:
print("\nError insering data into ociprice - " + str(e) + "\n")
raise SystemExit
finally:
cursor.close()
conn.close()