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()