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()