import requests

import pymssql

url = 'https://v1.cn-abs.com/ajax/ChartMarketHandler.ashx'

headers = {

    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36'

}

# 每个交易场所每一年的发行金额

def get_marketInventory():

    FromData = {

        'type': 'marketInventory'

    }

    rep = requests.post(url, data=FromData).json()

    for i in rep:

        # print(i['SeriesName'],i['Points'])

        for j in i['Points']:

            result = {

                '交易场所': i['SeriesName'],

                '发行年份': j['X'],

                '发行金额': j['Y']

            }

            yield result

# 各个资产类型每一年的发行金额

def get_marketTotal():

    FromData = {

        'type': 'marketTotal'

    }

    rep = requests.post(url, data=FromData).json()

    for i in rep:

        # print(i['SeriesName'],i['Points'])

        result = {

            '资产类型': i['SeriesName'],

            '发行金额': "" + str(i['Points'][0]['Y'][0]).replace("[", "'").replace("]", "'"),

            '存量金额': "" + str(i['Points'][1]['Y'][0]).replace("[", "'").replace("]", "'")

        }

        yield result

def sql_marketInventory():

    result = get_marketInventory()

    for res in result:

        MarketPlace = res['交易场所']

        IssueYear = res['发行年份']

        IssueAmount = res['发行金额']

        sqlText = "insert into [InvestSuite].[dbo].[MarketInventory] (MarketPlace,IssueYear,IssueAmount) " \

                  "values(N'%s','%s','%s')"%(MarketPlace,IssueYear,IssueAmount)

        cur.execute(sqlText)

    conn.commit()

    # 如果update/delete/insert记得要conn.commit()

    # cur.close()

    # conn.close()

    # sqlText = 'SELECT * FROM [InvestSuite].[dbo].[MarketInventory] '

    # cur.execute(sqlText)

    # print(cur.fetchall())

def sql_marketTotal():

    result = get_marketTotal()

    for res in result:

        AssetType=res['资产类型']

        CurrentIssueAmount=res['发行金额']

        TotalIssueAmount=res['存量金额']

        sqlText = "insert into [InvestSuite].[dbo].[MarketTotal] (AssetType,CurrentIssueAmount,TotalIssueAmount) " \

                  "values(N'%s','%s','%s')" % (AssetType,CurrentIssueAmount,TotalIssueAmount)

        cur.execute(sqlText)

    conn.commit()

# 清空两张表的所有数据

def clean():

    sql_text='delete from [InvestSuite].[dbo].[MarketTotal]'

    cur.execute(sql_text)

    conn.commit()

    sql_text = 'delete from [InvestSuite].[dbo].[MarketInventory]'

    cur.execute(sql_text)

    conn.commit()

if __name__ == '__main__':

    conn = pymssql.connect(host='', user='', password='',

                           database='', charset='utf8')

    cur = conn.cursor()

    if not cur:

        raise Exception('数据库连接失败！')

    # parse_marketInventory()

    # parse_marketTotal()

    clean()

    get_marketInventory()

    sql_marketInventory()

    get_marketTotal()

    sql_marketTotal()

    cur.close()

    conn.close()