Esempio n. 1
0
def avg_yjl_history():
    con=get_mysql_conn('db_jisilu','local')
    con2=get_mysql_conn('db_stock','local')

    cursor=con.cursor()
    cursor2=con2.cursor()

    start = datetime.datetime(2019,2,25)
    update_sql = 'insert into tb_bond_avg_yjl (Date,溢价率均值,溢价率最大值,溢价率最小值,溢价率中位数,转债数目) values (%s,%s,%s,%s,%s,%s)'
    while 1:
        if start>=datetime.datetime.now():
            break
        date_str = start.strftime('%Y-%m-%d')
        try:
            search_sql = 'select `溢价率` from `tb_jsl_{}`'.format(date_str)
            cursor.execute(search_sql)

        except Exception as e:
            logger.error(e)

        else:
            content = cursor.fetchall()
            data=[]
            for item in content:
                data.append(item[0])

            np_data = np.array(data)
            max_value= np.round(np_data.max(),2)
            min_value= np.round(np_data.min(),2)
            mean = np.round(np_data.mean(),2)
            median=np.round(np.median(np_data),2)
            count=len(np_data)
            t_value=(date_str,float(mean),float(max_value),float(min_value),float(median),count)
            # print(t_value)

            try:
                cursor2.execute(update_sql,t_value)
                con2.commit()

            except Exception as e:
                logger.error(e)
                con2.rollback()
            else:
                logger.info('update')


        finally:

            start=start+datetime.timedelta(days=1)
Esempio n. 2
0
def main():
    filename = os.path.join(DATA_PATH, 'blacklist.csv')
    # 本地更新
    logger.info('update local')
    db_name = 'db_stock'
    conn = get_mysql_conn(db_name, local='local')
    create_tb(conn)
    update_data(filename, conn)

    # 远程更新
    # db_name = 'db_stock'
    logger.info('update remote')
    remote_conn = get_mysql_conn('', local='ali')
    create_tb(remote_conn)
    update_data(filename, remote_conn)
Esempio n. 3
0
def convert_name_db():
    conn = get_mysql_conn('db_stock', 'local')
    cursor = conn.cursor()
    query_cmd = '''
    select * from tb_bond_jisilu
    '''

    cursor.execute(query_cmd)
    ret = cursor.fetchall()

    for item in ret:
        name = item[1]
        code = item[0]
        zg_name = item[3]
        zg_code = item[4]
        grade = item[17]


        get_area = '''
        select area from tb_basic_info where code = %s
        '''

        cursor.execute(get_area,zg_code)
        result= cursor.fetchone()[0]


        insert_cmd = '''
        insert into tb_bond_kind_info (可转债名称,可转债代码,正股名称,正股代码,评级,地区,更新时间) values(%s,%s,%s,%s,%s,%s,%s)
        '''

        cursor.execute(insert_cmd,(name,code,zg_name,zg_code,grade,result,datetime.datetime.now().strftime('%Y-%m-%d')))

    conn.commit()
Esempio n. 4
0
    def data_sync(self):
        conn = get_mysql_conn('db_stock', 'local')
        cursor = conn.cursor()
        # 最新的数据库
        select_cmd = '''select * from tb_delivery_gj'''
        cursor.execute(select_cmd)
        ret = list(cursor.fetchall())
        print('new db ', len(ret))
        # 旧的数据库
        select_cmd2 = '''select * from tb_delivery_gj_django'''
        cursor.execute(select_cmd2)
        ret2 = list(cursor.fetchall())
        print('old db ', len(ret2))
        ret_copy = ret.copy()

        for item in ret:
            # print(item)
            for item2 in ret2:
                if item[0] == item2[0] and item[1] == item2[1] and item[2] == item2[2] and item[4] == item2[4] and item[
                    5] == item2[5]:
                    try:
                        ret_copy.remove(item)
                    except Exception as e:
                        # print(e)
                        # print()
                        pass

        # print(ret_copy)
        for i in ret_copy:
            # print(i)
            update_sql = '''
            insert into tb_delivery_gj_django (成交日期,证券代码,证券名称,操作,成交数量,成交均价,成交金额,)
            '''

        print('diff len ', len(ret_copy))
Esempio n. 5
0
def get_code(name):
    conn = get_mysql_conn('db_stock')
    cur = conn.cursor()
    cmd = 'select code from tb_basic_info where name=\'{}\''.format(name)
    cur.execute(cmd)
    ret = cur.fetchone()
    return ret[0]
Esempio n. 6
0
def clone_database():
    local_db = get_mysql_conn('db_zdt', local=True)
    cur = local_db.cursor()
    cur.execute('show tables')
    tables = cur.fetchall()
    local_engine = get_engine('db_zdt', local=True)
    dfs = []
    for table in tables:

        try:
            result = re.findall('(\d+)zdt$', table[0])
            if result:
                print(table[0])
                current = result[0]
                # d= datetime.datetime.strptime(current,'%Y%m%d').strftime('%Y-%m-%d')
                # print(d)
                df = pd.read_sql(table[0], local_engine, index_col='index')
                # df['涨停日期']=d
                df = df.rename(columns={
                    '最后一次涨停时间A': '最后一次涨停时间',
                    '第一次涨停时间A': '第一次涨停时间'
                })
                try:
                    print(df.head())
                    df.to_sql(table[0], local_engine, if_exists='replace')
                except Exception as e:
                    print(e)

        except Exception as e:
            print(e)
            print(table[0])
