def insert_stock_brief(stock_brief):
conn = get_sql_conn()
sql = ('insert into StockBrief (codeId, name) values ('
+ '\'' + stock_brief.code_id + '\'' + ", "
+ '\'' + stock_brief.name + '\'' + ");")
conn.execute(sql)
conn.commit()
def select_net_present_value(stock_id, season):
conn = get_sql_conn()
ret_list = []
str_sql = NetPresentValueTable.gen_net_present_value_sql(stock_id, season)
try:
cursor = conn.execute(str_sql)
for row in cursor:
net_present_item = NetPresentValue()
net_present_item.stock_id = row[0]
net_present_item.year = row[1]
net_present_item.season = row[2]
net_present_item.cash_flow_value = row[3]
net_present_item.profit_value = row[4]
if net_present_item.profit_value <= 0 or net_present_item.cash_flow_value <= 0:
net_present_item.net_present_value = -100.0
else:
net_present_item.net_present_value = 1.0 * net_present_item.cash_flow_value / net_present_item.profit_value
ret_list.append(net_present_item)
except Exception:
pass
return ret_list
def insert_k_line_list(k_line_list, table_id):
conn = get_sql_conn()
list_len = len(k_line_list)
for i in range(0, list_len):
k_line_item = k_line_list[i]
sql = KLineTable.gen_insert_sql(k_line_item, table_id)
conn.execute(sql)
conn.commit()
def get_stock_id_list():
ret_list = []
conn = get_sql_conn()
sql = 'select codeId from StockBrief order by codeId;'
cursor = conn.execute(sql)
for row in cursor:
code_id = row[0]
ret_list.append(code_id)
return ret_list
def insert_buy_recd_list(buy_recd_list):
conn = get_sql_conn()
list_len = len(buy_recd_list)
for i in range(0, list_len):
sql = KLineBuyRecdTable.gen_insert_sql(buy_recd_list[i])
try:
conn.execute(sql)
except Exception:
pass
conn.commit()
def insert_profit_recd_list(profit_recd_list):
conn = get_sql_conn()
list_len = len(profit_recd_list)
for i in range(0, list_len):
sql = ProfitRecdTable.gen_insert_sql(profit_recd_list[i])
try:
conn.execute(sql)
except Exception:
pass
conn.commit()
def insert_cash_flow_recd_list(cash_flow_recd_list):
conn = get_sql_conn()
list_len = len(cash_flow_recd_list)
for i in range(0, list_len):
sql = CashFlowRecdTable.gen_insert_sql(cash_flow_recd_list[i])
try:
conn.execute(sql)
except Exception:
pass
conn.commit()
def select_stock_recd(code_id):
profit_recd_list = []
conn = get_sql_conn()
str_sql = ProfitRecdTable.gen_select_stock_is_profitable(code_id)
cursor = conn.execute(str_sql)
for row in cursor:
item = ProfitRecd()
item.codeId = row[0]
item.year = row[1]
item.season = row[2]
item.value = row[3]
item.recd_type_id = row[4]
profit_recd_list.append(item)
return profit_recd_list
def select_records(cash_flow_recd):
cash_flow_recd_list = []
conn = get_sql_conn()
str_sql = CashFlowRecdTable.gen_select_sql(cash_flow_recd)
cursor = conn.execute(str_sql)
for row in cursor:
item = CashFlowRecd()
item.codeId = row[0]
item.year = row[1]
item.season = row[2]
item.value = row[3]
item.recd_type_id = row[4]
cash_flow_recd_list.append(item)
return cash_flow_recd_list
def select_holding_buy_recd_list_by_date(date):
conn = get_sql_conn()
ret_list = []
str_sql = KLineBuyRecdTable.gen_holding_select_sql_by_date(date)
cursor = conn.execute(str_sql)
for row in cursor:
recd = KLineBuyRecd()
recd.code_id = row[0]
recd.buy_date = row[1]
recd.sell_date = row[2]
recd.buy_price = row[3]
recd.sell_price = row[4]
recd.days = row[5]
ret_list.append(recd)
return ret_list
def select_holding_code():
recd_list = []
conn = get_sql_conn()
str_sql = 'select codeId,buyDate,sellDate,buyPrice,sellPrice,takeDays from KLineBuyRecdTable where sellDate ' \
'is \'None\' '
cursor = conn.execute(str_sql)
for row in cursor:
recd = KLineBuyRecd()
recd.code_id = row[0]
recd.buy_date = row[1]
recd.sell_date = row[2]
recd.buy_price = row[3]
recd.sell_price = row[4]
recd.days = row[5]
recd_list.append(recd)
return recd_list
def select_k_line_list(code_id, table_id, start_date=datetime(1800, 1, 1), end_date=datetime.now()):
k_line_list = []
conn = get_sql_conn()
sql = KLineTable.gen_select_sql(code_id, table_id, start_date, end_date)
cursor = conn.execute(sql)
for row in cursor:
k_line = KLine()
k_line.codeId = row[0]
k_line.year = row[1]
k_line.month = row[2]
k_line.day = row[3]
k_line.open = row[4]
k_line.close = row[5]
k_line.high = row[6]
k_line.low = row[7]
k_line.takeover = row[8]
k_line.cost = row[9]
k_line.price = row[10]
k_line_list.append(k_line)
return k_line_list
def clear_brief_table():
conn = get_sql_conn()
sql = 'delete from StockBrief'
conn.execute(sql)
conn.commit()
def clear_table():
conn = get_sql_conn()
sql = 'delete from KLineBuyRecdTable;'
conn.execute(sql)
conn.commit()
def clear_cash_flow_recd_table():
conn = get_sql_conn()
sql = 'delete from CashFlowRecd'
conn.execute(sql)
conn.commit()
def clear_profit_recd_table():
conn = get_sql_conn()
sql = 'delete from ProfitRecd'
conn.execute(sql)
conn.commit()
def clear_k_line_table(table_id):
conn = get_sql_conn()
sql = ('delete from ' + KLineTable.get_table_name(table_id))
conn.execute(sql)
conn.commit()
