def get_date_start_end(self, n): sqlUtil = SqlUtil() sqlUtil.connect(); cursor = sqlUtil.db.cursor(); select_sql = "SELECT code, date FROM stock_history WhERE code = '000001' ORDER BY date desc LIMIT " + str(n) # 002752、300208 rows = []; try: cursor.execute(select_sql) rows = cursor.fetchall() except: print("Error: unable to fetch data") sqlUtil.db.close() rows_count = len(rows) date_start = rows[rows_count - 1][1] date_end = rows[0][1] return date_start, date_end
def update_ma(self, array_ma, n): sqlUtil = SqlUtil() sqlUtil.connect() cursor = sqlUtil.db.cursor() #print(array_ma) array_ma_count = len(array_ma) if array_ma_count: ma_n = "ma" + str(n) update_sql = "UPDATE stock_history SET " + ma_n + " = CASE id " when_str = "" id_str = "" for array_item in array_ma: #print(array_item) when_item = "WHEN " + str(array_item[0]) + " THEN " + str( array_item[1]) + " " when_str = when_str + when_item id_str = id_str + str(array_item[0]) + "," id_str = id_str[0:len(id_str) - 1] update_sql = update_sql + when_str + " END WHERE id IN (" + id_str + ")" try: cursor.execute(update_sql) sqlUtil.db.commit() #print("update_ma sucess"); except Exception as e: # 发生错误时回滚 sqlUtil.db.rollback() #print("update_ma error:" + e); sqlUtil.db.close()
def select_stock(self): sqlUtil = SqlUtil() sqlUtil.connect(); cursor = sqlUtil.db.cursor(); select_sql = 'SELECT code, name, industry FROM stock ' # 002752、300208 rows = []; try: cursor.execute(select_sql) rows = cursor.fetchall() except: print("Error: unable to fetch data") sqlUtil.db.close() return rows
def insert_stock_concepts(self, concept_values): sqlUtil = SqlUtil() sqlUtil.connect() cursor = sqlUtil.db.cursor() insert_sql = "INSERT INTO stock_concept(concept, code, name) VALUES " + concept_values + "" print('insert_stock_concept insert_sql = ' + insert_sql) try: cursor.execute(insert_sql) sqlUtil.db.commit() # print("insert_stock_history sucess"); except Exception as e: # 发生错误时回滚 sqlUtil.db.rollback() print("insert_stock_concept error:" + e) # 关闭数据库连接 sqlUtil.db.close()
def insert_stock_historys(self, history_values): sqlUtil = SqlUtil() sqlUtil.connect(); cursor = sqlUtil.db.cursor(); insert_sql = "INSERT INTO stock_history(date, code, name, close, high, low, open, pre_close, up_down_price, up_down_range, turn_over_rate, bargain_volume, bargain_amount, total_market_value, flow_market_value, bargain_ticket_count) VALUES " + history_values + "" print('insert_stock_historys insert_sql = ' + insert_sql) try: cursor.execute(insert_sql); sqlUtil.db.commit(); # print("insert_stock_history sucess"); except Exception as e: # 发生错误时回滚 sqlUtil.db.rollback(); print("insert_stock_history error:" + e); # 关闭数据库连接 sqlUtil.db.close()
def select_stock_history_by_days(self, codes, date_start, date_end): sqlUtil = SqlUtil() sqlUtil.connect(); cursor = sqlUtil.db.cursor(); select_sql = "SELECT id, date, code, name, close, high, low, open, pre_close, up_down_price, up_down_range, turn_over_rate, bargain_volume, bargain_amount, total_market_value, flow_market_value, bargain_ticket_count FROM stock_history WHERE code in " + codes + " AND date >= \'" + date_start + "\' AND date <= \'" + date_end + "\' ORDER BY code, date desc"; #print('select_sql:'+select_sql) rows = []; try: cursor.execute(select_sql) rows = cursor.fetchall() except Exception: print("select_stock_history_by_codes error:" + Exception) sqlUtil.db.close() return rows
def select_stock_history_by_code(self, code): sqlUtil = SqlUtil() sqlUtil.connect(); cursor = sqlUtil.db.cursor(); select_sql = "SELECT id, date, code, name, close, high, low, open, pre_close, up_down_price, up_down_range, turn_over_rate, bargain_volume, bargain_amount, total_market_value, flow_market_value, bargain_ticket_count FROM stock_history WHERE code = \'" + code + "\' ORDER BY date desc"; print('select_sql:'+select_sql) rows = []; try: cursor.execute(select_sql) rows = cursor.fetchall() except: print("Error: unable to fetch data") sqlUtil.db.close() return rows
def insert_stock_nows(self, now_values): sqlUtil = SqlUtil() sqlUtil.connect() cursor = sqlUtil.db.cursor() insert_sql = "INSERT INTO stock_now(time, code, name, price, high, low, open, pre_close, bargain_volume, bargain_amount) VALUES " + now_values + "" print('insert_stock_nows insert_sql = ' + insert_sql) try: cursor.execute(insert_sql) sqlUtil.db.commit() #print("insert_stock_history sucess"); except Exception as e: # 发生错误时回滚 sqlUtil.db.rollback() print("insert_stock_nows error:" + e) # 关闭数据库连接 sqlUtil.db.close()
def insert_stock_select(self, batch, code, name, type, remark): sqlUtil = SqlUtil() sqlUtil.connect() cursor = sqlUtil.db.cursor() insert_sql = "INSERT INTO stock_select(batch, code, name, type, remark) VALUES ( \'" + batch + "\', \'" + code + "\' , \'" + name + "\' , \'" + type + "\' , \'" + remark + "\')" print(insert_sql) try: cursor.execute(insert_sql) sqlUtil.db.commit() #print("insert_stock_history sucess"); except Exception as e: # 发生错误时回滚 sqlUtil.db.rollback() print("insert_stock_history error:" + e) # 关闭数据库连接 sqlUtil.db.close()