Esempio n. 7
0
def find_lower_bond():
    # 和均值的比较因子,正常为1

    percent = 1
    con = get_mysql_conn('db_stock','local')
    cursor = con.cursor()
    query_avg_sql = '''
    SELECT `评级`,count(*) as n,round(AVG(`最小值`),2) as `均值` FROM `tb_bond_kind_info` GROUP BY `评级`
    '''
    cursor.execute(query_avg_sql)
    ret = cursor.fetchall()
    d= {}
    for item in ret:
        d[item[0]]=item[2]

    print(d)
    query_all_bond_sql = '''
    select `可转债代码`,`评级`,`可转债价格`,`可转债名称` from tb_bond_jisilu
    '''
    cursor.execute(query_all_bond_sql)
    total_bond_ret = cursor.fetchall()
    for item in total_bond_ret:
        if item[2] <= percent * d.get(item[1]):
            ration = round((item[2]-d.get(item[1]))/d.get(item[1])*100,2)

            print(f'{item[3]}:评级{item[1]},当前价格:{item[2]},低于比例{ration}')

    print('done')
Esempio n. 8
0
    def get_current_position(self):
        engine = get_engine('db_position')

        df = pd.read_sql('tb_position_2019-06-17', con=engine)

        # 只关注可转债
        df = df[df['证券代码'].map(self.identify_market)]

        kzz_stocks = dict(zip(list(df['证券代码'].values),
                              list(df['证券名称'].values)))

        cons = get_mysql_conn('db_stock', 'local')
        cursor = cons.cursor()
        query_cmd = 'select 正股代码,正股名称,溢价率 from tb_bond_jisilu where 可转债代码=%s'
        zg_stocks = {}
        kzz_yjl = {}
        zg_yjl = {}
        for code in kzz_stocks:
            cursor.execute(query_cmd, (code))
            ret = cursor.fetchone()
            if ret:
                zg_stocks[ret[0]] = ret[1]
                kzz_yjl[code] = ret[2]
                zg_yjl[ret[0]] = ret[2]

        # 可转债代码
        # dict,dict,dict,dict
        return (kzz_stocks, zg_stocks, kzz_yjl, zg_yjl)
Esempio n. 9
0
def history_data():
    url = 'https://www.jisilu.cn/data/cbnew/cb_index/'
    headers = {
        'Host':
        'www.jisilu.cn',
        'Referer':
        'https://www.jisilu.cn/data/cbnew/',
        'User-Agent':
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
    }
    r = requests.get(url=url, headers=headers)
    r.encoding = 'utf8'

    # print(r.text)

    content = r.text
    date = re.search('var __date = (.*?);', content, re.S).group(1)
    data = re.search('var __data = (.*?);', content, re.S).group(1)
    date_ = eval(date)
    data_ = eval(data)
    price = data_.get('price')
    volume = data_.get('volume')
    amount = data_.get('amount')
    count = data_.get('count')  # 数量
    increase_val = data_.get('increase_val')  # 涨跌额
    increase_rt = data_.get('increase_rt')  # 涨跌幅

    # df = pd.DataFrame({'日期':date_,'指数':price,'成交额': amount,'涨跌':increase_val,'涨跌额':increase_rt,'转债数目':increase_rt})
    con = get_mysql_conn('db_stock', 'local')
    cursor = con.cursor()

    create_sql = '''
    create table if not exists tb_cb_index (`日期` date ,`指数` float ,`成交额(亿元)` float,`涨跌` float ,`涨跌额` float ,`转债数目` float ,`剩余规模` float )
    '''

    try:
        cursor.execute(create_sql)
    except Exception as e:
        print(e)
        con.rollback()
    else:
        con.commit()

    update_data_sql = '''
    insert into `tb_cb_index` (`日期`,`指数`,`成交额(亿元)`,`涨跌`,`涨跌额`,`转债数目`,`剩余规模`) values (%s,%s,%s,%s,%s,%s,%s);
    '''
    for index, item in enumerate(date_):
        value_tuple = (item, price[index], volume[index], increase_val[index],
                       increase_rt[index], count[index], amount[index])

        try:
            cursor.execute(update_data_sql, value_tuple)
        except Exception as e:
            print(value_tuple)
            print(e)
            con.rollback()
        else:
            con.commit()
