def get_exist_company_and_security():
exist_company_map = {}
exist_security_map = {}
company_sql = """select company_code, unique_code, name_origin, company_short_name, security_code, country_code_listed, ipo_date, info_disclosure_id, download_link, market_type, exchange_market_code,
name_en, opd_sector_code, opd_industry_code, gics_sector_code, gics_industry_group_code, status, delist_date, website_url from company"""
result = dbtools.query_common(company_sql)
for r in result:
company = Company(r[0], r[1], r[3], r[5], r[10], r[4])
company.name_en = r[11]
company.opd_sector_code = r[12]
company.opd_industry_code = r[13]
company.gics_sector_code = r[14]
company.gics_industry_group_code = r[15]
# company.status = r[16]
# company.delist_date = r[17]
company.website_url = r[18]
exist_company_map[company.company_code] = company
# 获取证券表信息
security_sql = "select company_code, unique_code, name_origin, market_type, security_code, security_type, country_code_listed, exchange_market_code, listing_date, STATUS, delisting_date from security_hkg where country_code='HKG'"
result = dbtools.query_common(security_sql)
for s in result:
security = Security(s[0], s[1], s[2], s[4], s[5], s[6], s[7])
security.market_type = s[3]
security.listing_date = s[8]
security.delist_date = s[10]
security.status = s[9]
exist_security_map[security.company_code] = security
return exist_company_map, exist_security_map
def get_exist_company_with_security():
# 获取公司表信息
exist_company_map = {}
# company_code, unique_code, security_code, company_name, company_short_name, country_code, info_disclosure_id, download_link, exchange_market_code
sql = """SELECT company_code, unique_code, '', name_origin, company_short_name, country_code_listed, info_disclosure_id, download_link, exchange_market_code,
name_en, opd_sector_code, opd_industry_code, gics_sector_code, gics_industry_group_code, STATUS, website_url FROM company WHERE country_code_listed='CHN'"""
result = dbtools.query_common(sql)
for r in result:
company = Company(r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8])
if company.company_code == 'CHN75976':
print('d')
pass
company.name_en = r[9]
company.opd_sector_code = r[10]
company.opd_industry_code = r[11]
company.gics_sector_code = r[12]
company.gics_industry_group_code = r[13]
company.status = r[14]
company.website_url = r[15]
exist_company_map[company.unique_code] = company
# 获取证券表信息
security_sql = "SELECT unique_code, company_code, name_origin, market_type, security_code, security_type, equity_type, country_code_listed, exchange_market_code, ipo_date, STATUS, delist_date FROM security WHERE country_code_listed = 'CHN' "
security_result = dbtools.query_common(security_sql)
for sr in security_result:
s = Security(sr[0], sr[1], sr[2], sr[3], sr[4], sr[5], sr[6], sr[7], sr[8], sr[9], sr[10], sr[11])
if s.unique_code == 'CHNSSE603489':
print('d')
exist_company_map[s.unique_code].security_box.append(s)
return exist_company_map
def insert_new_to_db(new_company_box, new_security_box):
if len(new_company_box) == 0:
return True
company_values_box, security_values_box = gen_insert_value_box(new_company_box, new_security_box)
company_insert_sql = "INSERT INTO company_hkg(company_code, unique_code, name_en, company_short_name, security_code, original_industry_describe, opd_sector_code, opd_industry_code, gics_sector_code, gics_industry_group_code, country_code_listed, country_code_origin, isin,exchange_market_code, website_url,registered_address, office_address, gmt_create, user_create) VALUES {values}"
company_insert_sql = company_insert_sql.format(values=', '.join(company_values_box))
print(company_insert_sql)
dbtools.query_common(company_insert_sql)
def temp():
sql = "SELECT source_report_id, statusid FROM fs_process_index WHERE country_code = 'CHN'"
result = dbtools.query_opd_fdss(sql)
id_map = {}
for r in result:
if r[0] not in id_map:
id_map[r[0]] = [r[1]]
else:
id_map[r[0]].append(r[1])
for id in id_map:
update_sql = "update financial_origin_began set error_code = {} where report_id='{}'".format(max(id_map[id]), id)
print(update_sql)
dbtools.query_common(update_sql)
def insert_new_to_db(new_company_box, new_security_box):
if len(new_company_box)==0:
return True
company_values_box, security_values_box = gen_insert_value_box(new_company_box, new_security_box)
company_insert_sql = "INSERT INTO company(company_code, unique_code, name_origin, name_en, company_short_name, original_industry_describe, opd_sector_code, opd_industry_code, gics_sector_code, gics_industry_group_code, csrc_code, country_code_listed, country_code_origin, established_date, info_disclosure_id, isin, exchange_market_code, STATUS, website_url, download_link, gmt_create, user_create) VALUES {values}"
company_insert_sql = company_insert_sql.format(values=', '.join(company_values_box))
print(company_insert_sql)
dbtools.query_common(company_insert_sql)
security_insert_sql = "INSERT INTO security(unique_code, company_code, name_origin, market_type, security_code, security_type, equity_type, country_code_listed, exchange_market_code, ipo_date, status, delist_date) VALUES {values}"
security_insert_sql = security_insert_sql.format(values=', '.join(security_values_box))
print(security_insert_sql)
dbtools.query_common(security_insert_sql)
def get_unlisted_status():
delist_info = {}
delist_list_sql = "SELECT security_code, status, delisting_date FROM `company_listing_status_hkg`"
result = dbtools.query_common(delist_list_sql)
for r in result:
delist_info[r[0]] = [int(r[1]), r[2]]
return delist_info
def get_stander_industry_map():
stander_industry_map = {}
map_sql = "SELECT sector_industry_key,opd_sector_code,opd_industry_code,gics_sector_code,gics_industry_group_code FROM opd_sector_relationship WHERE country_code = 'hkg'"
result = dbtools.query_common(map_sql)
for r in result:
original_describe = clear_label(r[0])
stander_industry_map[original_describe] = [r[1], r[2], r[3], r[4]]
return stander_industry_map
def get_security_info():
splider_security_box = []
security_info_sql = "select company_code, unique_code, company_short_name, country_code, exchange_market_code, security_code, security_type from company_base_info_hkg where country_code='hkg' and security_type in ('Equity', 'REITs')"
security_info_list = dbtools.query_common(security_info_sql)
for s in security_info_list:
splider_security_box.append(Security(s[0], s[1], s[2], s[5], s[6], s[3], s[4]))
return splider_security_box
def get_base_info():
splider_company_box = []
base_info_sql = "SELECT company_code, unique_code, company_short_name, country_code, exchange_market_code, security_code, security_type from company_base_info_hkg where country_code='hkg' and security_type in ('Equity', 'REITs')"
base_info_list = dbtools.query_common(base_info_sql)
for b in base_info_list:
splider_company_box.append(Company(b[0], b[1], b[2], b[3], b[4], b[5], b[6]))
return splider_company_box
def get_base_info():
splider_company_box = []
exist_company = []
base_info_sql = "SELECT company_code, unique_code, market_company_code, company_name, company_short_name, country_code, info_disclosure_id, download_link, exchange_market_code, market_type FROM company_base_info WHERE country_code='chn'"
base_info_list = dbtools.query_common(base_info_sql)
for b in base_info_list:
c = Company(b[0], b[1], b[2], b[3], b[4], b[5], b[6], b[7], b[8], b[9])
if c.unique_code not in exist_company:
splider_company_box.append(c)
exist_company.append(c.unique_code)
return splider_company_box
def get_db_raw_info():
raw_info_map = {}
aim_label_box = []
for aim_label_list in marked_info_label.values():
aim_label_box += aim_label_list
condition = " where display_label in ('{}')".format("','".join(aim_label_box))
raw_info_sql = 'select unique_code, display_label, information, gmt_create from company_raw_info' + condition
raw_info = dbtools.query_common(raw_info_sql)
for info in raw_info:
unique_code = info[0]
label = info[1]
value = info[2]
create_time = info[3]
if unique_code not in raw_info_map:
raw_info_map[unique_code] = {}
if label not in raw_info_map[unique_code]:
raw_info_map[unique_code][label] = {}
raw_info_map[unique_code][label][create_time] = value
return raw_info_map
def modify_exist_info(modify_company_box, modify_security_box):
for modify_c in modify_company_box:
update_sql = """UPDATE company SET
name_origin="{name_origin}",
name_en="{name_en}",
company_short_name="{company_short_name}",
original_industry_describe="{original_industry_describe}",
opd_sector_code="{opd_sector_code}",
opd_industry_code="{opd_industry_code}",
gics_sector_code="{gics_sector_code}",
gics_industry_group_code="{gics_industry_group_code}",
csrc_code="{csrc_code}",
established_date="{established_date}",
status={status},
website_url="{website_url}",
download_link="{download_link}",
gmt_update=now(),
user_update="{user_update}"
WHERE unique_code="{unique_code}"
""".format(
name_origin=modify_c.company_name,
name_en=modify_c.name_en,
company_short_name=modify_c.company_short_name,
original_industry_describe=modify_c.original_industry_describe,
opd_sector_code=modify_c.opd_sector_code,
opd_industry_code=modify_c.opd_industry_code,
gics_sector_code=modify_c.gics_sector_code,
gics_industry_group_code=modify_c.gics_industry_group_code,
csrc_code=modify_c.csrc_code,
established_date=modify_c.established_date,
status=modify_c.status,
delist_date=modify_c.delist_date,
website_url=modify_c.website_url,
download_link=modify_c.download_link,
gmt_update = 'now()',
user_update='program_auto',
unique_code=modify_c.unique_code
)
print(update_sql)
dbtools.query_common(update_sql)
for modify_s in modify_security_box:
update_sql = """UPDATE security SET
delist_date="{delist_date}",
ipo_date="{ipo_date}",
equity_type="{equity_type}",
name_origin="{name_origin}",
status="{status}"
gmt_update=now(),
user_update="{user_update}"
WHERE security_code="{security_code}
""".format(
delist_date=modify_s.company_name,
ipo_date=modify_s.name_en,
equity_type=modify_s.company_short_name,
name_origin=modify_s.original_industry_describe,
status=modify_s.opd_sector_code,
gmt_update='now()',
user_update='program_auto',
security_code=modify_s.security_code
)
print(update_sql)
dbtools.query_common(update_sql)
return True