def check_data():
'''
数据检查
:return:
'''
data_df = pd.DataFrame()
info_df = pd.DataFrame()
table_info = conf.qiye_info_table
table_data = conf.qiye_data_table
need_url = []
sql_info = '''SELECT DISTINCT url FROM {} '''.format(table_info)
sql_data = '''SELECT DISTINCT url FROM {} where url is not null '''.format(
table_data)
res_info = dbhandler.get_date(sql_info, table_info)
res_data = dbhandler.get_date(sql_data, table_data)
if res_info:
info_df = pd.DataFrame(list(res_info), columns=['url'])
info_df['url'] = info_df['url'].apply(str)
if res_data:
data_df = pd.DataFrame(list(res_data), columns=['url'])
data_df['url'] = data_df['url'].apply(str)
print(len(list(data_df['url'])))
for url in list(data_df['url']):
if url not in list(info_df['url']):
need_url.append(url)
print(need_url)
print(len(need_url))
sql = '''update {} set status=0 where url in {}'''.format(
table_data, tuple(need_url))
up_bo = dbhandler.exec_sql(sql, table_data)
print(up_bo)
def need_parsing_url():
table_name = conf.qiye_data_table
sql = 'select url from {} where status=0 limit 50 '.format(table_name)
res = dbhandler.get_date(sql, table_name)
if res:
data_df = pd.DataFrame(list(res), columns=['url'])
data_df['url'] = data_df['url'].apply(str)
return data_df
def get_data():
'''
@return:
'''
sql = '''SELECT url,book_name,book_author,book_isdn,score,com_num from douban_book;'''
res = dbhandler.get_date(sql, conf.douban_book_table)
pd.DataFrame(list(res),
columns=[
'url', 'book_name', 'book_author', 'book_isdn', 'score',
'com_num'
]).to_excel('./豆瓣书籍数据.xlsx', index=False)
def get_book_already():
'''
获取已经爬取过的数据url
@return:
'''
df = pd.DataFrame()
table_name = conf.douban_book_table
sql = '''SELECT DISTINCT url FROM {}'''.format(table_name)
res = dbhandler.get_date(sql, table_name)
if res:
df = pd.DataFrame(list(res), columns=['url'])
return df
def all_shop_phone(pw):
'''
获取全部店铺的url
@return:
'''
df = pd.DataFrame()
table_name = conf.dzdp_shop_table
sql = '''SELECT DISTINCT shop_name,url,city from {} where shop_type='{}' '''.format(table_name, pw)
res = dbhandler.get_date(sql, table_name)
if res:
df = pd.DataFrame(list(res), columns=['shop_name', 'url', 'city'])
return df
def get_all_shop():
'''
获取商铺列表中的所有商铺
@return:
'''
dataDf = pd.DataFrame()
tablaName = conf.dzdp_shop_table
sql = '''select DISTINCT shop_name,shop_id,url from {} '''.format(tablaName)
res = dbhandler.get_date(sql, tablaName)
if res:
dataDf = pd.DataFrame(list(res), columns=['shop_name', 'shop_id', 'url'])
dataDf['shop_id'] = dataDf['shop_id'].astype(str)
return dataDf
def get_cities():
'''
获取数据库中所有的城市
@return:
'''
city_list = []
table_name = conf.area_division_table
sql = '''select distinct city_name from {}'''.format(table_name)
res = dbhandler.get_date(sql, conf.area_division_table)
if res:
df = pd.DataFrame(list(res), columns=['city_name'])
city_list = df['city_name'].tolist()
return city_list
def al_prov_city(s_type, pw):
'''
获取数据库中已经计算过的城市
@return:
'''
df = pd.DataFrame()
table_name = conf.gaodemap_baidu_data_table
sql = '''SELECT DISTINCT coun_code,coun_name FROM {} where s_type={} and shop_type='{}' '''.format(
table_name, s_type, pw)
res = dbhandler.get_date(sql, table_name)
if res:
df = pd.DataFrame(list(res), columns=['coun_code', 'coun_name'])
return df
def get_pro_city(pro_name):
'''
获取省份的城市信息
@param pro_name: 省份名称
@return:
'''
dataDf = pd.DataFrame()
table_name = conf.area_division_table
sql = '''SELECT DISTINCT prov_name,city_name from {} WHERE prov_name='{}' '''.format(table_name, pro_name)
res = dbhandler.get_date(sql, table_name)
if res:
dataDf = pd.DataFrame(list(res), columns=['pro_name', 'city_name'])
dataDf['city_name'] = dataDf['city_name'].apply(lambda x: x.replace('市', ''))
return dataDf
def already_cityArea(city_name):
'''
获取该城市已经采集过数据的行政区
@param city_name: 城市名称
@return:
'''
city_area_df = pd.DataFrame()
tableName = conf.dzdp_shop_table
sql = '''select distinct city,region from {} where city ='{}' '''.format(tableName, city_name)
res = dbhandler.get_date(sql, tableName)
if res:
city_area_df = pd.DataFrame(list(res), columns=['city_name', 'area_name'])
return city_area_df
def already_shop_phone(pw):
'''
获取以及计算获取过手机号的店铺
@return:
'''
url_list = []
table_name = conf.dzdp_shop_phone_table
sql = '''
SELECT DISTINCT(url) from {} where shop_type='{}' '''.format(table_name, pw)
res = dbhandler.get_date(sql, table_name)
if res:
url_df = pd.DataFrame(list(res), columns=['url'])
url_list = url_df['url'].tolist()
return url_list
def get_needshop_info2():
'''
获取已经采集过的商铺手机号信息
@return:
'''
dataDf = pd.DataFrame()
tableName = conf.dzdp_shop_phone_table
sql = '''select DISTINCT a.shop_name,a.city,a.region,a.shop_id,a.url from {} a where a.shop_id not in (SELECT DISTINCT shop_id from {})'''.format(
conf.dzdp_shop_table, tableName)
print(sql)
res = dbhandler.get_date(sql, tableName)
if res:
dataDf = pd.DataFrame(list(res), columns=['shop_name', 'city', 'region', 'shop_id', 'url'])
dataDf['shop_id'] = dataDf['shop_id'].astype(str)
return dataDf
def get_need_city(pro_list):
'''
获取需要的城市
@return:
'''
df = pd.DataFrame()
table_name = conf.area_division_table
if 1 == len(pro_list):
sql = '''SELECT DISTINCT prov_code,prov_name,city_code,city_name,coun_code,coun_name FROM {}
where prov_name in {} order by prov_name'''.format(table_name, pro_list[0])
else:
sql = '''SELECT DISTINCT prov_code,prov_name,city_code,city_name,coun_code,coun_name FROM {}
where prov_name in {} order by prov_name'''.format(table_name, tuple(pro_list))
res = dbhandler.get_date(sql, table_name)
if res:
df = pd.DataFrame(list(res), columns=['prov_code', 'prov_name', 'city_code',
'city_name', 'coun_code', 'coun_name'])
return df