Esempio n. 10
0
def update_daily():
    '''
    每天更新行情
    :return:
    '''
    # 运行静态方法
    SaveData.daily_market()
    time.sleep(20)
    daily_conn = get_mysql_conn('daily')
    cursor = daily_conn.cursor()
    today = datetime.datetime.now().strftime('%Y-%m-%d')
    cmd = 'select * from `{}`;'.format(today)
    cursor.execute(cmd)
    #today = '2017-11-17'
    #daily_df = pd.read_sql_table(today,daily_conn,index_col='index')
    days_info = cursor.fetchall()
    for i in days_info:
        code = i[1]
        name = i[2]
        close = i[4]
        opens = i[5]
        high = i[6]
        low = i[7]
        vol = i[9]
        amount = i[11]

        try:
            history_conn = get_mysql_conn('history')
            history_cur = history_conn.cursor()
            history_cur.execute('select count(*) from `{}`;'.format(code))
        except Exception as e:
            print(e)
            continue
        l = history_cur.fetchone()
        df = pd.DataFrame(columns=[
            'datetime', 'code', 'name', 'open', 'close', 'high', 'low', 'vol',
            'amount'
        ])
        df.loc[l] = [today, code, name, opens, close, high, low, vol, amount]
        try:
            df.to_sql(code, engine, if_exists='append')
            print(code)
        except Exception as e:
            print(df)
            print(e)
Esempio n. 11
0
 def __init__(self):
     self.engine = get_engine('db_stock', local=True)
     self.conn = get_mysql_conn('db_stock', local='local')
     self.info = pd.read_sql('tb_basic_info', con=self.engine, index_col='code')
     self.db = pymongo.MongoClient(MONGO_HOST, MONGO_PORT)
     self.doc = self.db['db_stock']['break_low_high']
     ts.set_token(token)
     self.pro = ts.pro_api()
     self.count = 0
Esempio n. 12
0
def dongbei(code):
    dongbei_area = ['黑龙江', '吉林', '辽宁']
    conn = get_mysql_conn('db_stock')
    cur = conn.cursor()
    cmd = 'select area from tb_basic_info where code=\'{}\''.format(code)
    cur.execute(cmd)
    ret = cur.fetchone()
    if ret[0] in dongbei_area:
        return True
    else:
        return False
Esempio n. 13
0
def check_blacklist(code):
    conn = get_mysql_conn('db_stock')
    cur = conn.cursor()
    cmd = 'select * from tb_blacklist where code=\'{}\''.format(code)
    cur.execute(cmd)
    ret = cur.fetchone()
    if len(ret) == 0:
        return False
    else:
        print(ret[3])
        return True
Esempio n. 14
0
def find_zz_zg_diff_history():

    con = get_mysql_conn('db_jisilu', 'local')
    cursor = con.cursor()
    current = datetime.date.today()
    days=60
    tb_name ='tb_jsl_{}'
    num_list =[]
    for i in range(days):

        start = (current+datetime.timedelta(days=-1*i)).strftime("%Y-%m-%d")
        name = tb_name.format(start)

        query_cmd = 'select count(*) from `{}` WHERE `正股涨跌幅`>=`可转债涨幅` and `正股涨跌幅`<=0'.format(name)
        try:
            cursor.execute(query_cmd)
        except Exception as e:
            logger.error(e)
            con.rollback()
            continue

        else:
            get_count = cursor.fetchone()
            num = get_count[0]
            num_list.append((start,num))
    # print(num_list)
    # print(sorted(num_list,key=lambda x:x[1],reverse=True))
    con.close()

    con2 = get_mysql_conn('db_stock','local')
    cur=con2.cursor()
    insert_sql = 'insert into `tb_zz_more_drop_zg` (date,number) values (%s,%s)'
    try:
        cur.executemany(insert_sql,(num_list))
        con2.commit()
    except Exception as e:
        logger.error(e)
        con2.rollback()
    else:
        logger.info('入库成功')
Esempio n. 15
0
    def get_close_price(self):
        conn = get_mysql_conn('db_jisilu','local')
        cursor = conn.cursor()

        cmd = 'select 可转债代码,可转债价格 from `tb_jsl_{}`'.format(self.yesterday)
        try:
            cursor.execute(cmd)
            result = cursor.fetchall()
        except Exception as e:
            return None

        else:
            d={}
            for item in result:
                d[item[0]]=item[1]
            return d
Esempio n. 16
0
def main():
    con = get_mysql_conn('db_stock', 'local')
    cursor = con.cursor()
    code_list = get_code()
    update_sql = '''
    update tb_bond_kind_info set `最小值` = %s, `最小值-发生时间` = %s where `可转债代码` = %s
    '''
    for i in code_list:
        min_close, min_date = get_history_data(i)
        try:
            cursor.execute(update_sql, (float(min_close), min_date, i))
        except Exception as e:
            print(e)
            con.rollback()
        con.commit()
    con.close()
