def insertsql(data): for dt in data: sql = "insert into population(shopid,radius,date,num1,num2,num3) values(%s,%s,'%s',%s,%s,%s)" % ( dt[0], dt[1], dt[2], dt[3], dt[4], dt[5]) if "None" in sql: sql = sql.replace("None", "null") mymysqlclass(myconfig).dochange(sql)
def save(self,table = "weather_hour"): columnsList = list() valueList = list() count = 0 for column, value in self.__dict__.items(): columnsList.append(column) valueList.append(value) count += 1 # sql = "select 1 from {0} where time='{1}' and district='{2}'".format(table, self.time, self.district) # with mymysqlclass(myconfig) as mp: # result = mp.select(sql) # if len(result)== 0: sql = "replace into {0}({1}) values({2})".format(table, ",".join(columnsList), ",".join(['%s'] * count)) mymysqlclass(myconfig).dochange(sql,[valueList,])
def dosql_surround(self): sql = "select cfrom,tend from tmp_corlike" shops = mymysqlclass(myconfig).select(sql) types = mymysqlclass(myconfig).select( "select distinct type1,type2 from surround where date='2018-06-24'" ) for cf, te in shops: for standard, type in types: sql = "select count(1) from surround where shopid ={0} and date='2018-06-24' and type1='{1}' and type2='{2}' and distance<=500".format( te, standard, type) tmp = mymysqlclass(myconfig).select(sql) val = tmp[0][0] sql = "insert into pre_surround(shopid,date,radius,type1,type2,num) values(%s,%s,%s,%s,%s,%s)" print(sql) mymysqlclass(myconfig).dochange( sql, (cf, "2018-06-24", 0.5, standard, type, val))
def tmp(self): sql = "select id from population_hour" data = mymysqlclass(myconfig).select(sql) for id in data: ids = mymysqlclass(myconfig).select( "select id, shopid, date, radius, posi, num1, num2, num3, createtime from population_hour where id = %s" % (id)) id, shopid, date, radius, posi, num1, num2, num3, createtime = ids[ 0] try: sql = "insert into population_hour_bak(id,shopid,date,radius,posi,num1,num2,num3,createtime) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)" mymysqlclass(myconfig).dochange( sql, (id, shopid, date, radius, posi, num1, num2, num3, createtime)) except: print(ids)
def GetGeo(): ''' 获取shopid,location映射关系 :return: ''' sql = "select shopid,location from today where location is not null" with mymysqlclass(myconfig) as my: result = my.select(sql) return result
def dosql_people(self): sql = "select distinct cfrom from corlike" shops = mymysqlclass(myconfig).select(sql) for shop in shops: sql = "select tend,cor/b.corsum from corlike,(select sum(cor) as corsum from corlike where cfrom=%s) as b where cfrom=%s" % ( shop[0], shop[0]) data = mymysqlclass(myconfig).select(sql) for dt in [dateutilsclass.getDay(n) for n in range(3, 4)]: dt = str(dt) val = 0 for radius in [0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0]: for standard, type in [("性别", "男"), ("性别", "女"), ("年龄段", "25-34"), ("年龄段", "19-24"), ("年龄段", "35-44"), ("年龄段", "45-54"), ("年龄段", ">=55"), ("年龄段", "0-18"), ("职业", "服务人员"), ("职业", "公司职员"), ("职业", "学生"), ("职业", "医疗人员"), ("职业", "公务员"), ("职业", "教职工"), ("职业", "家庭主妇"), ("职业", "出租车司机"), ("职业", "科研人员"), ("职业", "货车司机"), ("职业", "快递员"), ("是否有小孩", "是"), ("是否有小孩", "否"), ("资产等级", "中产"), ("资产等级", "工薪一族"), ("资产等级", "富豪"), ("资产等级", "超级富豪"), ("消费能力", "中等"), ("消费能力", "较弱"), ("消费能力", "较强"), ("消费能力", "弱"), ("消费能力", "强")]: for sid, rate in data: sql = "select {0}*val from people where shopid ={1} and date='{2}' and radius={3} and standard='{4}' and type='{5}'".format( rate, sid, dt[:10], radius, standard, type) tmp = mymysqlclass(myconfig).select(sql) if len(tmp) == 1: val += tmp[0][0] sql = "insert into pre_people(shopid,date,standard,type,val,radius) values(%s,%s,%s,%s,%s,%s)" print(sql) print(shop, dt[:10], standard, type, val, radius) mymysqlclass(myconfig).dochange( sql, (shop[0], dt[:10], standard, type, val, radius))
def dosql(self): sql = "select distinct cfrom from corlike" shops = mymysqlclass(myconfig).select(sql) for shop in shops: sql = "select tend,cor/b.corsum from corlike,(select sum(cor) as corsum from corlike where cfrom=%s) as b where cfrom=%s" % ( shop[0], shop[0]) data = mymysqlclass(myconfig).select(sql) for dt in [dateutilsclass.getDay(n) for n in range(3, 4)]: dt = str(dt) num1, num2, num3 = [0] * 3 for radius in [0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0]: for sid, rate in data: sql = "select {0}*num1,{0}*num2,{0}*num3 from population where shopid ={1} and date='{2}' and radius={3}".format( rate, sid, dt[:10], radius) tmp = mymysqlclass(myconfig).select(sql) if len(tmp) == 1: num1 += tmp[0][0] num2 += tmp[0][1] num3 += tmp[0][2] sql = "insert into population_pre(shopid,date,num1,num2,num3,radius) values(%s,%s,%s,%s,%s,%s)" print(sql) print(shop, dt[:10], num1, num2, num3, radius) mymysqlclass(myconfig).dochange( sql, (shop[0], dt[:10], num1, num2, num3, radius))
def changsha(): with mymysqlclass(myconfig) as my: shops = my.select( 'select b.store_code,b.store_address from dw.dim_stores_info_new b where b.city="长沙"' ) for shop, store_address in shops: if "区" in store_address: a1 = store_address.split("区")[0] if "市" in a1: a1 = a1.split("市")[1] a1 = a1.replace("长沙", "") sql = "update today set district='{0}' where shopid={1}".format( a1, shop) print(sql)
def save(self, table="weather_hour_f"): columnsList = list() valueList = list() count = 0 for column, value in self.__dict__.items(): columnsList.append(column) valueList.append(value) count += 1 # sql = "select 1 from {0} where time='{1}' and area ='{2}' and ifpredict={3}".format(table, self.time, self.area,self.ifpredict) sql = "insert into {0}({1}) values({2})".format( table, ",".join(columnsList), ",".join(['%s'] * count)) try: with mymysqlclass(myconfig) as my: my.dochange(sql, valueList) except: print("已经存在...")
def wuhan(): with mymysqlclass(myconfig) as my: shops = my.select("select shopid,location from today WHERE city='武汉'") for shopid, location in shops: lng, lat = location.split(",") url = "https://restapi.amap.com/v3/geocode/regeo?key=8325164e247e15eea68b59e89200988b&s=rsv3&location={0},{1}&radius=2800&callback=&platform=JS&logversion=2.0&sdkversion=1.3&appname=https%3A%2F%2Flbs.amap.com%2Fconsole%2Fshow%2Fpicker&csid=350D4CAC-4C25-4904-A424-8257380EAB28".format( lng, lat) resp = requests.get(url).json() district = resp.get("regeocode").get("formatted_address").split( "武汉市")[1].split("区")[0] sql = "update today set district='{0}' where shopid={1}".format( district, shopid) print(sql)
def save(self): pass # 114.343466,30.554921 targets = GetGeo() tm = str(datetime.now())[:16] + ":00" print(tm) url = "http://forecast.weather.com.cn/town/api/v1/sk?lat={0}&lng={1}" results = [] for shop, location in targets: lng, lat = location.split(",") url0 = url.format(lat, lng) resp = requests.get(url0).json() data = [ shop, location, tm, resp.get("weather"), resp.get("weathercode"), resp.get("temp"), resp.get("humidity"), resp.get("WD"), resp.get("WS") ] results.append(tuple(data)) insertsql = "insert into weather_hour_c(shopid,location,`time`,wstatus,wcode,temp,humid,wd,wr) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)" with mymysqlclass(myconfig) as my: my.insertmany(insertsql, results)
data = myhiveclass(config).select(sql) return data[0][0] if __name__=="__main__": # a =createModel() # a.checkRegDegree() # a.predict() # fitChangsha().staticForest() a = fitChangsha() trains = list() Y_trains = list() sql = "select shopid from today where city='武汉' and shopid not in (117045,117202,117030,117035,117037,117122,117235,117206,117180,117287, 117373,117375,117368,117372,117360)" shops = mymysqlclass(myconfig).select(sql) sql = "select store_code from dw.dim_stores_info where city in ('南宁','长沙')" other_shops = myhiveclass(config).select(sql) days = list(map(dateutilsclass.getDay,reversed(list(range(3,30))))) # max_steps = len(shops)*len(list(days)) whdata = dict() whdates = dict() otdata = dict() otdates = dict() # for shop,date in product(shops,list(days)): # shop = shop[0] # date = str(date)[:10] # if str(shop)+","+str(date)[:10] in whdata: # continue # try: # val = a.getTrain(shop,date)