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()