def start_loading(world_socket, order_id, truck_id): print("Entered start_loading()") command = world.ACommands() load = command.load.add() seqnum = generate_seqnum_and_add_to_waiting_ack_set() load.seqnum = seqnum try: conn, cursor = connect_db() query = f""" SELECT warehouse_id FROM amazon_frontend_order WHERE id={order_id} """ cursor.execute(query) load.whnum = cursor.fetchone()[0] load.truckid = truck_id load.shipid = order_id cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error) update_order_status(order_id, order_status.LOADING) resend_to_world_until_ack_received(command, seqnum, world_socket) print("Exited start_loading()")
def handle_truck_arrived(world_socket, truck_arrived): print("Entered handle_truck_arrived()") try: conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET truck_id={truck_arrived.truck_id} WHERE id={truck_arrived.package_id} """ execute_and_commit(query, conn, cursor) query = f""" SELECT status FROM amazon_frontend_order WHERE id={truck_arrived.package_id} """ cursor.execute(query) row = cursor.fetchone() if row[0] == order_status.PACKED: start_loading(world_socket, truck_arrived.package_id, truck_arrived.truck_id) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error)
def handle_purchased(world_socket, ups_socket, purchased): print("Entered handle_purchased()") # Reply ACK to world reply_ack_to_world_with_lock(purchased, world_socket) try: conn, cursor = connect_db() query = "" for thing in purchased.things: query += f""" UPDATE amazon_frontend_warehousestock SET num_product=num_product+{thing.count} WHERE warehouse_id={purchased.whnum} AND product_id={thing.id}; """ execute_and_commit(query, conn, cursor) query = f""" SELECT id FROM amazon_frontend_order WHERE status='{order_status.PURCHASING}' """ cursor.execute(query) orders = cursor.fetchall() for order in orders: order_id = order[0] if check_warehouse_inventory(order_id, purchased.whnum) == True: start_packing(world_socket, order_id) start_getting_truck(ups_socket, order_id) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error)
def handle_packed(world_socket, packed): print("Entered handle_packed()") # Reply ACK to world reply_ack_to_world_with_lock(packed, world_socket) try: conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET status='{order_status.PACKED}', time_packed='{get_current_time()}' WHERE id={packed.shipid} """ execute_and_commit(query, conn, cursor) query = f""" SELECT truck_id FROM amazon_frontend_order WHERE id={packed.shipid} """ cursor.execute(query) truck_id = cursor.fetchone()[0] if truck_id is not None: start_loading(world_socket, packed.shipid, truck_id) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error) print("Exited handle_packed()")
def start_getting_truck(ups_socket, order_id): ''' Start requesting a truck for order: orderid ''' print("Entered start_getting_truck()") Ucommand = ups.AUCommands() get_truck = Ucommand.get_truck try: conn, cursor = connect_db() query = f""" SELECT ups_account, warehouse_id, location_x, location_y, destination_x, destination_y FROM amazon_frontend_order, amazon_frontend_warehouse WHERE amazon_frontend_order.warehouse_id=amazon_frontend_warehouse.id AND amazon_frontend_order.id={order_id} """ cursor.execute(query) row = cursor.fetchone() get_truck.order_id = order_id get_truck.ups_account = row[0] get_truck.warehouse_id = row[1] get_truck.location_x = row[2] get_truck.location_y = row[3] get_truck.destination_x = row[4] get_truck.destination_y = row[5] cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error) send_to_ups_with_lock(Ucommand, ups_socket) print("Exited start_getting_truck()")
def check_warehouse_inventory(order_id, warehouse_id): try: conn, cursor = connect_db() query = f""" SELECT product_id, num_product FROM amazon_frontend_orderproducttuple WHERE order_id={order_id} """ cursor.execute(query) rows = cursor.fetchall() for row in rows: product_id, num_product = row query = f""" SELECT num_product FROM amazon_frontend_warehousestock WHERE warehouse_id={warehouse_id} AND product_id={product_id} """ cursor.execute(query) num_in_stock = cursor.fetchone()[0] if num_in_stock < num_product: return False cursor.close() return True except (Exception, psycopg2.DatabaseError) as error: print(error)
def connect_world_command(world_id): conn, cursor = connect_db() query = f""" SELECT id, location_x, location_y FROM amazon_frontend_warehouse """ cursor.execute(query) rows = cursor.fetchall() cursor.close() initWarehouses = [] for row in rows: initWarehouse = world.AInitWarehouse() initWarehouse.id = row[0] initWarehouse.x = row[1] initWarehouse.y = row[2] initWarehouses.append(initWarehouse) command = world.AConnect() command.worldid = world_id command.isAmazon = True for initWarehouse in initWarehouses: command.initwh.add().CopyFrom(initWarehouse) return command
def update_order_status(order_id, new_status): conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET status='{new_status}' WHERE id={order_id} """ execute_and_commit(query, conn, cursor) cursor.close()
def check_canceled(order_id): conn, cursor = connect_db() query = f""" SELECT status FROM amazon_frontend_order WHERE id={order_id} """ cursor.execute(query) status = cursor.fetchone()[0] cursor.close() return status == order_status.CANCELED
def collect_stock_day(pools: [str], db_type: str, update=False): pro = _init_api(TOKEN) conn = connect_db(db_type) cursor = conn.cursor() for i, code in enumerate(pools): try: start = "20000101" if update: cursor.execute("select date from {0} where code='{1}'".format( STOCK_DAY[TABLE], code)) rs = cursor.fetchall() if len(rs) > 0: start = sorted(rs, reverse=True)[0][0] cursor.execute( ("delete from {0} where code='{1}' " "and date='{2}'").format(STOCK_DAY[TABLE], code, start)) start = str(start).replace("-", "") print("start:", start) daily = pro.daily(ts_code=code, start_date=start) adj_factor = pro.adj_factor(ts_code=code) if update: adj_factor = adj_factor[adj_factor["trade_date"] >= start] print("adj:", adj_factor.shape) df = natural_outer_join(daily, adj_factor) # 打印进度 print('Seq: ' + str(i + 1) + ' of ' + str(len(pools)) + ' Code: ' + str(code)) df = unify_df_col_nm(df) # print(df.columns) except Exception as err: print(err) print('No DATA Code: ' + str(i)) continue for _, row in df.iterrows(): try: row["date"] = (datetime.datetime.strptime( row["date"], "%Y%m%d")).strftime('%Y-%m-%d') cursor.execute( _sql_insert(db_type, STOCK_DAY[TABLE], STOCK_DAY[COLUMNS]), tuple(row[list(STOCK_DAY[COLUMNS])])) conn.commit() except Exception as err: print("error:", err) continue close_db(conn)
def gen_data(pred_period=20, lower_bound="2011-01-01", start="2014-01-01"): db_type = "sqlite3" conn = dbop.connect_db(db_type) cursor = conn.cursor() df_all, cols_future = prepare_data(cursor, pred_period=pred_period, start=lower_bound) data_period = (df_all.index >= start) df_all = df_all[data_period] df_all = df_all[df_all["amt"] != 0] return df_all, cols_future
def init_table(table_name, db_type): conn = connect_db(db_type) cursor = conn.cursor() configs = _parse_config(path="database\\config\\{}".format(table_name)) sql_drop = "drop table {}".format(table_name) sql_create = configs["create"] print(sql_create) try: cursor.execute(sql_drop) except Exception as e: pass cursor.execute(sql_create) close_db(conn)
def handle_destination_changed(destination_changed): print("Entered handle_destination_changed()") try: conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET destination_x={destination_changed.new_destination_x}, destination_y={destination_changed.new_destination_y} WHERE id={destination_changed.package_id} """ execute_and_commit(query, conn, cursor) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error)
def handle_delivered(delivered): print("Entered handle_delivered()") try: conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET status='{order_status.DELIVERED}', time_delivered='{get_current_time()}' WHERE id={delivered.package_id} """ execute_and_commit(query, conn, cursor) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error)
def start_packing(world_socket, order_id): print("Entered start_packing()") command = world.ACommands() pack = command.topack.add() seqnum = generate_seqnum_and_add_to_waiting_ack_set() pack.seqnum = seqnum try: conn, cursor = connect_db() query = f""" SELECT product_id, description, num_product FROM amazon_frontend_orderproducttuple, amazon_frontend_product WHERE amazon_frontend_orderproducttuple.product_id=amazon_frontend_product.id AND amazon_frontend_orderproducttuple.order_id={order_id} """ cursor.execute(query) rows = cursor.fetchall() for row in rows: thing = pack.things.add() thing.id = row[0] thing.description = row[1] thing.count = row[2] query = f""" SELECT warehouse_id FROM amazon_frontend_order WHERE id={order_id} """ cursor.execute(query) pack.whnum = cursor.fetchone()[0] pack.shipid = order_id # Update warehouse stock query = "" for thing in pack.things: query += f""" UPDATE amazon_frontend_warehousestock SET num_product=num_product-{thing.count} WHERE warehouse_id={pack.whnum} AND product_id={thing.id}; """ execute_and_commit(query, conn, cursor) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error) update_order_status(order_id, order_status.PACKING) resend_to_world_until_ack_received(command, seqnum, world_socket)
def collect_index_day(pools: [str], db_type: str, update=False): conn = connect_db(db_type) cursor = conn.cursor() for i, code in enumerate(pools): try: start = "2000-01-01" if update: cursor.execute("select date from {0} where code='{1}'".format( INDEX_DAY[TABLE], code)) rs = cursor.fetchall() # print(rs) if len(rs) > 0: start = sorted(rs, reverse=True)[0][0] cursor.execute( ("delete from {0} where code='{1}' " "and date='{2}'").format(INDEX_DAY[TABLE], code, start)) print("start:", start) df = ts.get_k_data(code=code, start=start) # 打印进度 print('Seq: ' + str(i + 1) + ' of ' + str(len(pools)) + ' Code: ' + str(code)) df.columns = unify_col_names(df.columns) print(df.shape) except Exception as err: print(err) print('No DATA Code: ' + str(i)) continue for _, row in df.iterrows(): try: cursor.execute( _sql_insert(db_type, INDEX_DAY[TABLE], INDEX_DAY[COLUMNS]), tuple(row[list(INDEX_DAY[COLUMNS])])) conn.commit() except Exception as err: print(err) continue close_db(conn)
def handle_loaded(world_socket, ups_socket, loaded): print("Entered handle_loaded()") # Reply ACK to world reply_ack_to_world_with_lock(loaded, world_socket) try: conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET status='{order_status.LOADED}', time_loaded='{get_current_time()}' WHERE id={loaded.shipid} """ execute_and_commit(query, conn, cursor) start_delivering(ups_socket, loaded.shipid) except (Exception, psycopg2.DatabaseError) as error: print(error) print("Exited handle_loaded()")
def start_delivering(ups_socket, order_id): print("Entered start_delivering()") try: conn, cursor = connect_db() query = f""" UPDATE amazon_frontend_order SET status='{order_status.DELIVERING}' WHERE id={order_id} """ execute_and_commit(query, conn, cursor) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error) deliver = ups.request_init_delivery() deliver.package_id = order_id command = ups.AUCommands() command.init_delivery.CopyFrom(deliver) send_to_ups_with_lock(command, ups_socket) print("Exiting start_delivering")
def handle_cancel(cancel): print("Entered handle_cancel()") try: conn, cursor = connect_db() query = f""" SELECT status FROM amazon_frontend_order WHERE id={cancel.order_id} """ cursor.execute(query) status = cursor.fetchone()[0] if status == order_status.PURCHASING: query = f""" UPDATE amazon_frontend_order SET status='{order_status.CANCELED}' WHERE id={cancel.order_id} """ execute_and_commit(query, conn, cursor) cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error)
#References: #https://medium.com/ai-society/a-concise-recommender-systems-tutorial-fa40d5a9c0fa #https://cambridgespark.com/content/tutorials/implementing-your-own-recommender-systems-in-Python/index.html #https://hackernoon.com/introduction-to-recommender-system-part-1-collaborative-filtering-singular-value-decomposition-44c9659c5e75 from pearson_similarity import similarity from recommend import recommend from db_operations import connect_db, get_reviews, insert_recommendations, clear_recommendations conn = connect_db() records = get_reviews(conn) num_users = len(records) to_insert = [] for i in range(num_users): user_id = records[i][0] recommendations = recommend(records, i, 10, similarity) as_tuples = [(user_id, int(item), recommendations[item]) for item in recommendations] to_insert.extend(as_tuples) print('Done fetching reviews!') clear_recommendations(conn) insert_recommendations(conn, to_insert) conn.commit() conn.close()
def insert_to_db(row, db_type: str, table_name, columns): conn = connect_db(db_type) cursor = conn.cursor() cursor.execute(_sql_insert(db_type, table_name, columns), list(row[columns])) conn.commit()
def handle_buy(world_socket, ups_socket, buy): print("Entered handle_buy()") Acommand = world.ACommands() purchase = Acommand.buy.add() seqnum = generate_seqnum_and_add_to_waiting_ack_set() purchase.seqnum = seqnum # Ucommand = ups.AUCommands() # get_truck = Ucommand.get_truck try: conn, cursor = connect_db() # Acommand # retrieve things in an order, be careful with the columns queried query = f""" SELECT product_id, description, num_product FROM amazon_frontend_orderproducttuple, amazon_frontend_product WHERE amazon_frontend_orderproducttuple.product_id=amazon_frontend_product.id AND amazon_frontend_orderproducttuple.order_id={buy.order_id} """ cursor.execute(query) rows = cursor.fetchall() for row in rows: thing = purchase.things.add() thing.id = row[0] thing.description = row[1] thing.count = row[2] query = f""" SELECT COUNT(*), MIN(id) FROM amazon_frontend_warehouse; """ cursor.execute(query) num_warehouse, min_warehouse_id = cursor.fetchone() purchase.whnum = buy.order_id % int(num_warehouse) + int( min_warehouse_id) query = f""" UPDATE amazon_frontend_order SET warehouse_id={purchase.whnum} WHERE id={buy.order_id} """ execute_and_commit(query, conn, cursor) # # Ucommand # query = f""" # SELECT ups_account, warehouse_id, location_x, location_y, destination_x, destination_y # FROM amazon_frontend_order, amazon_frontend_warehouse # WHERE amazon_frontend_order.warehouse_id=amazon_frontend_warehouse.id AND amazon_frontend_order.id={buy.order_id} # """ # cursor.execute(query) # row = cursor.fetchone() # get_truck.order_id = buy.order_id # get_truck.ups_account = row[0] # get_truck.warehouse_id = row[1] # get_truck.location_x = row[2] # get_truck.location_y = row[3] # get_truck.destination_x = row[4] # get_truck.destination_y = row[5] cursor.close() except (Exception, psycopg2.DatabaseError) as error: print(error) # send_to_ups_with_lock(Ucommand, ups_socket) resend_to_world_until_ack_received(Acommand, seqnum, world_socket)