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: sqlite3实例
: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)
a = cur.execute("select marketid from market where market = '%s'" %
market.upper())
marketid = [i for i in a]
marketid = marketid[0][0]
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