def query_stock_trade_daily_after_date(code, start_date, size):
"""
查询指定交易日期（包含）后指定数量的数据
:param code:
:param start_date:
:param size:
:return: DafaFrame
`code`, `open`, `high`, `low`, `close`, `volume`, `amount`, `turn_over_ratio`, `increase`, `amplitude`, `trade_date`
"""
sql = """
SELECT
`code`, `open`, `high`, `low`, `close`, `volume`, `amount`, `turn_over_ratio`, `increase`, `amplitude`, `trade_date`
FROM
stock_trade_daily
WHERE
CODE = '{}'
AND trade_date >= '{}'
ORDER BY
trade_date
LIMIT {};
""".format(code, start_date, size)
try:
return pd.read_sql(sql, con=db_util.get_connection())
except:
logger.exception("query_stock_trade_daily_after_date error")
return None
def query_stock_base_to_map():
"""
查询所有Stock信息，并已code为索引
:return: Series
"""
result_series = pd.Series()
sql = 'select code, type, `name`, status, time_to_market from stock_base where type = 1'
try:
cur = db_util.get_connection().cursor()
cur.execute(sql)
res = cur.fetchall()
cur.close()
for r in res:
result_series = result_series.append(
pd.Series({
r[0]: {
'code': r[0],
'type': r[1],
'name': r[2],
'status': r[3],
'time_to_market': r[4]
}
}))
except:
__logger.exception("Query query_stock_base_to_map error")
return result_series
def query_stock_max_trade_date():
"""
获取每一个stock数据最新的交易日期
:return: DafaFrame
code, max_trade_date
"""
# 获取每一个stock数据最新的交易日期
sql = """
SELECT
t1.code ,
t2.max_trade_date
FROM
stock_base t1
LEFT JOIN(
SELECT
CODE ,
max(trade_date) AS max_trade_date
FROM
stock_trade_daily
GROUP BY
CODE
) t2 ON t1. CODE = t2. CODE
WHERE
t1. STATUS = 1
AND t1.type = 1 order by t2.max_trade_date;
"""
try:
return pd.read_sql(sql, con=db_util.get_connection())
except:
logger.exception("query_stock_max_trade_date error")
return None
def query_stock_base_by_code(code):
"""
获取stock基本信息数据
:return:
"""
result = None
sql = 'select code, type, `name`, status, time_to_market from stock_base where code = "{}"'.format(
code)
try:
cur = db_util.get_connection().cursor()
cur.execute(sql)
res = cur.fetchone()
cur.close()
if res:
result = {
'code': res[0],
'type': res[1],
'name': res[2],
'status': res[3],
'time_to_market': res[4]
}
except:
__logger.exception("Query stock_base error")
return result
def query_stock_base():
"""
获取stock基本信息数据
:return:
"""
result_list = []
sql = 'select code, type, `name`, status, time_to_market from stock_base where type = 1'
try:
cur = db_util.get_connection().cursor()
cur.execute(sql)
res = cur.fetchall()
cur.close()
for r in res:
result_list.append({
'code': r[0],
'type': r[1],
'name': r[2],
'status': r[3],
'time_to_market': r[4]
})
except:
__logger.exception("Query stock_base error")
return result_list
def insert_stock_trade_daily_batch(datas):
"""
批量保存数据库交易信息
:param
datas: 交易信息二维数组
数据信息按照以下字段顺序排列：`code`, `open`, `high`, `low`, `close`, `volume`, `amount`, `turn_over_ratio`, `increase`, `amplitude`, `trade_date`
:return:
"""
sql = """insert into stock_trade_daily(`code`, `open`, `high`, `low`, `close`, `volume`, `amount`, `turn_over_ratio`, `increase`, `amplitude`, `trade_date`) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
conn = db_util.get_connection()
try:
cur = conn.cursor()
cur.executemany(sql, datas)
conn.commit()
cur.close()
except:
conn.rollback()
logger.exception("Insert stock_trade_daily_batch error")
def query_stock_trade_daily(code, start_date, end_date):
"""
根据code、起止日期（包含）查询数据，并按照交易日期升序排序
:param code:
:param start_date:
:param end_date:
:return: DafaFrame
`code`, `open`, `high`, `low`, `close`, `volume`, `amount`, `turn_over_ratio`, `increase`, `amplitude`, `trade_date`
"""
sql = 'select `code`, `open`, `high`, `low`, `close`, `volume`, `amount`, `turn_over_ratio`, `increase`, `amplitude`, `trade_date` from stock_trade_daily where `code` = "{code}" and `trade_date` BETWEEN "{start_date}" and "{end_date}" order by `trade_date`'.format(
code=code, start_date=start_date, end_date=end_date)
try:
return pd.read_sql(sql, con=db_util.get_connection())
except:
logger.exception("query_stock_trade_daily error")
return None
def delete_stock_trade_daily_by_trade_date(start_date, end_date):
"""
根据起止日期删除交易数据
:param start_date:
:param end_date:
:return:
"""
sql = 'delete from stock_trade_daily where trade_date BETWEEN "{}" and "{}"'.format(
start_date, end_date)
conn = db_util.get_connection()
try:
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
except:
conn.rollback()
logger.exception("Delete stock_trade_daily error")
def update_stock_status(code, status):
"""
修改stock交易状态
:param code:
:param status: 1: 正常; 2: 停牌
:return:
"""
sql = 'update stock_base set status = {} where code = "{}" and type = 1'.format(
status, code)
conn = db_util.get_connection()
try:
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
except:
conn.rollback()
__logger.exception("Update stock_status error")
def select_one(date):
"""
根据日期查询是否是交易日信息
:param date:
:return:
"""
result = {}
sql = 'select `date`, is_open from stock_trade_calendar where `date` = "{}"'.format(
date)
conn = db_util.get_connection()
try:
cur = conn.cursor()
cur.execute(sql)
res = cur.fetchone()
cur.close()
if len(res) > 0:
result['date'] = str(res[0])
result['is_open'] = res[1]
except:
__logger.exception("Query stock_trade_calendar error")
return result