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'
