复制#-*- coding:UTF-8 -*- import pymysql import requests import json import re from bs4 import BeautifulSoup if __name__ == __main__: #打开数据库连接:host-连接主机地址,上市port-端口号,user-用户名,passwd-用户密码,db-数据库名,charset-编码 conn = pymysql.connect(host=127.0.0.1, port=3306, user=root, passwd=yourpasswd,db=financialdata,charset=utf8) #使用cursor()方法获取操作游标 cursor = conn.cursor() #主要财务指标 cwzb_dict = {EPS:基本每股收益,EPS_DILUTED:摊薄每股收益,GROSS_MARGIN:毛利率, CAPITAL_ADEQUACY:资本充足率,LOANS_DEPOSITS:贷款回报率,ROTA:总资产收益率, ROEQUITY:净资产收益率,CURRENT_RATIO:流动比率,QUICK_RATIO:速动比率, ROLOANS:存贷比,INVENTORY_TURNOVER:存货周转率,GENERAL_ADMIN_RATIO:管理费用比率, TOTAL_ASSET2TURNOVER:资产周转率,FINCOSTS_GROSSPROFIT:财务费用比率,TURNOVER_CASH:销售现金比率,YEAREND_DATE:报表日期} #利润表 lrb_dict = {TURNOVER:总营收,OPER_PROFIT:经营利润,PBT:除税前利润, NET_PROF:净利润,EPS:每股基本盈利,DPS:每股派息, INCOME_INTEREST:利息收益,INCOME_NETTRADING:交易收益,INCOME_NETFEE:费用收益,YEAREND_DATE:报表日期} #资产负债表 fzb_dict = { FIX_ASS:固定资产,CURR_ASS:流动资产,CURR_LIAB:流动负债, INVENTORY:存款,CASH:现金及银行存结,OTHER_ASS:其他资产, TOTAL_ASS:总资产,TOTAL_LIAB:总负债,EQUITY:股东权益, CASH_SHORTTERMFUND:库存现金及短期资金,DEPOSITS_FROM_CUSTOMER:客户存款, FINANCIALASSET_SALE:可供出售之证券,LOAN_TO_BANK:银行同业存款及贷款, DERIVATIVES_LIABILITIES:金融负债,DERIVATIVES_ASSET:金融资产,YEAREND_DATE:报表日期} #现金流表 llb_dict = { CF_NCF_OPERACT:经营活动产生的现金流,CF_INT_REC:已收利息,CF_INT_PAID:已付利息, CF_INT_REC:已收股息,CF_DIV_PAID:已派股息,CF_INV:投资活动产生现金流, CF_FIN_ACT:融资活动产生现金流,CF_BEG:期初现金及现金等价物,CF_CHANGE_CSH:现金及现金等价物净增加额, CF_END:期末现金及现金等价物,CF_EXCH:汇率变动影响,YEAREND_DATE:报表日期} #总表 table_dict = {cwzb:cwzb_dict,lrb:lrb_dict,fzb:fzb_dict,llb:llb_dict} #请求头 headers = {Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8, Accept-Encoding: gzip, deflate, Accept-Language: zh-CN,zh;q=0.8, User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.109 Safari/537.36,} #上市股票地址 target_url = http://quotes.money.163.com/hkstock/cwsj_00700.html req = requests.get(url = target_url, headers = headers) req.encoding = utf-8 html = req.text page_bf = BeautifulSoup(html, lxml) #股票名称，WordPress模板股票代码 name = page_bf.find_all(span,公司跟巴股 class_ = name)[0].string code = page_bf.find_all(span, class_ = code)[0].string code = re.findall(\d+,code)[0] #打印股票信息 print(name + : + code) print() #存储各个表名的网站模板列表 table_name_list = [] table_date_list = [] each_date_list = [] url_list = [] #表名和表时间 table_name = page_bf.find_all(div, class_ = titlebar3) for each_table_name in table_name: #表名 table_name_list.append(each_table_name.span.string) #表时间 for each_table_date in each_table_name.div.find_all(select, id = re.compile(.+1$)): url_list.append(re.findall((\w+)1,each_table_date.get(id))[0]) for each_date in each_table_date.find_all(option): each_date_list.append(each_date.string) table_date_list.append(each_date_list) each_date_list = [] #插入信息 for i in range(len(table_name_list)): print(表名:,table_name_list[i]) print() #获取数据地址 url = http://quotes.money.163.com/hk/service/cwsj_service.php?symbol={}&start={}&end={}&type={}&unit=yuan.format(code,table_date_list[i][-1],table_date_list[i][0],url_list[i]) req_table = requests.get(url = url, headers = headers) value_dict = {} for each_data in req_table.json(): value_dict[股票名] = name value_dict[股票代码] = code forkey, value in each_data.items(): if keyin table_dict[url_list[i]]: value_dict[table_dict[url_list[i]][key]] = value # print(value_dict) sql1 = """ INSERTINTO %s (`股票名`,`股票代码`,`报表日期`) VALUES (%s,%s,%s)""" % (url_list[i],value_dict[股票名],value_dict[股票代码],value_dict[报表日期]) print(sql1) try: cursor.execute(sql1) # 执行sql语句 conn.commit() except: # 发生错误时回滚 conn.rollback() forkey, value in value_dict.items(): if keynotin [股票名,股票代码,报表日期]: sql2 = """ UPDATE %s SET %s=%sWHERE `股票名`=%sAND `报表日期`=%s""" % (url_list[i],key,value,value_dict[股票名],value_dict[报表日期]) print(sql2) try: cursor.execute(sql2) # 执行sql语句 conn.commit() except: # 发生错误时回滚 conn.rollback() value_dict = {} # 关闭数据库连接 cursor.close() conn.close() 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.高防服务器 |
喜欢
讨厌