def get_stock_list(connect, market, quotations):
marketid = get_marketid(connect, market)
stktype_list = get_stktype_list(quotations)
sql = "select stockid, marketid, code, valid, type from stock where marketid={} and type in {}"\
.format(marketid, stktype_list)
cur = connect.cursor()
a = cur.execute(sql).fetchall()
connect.commit()
cur.close()
return a
def tdx_import_data(connect, market, ktype, quotations, src_dir, dest_dir, progress=ProgressBar):
"""导入通达信指定盘后数据路径中的K线数据。注：只导入基础信息数据库中存在的股票。
:param connect : sqlit3链接
:param market : 'SH' | 'SZ'
:param ktype : 'DAY' | '1MIN' | '5MIN'
:param quotations: 'stock' | 'fund' | 'bond'
:param src_dir : 盘后K线数据路径，如上证5分钟线：D:\\Tdx\\vipdoc\\sh\\fzline
:param dest_dir : HDF5数据文件所在目录
:param progress : 进度显示函数
:return: 导入记录数
"""
add_record_count = 0
market = market.upper()
h5file = open_h5file(dest_dir, market, ktype)
if ktype.upper() == "DAY":
suffix = ".day"
func_import_from_file = tdx_import_day_data_from_file
elif ktype.upper() == "1MIN":
suffix = ".lc1"
func_import_from_file = tdx_import_min_data_from_file
elif ktype.upper() == "5MIN":
suffix = ".lc5"
func_import_from_file = tdx_import_min_data_from_file
marketid = get_marketid(connect, market)
stktype_list = get_stktype_list(quotations)
sql = "select stockid, marketid, code, valid, type from stock where marketid={} and type in {}".format(marketid, stktype_list)
cur = connect.cursor()
a = cur.execute(sql)
a = a.fetchall()
total = len(a)
for i, stock in enumerate(a):
if stock[3] == 0:
if progress:
progress(i, total)
continue
filename = src_dir + "\\" + market.lower() + stock[2]+ suffix
this_count = func_import_from_file(connect, filename, h5file, market, stock)
add_record_count += this_count
if this_count > 0:
if ktype == 'DAY':
update_hdf5_extern_data(h5file, market.upper() + stock[2], 'DAY')
elif ktype == '5MIN':
update_hdf5_extern_data(h5file, market.upper() + stock[2], '5MIN')
if progress:
progress(i, total)
connect.commit()
h5file.close()
return add_record_count
def get_codepre_list(connect, marketid, quotations):
"""获取前缀代码表"""
stktype_list = get_stktype_list(quotations)
sql = "select codepre, type from `hku_base`.`coderuletype` " \
"where marketid={marketid} and type in {type_list}"\
.format(marketid=marketid, type_list=stktype_list)
cur = connect.cursor()
cur.execute(sql)
a = cur.fetchall()
cur.close()
return sorted(a, key=lambda k: len(k[0]), reverse=True)
def import_stock_name(connect, api, market, quotations=None):
"""更新每只股票的名称、当前是否有效性、起始日期及结束日期
如果导入的代码表中不存在对应的代码，则认为该股已失效
:param connect: sqlite3实例
:param api: pytdx接口，必须在函数外进行连接
:param market: 'SH' | 'SZ'
:param quotations: 待导入的行情类别，空为导入全部 'stock' | 'fund' | 'bond' | None
"""
cur = connect.cursor()
newStockDict = {}
pytdx_market = to_pytdx_market(market.upper())
stk_count = api.get_security_count(pytdx_market)
for i in range(int(stk_count / 1000) + 1):
stock_list = api.get_security_list(pytdx_market, i * 1000)
for stock in stock_list:
newStockDict[stock['code']] = stock['name']
marketid = get_marketid(connect, market)
stktype_list = get_stktype_list(quotations)
a = cur.execute(
"select stockid, code, name, valid from stock where marketid={} and type in {}"
.format(marketid, stktype_list))
a = a.fetchall()
oldStockDict = {}
for oldstock in a:
oldstockid, oldcode, oldname, oldvalid = oldstock[0], oldstock[
1], oldstock[2], int(oldstock[3])
oldStockDict[oldcode] = oldstockid
# 新的代码表中无此股票，则置为无效
if (oldvalid == 1) and (oldcode not in newStockDict):
cur.execute("update stock set valid=0 where stockid=%i" %
oldstockid)
# 股票名称发生变化，更新股票名称;如果原无效，则置为有效
if oldcode in newStockDict:
if oldname != newStockDict[oldcode]:
cur.execute("update stock set name='%s' where stockid=%i" %
(newStockDict[oldcode], oldstockid))
if oldvalid == 0:
cur.execute(
"update stock set valid=1, endDate=99999999 where stockid=%i"
% oldstockid)
# 处理新出现的股票
codepre_list = get_codepre_list(connect, marketid, quotations)
today = datetime.date.today()
today = today.year * 10000 + today.month * 100 + today.day
count = 0
for code in newStockDict:
if code not in oldStockDict:
for codepre in codepre_list:
length = len(codepre[0])
if code[:length] == codepre[0]:
count += 1
#print(market, code, newStockDict[code], codepre)
sql = "insert into Stock(marketid, code, name, type, valid, startDate, endDate) \
values (%s, '%s', '%s', %s, %s, %s, %s)" \
% (marketid, code, newStockDict[code], codepre[1], 1, today, 99999999)
cur.execute(sql)
break
#print('%s新增股票数：%i' % (market.upper(), count))
connect.commit()
cur.close()
return count
def tdx_import_stock_name_from_file(connect,
filename,
market,
quotations=None):
"""更新每只股票的名称、当前是否有效性、起始日期及结束日期
如果导入的代码表中不存在对应的代码，则认为该股已失效
:param connect: mysql实例
:param filename: 代码表文件名
:param market: 'SH' | 'SZ'
:param quotations: 待导入的行情类别列表，空为导入全部 'stock' | 'fund' | 'bond' | None
"""
cur = connect.cursor()
newStockDict = {}
with open(filename, 'rb') as f:
data = f.read(50)
data = f.read(314)
while data:
a = struct.unpack('6s 17s 8s 283s', data)
stockcode = a[0].decode()
stockname = a[2].decode(encoding='gbk').encode('utf8')
pos = stockname.find(0x00)
if pos >= 0:
stockname = stockname[:pos]
newStockDict[stockcode] = stockname.decode(encoding='utf8').strip()
data = f.read(314)
marketid = get_marketid(connect, market)
stktype_list = get_stktype_list(quotations)
cur.execute(
"select stockid, code, name, valid from `hku_base`.`stock` where marketid={} and type in {}"
.format(marketid, stktype_list))
a = cur.fetchall()
oldStockDict = {}
for oldstock in a:
oldstockid, oldcode, oldname, oldvalid = oldstock[0], oldstock[
1], oldstock[2], int(oldstock[3])
oldStockDict[oldcode] = oldstockid
# 新的代码表中无此股票，则置为无效
if (oldvalid == 1) and (oldcode not in newStockDict):
cur.execute(
"update `hku_base`.`stock` set valid=0 where stockid=%i" %
oldstockid)
# 股票名称发生变化，更新股票名称;如果原无效，则置为有效
if oldcode in newStockDict:
if oldname != newStockDict[oldcode]:
cur.execute(
"update `hku_base`.`stock` set name='%s' where stockid=%i"
% (newStockDict[oldcode], oldstockid))
if oldvalid == 0:
cur.execute(
"update `hku_base`.`stock` set valid=1, endDate=99999999 where stockid=%i"
% oldstockid)
# 处理新出现的股票
codepre_list = get_codepre_list(connect, marketid, quotations)
today = datetime.date.today()
today = today.year * 10000 + today.month * 100 + today.day
count = 0
for code in newStockDict:
if code not in oldStockDict:
for codepre in codepre_list:
length = len(codepre[0])
if code[:length] == codepre[0]:
count += 1
#print(market, code, newStockDict[code], codepre)
sql = "insert into `hku_base`.`stock` (marketid, code, name, type, valid, startDate, endDate) \
values (%s, '%s', '%s', %s, %s, %s, %s)" \
% (marketid, code, newStockDict[code], codepre[1], 1, today, 99999999)
cur.execute(sql)
break
#print('%s新增股票数：%i' % (market.upper(), count))
connect.commit()
cur.close()
return count