Esempio n. 17
0
def get_today_index():
    url = 'https://www.jisilu.cn/data/cbnew/cb_index_quote/'
    headers = {
        'Host':
        'www.jisilu.cn',
        'X-Requested-With':
        'XMLHttpRequest',
        'Referer':
        'https://www.jisilu.cn/data/cbnew/',
        'User-Agent':
        'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
    }
    r = requests.get(url=url, headers=headers)

    # print(r.text)
    data_ = r.json()
    price = data_.get('cur_index')
    volume = data_.get('volume')
    amount = data_.get('amount')
    count = data_.get('count')  # 数量
    increase_val = data_.get('cur_increase_val')  # 涨跌额
    increase_rt = data_.get('cur_increase_rt')  # 涨跌幅

    con = get_mysql_conn('db_stock', 'local')
    cursor = con.cursor()
    current = datetime.datetime.now().strftime('%Y-%m-%d')
    update_data_sql = '''
    insert into `tb_cb_index` (`日期`,`指数`,`成交额(亿元)`,`涨跌`,`涨跌额`,`转债数目`,`剩余规模`) values (%s,%s,%s,%s,%s,%s,%s);
    '''
    value_tuple = (current, price, volume, increase_val, increase_rt, count,
                   amount)

    print(value_tuple)
    try:
        cursor.execute(update_data_sql, value_tuple)
    except Exception as e:
        print(value_tuple)
        print(e)
        con.rollback()
    else:
        con.commit()
        logger.info('爬取成功并入库')
Esempio n. 18
0
    def notice(self):
        buy, sell = self.run()
        sub = '{}: 美元汇率{}'.format(
            datetime.datetime.now().strftime('%Y-%m-%d %H:%M'), buy)
        logger.info(sub)
        # sendmail('',sub)

        conn = get_mysql_conn('db_stock', 'local')
        cursor = conn.cursor()
        cmd = 'insert into `usd_ratio` (`price`,`date`) VALUES ({},{!r})'.format(
            buy,
            datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))

        try:
            cursor.execute(cmd)
            conn.commit()
        except Exception as e:
            logger.error(e)
            conn.rollback()

        conn.close()
Esempio n. 19
0
 def update_sold(self):
     cur = self.conn.cursor()
     tb_name = 'tb_sold_stock'
     cur.execute('select * from {}'.format(tb_name))
     content = cur.fetchall()
     db_daily = get_mysql_conn('db_daily')
     db_cursor = db_daily.cursor()
     stock_table = datetime.datetime.now().strftime('%Y-%m-%d')
     # stock_table = '2018-05-02'
     for i in content:
         cmd = 'select `trade` from `{}` where `code`=\'{}\''.format(
             stock_table, i[0])
         print(cmd)
         db_cursor.execute(cmd)
         ret = db_cursor.fetchone()
         sold_price = i[3]
         percentange = round(
             float(ret[0] - sold_price) / sold_price * 100, 2)
         update_cmd = 'update  `{}` set `当前价`={} ,`卖出后涨跌幅`= {} where `代码`=\'{}\''.format(
             tb_name, ret[0], percentange, i[0])
         print(update_cmd)
         cur.execute(update_cmd)
         self.conn.commit()
Esempio n. 20
0
def merge_database():
    local_db = get_mysql_conn('db_zdt', local=True)
    cur = local_db.cursor()
    cur.execute('show tables')
    tables = cur.fetchall()
    local_engine = get_engine('db_zdt', local=True)
    dfs = []
    for table in tables:
        try:
            result = re.findall('(\d+)zdt$', table[0])
            if len(result) > 0:
                print(table[0])
                df = pd.read_sql(table[0], local_engine, index_col='index')
                dfs.append(df)

        except Exception as e:
            print(e)
            print(table[0])
    dfx = pd.concat(dfs)
    print(dfx.head())

    # ali_engine = get_engine(None,local=False)
    local_engine_stock = get_engine('db_stock', local=True)
    dfx.to_sql('tb_zdt', local_engine_stock, if_exists='replace')
Esempio n. 21
0
# @Time : 2020/1/14 0:05
# @File : fd_money.py
# 涨停封单数据
from settings import get_mysql_conn
import datetime
import tushare as ts
import matplotlib.pyplot as plt

conn = get_mysql_conn('db_zdt', 'local')
cursor = conn.cursor()
diff_day = 20
dataset = []
date = []
for d in range(diff_day):
    day = datetime.datetime.now() + datetime.timedelta(days=-1 * d)
    # if ts.is_holiday(day.strftime('%Y-%m-%d')):
    #     continue

    sql = 'select 封单金额 as total_money from `{}zdt`'.format(
        day.strftime('%Y%m%d'))

    # sql = '''select sum(封单金额) as total_money from `20200113zdt`'''
    # print(sql)
    try:
        cursor.execute(sql)
        ret = cursor.fetchone()
        # print(ret[0])
        dataset.append(int(ret[0] / 10000))
        date.append(day.strftime('%Y%m%d'))
    except Exception as e:
        pass
