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