def updateMarketDataInfoTableWithEarliestDateTimeFromIB(
self, ib, timeOutTime=10):
"""updates earliestDateTime in the MarketDataInfoTable
Does not use MarketDataInfoTableDataFrame for the looping.
Updates MarketDataInfoTableDataFrame at the end"""
if not (isinstance(ib, IB) and ib.isConnected()):
return None
ssn = self.Session()
table = self.MarketDataInfoTable
# loop over the rows of MarketDataInfoTable
for row in ssn.query(table).order(table.tableName):
tableName = row.tableName
tableORM = self.getTableORMByTablename(tableName)
conId = row.conId
# get the qualified Contract for this conId from IB
qc = utils.getQualifiedContractFromConId(ib=ib,
conId=conId,
timeOutTime=timeOutTime)
# get the earliest DateTime for this conId from IB
eDT = utils.getEarliestDateTimeFromIB(ib, qualifiedContract=qc)
row.earliestDateTime = eDT
pass
ssn.commit()
ssn.close()
self.MarketDataInfoTableDataFrame = self.createMarketDataInfoTableDataFrameFromMarketDataInfoTable(
ib=ib, timeOutTime=timeOutTime)
pass
def instantiateExistingTablesAndClasses(self, ib=None, timeOutTime=10):
"""recreate all the SA classes based on the tables found on disk and fills the MarketDataInfoTableDataFrame"""
# the table MarketDataInfoTable contains all tables with market data info
tableMDIT = self.MarketDataInfoTable
ssn = self.Session()
# loop over rows on disk (do not use MarketDataInfoTableDataFrame)
for row in ssn.query(tableMDIT).order_by(tableMDIT.tableName).all():
tableName = row.tableName
tableORM = self.getTableORMByTablename(tableName)
conId = row.conId
qc = utils.getQualifiedContractFromConId(ib=ib,
conId=conId,
timeOutTime=timeOutTime)
ssn.close()
self.MarketDataInfoTableDataFrame = self.createMarketDataInfoTableDataFrameFromMarketDataInfoTable(
ib=ib, timeOutTime=timeOutTime)
pass
def createMarketDataInfoTableDataFrameFromMarketDataInfoTable(
self, ib=None, timeOutTime=10):
"""this DataFrame has the same columns as the MarketDataInfoTable.
In addition, we add the columns
'talbeORM´, 'qualifiedContract'
Make sure that this function is called each time the MarketDataInfoTable is updated
"""
# if the table does not exist on disk, return None
if not self.DBEngine.has_table('MarketDataInfoTable'):
return None
ssn = self.Session()
# if the table contains no rows, return None
if ssn.query(self.MarketDataInfoTable).count() == 0:
ssn.close()
return None
table = self.MarketDataInfoTable
query = ssn.query(table).order_by(table.tableName)
df = pd.read_sql(query.statement, ssn.bind)
tableNames = [
r.tableName for r in ssn.query(table.tableName).order_by(
table.tableName).all()
]
tableORMs = []
for tableName in tableNames:
tableORMs.append(self.getTableORMByTablename(tableName))
pass
df.loc[:, 'tableORM'] = tableORMs
qcs = None
if (isinstance(ib, IB) and ib.isConnected()):
qcs = [
utils.getQualifiedContractFromConId(ib=ib,
conId=row.conId,
timeOutTime=timeOutTime)
for (indx, row) in df.iterrows()
]
df.loc[:, 'qualifiedContract'] = qcs
ssn.close()
return (df)
def instantiateMyDB(args):
"""instantiate all SQ ORM classes using a config file passed in the arguments"""
# load the config file
configFile = args.configFile
config = ConfigParser(interpolation=ExtendedInterpolation(),
defaults=os.environ)
config.read(configFile)
# create connection to IB
ib = IB()
assert (isinstance(ib, IB))
ib.errorEvent += myErrorCallback
# load data from configFile
a = config.get('MarketData', 'ConIdList')
conIdList = eval(a)
host = config.get('InteractiveBrokers', 'host')
port = config.getint('InteractiveBrokers', 'port')
clientId = config.getint('InteractiveBrokers', 'clientId')
DBType = config.get('DataBase', 'DBType')
DBFileName = config.get('DataBase', 'DBFileName')
timeOutTime = config.getint('InteractiveBrokers',
'timeOutTimeShortRequests')
# override configFile if clientId is given on the command line
if args.clientId is not None:
clientId = args.clientId
# override configFile if timeOutTime is given on the command line
if args.timeOutTime is not None:
timeOutTime = args.TimeOutTime
# connect to interactive brokers
try:
ib.connect(host=host, port=port, clientId=clientId)
except:
import random
clientId = clientId + random.randint(1, 100000)
ib.connect(host=host, port=port, clientId=clientId)
pass
# create database class
mydb = tradingDB(DBType=DBType, DBFileName=DBFileName)
# loop over all conIds defined in the config File and create the sqlalchemy ORM classes
# these tables will appear in the metadata of the DBDeclarativeBase attribute of mydb
# prepare a dataframe that holds all infos that should be put into the MarketDataInfoTable on disk
# and the MarketDataInfoTableDataFrame in memory
nTables = len(conIdList)
featureList = [
'conId',
'qualifiedContract',
'earliestDateTime',
'category',
'kwargs',
'tableName',
'tableORM',
]
dfWithInfoAboutTables = pd.DataFrame(None,
index=range(nTables),
columns=featureList)
dfWithInfoAboutTables.loc[:, 'conId'] = conIdList
df = dfWithInfoAboutTables
for indx in df.index:
conId = df.at[indx, 'conId']
a = (f'about to qualify contract: conId: {conId}')
logging.info(a)
print(a)
qc = utils.getQualifiedContractFromConId(ib=ib,
conId=conId,
timeOutTime=timeOutTime)
df.at[indx, 'qualifiedContract'] = qc
# calculate the earliest Date for this contract
earliestDateTime = utils.getEarliestDateTimeFromIBAsDateTime(
ib=ib, qualifiedContract=qc, timeOutTime=timeOutTime)
df.at[indx, 'earliestDateTime'] = earliestDateTime
# set the category that should be MarketData for the tables to be generated in this loop
category = mydb.tableCategories.MarketData.value
df.at[indx, 'category'] = category
# set the keyword arguments for the call to calculateTableName
kwargs = {}
kwargs['category'] = category
kwargs['earliestDateTime'] = earliestDateTime
kwargs.update(qc.dict())
df.at[indx, 'kwargs'] = kwargs
# calculate the tableName
tableName = mydb.calculateTableName(**kwargs)
df.at[indx, 'tableName'] = tableName
# create the sqlalchemy ORM class; this will write the class to the mydb.DBDeclarativeBase.metadata object
a = (
f'creating MarketData Table: conId: {conId}; tableName: {tableName}'
)
logging.info(a)
print(a)
tableORM = mydb.getTableORMByTablename(tableName=tableName)
df.at[indx, 'tableORM'] = tableORM
pass
# now all the ORM tables should be defined.
# they are not yet created on disk.
# also, the MarketDataInfoTable is not populated and the MarketDataInfoTableDataFrame is not populated
# loop over all conIds defined in the config File and create a row in the Market Data Info Table
# also, populate the corresponding dataframe
# create all tables on disk if they do not yet exist
mydb.createAllTables()
ssn = mydb.Session()
for indx, row in dfWithInfoAboutTables.iterrows():
tableName = row.tableName
print(f'upserting a row for {tableName} to the MarketDataInfoTable')
# create a row for each conId in the MDIT table
# first, instantiate a row in the MarketDataInfoTable
MDIT = mydb.MarketDataInfoTable(tableName=tableName)
# set all available column values
kwargs = row.kwargs
for k, v in kwargs.items():
if k in MDIT.__table__.columns:
setattr(MDIT, k, v)
pass
pass
# upsert this table Row to the table
d = utils.convertTableRowToDict(MDIT)
# # only update values that are not none
# rowOfTableOnDisk = ssn.query(mydb.MarketDataInfoTable).filter(mydb.MarketDataInfoTable.tableName==tableName).first()
# for k,v in d.items():
# if v is None:
# a = None
# print(k,v)
# try:
# a = getattr(rowOfTableOnDisk, 'earliestDateTime', None)
# except:
# pass
#
# d[k] = a
# print(a)
# pass
# pass
ssn.execute(mydb.upsert(mydb.MarketDataInfoTable, [d]))
ssn.commit()
ssn.close()
mydb.MarketDataInfoTableDataFrame = mydb.createMarketDataInfoTableDataFrameFromMarketDataInfoTable(
ib=ib, timeOutTime=timeOutTime)
# disconnect from interactive brokers
ib.disconnect()
return (mydb)