import json,requests,pymysql
from pprint import pprint
from datetime import datetime
dt=datetime.now()
todayy=datetime(dt.year,dt.month,dt.day,0,0,0)
mysql_110= {
"host": "172.18.28.110",
"port":3306,
"user": "rduser",
"password": "*****8",
"db": "test",
"charset":"utf8"}
#连接mysql
mysql_conn=pymysql.connect(**mysql_110)
sql0="""
CREATE TABLE `jrj_news_original` (
`seq` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序列号，代理主键',
`news_id` int(8) NOT NULL COMMENT '资讯id',
`title` varchar(300) NOT NULL COMMENT '资讯标题',
`detail` mediumtext DEFAULT NULL COMMENT '摘要',
`list_date` datetime NOT NULL COMMENT '发布时间',
`key_word` varchar(100) DEFAULT NULL COMMENT '关键词',
`stk_code` varchar(10) DEFAULT NULL COMMENT '股票代码',
`stk_sname` varchar(30) DEFAULT NULL COMMENT '股票简称',
`img_url` mediumtext DEFAULT NULL COMMENT '图片链接',
`info_url` varchar(100) DEFAULT NULL COMMENT '全文连接',
`content` text DEFAULT NULL COMMENT '内容',
`infocls` varchar(6) DEFAULT NULL COMMENT '栏目ID',
`channum` varchar(3) DEFAULT NULL COMMENT '频道ID',
`cls` int(6) DEFAULT NULL COMMENT '类别ID',
`from_type` int(3) NOT NULL COMMENT '来源类别',
PRIMARY KEY (`seq`),
KEY `list_date` (`list_date`,`news_id`,`stk_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1341 DEFAULT CHARSET=utf8 COMMENT='资讯原表\r\n数据来源：北京提供js格式连接\r\n更新频率：每3分钟更新一次\r\n';
"""
#创建游标
cursor=mysql_conn.cursor()
#删除当天的数据
deleSql='''
delete from JRJ_NEWS_ORIGINAL
where list_date>=curdate()
'''
cursor.execute(deleSql)
#获取所有查询结果
#datas=cursor.fetchall()
companyUrl='http://stock.jrj.com.cn/share/news/app/company/'+todayy.strftime('%Y-%m-%d')+'.js'
#上市公司 100003 4
hml1=requests.get(companyUrl)
#数据全文
#print(hml1.text[27:][:-6])
tex=json.loads(hml1.text[27:][:-6])
print('上市公司 数据样例：\n',tex[0])
len1=len(tex)
print(len1)
for i in range(len1):
if datetime.strptime(tex[i][0]['makedate'],'%Y-%m-%d %H:%M:%S')<todayy:
break
print("----- has insert into( %d ) company datas-----"%(i+1))
#print(tex[i][0]['stockcode'].split(','))
if len(tex[i][0]['stockcode'].split(','))==0:
sql1=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,IMG_URL,INFO_URL,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,100003,4);'''
args1=[tex[i][0]['iiid'],tex[i][0]['title'].replace('"',"'"),tex[i][0]['detail'].replace('"',"'"),tex[i][0]['makedate'],tex[i][0]['imgurl'],tex[i][0]['infourl']]
cursor.execute(sql1,args1)
elif len(tex[i][0]['stockcode'].split(',')) == 1:
sql2='''INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,STK_CODE,STK_SNAME,IMG_URL,INFO_URL,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,100003,4);'''
args2=[tex[i][0]['iiid'],tex[i][0]['title'].replace('"',"'"),tex[i][0]['detail'].replace('"',"'"),tex[i][0]['makedate'],tex[i][0]['stockcode']
,tex[i][0]['stockname'],tex[i][0]['imgurl'],tex[i][0]['infourl']]
cursor.execute(sql2,args2)
else :
for x in range(len(tex[i][0]['stockcode'].split(','))):
sql3='''INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,STK_CODE,STK_SNAME,IMG_URL,INFO_URL,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,100003,4);'''
args3=[tex[i][0]['iiid'],tex[i][0]['title'],tex[i][0]['detail'],tex[i][0]['makedate'],tex[i][0]['stockcode'].split(',')[x]
,tex[i][0]['stockname'].split(',')[x],tex[i][0]['imgurl'],tex[i][0]['infourl']]
cursor.execute(sql3,args3)
#保存提交cursor的执行结果
mysql_conn.commit()
#机会早知道 100001 2
chanceUrl='http://stock.jrj.com.cn/share/news/app/qingbao/'+todayy.strftime('%Y-%m-%d')+'.js'
html2=requests.get(chanceUrl)
#数据全文
#print(hml1.text[26:][:-2])
txt=json.loads(html2.text[26:][:-2])
print('机会早知道 数据样例：\n',txt[0])
len1=len(txt)
print(len1)
for i in range(len1):
#print(txt[i]['stockcode'].split(','))
if datetime.strptime(txt[i]['makedate'],'%Y-%m-%d %H:%M:%S')<todayy:
break
print("----- has insert into( %d ) Chance Early Know datas-----"%(i+1))
if len(txt[i]['stockcode'].split(','))==0:
sql1=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,IMG_URL,INFO_URL,CONTENT,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,100001,2);'''
args1=[txt[i]['iiid'],txt[i]['title'],txt[i]['detail']
,txt[i]['makedate'],txt[i]['keyword'],txt[i]['imgurl'],txt[i]['infourl'],txt[i]['content']]
cursor.execute(sql1,args1)
#print(''' INSERT INTO NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,IMG_URL,INFO_URL)
#VALUES({0},"{1}","{2}","{3}","{4}","{5}","{6}");'''.format(txt[i]['iiid'],txt[i]['title'],txt[i]['detail']
#,txt[i]['makedate'],txt[i]['keyword'],txt[i]['imgurl'],txt[i]['infourl']
#))
elif len(txt[i]['stockcode'].split(',')) == 1:
sql2=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,IMG_URL,INFO_URL,CONTENT,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,100001,2);'''
args2=[txt[i]['iiid'],txt[i]['title'],txt[i]['detail'],txt[i]['makedate'],txt[i]['keyword']
,txt[i]['stockcode'],txt[i]['imgurl'],txt[i]['infourl'],txt[i]['content']]
cursor.execute(sql2,args2)
#print('INSERT INTO NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,IMG_URL,INFO_URL)
#VALUES({0},"{1}","{2}","{3}","{4}","{5}","{6}","{7}");'.
#format(txt[i]['iiid'],txt[i]['title'],txt[i]['detail'],txt[i]['makedate'],default(txt[i]['keyword'])
#,txt[i]['stockcode'],default(txt[i]['imgurl']),default(txt[i]['infourl'])))
else :
for x in range(len(txt[i]['stockcode'].split(','))):
sql3=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,IMG_URL,INFO_URL,CONTENT,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,100001,2);'''
args3=[txt[i]['iiid'],txt[i]['title'],txt[i]['detail'],txt[i]['makedate'],txt[i]['keyword']
,txt[i]['stockcode'].split(',')[x],txt[i]['imgurl'],txt[i]['infourl'],txt[i]['content']]
cursor.execute(sql3,args3)
#print('INSERT INTO NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,IMG_URL,INFO_URL)
#VALUES({0},"{1}","{2}","{3}","{4}","{5}","{6}","{7}");'.
#format(txt[i]['iiid'],txt[i]['title'],txt[i]['detail'],txt[i]['makedate'],default(txt[i]['keyword'])
#,txt[i]['stockcode'].split(',')[x],default(txt[i]['imgurl']),default(txt[i]['infourl'])))
#保存提交cursor的执行结果
mysql_conn.commit()
#涨跌停揭秘 100002 3
secretUrl='http://stock.jrj.com.cn/share/news/app/zhangting/'+todayy.strftime('%Y-%m-%d')+'.js'
html3=requests.get(secretUrl)
txt2=json.loads(html3.text[26:][:-2])
len2=len(txt2)
for i in range(len2):
#print(txt2[i]['stockcode'].split(','))
if datetime.strptime(txt2[i]['makedate'],'%Y-%m-%d %H:%M:%S')<todayy:
break
print("----- has insert into( %d ) Secret Up and Down datas-----"%(i+1))
if len(txt2[i]['stockcode'].split(','))==0:
sql1=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,IMG_URL,INFO_URL,CONTENT,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,100002,3);'''
args1=[txt2[i]['iiid'],txt2[i]['title'],txt2[i]['detail']
,txt2[i]['makedate'],txt2[i]['keyword'],txt2[i]['imgurl'],txt2[i]['infourl'],txt[i]['content']]
cursor.execute(sql1,args1)
elif len(txt2[i]['stockcode'].split(',')) == 1:
sql2=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,IMG_URL,INFO_URL,CONTENT,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,100002,3);'''
args2=[txt2[i]['iiid'],txt2[i]['title'],txt2[i]['detail'],txt2[i]['makedate'],txt2[i]['keyword']
,txt2[i]['stockcode'],txt2[i]['imgurl'],txt2[i]['infourl'],txt[i]['content']]
cursor.execute(sql2,args2)
else :
for x in range(len(txt2[i]['stockcode'].split(','))):
sql3=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,IMG_URL,INFO_URL,CONTENT,CLS,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,100002,3);'''
args3=[txt2[i]['iiid'],txt2[i]['title'],txt2[i]['detail'],txt2[i]['makedate'],txt2[i]['keyword']
,txt2[i]['stockcode'].split(',')[x],txt2[i]['imgurl'],txt2[i]['infourl'],txt[i]['content']]
cursor.execute(sql3,args3)
#保存提交cursor的执行结果
mysql_conn.commit()
import random
#乌兰木数据 null 1
noTypeUrl='http://mapp.jrj.com.cn/co/zk/1.js?'+str(random.randint(1,9))+str(random.randint(1,9))
print(noTypeUrl)
hml1=requests.get(noTypeUrl)
tx=hml1.json()
print(len(tx['data']))
len4=len(tx['data'])
data1=tx['data']
for i in range(len4):
print("----- has insert into( %d )datas-----"%i)
if datetime.strptime(data1[i]['makedate'],'%Y-%m-%d %H:%M:%S')<todayy:
break
if len(data1[i]['infostocks'])==0:
sql1=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,IMG_URL,INFO_URL,INFOCLS,CHANNUM,AUTHOR,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,1);'''
args1=[data1[i]['iiid'],data1[i]['title'],data1[i]['detail'],data1[i]['makedate'],data1[i]['keyword']
,data1[i]['imgurl'],data1[i]['infourl'],data1[i]['infocls'],data1[i]['channum'],data1[i]['author']]
cursor.execute(sql1,args1)
elif len(data1[i]['infostocks'])== 1:
sql2=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,STK_SNAME,IMG_URL,INFO_URL,INFOCLS,CHANNUM,AUTHOR,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,1);'''
args2=[data1[i]['iiid'],data1[i]['title'],data1[i]['detail'],data1[i]['makedate'],data1[i]['keyword']
,data1[i]['infostocks'][0]['stockcode'],data1[i]['infostocks'][0]['stockname'],data1[i]['imgurl']
,data1[i]['infourl'],data1[i]['infocls'],data1[i]['channum'],data1[i]['author']]
cursor.execute(sql2,args2)
else :
for x in range(len(data1[i]['infostocks'])):
sql3=''' INSERT INTO JRJ_NEWS_ORIGINAL(NEWs_ID,TITLE,DETAIL,LIST_DATE,KEY_WORD,STK_CODE,STK_SNAME,IMG_URL,INFO_URL,INFOCLS,CHANNUM,AUTHOR,FROM_TYPE)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,1);'''
args3=[data1[i]['iiid'],data1[i]['title'],data1[i]['detail'],data1[i]['makedate'],data1[i]['keyword']
,data1[i]['infostocks'][x]['stockcode'],data1[i]['infostocks'][x]['stockname'],data1[i]['imgurl']
,data1[i]['infourl'],data1[i]['infocls'],data1[i]['channum'],data1[i]['author']]
cursor.execute(sql3,args3)
#保存提交cursor的执行结果
mysql_conn.commit()
cursor.close()
mysql_conn.close()