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)
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)
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)
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))
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()
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))
def break_low(self, date): ''' 筛选出一年内创新低的股票 :param date: 某一天的日期 ‘'2017-11-11 :return: ''' #cmd = 'select * from `{}`'.format(date) df = pd.read_sql_table(date, daily_engine,index_col='index') # **** 这里的index需要删除一个 low_db= get_mysql_conn('db_selection') low_cursor = low_db.cursor() for i in range(len(df)): code = df.loc[i]['code'] cur_low = df.loc[i]['low'] mins_date,mins = self.get_lowest(code, '2017',date) if not mins_date: continue if mins and float(cur_low)<=float(mins) and float(cur_low) !=0.0: print code, print df.loc[i]['name'] print 'year mins {} at {}'.format(mins,mins_date) print 'curent mins ',cur_low create_cmd = 'create table if not exists break_low' \ '(`index` int primary key auto_increment,datetime datetime,code text,name text,low_price float,last_price float, last_price_date datetime);' low_cursor.execute(create_cmd) insert_cmd = 'insert into break_low (datetime,code,name,low_price,last_price,last_price_date) values (%s,%s,%s,%s,%s,%s);' insert_data = (date,code,df.loc[i]['name'],cur_low,mins,mins_date) low_cursor.execute(insert_cmd,insert_data) low_db.commit()
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]
def get_blacklist(self): conn=get_mysql_conn('db_stock','local') cursor = conn.cursor() query = 'select CODE from tb_blacklist' cursor.execute(query) ret = cursor.fetchall() return [i[0] for i in ret]
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)] # print(df) 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] kzz_code = list(df['证券代码'].values) return (kzz_code, kzz_stocks, zg_stocks, kzz_yjl, zg_yjl)
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')
def break_low(self, date): ''' 筛选出一年内创新低的股票 :param date: 某一天的日期 ‘'2017-11-11 :return: ''' #cmd = 'select * from `{}`'.format(date) df = pd.read_sql_table(date, daily_engine, index_col='index') # **** 这里的index需要删除一个 low_db = get_mysql_conn('db_selection') low_cursor = low_db.cursor() for i in range(len(df)): code = df.loc[i]['code'] cur_low = df.loc[i]['low'] mins_date, mins = self.get_lowest(code, '2017', date) if not mins_date: continue if mins and float(cur_low) <= float(mins) and float( cur_low) != 0.0: print code, print df.loc[i]['name'] print 'year mins {} at {}'.format(mins, mins_date) print 'curent mins ', cur_low create_cmd = 'create table if not exists break_low' \ '(`index` int primary key auto_increment,datetime datetime,code text,name text,low_price float,last_price float, last_price_date datetime);' low_cursor.execute(create_cmd) insert_cmd = 'insert into break_low (datetime,code,name,low_price,last_price,last_price_date) values (%s,%s,%s,%s,%s,%s);' insert_data = (date, code, df.loc[i]['name'], cur_low, mins, mins_date) low_cursor.execute(insert_cmd, insert_data) low_db.commit()
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])
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]
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()
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, 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, e: print df print e
def put_to_redis(): r = redis.Redis(host='localhost', port=6379, db=10) conn = get_mysql_conn('db_parker', local=True) cursor = conn.cursor() cmd = 'select `identity_number` from frauds' cursor.execute(cmd) ret = cursor.fetchall() for i in ret: r.lpush('identity', i[0])
def run_sql_script(): conn = get_mysql_conn('db_rocky',local='local') cur=conn.cursor() with open('tb_pressure.sql','r') as f: for line in f: cur.execute(line) conn.commit() conn.close()
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
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('入库成功')
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)
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
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
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
def dongbei(code): dongbei_area = [u'黑龙江', u'吉林', u'辽宁'] 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
def fetch_detail(): db_name = 'db_stock' conn = get_mysql_conn(db_name, local='local') cursor = conn.cursor() query_sql = ''' select * from tb_cnstock where Content is null; ''' user_agent = random.choice(my_useragent) headers = { 'User-Agent': user_agent, 'Host': "ggjd.cnstock.com", 'DNT': '1', 'Accept': 'text/html, application/xhtml+xml, */*', } cursor.execute(query_sql) ret = cursor.fetchall() for item in ret: url = item[2] if url: url = url.strip() try: r = requests.get(url=url, headers=headers) except Exception as e: logger.error(e) continue else: root = etree.HTML(r.text) try: string_list = root.xpath( '//div[@class="main-content text-large"]')[0].xpath( 'string(.)') except Exception as e: print(url) print(e) continue # content = ' '.join(string_list) content = string_list.strip() content = re.sub('缩小文字', '', content) content = re.sub('放大文字', '', content) content = content.strip() update_sql = ''' update tb_cnstock set Content = %s where URL = %s ''' try: cursor.execute(update_sql, (content, url)) conn.commit() except Exception as e: print(e) logger.error(e) conn.rollback()
def remove_row(): r = redis.StrictRedis('localhost', 6379, db=0) db = get_mysql_conn('history') cur = db.cursor() for k in r.keys(): print(k) cmd = 'delete from `{}` where datetime > \'2017-11-16\';'.format(k) try: cur.execute(cmd) db.commit() except: db.rollback() db.close()
def groupcheck(): conn = get_mysql_conn('',local='ali') cur = conn.cursor() cmd = 'select `email` from aws_users group by`email`' cur.execute(cmd) ret = cur.fetchall() domain = {} for i in ret: mail = i[0].split('@')[1] domain.setdefault(mail, 0) domain[mail] += 1 result = sorted(domain.items(), key=lambda x: x[1], reverse=True) print(result[-100:])
def create_db_case(): low_db = get_mysql_conn('db_selection') low_cursor = low_db.cursor() code = '12345' cur_low = 12.22 date = '2017-01-11' name = u'总公司' create_cmd = 'create table if not exists break_low ' \ '(`index` int primary key auto_increment,code text ,name text , cur_low float ,datetime datetime);' low_cursor.execute(create_cmd) insert_cmd = 'insert into break_low (code,name,cur_low,datetime) values (%s,%s,%s,%s);' low_info = (code, name, cur_low, date) low_cursor.execute(insert_cmd, low_info) low_db.commit()
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()
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
def fetch_detail(): db_name = 'db_stock' conn = get_mysql_conn(db_name, local='local') cursor = conn.cursor() query_sql = ''' select * from tb_cnstock where Content is null; ''' user_agent = random.choice(my_useragent) headers = {'User-Agent': user_agent, 'Host': "ggjd.cnstock.com", 'DNT': '1', 'Accept': 'text/html, application/xhtml+xml, */*', } cursor.execute(query_sql) ret = cursor.fetchall() for item in ret: url = item[2] if url: url = url.strip() try: r = requests.get(url=url, headers=headers) except Exception as e: logger.error(e) continue else: root = etree.HTML(r.text) try: string_list = root.xpath('//div[@class="main-content text-large"]')[0].xpath('string(.)') except Exception as e: print(url) print(e) continue # content = ' '.join(string_list) content = string_list.strip() content = re.sub('缩小文字', '', content) content = re.sub('放大文字', '', content) content = content.strip() update_sql = ''' update tb_cnstock set Content = %s where URL = %s ''' try: cursor.execute(update_sql, (content, url)) conn.commit() except Exception as e: print(e) logger.error(e) conn.rollback()
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, e: print e print table[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()
def today(): 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('爬取成功并入库')
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()
def convert_sql(): conn = get_mysql_conn('spider','XGD') cur = conn.cursor() length = r.llen('hlj0706') print(length) for i in r.lrange('hlj0706',0,length): # print(type(i)) # print(type(eval(i))) d = eval(i) name=d.get('name') cid=d.get('identify_number') cmd ='insert into `tb_hljsxr` (`executed_name`,`cidno`)VALUES ("{}","{}")'.format(name,cid) print(cmd) try: cur.execute(cmd) conn.commit() except Exception as e: print(e) conn.close()
def convert_sql(): conn = get_mysql_conn('spider', 'XGD') cur = conn.cursor() length = r.llen('hlj0706') print(length) for i in r.lrange('hlj0706', 0, length): # print(type(i)) # print(type(eval(i))) d = eval(i) name = d.get('name') cid = d.get('identify_number') cmd = 'insert into `tb_hljsxr` (`executed_name`,`cidno`)VALUES ("{}","{}")'.format( name, cid) print(cmd) try: cur.execute(cmd) conn.commit() except Exception as e: print(e) conn.close()
def remote_mysql2(): ''' 可以运行 :return: ''' # with SSHTunnelForwarder( # ('x', 8220), # ssh_password="******", # ssh_username="******", # remote_bind_address=('x', 3306)) as server: # conn = pymysql.connect(host='127.0.0.1', # port=server.local_bind_port, # user='******', # passwd='x', # db='db_parker') conn = get_mysql_conn() cursor = conn.cursor() cursor.execute("SELECT count(*) from tb_houses;") data = cursor.fetchone() conn.close()
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()
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')
def years_gj_each_month_day(self): filename = 'GJ_2019-04-22.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()
import time from elasticsearch import Elasticsearch from elasticsearch import helpers from bs4 import BeautifulSoup from setting import get_mysql_conn es = Elasticsearch(['10.18.6.102:9200']) conn = get_mysql_conn('spider', 'XGD') cursor = conn.cursor() cmd = 'select count(*) from bidlibrary' cursor.execute(cmd) count_num = cursor.fetchall() count = count_num[0][0] bulk = 5 each_bucket = int(count / bulk) sql = 'select * from bidlibrary where source="重庆市政府采购网" limit 200' idx = 0 for i in range(bulk + 1): offset = i * each_bucket word = sql.format(offset, each_bucket) print(word) try: cursor.execute(word) rets = cursor.fetchall() except Exception as e: print(e) continue actions = [] for ret in rets:
import random import time import tushare as ts import pandas as pd import os, datetime, math import numpy as np import logging from setting import get_engine, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, REDIS_HOST, get_mysql_conn import redis from threading import Thread import MySQLdb from collect_data import SaveData engine = get_engine('history') conn = ts.get_apis() MYSQL_DB = 'history' cursor = get_mysql_conn(MYSQL_DB).cursor() # pd.set_option('display.max_rows', None) class Kline(): def __init__(self): logging.info('tushare version: {}'.format(ts.__version__)) path = os.path.join(os.getcwd(), 'data') self.today_date = datetime.datetime.now().strftime('%Y-%m-%d') logging.info(self.today_date) if not os.path.exists(path): os.mkdir(path) os.chdir(path) # self.conn=ts.get_apis()
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()
def getinfo(max_index_use=4, days=-30): last_day = datetime.datetime.now() + datetime.timedelta(days=days) stock_news_site = "http://ggjd.cnstock.com/gglist/search/ggkx/" index = 0 max_index = max_index_use num = 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') all_contents = [] cmd_list = [] db_name = 'db_stock' conn = get_mysql_conn(db_name, local='local') cur = conn.cursor() while index <= max_index: user_agent = random.choice(my_useragent) company_news_site = stock_news_site + str(index) headers = {'User-Agent': user_agent, 'Host': "ggjd.cnstock.com", 'DNT': '1', 'Accept': 'text/html, application/xhtml+xml, */*', } raw_content = "" retry = 6 for _ in range(retry): try: req = requests.get(url=company_news_site, headers=headers) 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) else: if req: raw_content = req.text break if raw_content is None: return try: soup = BeautifulSoup(raw_content, "html.parser") all_content = soup.find_all("span", "time") except Exception as e: logger.info(e) return None for i in all_content: news_time = i.string node = i.next_sibling url = node['href'] try: year = re.findall('tjd_ggkx/(\d+)/', url)[0][:4] except Exception as e: continue news_time_f = datetime.datetime.strptime(year + '-' + news_time, '%Y-%m-%d %H:%M') if news_time_f >= last_day: str_temp = "No.%s \n%s\t%s\n---> %s \n\n" % (str(num), news_time, node['title'], node['href']) all_contents.append(str_temp) f_open.write(str_temp) url = url.strip() try: r = requests.get(url=url, headers=headers) except Exception as e: logger.error(e) continue root = etree.HTML(r.text) try: string_list = root.xpath('//div[@class="main-content text-large"]')[0].xpath('string(.)') except Exception as e: print(url) print(e) string_list='' # content = ' '.join(string_list) content = string_list.strip() content = re.sub('缩小文字', '', content) content = re.sub('放大文字', '', content) content = content.strip() cmd = '''INSERT INTO tb_cnstock (Date,Title,URL,Content) VALUES(\'%s\',\'%s\',\'%s\',\'%s\');''' % ( news_time_f, node['title'].strip(), node['href'].strip(),content) cmd_list.append(cmd) num = num + 1 # itchat.send(str_temp,toUserName=username) # time.sleep(1) # print("index %d" %index) index = index + 1 f_open.close() # if len(all_contents) > 0: # sendmail(''.join(all_contents), temp_time) for i in cmd_list: logger.info(i) try: cur.execute(i) conn.commit() except Exception as e: logger.info(e) conn.rollback() continue conn.close()
# -*-coding=utf-8-*- # @Time : 2019/1/2 16:36 # @File : lagou_data_preset.py # 清洗拉勾数据 from setting import get_mysql_conn conn=get_mysql_conn('db_rocky','local') cursor = conn.cursor() query_postion_id = ''' select t1.positionId,t2.positionName from tb_jobs_details t1,tb_jobs t2 where t1.positionId=t2.positionId ''' cursor.execute(query_postion_id) result = cursor.fetchall() for item in result: positionid=item[0] title=item[1] update_cmd = ''' update tb_jobs_details set jobTitle = %s where positionId = %s ''' try: cursor.execute(update_cmd,(title,positionid)) print('done') except Exception as e: print(e) conn.rollback() conn.commit()
# -*- coding: utf-8 -*- # @Time : 2019/1/19 14:37 # @File : stockholder_info.py # 股东信息获取 import pandas as pd import time import pymysql import tushare as ts import config from setting import get_mysql_conn conn = get_mysql_conn('db_stock', 'local') cursor = conn.cursor() token = config.token ts.set_token(token) pro = ts.pro_api() def get_stock_list(): df = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date') return dict(zip(list(df['ts_code'].values), list(df['name'].values))) # 生产日期 2000到2018 def create_date(): start_date = '20{}0101' end_date = '20{}1231'