def insert_db(rows):
err_row = None
try:
i = 0
with db_utils.get_connect() as con:
cur = con.cursor()
for row in rows:
err_row = row
result = cur.execute(
"""insert into cb_index_history( date, mid_price, avg_premium) values(:date, :mid_price, :avg_premium)""",
{
'date': row['date'],
'mid_price': row['mid_price'],
'avg_premium': row['avg_premium']
})
if result.rowcount != 1:
print('insert db_index is failure. row:' + row['date'])
else:
i += 1
print("insert db_index is complete. count:" + str(i))
except Exception as e:
# cur_file.close()
print("db操作出现异常. err_row" + str(err_row), e)
raise e
def query_database_view():
table_html = ''
sql_code = ''
table_height_style = ''
if len(request.form) > 0:
sql_code = request.form['sql_code']
if sql_code is None or sql_code.strip(' ') == '':
raise Exception('SQL不能为空')
if not sql_code.lower().strip().startswith('select'):
raise Exception("仅允许select操作")
db_type = request.form['db_type']
cur = None
if db_type == 'daily':
with get_daily_connect() as con:
cur = con.cursor()
cur.execute(sql_code)
else:
with get_connect() as con:
cur = con.cursor()
cur.execute(sql_code)
table = from_db_cursor(cur)
if table.rowcount > 10:
table_height_style = """style="height:500px" """
table_html = utils.table_html_utils.build_table_html(table)
return render_template("query_database.html",
table_html=table_html,
sql_code=sql_code,
table_height_style=table_height_style)
def save_db_data():
# 删除整个db
if os.path.exists(db_file_path):
os.unlink(db_file_path)
# 获取文件(字符串?)
file = request.files['file']
s = file.read().decode('utf-8')
# 灌入上传的数据
with get_connect() as con:
con.executescript(s)
return 'OK'
def download_cb_data():
today = datetime.now()
ymd = today.strftime('%Y-%m-%d')
file_name = 'dump/data_' + ymd + '.sql'
with open(file_name, 'w') as f:
with get_connect() as con:
for line in con.iterdump():
f.write('%s\n' % line)
# 需要知道2个参数, 第1个参数是本地目录的path, 第2个参数是文件名(带扩展名)
directory = os.getcwd() # 假设在当前目录
return send_from_directory(directory, file_name, as_attachment=True)
def execute_sql():
sql_code = request.form['sql_code']
db_type = request.form['db_type']
if sql_code is None or sql_code.strip(' ') == '':
raise Exception('SQL不能为空')
if not sql_code.lower().strip().startswith('update') and not sql_code.lower().strip().startswith('insert'):
raise Exception("仅允许update/insert操作")
if db_type == 'daily':
with get_daily_connect() as con:
con.executescript(sql_code)
else:
with get_connect() as con:
con.executescript(sql_code)
return 'OK'
def update_idx_data():
driver = get_chrome_driver("https://www.jisilu.cn/data/cbnew/cb_index/",
15)
table = driver.find_element_by_id('table_cb_index_body')
s = table.text
lines = s.splitlines()
rows = []
for line in lines:
ss = line.split()
d = ss[0]
idx_data = ss[1]
row = {'date': d, 'idx_data': idx_data}
rows.append(row)
driver.close()
err_row = None
try:
i = 0
with db_utils.get_connect() as con:
cur = con.cursor()
for row in rows:
err_row = row
result = cur.execute(
"""update cb_index_history set idx_data = :idx_data where strftime('%Y-%m-%d', date) = :date""",
{
'date': row['date'],
'idx_data': row['idx_data']
})
if result.rowcount != 1:
print('update db_index is failure. row:' + row['date'])
else:
i += 1
print("update db_index is complete. count:" + str(i))
except Exception as e:
# cur_file.close()
print("db操作出现异常. err_row" + str(err_row), e)
raise e
def create_db():
with db_utils.get_connect() as con:
# 只执行一次
con.executescript("""
drop table if exists stock_report;
create table if not exists stock_report(
id INTEGER PRIMARY KEY AUTOINCREMENT,
bond_code text NOT NULL,
cb_name_id text NOT NULL,
stock_code text NOT NULL,
stock_name text NOT NULL,
last_date text NOT NULL,
revenue real,
qoq_revenue_rate real,
yoy_revenue_rate real,
net real,
qoq_net_rate real,
yoy_net_rate real,
margin real,
qoq_margin_rate real,
yoy_margin_rate real,
roe real,
qoq_roe_rate real,
yoy_roe_rate real,
al_ratio real,
qoq_rl_ratio_rate real,
yoy_al_ratio_rate real,
pe real
)""")
print("create db is successful")
def create_db():
# 使用:memory:标识打开的是内存数据库
# con = sqlite3.connect(":memory:")
with db_utils.get_connect() as con:
# 使用executescript可以执行多个脚本
con.executescript("""
drop table if exists changed_bond;
create table if not exists changed_bond(
id INTEGER PRIMARY KEY AUTOINCREMENT,
cb_num_id int NOT NULL,
bond_code text NOT NULL,
cb_name_id text NOT NULL,
bond_date_id text NOT NULL,
stock_code text NOT NULL,
stock_name text NOT NULL,
industry text NOT NULL,
sub_industry text NOT NULL,
cb_price2_id real NOT NULL,
cb_mov2_id real NOT NULL,
cb_mov3_id real NOT NULL,
stock_price_id real NOT NULL,
cb_mov_id real NOT NULL,
cb_price3_id real NOT NULL,
cb_strike_id real NOT NULL,
cb_premium_id real NOT NULL,
cb_value_id real NOT NULL,
cb_t_id text NOT NULL,
bond_t1 text NOT NULL,
red_t text NOT NULL,
remain_amount real NOT NULL,
cb_trade_amount_id real NOT NULL,
cb_trade_amount2_id real NOT NULL,
cb_to_share real NOT NULL,
cb_to_share_shares real NOT NULL,
market_cap real NOT NULL,
stock_pb real NOT NULL,
BT_yield real NOT NULL,
AT_yield real NOT NULL,
BT_red real,
AT_red real,
npv_red real NOT NULL,
npv_value real NOT NULL,
rating text NOT NULL,
discount_rate real NOT NULL,
elasticity real NOT NULL,
cb_ol_value real NOT NULL,
cb_ol_rank int NOT NULL,
cb_nl_value real NOT NULL,
cb_nl_rank int NOT NULL,
cb_ma20_deviate real NOT NULL,
cb_hot int NOT NULL,
duration real,
enforce_get text,
buy_back int,
down_revise int,
data_id INTEGER,
pinyin text,
declare_desc text,
enforce_start_date date, -- 强赎起始日
enforce_stop_date date, -- 不强赎截止日
enforce_declare_date date, -- 强赎宣告日
enforce_last_date date, -- 强赎最后交易日
enforce_price real -- 强赎价格
)""")