def add_tick_date(code, date, time, price, pchange, volume, amount, type, id):
"""
sqlalchemy 性能较差，考虑使用原生拼接产生sql
"""
'''
tick_data = TickDate(code=code, date=date, time=time, price=price, pchange=pchange, volume=volume, amount=amount,
type=type, id=id)
session.add(tick_data)
'''
'''
# 还是很慢
engine.execute(TickDate.__table__.insert(), {"code": code, "date": date, "time": time, "price": price,
"change": pchange, "volume": volume, "amount": amount, "type": type,
"id": id})
'''
'''
手工拼sql减少了sql语句的表结构的解析与sql的生成
'''
sql = "INSERT INTO tick_data(ID, CODE, DATE, TIME, PRICE, PCHANGE, VOLUME, AMOUNT, TYPE) VALUES (" \
+ '\'' + str(id) + '\'' + ',' \
+ '\'' + str(code) + '\'' + ',' \
+ '\'' + str(date) + '\'' + ',' \
+ '\'' + str(time) + '\'' + ',' \
+ '\'' + str(price) + '\'' + ',' \
+ '\'' + str(pchange) + '\'' + ',' \
+ '\'' + str(volume) + '\'' + ',' \
+ '\'' + str(amount) + '\'' + ',' \
+ '\'' + str(type) + '\'' \
+ ')'
try:
myDb.data_insert(db, cursor, sql)
except Exception as err:
print("插入失败：", err)
def today_ticks_insert():
"""
插入当日历史分笔
:return:
"""
db = myDb.db_connect()
cursor = db.cursor()
hs300 = get_hs300s()
print('获取当日分笔数据并入库......')
for index, row in hs300.iterrows():
date = datetime.datetime.now().strftime('%Y%m%d')
detail = tick_insert1(code=row['code'], date=date)
if detail is None:
continue
for index1, detail_row in detail.iterrows():
sql = "INSERT INTO tick_data(ID, CODE, DATE, TIME, PRICE, PCHANGE, VOLUME, AMOUNT, TYPE) VALUES (" \
+ '\'' + str(row['code']) + str(date) + str(detail_row['time']).replace(":", "") + '\'' + ',' \
+ '\'' + str(row['code']) + '\'' + ',' \
+ '\'' + str(date) + '\'' + ',' \
+ '\'' + str(detail_row['time']) + '\'' + ',' \
+ '\'' + str(detail_row['price']) + '\'' + ',' \
+ '\'' + str(detail_row['change']) + '\'' + ',' \
+ '\'' + str(detail_row['volume']) + '\'' + ',' \
+ '\'' + str(detail_row['amount']) + '\'' + ',' \
+ '\'' + str(detail_row['type']) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
db.close()
print('当日分笔数据并入库完毕')
return
def hs300_insert():
"""
沪深300成分股入库
:return:
"""
hs300 = tushare_data.get_hs300s()
db = myDb.db_connect()
cursor = db.cursor()
for index, row in hs300.iterrows():
sql = "INSERT INTO INDEX_STOCKS (CODE, NAME, INDEX_TYPE, DATE, WEIGHT) VALUES(" \
+ '\'' + row['code'] + '\'' + ',' \
+ '\'' + row['name'] + '\'' + ',' \
+ '\'' + '300' + '\'' + ',' + '\'' \
+ str(row['date'])[:10] + '\'' + ',' \
+ '\'' + str(row["weight"]) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
db.close()
def daily_today_insert(price_type):
"""
沪深300成份股日线数据入库
daily_info
price_type:string 复权类型
qfq：前复权
hfq：后复权
None：不复权,默认值
:return:
"""
print("插入复前权历史日线数据......")
db = myDb.db_connect()
cursor = db.cursor()
hs300 = get_hs300s()
table_2_insert = price_type + "_daily_info" if (
price_type != "bfq") else "daily_info"
for index, stocks in hs300.iterrows():
ts_code = util.stock_code_change(stocks['code'])
date = datetime.datetime.now().strftime('%Y%m%d')
daily = tushare.pro_bar(ts_code=ts_code,
adj=price_type,
start_date=date)
for index, row in daily.iterrows():
sql = "INSERT INTO " + table_2_insert + "(ID, CODE, TRADE_DATE, OPEN, CLOSE, HIGH, " \
+ "LOW, PRE_CLOSE,PCHANGE, PCT_CHANGE, VOL, AMOUNT) VALUES(" \
+ '\'' + str(row['ts_code'][:6]) + str(row['trade_date'][:10]) + '\'' + ',' \
+ '\'' + str(row['ts_code'][:6]) + '\'' + ',' \
+ '\'' + data_convert(row['trade_date'][:8]) + '\'' + ',' \
+ '\'' + str(row['open']) + '\'' + ',' \
+ '\'' + str(row['close']) + '\'' + ',' \
+ '\'' + str(row['high']) + '\'' + ',' \
+ '\'' + str(row['low']) + '\'' + ',' \
+ '\'' + str(row['pre_close']) + '\'' + ',' \
+ '\'' + str(row['change']) + '\'' + ',' \
+ '\'' + str(row['pct_chg']) + '\'' + ',' \
+ '\'' + str(row['vol']) + '\'' + ',' \
+ '\'' + str(row['amount']) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
cursor.close()
print("完成插入前复权历史日线数据")
def hist_daily_insert(price_type):
"""
沪深300成份股历史日线数据入库
daily_info
:return:
"""
print("插入历史复权日线数据......:", price_type)
db = myDb.db_connect()
cursor = db.cursor()
hs300 = get_hs300s()
table_2_insert = price_type + "_daily_info" if (
price_type != "bfq") else "daily_info"
for index, stocks in hs300.iterrows():
ts_code = util.stock_code_change(stocks['code'])
daily = tushare.pro_bar(ts_code=ts_code, adj=price_type)
for index1, row in daily.iterrows():
if row['trade_date'][:8] < '20180630':
continue
sql = "INSERT INTO " + table_2_insert + "(ID, CODE, TRADE_DATE, OPEN, CLOSE, HIGH, LOW, PRE_CLOSE," \
+ "PCHANGE, PCT_CHANGE, VOL, AMOUNT) VALUES(" \
+ '\'' + str(row['ts_code'][:6]) + str(row['trade_date'][:10]) + '\'' + ',' \
+ '\'' + str(row['ts_code'][:6]) + '\'' + ',' \
+ '\'' + str(row['trade_date'][:8]) + '\'' + ',' \
+ '\'' + str(row['open']) + '\'' + ',' \
+ '\'' + str(row['close']) + '\'' + ',' \
+ '\'' + str(row['high']) + '\'' + ',' \
+ '\'' + str(row['low']) + '\'' + ',' \
+ '\'' + str(row['pre_close']) + '\'' + ',' \
+ '\'' + str(row['change']) + '\'' + ',' \
+ '\'' + str(row['pct_chg']) + '\'' + ',' \
+ '\'' + str(row['vol']) + '\'' + ',' \
+ '\'' + str(row['amount']) + '\'' \
+ ')'
myDb.data_insert(db, cursor, sql)
cursor.close()
print("完成插入历史复权日线数据:", price_type)
return