爬取财务公司售汇汇率数据

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time：2021/12/18 10:06
# @Author：李宏
# @File:fms.py
# @Sofeware :PyCharm
import os
from datetime import datetime, timedelta
import json, requests
import pandas as pd
from option_mssql import mssql_query
from share import SI

def crawlFinanceCompanyExchangeRate():  # 功能： 爬取财务公司售汇汇率 数据
    sql = ' SELECT top 1 rq FROM hl order by rq desc '
    oldDate = mssql_query(sql, 3)['rq']
    if len(oldDate) > 0:
        beginDate = oldDate[0] + timedelta(days=1)
        beginDate = datetime.strftime(beginDate, '%Y-%m-%d')
    else:
        beginDate = '20000101'
    endDate = datetime.today()        # 获取昨天日期 + timedelta(days=-1)
    endDate = datetime.strftime(endDate, '%Y-%m-%d').replace('-', '')
    date_list = [datetime.strftime(x, '%Y-%m-%d') for x in list(pd.date_range(start=beginDate , end=endDate))]
    if len(date_list)==0:
        return None
    os.environ['NO_PROXY'] = 'https://cpfnw.eip.cnpc:8081'
    url = 'https://cpfnw.eip.cnpc:8081/getPriceByPage?pageNum=1&pageSize=100&startTime=&endTime='
    # proxy = {
    #     'https':'https://10.8.19.178:8081',
    #     'http':'http://10.8.19.178:8081'
    # }
    response = requests.get(url, verify=False)
    if response.status_code != requests.codes.ok:
       return None
    header = ['rq','hl','oy','yb','gb']
    records = json.loads(response.text)['pageList']
    df = pd.DataFrame(records)
    df = df[['price_date','shou_mei','shou_ou','shou_ying','shou_gang']]
    df.columns = header
    if len(date_list) > 1:
         df =  df[df['rq'].isin(date_list)]
    elif len(date_list) == 1:
         df =  df[ df['rq'] == date_list[0]]
    if len(date_list)==0 | df.shape[0] == 0 :
        SI.alarmMessageBox(" 无需下载！")
        return None
    df.sort_values(by='rq', inplace=True)
    df['bz'], df['state']= '', 0
    count = 0
    # print(df.head(5000))
    # exit()
    for row in range(df.shape[0]):
        sql = ''' 
            insert into hl(rq, hl, oy, yb, gb, bz, state) values(
            '''
        for col in range(df.shape[1]):
            if col == 0:
                sql = sql + '\'' + df.iloc[row, col].strip() + '\','
            elif not SI.isnumber(df.iloc[row, col]):
                sql = sql +   ' 0 ,'
            else:
                sql = sql + str(format(float(df.iloc[row, col]), '.4f')) + ','
        sql = sql[0:-1] + ')'
        # print(sql)
        count = count + mssql_query(sql, 8)  # 将 DataFrame 最终内容 存入数据库
    return count
if __name__ == '__main__':
    if crawlFinanceCompanyExchangeRate():
        SI.alarmMessageBox(f" 财务公司汇率售汇价 爬取完成! ")
    else:
        SI.alarmMessageBox(" 无需下载！ ")