def saveTB_DAILY(strDB, strTB, df, listColumnIndex=['code', 'trade_date']):
# save to temporary table
MySQLDBAPI.dropTable(strDB, strTB_TEMP)
MySQLDBAPI.save(strDB, strTB_TEMP, df)
strIndex = '_'.join(listColumnIndex)
strIndex = strIndex[:15]
sqlIndexExists = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE `TABLE_CATALOG` = 'def' AND `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = '{0}' AND `INDEX_NAME` = '{1}';".format(
strTB, strIndex)
con = MySQLDBAPI.connect(strDB)
df = pd.read_sql(sqlIndexExists, con)
con.dispose()
con = MySQLDBAPI.connectMDB(strDB)
cur = con.cursor()
if df.empty:
# insert 1 row
sqlCreate = "CREATE TABLE IF NOT EXISTS {0} SELECT * FROM {1} WHERE code IS NOT NULL LIMIT 1;".format(
strTB, strTB_TEMP)
cur.execute(sqlCreate)
# not null
sqlAlterColumnTradingDay = "ALTER TABLE {0} MODIFY trade_date datetime NOT NULL".format(
strTB)
cur.execute(sqlAlterColumnTradingDay)
sqlAlterColumnCode = "ALTER TABLE {0} MODIFY code varchar(30) NOT NULL".format(
strTB)
cur.execute(sqlAlterColumnCode)
for strColumn in set(listColumnIndex).difference(
set(['code', 'trade_date'])):
if strColumn.startswith('str') or strColumn in [
'freq', 'ContractRange'
]:
sql = "ALTER TABLE {0} MODIFY {1} varchar(20) NOT NULL".format(
strTB, strColumn)
else:
sql = "ALTER TABLE {0} MODIFY {1} double".format(
strTB, strColumn)
cur.execute(sql)
# add unique index if necessary
sqlAddIndexUnique = "CREATE UNIQUE INDEX {0} ON {1} ({2})".format(
strIndex, strTB, ','.join(listColumnIndex))
cur.execute(sqlAddIndexUnique)
# add non-unique index
for column in listColumnIndex:
sqlAddIndexOne = "CREATE INDEX {0} ON {1} ({0})".format(
column, strTB)
cur.execute(sqlAddIndexOne)
# temp -> formal table
print 'insert from TEMP to FORMAL'
cur.execute("INSERT IGNORE INTO {0} SELECT * FROM {1}".format(
strTB, strTB_TEMP))
con.commit()
cur.close()
con.close()
def clearTBFakeTomorrow(strDB, strTB):
con = MySQLDBAPI.connectMDB(strDB)
cur = con.cursor()
sql = 'delete from {0} where trade_date="{1}";'.format(strTB, dtTomorrow)
cur.execute(sql)
con.commit()
cur.close()
con.close()
return