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

微信扫码关注

更新实时通知