def sele_app_id():
conn = hu_utils.open_local_db("app_info")
ress = hu_utils.select_one(conn)
ids = []
for res in ress:
ids.append(res[0])
return ids
def clean_salary():
salary_info_z = []
sql = "select job_id,salary from zhilian_job where salary_code is null"
conn = hu_utils.open_line_db()
salary_infos = hu_utils.select_one(conn, sql)
print(len(salary_infos))
for salary_info in salary_infos:
# print(salary_info)
if salary_info["salary"] == "薪资面议" or salary_info["salary"] == "校招":
salary_info["salary_code"] = -1
salary_info_z.append(salary_info)
continue
if salary_info["salary"] == "1K以下":
salary_info["salary_code"] = 105
salary_info_z.append(salary_info)
continue
salary = salary_info["salary"].replace("K", "").split("-")
salary_mid = (float(salary[0]) + float(salary[1])) // 2
# print(salary_mid)
if salary_info["salary"] == "1K以下" or salary_mid <= 5:
salary_info["salary_code"] = 105
salary_info_z.append(salary_info)
continue
if salary_mid <= 10:
salary_info["salary_code"] = 510
salary_info_z.append(salary_info)
continue
if salary_mid <= 15:
salary_info["salary_code"] = 1015
salary_info_z.append(salary_info)
continue
if salary_mid <= 20:
salary_info["salary_code"] = 1520
salary_info_z.append(salary_info)
continue
if salary_mid <= 25:
salary_info["salary_code"] = 2025
salary_info_z.append(salary_info)
continue
if salary_mid <= 30:
salary_info["salary_code"] = 2530
salary_info_z.append(salary_info)
continue
if salary_mid <= 35:
salary_info["salary_code"] = 3035
salary_info_z.append(salary_info)
continue
if salary_mid <= 40:
salary_info["salary_code"] = 3540
salary_info_z.append(salary_info)
continue
else:
salary_info["salary_code"] = 4099
salary_info_z.append(salary_info)
continue
# print(salary_info["salary_code"])
# print("--------------")
# pprint(salary_info_z)
conn = hu_utils.open_line_db()
hu_utils.insert_update_many(conn, salary_info_z, "zhilian_job")
def clean_jobname():
job_name_infos = []
sql = "select job_id,jobname from zhilian_job"
conn = hu_utils.open_line_db()
jobname_infos = hu_utils.select_one(conn, sql)
print(jobname_infos)
for jobname_info in jobname_infos:
jobname_info["jobname"] = jobname_info["jobname"].lower()
job_name_infos.append(jobname_info)
conn = hu_utils.open_line_db()
hu_utils.insert_update_many(conn, job_name_infos, "zhilian_job")
def empltype_chart(self):
sql = """
SELECT
SUM(
CASE
WHEN a.emplType = '全职'
THEN 1
ELSE 0
END
) AS 全职,
SUM(
CASE
WHEN a.emplType = '校园'
THEN 1
ELSE 0
END
) AS 校园,
SUM(
CASE
WHEN a.emplType = '实习'
THEN 1
ELSE 0
END
) AS 实习,SUM(
CASE
WHEN a.emplType = '兼职/临时'
THEN 1
ELSE 0
END
) AS 兼职或临时
FROM
(SELECT
id,
emplType
FROM
zhilian_job
WHERE jobname LIKE '%{}%') AS a;
""".format(self.key)
conn = hu_utils.open_line_db()
job_info = hu_utils.select_one(conn, sql)
# print(len(job_info))
x = job_info[0].keys()
y = job_info[0].values()
print(x)
print(y)
plt.bar(x, y)
# plt.title("{}职位工作性质".format(self.key))
plt.savefig("{}职位工作性质".format(self.key))
plt.show()
def get_etid():
conn = hu_utils.open_local_db()
et_names = hu_utils.select_one(conn)
print "获取的企业数", len(et_names)
return et_names
def education_chart(self):
sql = """
SELECT
SUM(
CASE
WHEN a.edu_level_code = '-1'
THEN 1
ELSE 0
END
) AS 不限,
SUM(
CASE
WHEN a.edu_level_code = '1'
THEN 1
ELSE 0
END
) AS 博士,
SUM(
CASE
WHEN a.edu_level_code = '3'
THEN 1
ELSE 0
END
) AS 硕士,
SUM(
CASE
WHEN a.edu_level_code = '4'
THEN 1
ELSE 0
END
) AS 本科,
SUM(
CASE
WHEN a.edu_level_code = '5'
THEN 1
ELSE 0
END
) AS 大专,
SUM(
CASE
WHEN a.edu_level_code >5
THEN 1
ELSE 0
END
) AS 大专以下
FROM
(SELECT
id,
edu_level_code
FROM
zhilian_job
WHERE jobname LIKE '%{}%') AS a;
""".format(self.key)
conn = hu_utils.open_line_db()
job_info = hu_utils.select_one(conn, sql)
# print(len(job_info))
x = job_info[0].keys()
y = job_info[0].values()
print(x)
print(y)
plt.bar(x, y)
# plt.title("{}职位学历要求".format(self.key))
plt.savefig("{}职位学历要求".format(self.key))
plt.show()
def city_chart(self):
sql = """
SELECT
SUM(
CASE
WHEN a.city LIKE '%南京%'
THEN 1
ELSE 0
END
) AS 南京,
SUM(
CASE
WHEN a.city LIKE '%上海%'
THEN 1
ELSE 0
END
) AS 上海,
SUM(
CASE
WHEN a.city LIKE '%北京%'
THEN 1
ELSE 0
END
) AS 北京,
SUM(
CASE
WHEN a.city LIKE '%广州%'
THEN 1
ELSE 0
END
) AS 广州,
SUM(
CASE
WHEN a.city LIKE '%深圳%'
THEN 1
ELSE 0
END
) AS 深圳,
SUM(
CASE
WHEN a.city LIKE '%杭州%'
THEN 1
ELSE 0
END
) AS 杭州,
SUM(
CASE
WHEN a.city LIKE '%合肥%'
THEN 1
ELSE 0
END
) AS 合肥,
SUM(
CASE
WHEN a.city LIKE '%成都%'
THEN 1
ELSE 0
END
) AS 成都,
SUM(
CASE
WHEN a.city LIKE '%武汉%'
THEN 1
ELSE 0
END
) AS 武汉,
SUM(
CASE
WHEN a.city LIKE '%重庆%'
THEN 1
ELSE 0
END
) AS 重庆,
SUM(
CASE
WHEN a.city LIKE '%西安%'
THEN 1
ELSE 0
END
) AS 西安,
SUM(
CASE
WHEN a.city LIKE '%天津%'
THEN 1
ELSE 0
END
) AS 天津
FROM
(SELECT
id,
city,et_name
FROM
zhilian_job
WHERE jobname LIKE '%{}%') AS a;
""".format(self.key)
conn = hu_utils.open_line_db()
job_info = hu_utils.select_one(conn, sql)
# print(len(job_info))
x = job_info[0].keys()
y = job_info[0].values()
print(x)
print(y)
plt.bar(x, y)
# plt.title("{}职位地区分布".format(self.key))
plt.savefig("{}职位地区分布".format(self.key))
plt.show()
def workingExp(self):
sql = """
SELECT
SUM(
CASE
WHEN a.workingExp_code = '-1'
THEN 1
ELSE 0
END
) AS 不限,
SUM(
CASE
WHEN a.workingExp_code = '0'
THEN 1
ELSE 0
END
) AS 无经验,
SUM(
CASE
WHEN a.workingExp_code = '1'
THEN 1
ELSE 0
END
) AS 1年以下,
SUM(
CASE
WHEN a.workingExp_code = '103'
THEN 1
ELSE 0
END
) AS 1至3年,
SUM(
CASE
WHEN a.workingExp_code = '305'
THEN 1
ELSE 0
END
) AS 3至5年,
SUM(
CASE
WHEN a.workingExp_code = '510'
THEN 1
ELSE 0
END
) AS 5至10年,
SUM(
CASE
WHEN a.workingExp_code = '1099'
THEN 1
ELSE 0
END
) AS 10年以上
FROM
(SELECT
id,
workingExp_code
FROM
zhilian_job
WHERE jobname LIKE '%{}%') AS a;
""".format(self.key)
conn = hu_utils.open_line_db()
job_info = hu_utils.select_one(conn, sql)
# print(len(job_info))
x = job_info[0].keys()
y = job_info[0].values()
print(x)
print(y)
plt.bar(x, y)
# plt.title("{}职位经验要求".format(self.key))
plt.savefig("{}职位经验要求".format(self.key))
plt.show()
def salarly_chart(self):
sql = """
SELECT
SUM(
CASE
WHEN a.salary_code = '-1'
THEN 1
ELSE 0
END
) AS 薪资面议,
SUM(
CASE
WHEN a.salary_code = '105'
THEN 1
ELSE 0
END
) AS 5k以下,
SUM(
CASE
WHEN a.salary_code = '510'
THEN 1
ELSE 0
END
) AS 5k到10k,
SUM(
CASE
WHEN a.salary_code = '1015'
THEN 1
ELSE 0
END
) AS 10k到15k,
SUM(
CASE
WHEN a.salary_code = '1520'
THEN 1
ELSE 0
END
) AS 15k到20k,
SUM(
CASE
WHEN a.salary_code = '2025'
THEN 1
ELSE 0
END
) AS 20k到25k,
SUM(
CASE
WHEN a.salary_code = '2530'
THEN 1
ELSE 0
END
) AS 25k到30k,
SUM(
CASE
WHEN a.salary_code = '3035'
THEN 1
ELSE 0
END
) AS 30k到35k,
SUM(
CASE
WHEN a.salary_code = '3540'
THEN 1
ELSE 0
END
) AS 35k到40k,
SUM(
CASE
WHEN a.salary_code = '4099'
THEN 1
ELSE 0
END
) AS 40k以上
FROM
(SELECT
id,
salary_code
FROM
zhilian_job
WHERE jobname LIKE '%{}%') AS a;
""".format(self.key)
conn = hu_utils.open_line_db()
job_info = hu_utils.select_one(conn, sql)
# print(len(job_info))
x = job_info[0].keys()
y = job_info[0].values()
print(x)
print(y)
plt.bar(x, y)
plt.xticks(rotation=50)
# plt.title("{}职位薪资待遇".format(self.key))
plt.savefig("{}职位薪资待遇".format(self.key))
plt.show()