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)