Esempio n. 22
0
    def years_gj_each_month_day(self,filename):
        # filename = 'GJ_2019-05-11-05-16.csv'
        try:
            # 根据不同的格式选用不同的函数
            # t=pd.read_table(filename,encoding='gbk',dtype={'证券代码':np.str})
            t = pd.read_csv(filename, encoding='gbk', dtype={'证券代码': np.str})
            # t = pd.read_excel(filename, encoding='gbk',dtype={'证券代码': np.str})
        except Exception as e:
            print(e)
            # continue
        # fee=t['手续费'].sum()+t['印花税'].sum()+t['其他杂费'].sum()
        else:
            # df_list.append(t)
            # result.append(fee)
            df = t
        # df = pd.concat(df_list)
        df = df.reset_index(drop='True')

        # df['成交时间'] = df['成交时间'].map(lambda x: x.zfill(8))
        df['成交日期'] = df['成交日期'].astype(np.str) + df['成交时间']
        # for i in df['成交日期'].values:
        #     try:
        #         x = datetime.datetime.strptime(
        #             i, "%Y%m%d%H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
        #     except Exception as e:
        #         print(e)
        df['成交日期'] = df['成交日期'].map(lambda x: datetime.datetime.strptime(
            x, "%Y%m%d%H:%M:%S").strftime('%Y-%m-%d %H:%M:%S'))
        try:

            df['成交日期'] = pd.to_datetime(df['成交日期'])
        except Exception as e:
            print(e)
        # df=df[df['摘要']!='申购配号']
        # df=df[df['摘要']!='质押回购拆出']
        # df=df[df['摘要']!='拆出质押购回']
        # print(df.info())
        # print(df)
        # print(df['2017-01'])
        # del df['合同编号']
        # del df['备注']

        del df['股东帐户']
        del df['成交时间']

        # del df['结算汇率']
        # del df['Unnamed: 17']
        df=df.fillna(0)
        df=df[(df['操作']!='申购配号') & (df['操作']!='拆出质押购回') & (df['操作']!='质押回购拆出')]
        df = df.sort_values(by='成交日期', ascending=False)
        conn = get_mysql_conn('db_stock', 'local')
        cursor = conn.cursor()
        insert_cmd = '''
        insert into tb_delivery_gj_django (成交日期,证券代码,证券名称,操作,成交数量,成交均价,成交金额,余额,发生金额,手续费,印花税,过户费,本次金额,其他费用,交易市场) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
        check_dup = '''
        select * from tb_delivery_gj_django where 成交日期=%s and 证券代码=%s and 操作=%s and 成交数量=%s and 余额=%s
        '''
        for index, row in df.iterrows():
            date=row['成交日期']
            date=date.to_pydatetime()
            # print(type(date))

            # print(date)
            cursor.execute(check_dup, (date, row['证券代码'], row['操作'], row['成交数量'], row['余额']))
            if cursor.fetchall():
                print('有重复数据,忽略')

            else:
                cursor.execute(insert_cmd, (
                    date, row['证券代码'], row['证券名称'], row['操作'], row['成交数量'], row['成交均价'], row['成交金额'], row['余额'],
                    row['发生金额'], row['手续费'], row['印花税'], row['过户费'], row['本次金额'], row['其他费用'], row['交易市场']))

        conn.commit()
        conn.close()
Esempio n. 23
0
def getinfo(days=-30):
    last_day = datetime.datetime.now() + datetime.timedelta(days=days)

    url = "http://app.cnstock.com/api/waterfall?callback=jQuery19107348148582372209_1557710326005&colunm=qmt-tjd_ggkx&page={}&num=20&showstock=0"

    page = 1
    temp_time = time.strftime("[%Y-%m-%d]-[%H-%M]", time.localtime())

    store_filename = "StockNews-%s.log" % temp_time

    f_open = codecs.open(store_filename, 'w', 'utf-8')
    db_name = 'db_stock'

    conn = get_mysql_conn(db_name, local='local')

    cur = conn.cursor()

    run_flag = True

    while run_flag:
        headers = {'Referer': 'http://ggjd.cnstock.com/company/scp_ggjd/tjd_ggkx',
                   'User-Agent': 'Mozilla/5.0 (Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.131 Safari/537.36', }

        retry = 3
        response = None

        for _ in range(retry):
            try:
                response = requests.get(url=url.format(page), headers=headers)
                response.encoding = 'utf8'
            except Exception as e:
                if hasattr(e, 'code'):
                    logger.info("error code %d" % e.code)
                elif hasattr(e, 'reason'):
                    logger.info("error reason %s " % e.reason)
                time.sleep(5)
            else:
                if response.status_code == 200:
                    break

        try:
            text = response.text.encode('utf8').decode('unicode_escape')
            js = re.search('jQuery19107348148582372209_1557710326005\((.*?)\)$', text, re.S).group(1)
            js = re.sub('\r\n', '', js)
            js_data = json.loads(js)

        except Exception as e:
            logger.error(e)
            return None

        content = js_data.get('data', {}).get('item', {})

        if content is None:
            continue

        for item in content:
            title = item.get('title')

            if '晚间重要公告集锦' in title or '停复牌汇总' in title:
                continue

            link = item.get('link')
            link = link.replace('\\', '')
            pubdate_t = item.get('time')
            pubdate_dtype = datetime.datetime.strptime(pubdate_t, '%Y-%m-%d %H:%M:%S')

            if pubdate_dtype < last_day:
                run_flag = False

            keyword = item.get('keyword')
            if keyword:
                keyword = ' '.join(keyword)

            sub_content = None

            for i in range(2):
                try:
                    sub_content = requests.get(url=link, headers=headers)

                except Exception as e:
                    logger.error(e)
                    continue
                    # 重试

                else:
                    if sub_content.status_code == 200:
                        break

            root = Selector(text=sub_content.text)
            detail_content = root.xpath('//div[@id="qmt_content_div"]')[0].xpath('string(.)').extract_first()
            if detail_content:
                detail_content = detail_content.strip()
            temp_tuple = (pubdate_dtype, title, link, detail_content, keyword)
            insert_sql = 'insert into tb_cnstock (Date,Title,URL,Content,keyword) values (%s,%s,%s,%s,%s)'

            # es
            try:
                pubdate_dtype=pubdate_dtype.strftime("%Y-%m-%d"'T'"%H:%M:%S")
                body = {'Title': title, 'ULR': link, 'keyword': keyword, 'content': detail_content, 'Date': pubdate_dtype}

                es.index(index='cnstock',doc_type='doc',body=body)

            except Exception as e:
                logger.error(e)

            # mysql
            try:
                cur.execute(insert_sql, temp_tuple)
                conn.commit()
            except Exception as e:
                logger.error(e)
                conn.rollback()

            file_content = '{} ---- {}\n{}\n\n'.format(pubdate_t, title, link)
            f_open.write(file_content)

        page += 1

    f_open.close()
Esempio n. 24
0
 def __init__(self):
     self.conn = get_mysql_conn('db_stock', local=True)
     self.cur = self.conn.cursor()
     self.table_name = 'tb_profit'
     self.today = datetime.datetime.now().strftime('%Y-%m-%d')
Esempio n. 25
0
    def history_data(self):

        conn = get_mysql_conn('db_stock', local='local')
        cursor = conn.cursor()

        check_table = '''
        create table if not exists tb_bond_release (
        可转债代码 varchar(10),
        可转债名称 varchar(10),
        集思录建议 varchar(500),
        包销比例 float(6,3),
        中签率 float(6,3),
        上市日期 varchar(20),
        申购户数(万户) int,
        单账户中签(顶格) float(6,3),
        股东配售率 float(6,3),
        评级 varchar(8),
        现价比转股价 float(6,3),
        抓取时间 datetime
        );
        '''
        try:
            cursor.execute(check_table)
            conn.commit()
        except Exception as e:

            logger.error('创建数据库失败{}'.format(e))

        post_data = {
            'cb_type_Y': 'Y',
            'progress': '',
            'rp': 22,
        }
        r = self.download(url=self.pre_release_url, data=post_data)
        # print(r.json())
        js_data = r.json()
        rows = js_data.get('rows')
        for items in rows:
            item = items.get('cell')
            single_draw = item.get('single_draw')
            if single_draw:
                jsl_advise_text = item.get('jsl_advise_text')  # 集思录建议
                underwriter_rt = self.convert_float(
                    item.get('underwriter_rt'))  # 包销比例
                bond_nm = item.get('bond_nm')
                lucky_draw_rt = self.convert_float(
                    item.get('lucky_draw_rt'))  # 中签率
                if lucky_draw_rt:
                    lucky_draw_rt = lucky_draw_rt * 100
                list_date = item.get('list_date')
                valid_apply = self.convert_float(
                    item.get('valid_apply'))  # 申购户数(万户)
                single_draw = self.convert_float(
                    item.get('single_draw'))  # 单账户中签(顶格)
                ration_rt = self.convert_float(item.get('ration_rt'))  # 股东配售率
                rating_cd = item.get('rating_cd')  # 评级
                bond_id = item.get('bond_id')  # 可转债代码
                pma_rt = self.convert_float(item.get('pma_rt'))  # 现价比转股价
                update_time = datetime.datetime.now()

                check_exist = '''
                select * from tb_bond_release where 可转债代码=%s
                '''
                try:
                    cursor.execute(check_exist, (bond_id))
                except Exception as e:
                    logger.error('查询重复数据错误 {}'.format(e))

                else:
                    ret = cursor.fetchall()
                    # 存在则更新
                    if ret:

                        check_update = '''
                                        select * from tb_bond_release where 可转债代码=%s and 包销比例 is null
                                        '''
                        try:
                            cursor.execute(check_update, (bond_id))
                        except Exception as e:
                            logger.error('查询重复数据错误 {}'.format(e))

                        else:
                            ret = cursor.fetchall()
                            if not ret:
                                continue
                            # 更新
                            else:

                                update_sql = '''
                                update tb_bond_release set 包销比例=%s , 上市日期=%s ,抓取时间=%s where 可转债代码 = %s
                                '''
                                try:
                                    update_v = (underwriter_rt, list_date,
                                                update_time, bond_id)
                                    cursor.execute(update_sql, update_v)
                                    conn.commit()
                                except Exception as e:
                                    logger.error(e)

                    # 插入
                    else:
                        insert_sql = '''
                                        insert into tb_bond_release (可转债代码 , 可转债名称 , 集思录建议 , 包销比例 , 中签率 ,上市日期 ,申购户数(万户), 单账户中签(顶格), 股东配售率 ,评级 ,  现价比转股价,抓取时间) 
                                        values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                                        '''
                        v = (bond_id, bond_nm, jsl_advise_text, underwriter_rt,
                             lucky_draw_rt, list_date, valid_apply,
                             single_draw, ration_rt, rating_cd, pma_rt,
                             update_time)
                        try:
                            cursor.execute(insert_sql, v)
                            conn.commit()
                        except Exception as e:
                            logger.error(e)
                            conn.rollback()
Esempio n. 26
0
if _time < '11:30:00':
    today += 'morning'

headers = {
    'Connection': 'keep-alive',
    'Pragma': 'no-cache',
    'Cache-Control': 'no-cache',
    'User-Agent':
    'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36',
    'Accept': '*/*',
    'Referer': 'http://stockapp.finance.qq.com/mstats/?id=fund_close',
    'Accept-Encoding': 'gzip, deflate',
    'Accept-Language': 'zh,en;q=0.9,en-US;q=0.8',
}

conn = get_mysql_conn('db_fund', local='local')
cursor = conn.cursor()


def tencent_info():
    create_table = 'create table if not EXISTS `{}` (`基金代码` varchar(20) PRIMARY KEY,`基金简称` varchar(100),`最新规模-万` float,`实时价格` float,`涨跌幅` float,`成交额-万` float,`净值日期` VARCHAR(10),`单位净值` float,`累计净值` float,`折溢价率` float ,`申购状态` VARCHAR(20),`申赎状态` varchar(20),`基金经理` VARCHAR(200),`成立日期` VARCHAR(20), `管理人名称` VARCHAR(200),`更新时间` VARCHAR(20));'.format(
        today)

    cursor.execute(create_table)
    conn.commit()

    for p in range(1, 114):
        print('page ', p)
        params = (
            ('appn', 'rank'),
            ('t', 'ranklof/chr'),
Esempio n. 27
0
def update_jj(table):
    # table='2020-02-25' # 用于获取code列

    conn = get_mysql_conn('db_fund', local='local')
    # today=datetime.datetime.now().strftime('%Y-%m-%d')
    # print(today)
    logger = llogger(f'{table}_realtime.log')
    query = 'select `基金代码`,`基金简称`,`实时价格` from `{}`'.format(table)
    # print(query)
    cursor = conn.cursor()
    cursor.execute(query)
    session = requests.Session()

    ret = cursor.fetchall()
    url = 'http://web.ifzq.gtimg.cn/fund/newfund/fundSsgz/getSsgz?app=web&symbol=jj{}&_var=LOAD_1582735233556_37'
    add_column1 = 'alter table `{}` add column `实时净值` float'.format(table)
    add_column2 = 'alter table `{}` add column `溢价率` float'.format(table)

    update_sql = 'update `{}` set `实时净值`= %s,`溢价率`=%s where  `基金代码`=%s'.format(
        table)

    try:
        cursor.execute(add_column1)
    except Exception as e:
        conn.rollback()
    else:
        conn.commit()
    try:
        cursor.execute(add_column2)
    except Exception as e:
        conn.rollback()
    else:
        conn.commit()

    for item in ret:
        # pass
        # print(item[0])

        code = item[0]
        realtime_price = item[2]
        s_resp = session.get(url.format(code), headers=headers)
        # print(s_resp.text)
        print()
        print(code)
        content = re.search('LOAD_\d+_\d+=(.*)', s_resp.text).group(1)
        # print(content)
        js = json.loads(content)
        # print(js)
        try:
            data_list = js.get('data').get('data')
        except Exception as e:
            continue

        # print(data_list)
        last_one = data_list[-1]
        time_ = last_one[0]

        jj_ = last_one[1]
        yjl = -1 * round((jj_ - realtime_price) / realtime_price * 100, 2)
        print(f'溢价率-{yjl}')
        cursor.execute(update_sql, (jj_, yjl, code))
        conn.commit()

    conn.close()
Esempio n. 28
0
# 收集可转债的市场全景图
from settings import get_engine,sendmail,llogger,get_mysql_conn
import pandas as pd
import datetime
from config import token
import tushare as ts
today = datetime.datetime.now().strftime('%Y-%m-%d')
today_fmt = datetime.datetime.now().strftime('%Y%m%d')
# today='2020-02-06'
# today_fmt='20200206'
cons=ts.get_apis()
logger=llogger('log/bond_daily.log')
# ts.set_token(token)

# pro = ts.pro_api()
conn=get_mysql_conn('db_bond_daily','local')

def creat_table(day):
    tb_name = 'bond_{}'.format(day)
    create_cmd = 'create table if not exists `{tb_name}` (`date` varchar(20),`code` varchar(10) primary key,`name` varchar(16),`open` float ,' \
                 '`close` float,`high` float,`low` float,`vol` float,`amount` float) '.format(tb_name=tb_name)
    cursor = conn.cursor()
    try:
        cursor.execute(create_cmd)
        conn.commit()
    except Exception as e:
        logger.error(e)
        return False
    else:
        return True
Esempio n. 29
0
# -*-coding=utf-8-*-

# @Time : 2019/7/12 18:41
# @File : transfer_data_es.py

from settings import get_mysql_conn
from elasticsearch import Elasticsearch

es = Elasticsearch('10.18.6.102:9200')
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()

query_cmd = 'select * from tb_cnstock'
cursor.execute(query_cmd)

ret = cursor.fetchall()

for item in ret:
    # print(item)
    date = item[0]
    title = item[1]
    url = item[2]
    content = item[3]
    keyword = item[4]
    body = {
        'Title': title,
        'ULR': url,
        'keyword': keyword,
        'content': content,
        'Date': date
    }
Esempio n. 30
0
def find_zz_zg_diff():
    current=datetime.date.today().strftime('%Y-%m-%d')
    # current ='2019-10-18'
    if ts.is_holiday(current):
        logger.info('假期')
        return

    con=get_mysql_conn('db_stock','local')
    cursor=con.cursor()
    query_cmd = 'select count(*) from tb_bond_jisilu WHERE `正股涨跌幅`>=`可转债涨幅` and `正股涨跌幅`<=0'
    minus_count_cmd = 'select count(*) from tb_bond_jisilu where `可转债涨幅`<0'
    plug_count_cmd = 'select count(*) from tb_bond_jisilu where `可转债涨幅`>=0'

    cursor.execute(query_cmd)
    get_count = cursor.fetchone()
    num=get_count[0]

    cursor.execute(minus_count_cmd)
    minus_count=cursor.fetchone()[0]

    cursor.execute(plug_count_cmd)
    plug_count=cursor.fetchone()[0]

    try:
        search_sql = 'select `溢价率` from `tb_bond_jisilu`'
        cursor.execute(search_sql)

    except Exception as e:
        logger.error(e)

    else:
        content = cursor.fetchall()
        data = []
        for item in content:
            data.append(item[0])
        np_data = np.array(data)
        max_value = np.round(np_data.max(), 2)
        min_value = np.round(np_data.min(), 2)
        mean = np.round(np_data.mean(), 2)
        median = np.round(np.median(np_data), 2)
        count = len(np_data)
        t_value = (current, float(mean), float(max_value), float(min_value), float(median), count)
        update_sql = 'insert into tb_bond_avg_yjl (Date,溢价率均值,溢价率最大值,溢价率最小值,溢价率中位数,转债数目) values (%s,%s,%s,%s,%s,%s)'
        try:
            cursor.execute(update_sql, t_value)
            con.commit()

        except Exception as e:
            logger.error(e)
            con.rollback()
        else:
            logger.info('update')

    cal_query = 'select `可转债涨幅` from tb_bond_jisilu'

    cursor.execute(cal_query)
    cal_result = cursor.fetchall()
    cal_result_list=[]
    for i in cal_result:
        cal_result_list.append(i[0])

    cal_result_np=np.array(cal_result_list)
    large_than_zero = len(cal_result_np[cal_result_np>=0])
    # small_than_zero = len(cal_result_np[cal_result_np<0])
    total_len = len(cal_result_np)
    raise_ratio = round(large_than_zero/total_len*100,2)
    max_v=cal_result_np.max()
    min_v=cal_result_np.min()
    mean=round(cal_result_np.mean(),2)
    median=round(np.median(cal_result_np),2)

    ripple_ratio = round(cal_result_np.var(),2)
    title='{}转债跌大于正股数:{}'.format(current,num)
    content=f'转债上涨比例:{raise_ratio}\n转债跌>正股数:{num}\n可转债涨幅>=0----{plug_count}\n可转债涨幅<0----{minus_count}\n涨幅最大值:{max_v}\n涨幅最小值:{min_v}\n涨幅均值:{mean}\n涨幅中位数:{median}\n涨幅波动的方差:{ripple_ratio}'

    try:
        send_aliyun(title,content,QQ_MAIL)
    except Exception as e:
        logger.error(e)
    else:
        logger.info('发送成功')
        logger.info(content)

    # 写入数据库
    insert_sql = 'insert into tb_bond_analysis (date,转债跌大于正股数量,可转债涨幅大于0,可转债涨幅小于0) values (%s,%s,%s,%s)'
    try:
        cursor.execute(insert_sql,(current,num,plug_count,minus_count))
        con.commit()
    except Exception as e:
        logger.error(e)
        con.rollback()
    else:
        logger.info('入库成功')