python抓取雪球股票数据存入数据库代码
#!/usr/bin/python import requests import json import MySQLdb import time from DB import DB url = "https://xueqiu.com/service/v5/stock/screener/quote/list?page=%s&size=90&order=desc&order_by=amount&exchange=CN&market=CN&type=%s&_=1573652558435" headers = { "Accept": "*/*", # "Accept-Encoding": "gzip, deflate, br", "Accept-Language": "zh-CN,zh;q=0.9", "Connection": "keep-alive", "Cookie": "aliyungf_tc=AQAAANr3VBkMZAgAUhVFeTTWn+RvdBpU; xq_a_token=584d0cf8d5a5a9809761f2244d8d272bac729ed4; xq_a_token.sig=x0gT9jm6qnwd-ddLu66T3A8KiVA; xq_r_token=98f278457fc4e1e5eb0846e36a7296e642b8138a; xq_r_token.sig=2Uxv_DgYTcCjz7qx4j570JpNHIs; _ga=GA1.2.1187356785.1534314931; _gid=GA1.2.1120971600.1534314931; Hm_lvt_1db88642e346389874251b5a1eded6e3=1534314931; Hm_lpvt_1db88642e346389874251b5a1eded6e3=1534314931; u=331534314932251; device_id=7bdbd08983b2b7e03fd0747d6a121e99", "Host": "xueqiu.com", "Referer": "https://xueqiu.com/", "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.170 Safari/537.36", "X-Requested-With": "XMLHttpRequest", } time_str_talbe = time.strftime("%Y%m", time.localtime()) time_str_alter = time.strftime("%d", time.localtime()) db = DB().getConnect() cursor = db.cursor() table_c_sql = ''' CREATE TABLE IF NOT EXISTS `xueqiu_gg_%s` ( id INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY ( id ) )ENGINE=innodb DEFAULT CHARSET=utf8; ''' % (time_str_talbe) try: cursor.execute(table_c_sql) db.autocommit(cursor) except: pass alter_sql = ''' alter table `xueqiu_gg_%s` add column `%s` float not null default 0; alter table `xueqiu_gg_%s` add column `%s` float not null default 0 ''' % (time_str_talbe, "pct_"+time_str_alter, time_str_talbe, "current_"+time_str_alter) print alter_sql try: cursor.execute(alter_sql) db.autocommit(cursor) except: pass listType = ["sha", "sza", "cyb", "zxb"] for lt in listType: page_flag = True page = 1 while page_flag: page_url = url % (str(page), lt) print page_url gg_response = requests.get(page_url, headers=headers, allow_redirects=False) print gg_response gg_dict = json.loads(gg_response.text) print gg_dict gg_list = gg_dict["data"]["list"] tmp = [] for gg_info in gg_list: sql_single = "select id from xueqiu_gg where symbol = '%s'" % (gg_info["symbol"]) cursor.execute(sql_single) db.autocommit(cursor) count = cursor.fetchone() pct = gg_info["percent"] if gg_info["percent"] == None: pct = 0 print count if count == None: sql = "INSERT INTO xueqiu_gg (`name`, `icode`, `symbol`, `areacode`) VALUES ('%s', '%s', '%s', '%s')" % \ (gg_info["name"], "", gg_info["symbol"], "") print sql cursor.execute(sql) db.autocommit(cursor) last_id = cursor.lastrowid data_sql = "INSERT INTO `xueqiu_gg_%s` (`gg_id`, `pct_%s`, `current_%s`) VALUES (%s, '%s', '%s')" % \ (time_str_talbe, time_str_alter, time_str_alter, last_id, pct, gg_info["current"]) else: last_id = count[0] data_sql = "update `xueqiu_gg_%s` set `gg_id`=%s,`pct_%s`=%s,`current_%s`=%s where gg_id = %s" % \ (time_str_talbe, last_id, time_str_alter, pct, time_str_alter, gg_info["current"], last_id) print data_sql tmp.append(gg_info) cursor.execute(data_sql) db.autocommit(cursor) page = page + 1 if len(tmp) < 90: page_flag = False
微信扫码关注
更新实时通知