def extract(self): self.queryData = QueryData.QueryData() self.queryData.connect() result = utils.sqlHandler(queryData=self.queryData, sqlType="query", sql=extract_sql, piece=10000) self.queryData.close() conn_load = QueryData.QueryData(host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get( "mysql", "dbname_2")) conn_load.connect() conn_load.update(delete_before_insert) conn_load.commit() time.sleep(2) for item in result: for key in item: if type(item[key]) == str and item[key] != "null": item[key] = "'" + item[key] + "'" item["create_date"] = time.strftime( "%Y-%m-%d", time.localtime(item["create_time"])) item["create_date"] = "'" + item["create_date"] + "'" conn_load.update(extract_load % item) print("完成合同主题抽取、转换、存储......") conn_load.commit() conn_load.close()
def load(self): # 计算创建日期在三天以内的静态 """ 整体过程是将处理后的主题类数据上传到数据库服务器 """ try: self.queryData = QueryData.QueryData(host=self.cp.get("mysql","host_2"),user=self.cp.get("mysql","user_2"),pwd=self.cp.get("mysql","pwd_2"),dbname=self.cp.get("mysql","dbname_2")) self.queryData.connect() conn = QueryData.QueryData() conn.connect() for hash_key in self.emp_result: per_theme_data = self.emp_result[hash_key][0] self.queryData.update(load_sql%per_theme_data) self.queryData.commit() self.calc_static_validity(conn,self.queryData) print ("完成市场主题存储......") # 数据存储之后,validity_for_once字段 self.queryData.close() conn.close() writelogging.logger.info("市场主题数据装载完成,本次%s条记录"%len(self.emp_result)) except Exception as e: writelogging.logger.error("市场主题数据装载失败") self.emp_result = {}
def addroute(start, end, dist): source_to_dest = GraphLibrary.Route(start, end, dist) dest_to_source = GraphLibrary.Route(end, start, dist) GraphLibrary.route_list.append(source_to_dest) GraphLibrary.route_list.append(dest_to_source) QueryData.addhubcity(start) QueryData.addhubcity(end)
def extract(self): try: self.queryData = QueryData.QueryData() self.queryData.connect() result = utils.sqlHandler(queryData=self.queryData,sqlType="query",sql=extract_sql,piece = 3000) self.queryData.close() except Exception as e: writelogging.logger.error("市场主题数据库连接失败,请检查用户名,密码") try: # 一下开始插入数据到另外一个数据库 self.queryData = QueryData.QueryData(host=self.cp.get("mysql","host_2"),user=self.cp.get("mysql","user_2"),pwd=self.cp.get("mysql","pwd_2"),dbname=self.cp.get("mysql","dbname_2")) self.queryData.connect() # 首先清除之前的错误数据 self.queryData.update(delete_before_insert) self.queryData.commit() for item in result: if (not item["status"]): item["status"] ="null" item["emp_status"] = "null" elif (item["status"] in self.emp_status): item["emp_status"] = self.emp_status[item["status"]] else: item["emp_status"] = "null" if (not item["validity"]): item["validity"] = "null" item["state"] = "null" elif ( item["validity"] in self.status_dict): item["state"] = self.status_dict[item["validity"]] else: item["state"] = "null" if item["update_time"] == "null": item["update_time"] = "0000-00-00 00:00:00" if item["create_time"] == "null": item["create_time"] = "0000-00-00 00:00:00" if (item["description"] and (item["description"].find("爷")>=0 or item["description"].find("奶")>=0) ): item["description"] = "非双亲陪同" else: item["description"]=" " # 循环遍历每一项,如果为None,则改为null # for key in item.keys(): # if (not item[key]): # item[key]="null" if (item["validity"]): self.queryData.update(extract_insert_sql%item) self.queryData.commit() self.queryData.close() print ("完成市场主题抽取......") writelogging.logger.info("市场信息表抽取完成,一共%s个记录"%len(result)) except Exception as e: writelogging.logger.error("市场主题数据抽取、数据库连接失败")
def TestaddRoute(self): code = QueryData.nametocode("Los Angeles") code2 = QueryData.nametocode("Seoul") EditRoute.addroute(code, code2, "12435") isValid = False for route in GraphLibrary.route_list: if route.startportcode == code and route.endportcode == code2: isValid = True break self.assertTrue(isValid)
def TestremoveRoute(self): EditRoute.removeroute("Los Angeles", "San Francisco") isValid = True code = QueryData.nametocode("Los Angeles") code2 = QueryData.nametocode("San Francisco") for route in GraphLibrary.route_list: if route.startportcode == code and route.endportcode == code2: isValid = False break self.assertTrue(isValid)
def test_addRoute(self): code = QueryData.nametocode("Los Angeles") code2 = QueryData.nametocode("Seoul") EditRoute.addroute(code, code2, "12435") isValid = False for route in GraphLibrary.route_list: if route.startportcode == code and route.endportcode == code2: isValid = True break self.assertTrue(isValid)
def test_removeRoute(self): EditRoute.removeroute("Los Angeles", "San Francisco") isValid = False code = QueryData.nametocode("Los Angeles") code2 = QueryData.nametocode("San Francisco") for route in GraphLibrary.route_list: if route.startportcode == code and route.endportcode == code2: isValid = True break self.assertTrue(isValid)
def parse_route(data): for route in data["routes"]: source_to_dest = GraphLibrary.Route(route["ports"][0], route["ports"][1], route["distance"]) dest_to_source = GraphLibrary.Route(route["ports"][1], route["ports"][0], route["distance"]) GraphLibrary.route_list.append(source_to_dest) GraphLibrary.route_list.append(dest_to_source) QueryData.addhubcity(route["ports"][0]) QueryData.addhubcity(route["ports"][1])
def changePredict(plant_num, collectedColor, idealColor): # Save the full data set to a 2D list dataArray = QueryData.collectData(plant_num) waterVals = dataArray[0] colorVals = dataArray[1] # Find the difference between the ideal and collected color difference = 1 - (1.0 * collectedColor / idealColor) newWater = waterVals[-1] # If the difference is significant in either direction, update based on correlation if difference > 0.05: slope = abs(regression(waterVals, colorVals)) addAmt = slope * abs(collectedColor - idealColor) lastWater = waterVals[-1] newWater = lastWater + addAmt elif difference < -0.05: slope = abs(regression(waterVals, colorVals)) addAmt = slope * abs(collectedColor - idealColor) lastWater = waterVals[-1] newWater = lastWater - addAmt healthy = True # Remove extreme irrigation volume changes if newWater > 30: while newWater > 7: newWater -= 1.1837513 healthy = False if newWater < 2: while newWater < 5: newWater += 0.391 healthy = False return newWater, healthy
def routeinfo(): cityList = QueryData.citylist() isValid = False while not isValid: print "Type the first valid city of new route." startcity = raw_input() isValid = startcity in cityList if startcity == "exit" or startcity == "quit" or startcity == "q" or startcity == "0": return legs = [] stops = [] # Gather dest city while (True): isValid = False while not isValid: endcity = raw_input( "Type the next valid city of route, or type exit to end") isValid = endcity in cityList or endcity == "exit" or endcity == "q" if not isValid: print "Wrong city. Not counted." if endcity == "exit" or endcity == "q": break stops.append(endcity) startcode = QueryData.nametocode(startcity) endcode = QueryData.nametocode(endcity) for route in GraphLibrary.route_list: if route.startportcode == startcode and \ route.endportcode == endcode: dist = route.distance legs.append(dist) stops.append(endcode) break startcity = endcity # after getting all the list print "Total Distance = " + str(totaldistance(legs)) + " km" print "Total Cost = $" + str(totalcost(legs)) print "Total Time = " + str(totaltime(legs, stops)) + " hrs"
def routeinfo(): cityList = QueryData.citylist() isValid = False while not isValid: print "Type the first valid city of new route." startcity = raw_input() isValid = startcity in cityList if startcity == "exit" or startcity == "quit" or startcity == "q" or startcity == "0": return legs = [] stops = [] # Gather dest city while(True): isValid = False while not isValid: endcity = raw_input("Type the next valid city of route, or type exit to end") isValid = endcity in cityList or endcity == "exit" or endcity == "q" if not isValid: print "Wrong city. Not counted." if endcity == "exit" or endcity == "q": break stops.append(endcity) startcode = QueryData.nametocode(startcity) endcode = QueryData.nametocode(endcity) for route in GraphLibrary.route_list: if route.startportcode == startcode and \ route.endportcode == endcode: dist = route.distance legs.append(dist) stops.append(endcode) break startcity = endcity # after getting all the list print "Total Distance = " + str(totaldistance(legs)) + " km" print "Total Cost = $" + str(totalcost(legs)) print "Total Time = " + str(totaltime(legs, stops)) + " hrs"
def removeroute(city, city2): code = QueryData.nametocode(city) code2 = QueryData.nametocode(city2) isValid = False for route in GraphLibrary.route_list: if code in route.startportcode and code2 in route.endportcode: isValid = True del route elif code2 in route.startportcode and code in route.startportcode: isValid = True del route if isValid: print "Remove of route between " + city + " and " + city2 + " is successful." else: print "Remove unsuccessful."
def extract(self): queryData = QueryData.QueryData() try: queryData.connect() result = queryData.query(sql) queryData.close() self.result = result except Exception as e: raise
def editnetwork(): while(True): isValid = False cityList = QueryData.citylist() while(not isValid): print "Type a valid city to edit, or type 0 or exit to quit" user_city = raw_input() isValid = user_city in cityList if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0": return while(True): s = "Type the number for corresponding options:\n" \ "1. Edit current city's code\n" \ "2. Edit current city's country\n" \ "3. Edit current city's continent\n" \ "4. Edit current city's timezone\n" \ "5. Edit current city's region\n" \ "6. Edit current city's population\n" \ "0. Quit" print s i = raw_input("Enter the number.") i = int(i) if i == 1: newcode = raw_input("Type the new code of the city.") editcode(user_city, newcode) elif i == 2: newcountry = raw_input("Type the new country of the city.") editcountry(user_city, newcountry) elif i == 3: newcont = raw_input("Type the new continent of the city.") editcontinent(user_city, newcont) elif i == 4: newzone = raw_input("Type the new timezone of the city.") edittimezone(user_city, newzone) elif i == 5: newreg = raw_input("Type the new region of the city.") editregion(user_city, newreg) elif i == 6: newpop = raw_input("Type the new population of the city.") editpop(user_city, newpop) elif i == 0: break else: print "Invalid Number!"
def editnetwork(): while (True): isValid = False cityList = QueryData.citylist() while (not isValid): print "Type a valid city to edit, or type 0 or exit to quit" user_city = raw_input() isValid = user_city in cityList if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0": return while (True): s = "Type the number for corresponding options:\n" \ "1. Edit current city's code\n" \ "2. Edit current city's country\n" \ "3. Edit current city's continent\n" \ "4. Edit current city's timezone\n" \ "5. Edit current city's region\n" \ "6. Edit current city's population\n" \ "0. Quit" print s i = raw_input("Enter the number.") i = int(i) if i == 1: newcode = raw_input("Type the new code of the city.") editcode(user_city, newcode) elif i == 2: newcountry = raw_input("Type the new country of the city.") editcountry(user_city, newcountry) elif i == 3: newcont = raw_input("Type the new continent of the city.") editcontinent(user_city, newcont) elif i == 4: newzone = raw_input("Type the new timezone of the city.") edittimezone(user_city, newzone) elif i == 5: newreg = raw_input("Type the new region of the city.") editregion(user_city, newreg) elif i == 6: newpop = raw_input("Type the new population of the city.") editpop(user_city, newpop) elif i == 0: break else: print "Invalid Number!"
def transform(self): """ 1、从数据表中读取所有员工信息 2、循环遍历每个员工,分别计算每个指标,并存储在字典中,如{"A指标":"##","B指标":"##"} 3、将第2个步骤中的结果存储在数据库中,从而完成转换,插入数据的原则是没有插入,有则修改结果 """ self.queryData = QueryData.QueryData(host=self.cp.get("mysql","host_2"),user=self.cp.get("mysql","user_2"),pwd=self.cp.get("mysql","pwd_2"),dbname=self.cp.get("mysql","dbname_2")) self.queryData.connect() # subSql = "select concat(emp_id,date(create_time)) from bi_sale_info where date(update_time) = date_sub(curdate(),interval 1 day)" # result4update = self.queryData.query(subSql) # result4update = [value["concat(emp_id,date(create_time))"] for value in result4update] # result4update = tuple(result4update) _sql = "select * from bi_sale_info where date(create_time) between '%s' and '%s'"%(start_date,end_date) times = 0 while True: # sql = _sql+" or concat(emp_id,date(create_time)) in"+str(result4update)+" limit %s,%s"%(times*limit,limit) result_info = self.queryData.query(_sql+" limit %s, %s"%(times*limit,limit)) length = len(result_info) for entry in result_info: group = "%s%s%s"%(entry["emp_id"],entry["campus_name"],entry["create_time"][0:10]) hl_md5_obj = hashlib.md5(group.encode("utf-8")) # 唯一键,标明订单属于哪一个员工,哪一个校区的订单 unique = hl_md5_obj.hexdigest() if (unique not in self.emp_result.keys()): self.emp_result[unique] =[{\ "emp_id":entry["emp_id"],\ "emp_name":entry["emp_cn_name"],\ "campus_name":entry["campus_name"],\ "create_date":entry["create_time"][0:10],\ "order_total":0,\ "order_valid_num":0,\ "order_invalid_num":0,\ "invite_total":0,\ "order_no_parents":0,\ "contact_total":0\ }] everyGroup = self.emp_result[unique][0] everyGroup["order_total"] +=1 if (entry["order_status"] in ["有效","再联系","邀约成功"]): everyGroup["order_valid_num"] += 1 if (entry["order_status"] in ["无效"]): everyGroup["order_invalid_num"] += 1 if (entry["order_status"] in ["邀约成功"]): everyGroup["invite_total"] += 1 if (entry["order_status"] in ["再联系"]): everyGroup["contact_total"] += 1 if (entry["description"] and entry["description"].find("非双亲")>=0 ): everyGroup["order_no_parents"] += 1 times+=1 time.sleep(1) if length < limit: print ("完成市场主题转换......") self.queryData.close() break
def removecity(city): del GraphLibrary.city_dictionary[city] # since the city is removed, we have to remove any route # which has the city as starting point code = QueryData.nametocode(city) for route in GraphLibrary.route_list: if code in route.startportcode: del route
def extract(self): delete_before_insert = "delete from bi_visit_info where date(visit_time) between '%s' and '%s' " % ( start_date, end_date) load_sql = "insert into bi_visit_info(student_id,visit_campus,visit_time,invite_role_id,department_name,v_ftm_id\ ) values(%(student_id)s,'%(visit_campus)s','%(visit_time)s',%(invite_role_id)s,'%(department_name)s','%(v_ftm_id)s')\ on duplicate key update student_id=values(student_id),visit_campus=values(visit_campus),invite_role_id=values(invite_role_id),department_name=values(department_name),v_ftm_id=values(v_ftm_id)" self.queryData = QueryData.QueryData() self.queryData.connect() resultSet = self.queryData.query(extract_sql) resultSet = self.getTarget(resultSet) conn_load = QueryData.QueryData(host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get( "mysql", "dbname_2")) conn_load.connect() conn_load.update(delete_before_insert) conn_load.commit() for item in resultSet: # student_id,visit_campus,invite_role_id,creator_role_id department_name = self.queryDept(item["invite_role_id"]) time.sleep(0.2) item["department_name"] = "" if department_name and department_name.get("data"): item["department_name"] = department_name["data"][0] if (not item["visit_campus"]): item["visit_campus"] = item["source_campus"] v_ftm_list = self.queryData.query( "select v_ftm from v_student where student_id=%(student_id)s" % item) item["v_ftm_id"] = "" v_ftm = v_ftm_list[0].get("v_ftm") if v_ftm: item["v_ftm_id"] = v_ftm conn_load.update(load_sql % item) print("完成visit主题抽取......") conn_load.commit() conn_load.close() self.queryData.close()
def extract(self): self.queryData = QueryData.QueryData() self.queryData.connect() conn_load = QueryData.QueryData(host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get( "mysql", "dbname_2")) conn_load.connect() conn_load.update(delete_before_insert) conn_load.commit() times = 0 while True: result = self.queryData.query( extract_sql % (start_date, end_date, times * limit)) total = len(result) print("Tmk抽取数量 %s" % total) for entry in result: # 处理需要转换的数据 if entry["dept_id"] == 74: entry["group_name"] = "成人组" elif entry["dept_id"] == 75: entry["group_name"] = "少儿组" elif entry["dept_id"] == 76: entry["group_name"] = "跟进组" else: entry["group_name"] = "null" for field in entry: value = entry[field] if (type(value) == str and value != "null"): entry[field] = "'" + value + "'" conn_load.update(extract_load % entry) times += 1 conn_load.commit() time.sleep(1) if (total < limit): print("完成TMK主题抽取......") self.queryData.close() conn_load.close() break
def shortestroute(): start = "" finish = "" cityList = QueryData.citylist() isValid = False while not isValid: start = raw_input("Type a departing city or type exit to quit.") isValid = start in cityList if start == "q" or start == "exit": return isValid = False while not isValid: end = raw_input("Type an arriving city or type exit to quit.") isValid = start in cityList if start == "q" or start == "exit": return
def load(self): # delete_before_insert = "delete from bi_visit_theme where visit_date between '%s' and '%s' "%(start_date,end_date) load_sql = "insert into bi_visit_theme(visit_campus,visit_num,visit_date) \ values(%(visit_campus)s,%(visit_num)s,%(visit_date)s)\ on duplicate key update visit_num = values(visit_num)" conn = QueryData.QueryData(host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get("mysql", "dbname_2")) conn.connect() for hash_key in self.visit_result: per_theme_data = self.visit_result[hash_key][0] for field in per_theme_data: if type(per_theme_data[field] ) == str and per_theme_data[field] != "null": per_theme_data[field] = "'" + per_theme_data[field] + "'" conn.update(load_sql % per_theme_data) self.visit_result = {} print("完成visit主题存储......") conn.commit() conn.close()
def transform(self): self.queryData = QueryData.QueryData( host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get("mysql", "dbname_2")) self.queryData.connect() sql = "select * from bi_visit_info where date(visit_time) between '%s' and '%s' " % ( start_date, end_date) resultSet = self.queryData.query(sql) for entry in resultSet: source_data = "%s%s" % (entry["visit_campus"], entry["visit_time"][0:10]) source_hash = hashlib.md5(source_data.encode("utf-8")).hexdigest() if self.visit_result.get(source_hash) == None: self.visit_result[source_hash]=[{\ "visit_date":entry["visit_time"][0:10],\ "visit_campus":entry["visit_campus"],\ "visit_num":0\ }] load_entry = self.visit_result[source_hash][0] load_entry["visit_num"] += 1 print("完成visit主题转换......")
def load(self): load_insert = "insert into bi_tmk_theme(\ emp_id,emp_name,group_name,order_total,order_new,order_old,campus_name,invite_success_total,\ contact_total,order_invalid_num,calculate_date,order_unknown_num,order_valid_num)\ values(%(emp_id)s,%(emp_name)s,%(group_name)s,%(order_total)s,%(order_new)s,%(order_old)s,\ %(campus_name)s,%(invite_success_total)s,%(contact_total)s,%(order_invalid_num)s,%(calculate_date)s,\ %(order_unknown_num)s,%(order_valid_num)s)\ on duplicate key update emp_name=values(emp_name),\ group_name=values(group_name),\ order_total=values(order_total),\ order_new=values(order_new),\ order_old=values(order_old),\ campus_name=values(campus_name),\ invite_success_total=values(invite_success_total),\ contact_total=values(contact_total),\ order_invalid_num=values(order_invalid_num),\ order_unknown_num=values(order_unknown_num),\ order_valid_num=values(order_valid_num)" conn = QueryData.QueryData(host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get("mysql", "dbname_2")) conn.connect() # 目标表中,如果员工号,校区,时间,有一项不同,就需要insert,完全相同则是update for hash_key in self.tmk_result: per_theme_data = self.tmk_result[hash_key][0] for field in per_theme_data: if type(per_theme_data[field] ) == str and per_theme_data[field] != "null": per_theme_data[field] = "'" + per_theme_data[field] + "'" conn.update(load_insert % per_theme_data) conn.commit() print("完成TMK主题存储......") conn.close() writelogging.logger.info("TMK主题数据装载结束,本次%s条记录" % len(self.tmk_result)) self.tmk_result = {}
def TestaveragePop(self): key = QueryData.averagecitysize() self.assertEqual(key, 11796143)
def start(self): MapParser.MapParser() s = "Type the number for corresponding options:\n" \ "1. Get all the cities from CSAir\n" \ "2. Get a specific information about one city\n" \ "3. Get all kinds of stats\n" \ "4. Get a specific route\n" \ "5. Edit the current city's information\n" \ "6. Add/Remove cities and routes\n" \ "7. Save the changed list to JSON File\n" \ "8. Calculuate the total flight\n" \ "9. Update CSAir with new Champaign hub town\n" \ "10. Calculate the shortest route between to cities.\n" \ "0. Exit the program" while True: print s i = raw_input("Enter the number.") i = int(i) if i == 1: cityList = QueryData.citylist() print "Here are the lists!" for city in cityList: print city elif i == 2: city_name = raw_input("Enter the city name.") valid = QueryData.getcitydata(city_name) if valid == False: print "No such city." elif i == 3: QueryData.getstat() elif i == 4: city_code = raw_input("Enter the city code.") valid = QueryData.getroute(city_code) if valid == False: print "No such code." elif i == 5: EditRoute.editnetwork() elif i == 6: EditRoute.addnetwork() elif i == 7: SaveFile.saveFile() elif i == 8: RouteInfo.routeinfo() elif i == 9: MapParser.newMapParser() elif i == 10: self.routeInfo.shortestroute() elif i == 0: print "See ya!" break else: print "Invalid Number!"
def TestshortestLength(self): key = QueryData.shortestflight() self.assertEqual(key, 334)
__author__ = 'Aman' import QueryData #Uncomment the below to test the accuracy for the code. #print QueryData.testTree() #Uncomment the below if we want to query for a particular row. QueryData.query()
"5. Edit the current city's information\n" \ "6. Add/Remove cities and routes\n" \ "7. Save the changed list to JSON File\n" \ "8. Calculuate the total flight\n" \ "9. Update CSAir with new Champaign hub town\n" \ "10. Calculate the shortest route between to cities.\n" \ "0. Exit the program" while True: print s i = raw_input("Enter the number.") i = int(i) if i == 1: cityList = QueryData.citylist() print "Here are the lists!" for city in cityList: print city elif i == 2: city_name = raw_input("Enter the city name.") valid = QueryData.getcitydata(city_name) if valid == False : print "No such city." elif i == 3: QueryData.getstat()
"5. Edit the current city's information\n" \ "6. Add/Remove cities and routes\n" \ "7. Save the changed list to JSON File\n" \ "8. Calculuate the total flight\n" \ "9. Update CSAir with new Champaign hub town\n" \ "10. Calculate the shortest route between to cities.\n" \ "0. Exit the program" while True: print s i = raw_input("Enter the number.") i = int(i) if i == 1: cityList = QueryData.citylist() print "Here are the lists!" for city in cityList: print city elif i == 2: city_name = raw_input("Enter the city name.") valid = QueryData.getcitydata(city_name) if valid == False: print "No such city." elif i == 3: QueryData.getstat()
def transform(self): sql = "select * from bi_tmk_info\ where date(update_time) = '%s'" unique = {} # 用来查询有没有更早的新单 self.queryData = QueryData.QueryData() self.queryData.connect() conn_query = QueryData.QueryData(host=self.cp.get("mysql", "host_2"), user=self.cp.get("mysql", "user_2"), pwd=self.cp.get("mysql", "pwd_2"), dbname=self.cp.get( "mysql", "dbname_2")) conn_query.connect() # subSql = "select concat(emp_id,date(create_time)) from bi_tmk_info where date(update_time) = date_sub(curdate(),interval 1 day)" # result4update = conn_query.query(subSql) # result4update = [value["concat(emp_id,date(create_time))"] for value in result4update] # # 避免因为一个结果生成类似("",)的元组,在sql中会产生错误 # result4update.extend(["查询无结果"+str(datetime.datetime.now())]*2) # result4update = tuple(result4update) global start_date while start_date <= end_date: result = conn_query.query(sql % start_date) unique.update(self.dataDistinct(result)) start_date = datetime.datetime.strptime( start_date, "%Y-%m-%d") + datetime.timedelta(days=1) start_date = start_date.strftime("%Y-%m-%d") time.sleep(1) # 遍历所有记录,去最新数据,当天多个电话去重 for index, _entry in enumerate(unique.values()): _entry = _entry[0] source_data = ("%s%s%s" % (_entry["emp_id"], _entry["campus_name"], _entry["update_time"][0:10])) source_hash = hashlib.md5(source_data.encode("utf-8")).hexdigest() if source_hash not in self.tmk_result: self.tmk_result[source_hash] = [{\ "emp_id":_entry["emp_id"],\ "emp_name":_entry["emp_name"],\ "calculate_date":_entry["update_time"][0:10],\ "campus_name":_entry["campus_name"],\ "group_name":_entry["group_name"],\ "order_total":0,\ "order_new":0,\ "order_old":0,\ "invite_success_total":0,\ "contact_total":0,\ "order_invalid_num":0,\ "order_valid_num":0,\ "order_unknown_num":0\ }] load_entry = self.tmk_result[source_hash][0] load_entry["order_total"] += 1 if _entry["validity"] in ["邀约成功"]: load_entry["invite_success_total"] += 1 if _entry["validity"] in ["再联系"]: load_entry["contact_total"] += 1 if _entry["validity"] in ["无效"]: load_entry["order_invalid_num"] += 1 if _entry["validity"] in ["未知"]: load_entry["order_unknown_num"] += 1 if _entry["validity"] in ["有效"]: load_entry["order_valid_num"] += 1 result = self.queryData.query( "select count(*) count from v_invitation where student_id=%s and date(s_d_time)<'%s'" % (_entry["student_id"], _entry["update_time"][0:10])) if (not result[0]["count"]): load_entry["order_new"] += 1 load_entry["order_old"] = load_entry["order_total"] - load_entry[ "order_new"] self.queryData.close() conn_query.close() print("完成tmk主题转换")
def start(self): MapParser.MapParser() s = "Type the number for corresponding options:\n" \ "1. Get all the cities from CSAir\n" \ "2. Get a specific information about one city\n" \ "3. Get all kinds of stats\n" \ "4. Get a specific route\n" \ "5. Edit the current city's information\n" \ "6. Add/Remove cities and routes\n" \ "7. Save the changed list to JSON File\n" \ "8. Calculuate the total flight\n" \ "9. Update CSAir with new Champaign hub town\n" \ "10. Calculate the shortest route between to cities.\n" \ "0. Exit the program" while True: print s i = raw_input("Enter the number.") i = int(i) if i == 1: cityList = QueryData.citylist() print "Here are the lists!" for city in cityList: print city elif i == 2: city_name = raw_input("Enter the city name.") valid = QueryData.getcitydata(city_name) if valid == False : print "No such city." elif i == 3: QueryData.getstat() elif i == 4: city_code = raw_input("Enter the city code.") valid = QueryData.getroute(city_code) if valid == False : print "No such code." elif i == 5: EditRoute.editnetwork() elif i == 6: EditRoute.addnetwork() elif i == 7: SaveFile.saveFile() elif i == 8: RouteInfo.routeinfo() elif i == 9: MapParser.newMapParser() elif i == 10: self.routeInfo.shortestroute() elif i == 0: print "See ya!" break else: print "Invalid Number!"
def addnetwork(): while(True): cityList = QueryData.citylist() s = "Type the number for corresponding options:\n" \ "1. Remove a city\n" \ "2. Remove a route\n" \ "3. Add a city\n" \ "4. Add a route\n" \ "0. Quit" print s i = raw_input("Enter the number.") i = int(i) if i == 1: isValid = False while(not isValid): print "Type a valid city to remove, or type 0 or exit to quit" user_city = raw_input() isValid = user_city in cityList if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0": return removecity(user_city) elif i == 2: isValid = False while(not isValid): print "Type the first valid city of route." user_city = raw_input() isValid = user_city in cityList if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0": return isValid = False while(not isValid): city2 = raw_input("Type the second valid city of route.") isValid = city2 in cityList removeroute(user_city, city2) elif i == 3: newcode = raw_input("Type the new code of city.") newname = raw_input("Type the new name of city.") newcountry = raw_input("Type the new country of city.") newcont = raw_input("Type the new continent of city.") newzone = raw_input("Type the new timezone of city.") newcoord1 = raw_input("Type the Latitude of city.") newcoord2 = raw_input("Type the longitude of city.") newpop = raw_input("Type the population of city.") newdist = raw_input("Type the distance of city.") newreg = raw_input("Type the region of city.") newpop = int(newpop) newdist = int(newdist) newreg = int(newreg) if newpop > 0 and newdist > 0 and newreg > 0: addcity(newcode, newname, newcountry, newcont, newzone, newcoord1, newcoord2, newpop, newreg) else: print "Invalid info on population/distance/region." elif i == 4: isValid = False while not isValid: print "Type the first valid city of new route, or type exit to quit." city1 = raw_input() isValid = city1 in cityList if city1 == "exit" or city1 == "quit" or city1 == "q" or city1 == "0": return isValid = False while not isValid: city2 = raw_input("Type the second valid city of route.") isValid = city2 in cityList if city2 == "q" or city2 == "exit": return newdist = raw_input("Type the distance of city.") newdist = int(newdist) newstart = QueryData.nametocode(city1) newend = QueryData.codetoname(city2) if newdist > 0: addroute(newstart, newend, newdist) else: print "Invalid distance." elif i == 0: break else: print "Invalid Number!"
def findW(plant_num, newTemp, newLight): dataArray = QueryData.collectData(plant_num) waterVals = dataArray[0] colorVals = dataArray[1] tempVals = dataArray[2] lightVals = dataArray[3] correlations = [] correlations.append(regression(waterVals, colorVals)) correlations.append(regression(tempVals, colorVals)) correlations.append(regression(lightVals, colorVals)) mseArray = [] wArray = [] # Use correlations to find predicted value and calculate loss accordingly for x in range(0, len(waterVals)): w = random.randint(0, 100) w = float(w) / 10.0 totalArr = [] wArray.append(w) for x in range(int(len(colorVals))): total = correlations[0] * waterVals[x] + correlations[ 1] * tempVals[x] + correlations[2] * lightVals[x] totalArr.append(int(w * total)) mseArray.append(round(getMSE(totalArr, colorVals) / 10, 2)) # Utilize quadric regression to plot MSE values x = Symbol('x') A, B, C = np.polyfit(wArray, mseArray, 2) y = A * x**2 + B * x + C yprime = str(y.diff(x)) arr = yprime.split(' + ') if len(arr) == 1: arr = yprime.split(' - ') # Parse and convert output to find ideal weight based on loss calculation stra = str(arr[1]) strb = str(arr[0][0:-2]) converta = float(stra[0:10]) convertb = float(strb[0:10]) predictedW = float(converta) / (float(convertb)) idealCol = GetIdealColor.idealColor() # Use weights to find theoretical predicted irrigation volume temporary = float(idealCol) / predictedW temporary -= (correlations[1] * newTemp + correlations[2] * newLight) theoreticalX = temporary / correlations[0] # Remove extreme irrigation volume changes if theoreticalX < 0: theoreticalX *= -1 healthy = True if theoreticalX > 30: while theoreticalX > 7: theoreticalX -= 1.1837513 healthy = False if theoreticalX < 2: while theoreticalX < 5: theoreticalX *= 1.23754 healthy = False # Return theoretical irrigation volume and crop health status return theoreticalX, healthy
def addnetwork(): while (True): cityList = QueryData.citylist() s = "Type the number for corresponding options:\n" \ "1. Remove a city\n" \ "2. Remove a route\n" \ "3. Add a city\n" \ "4. Add a route\n" \ "0. Quit" print s i = raw_input("Enter the number.") i = int(i) if i == 1: isValid = False while (not isValid): print "Type a valid city to remove, or type 0 or exit to quit" user_city = raw_input() isValid = user_city in cityList if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0": return removecity(user_city) elif i == 2: isValid = False while (not isValid): print "Type the first valid city of route." user_city = raw_input() isValid = user_city in cityList if user_city == "exit" or user_city == "quit" or user_city == "q" or user_city == "0": return isValid = False while (not isValid): city2 = raw_input("Type the second valid city of route.") isValid = city2 in cityList removeroute(user_city, city2) elif i == 3: newcode = raw_input("Type the new code of city.") newname = raw_input("Type the new name of city.") newcountry = raw_input("Type the new country of city.") newcont = raw_input("Type the new continent of city.") newzone = raw_input("Type the new timezone of city.") newcoord1 = raw_input("Type the Latitude of city.") newcoord2 = raw_input("Type the longitude of city.") newpop = raw_input("Type the population of city.") newdist = raw_input("Type the distance of city.") newreg = raw_input("Type the region of city.") newpop = int(newpop) newdist = int(newdist) newreg = int(newreg) if newpop > 0 and newdist > 0 and newreg > 0: addcity(newcode, newname, newcountry, newcont, newzone, newcoord1, newcoord2, newpop, newreg) else: print "Invalid info on population/distance/region." elif i == 4: isValid = False while not isValid: print "Type the first valid city of new route, or type exit to quit." city1 = raw_input() isValid = city1 in cityList if city1 == "exit" or city1 == "quit" or city1 == "q" or city1 == "0": return isValid = False while not isValid: city2 = raw_input("Type the second valid city of route.") isValid = city2 in cityList if city2 == "q" or city2 == "exit": return newdist = raw_input("Type the distance of city.") newdist = int(newdist) newstart = QueryData.nametocode(city1) newend = QueryData.codetoname(city2) if newdist > 0: addroute(newstart, newend, newdist) else: print "Invalid distance." elif i == 0: break else: print "Invalid Number!"