def retrieve_DataSeriesID(conn, cursor, name=None, ticker=None, insertIfNot=False):
'''
TODO:
Incorporate idea of rollbacks.
rowID_or_Error working differently in mySQL.
This got messy with fixes.
'''
statement = __get_retrieve_DataSeriesID_Statement(name=name, ticker=ticker)
(success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=1, expectedCount=1)
if success:
return results
else:
if insertIfNot:
assert name is not None
assert ticker is not None
log.info('DATABASE: Series ID Not Found for %s... Creating.', ticker)
statement = __get_insert_DataSeriesID_Statement(name, ticker)
(success, rowID_or_Error) = DB_util.commitDBStatement(conn, cursor, statement)
if success:
statement = __get_insert_DataSeriesID_Metadata(rowID_or_Error)
(success, junk_last_id) = DB_util.commitDBStatement(conn, cursor, statement) # mySQL doesn't return good lastrowid when no autoincrement.
if success:
return rowID_or_Error # a row ID
if not success:
log.error('DATABASE: Series ID Not Created for %s. Error:\n%s', ticker, rowID_or_Error)
raise Exception('Series ID Failed to be Created')
else:
return None
def retrieve_WordSeriesMetaData(cursor, columnName, seriesID): ''' ''' statement = __get_retrieve_WordSeriesMetaData_Statement(seriesID, columnName) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=1, expectedCount=1) return results # Don't care about success. If not successful, will fail with error
def retrieve_DataSeries_Filtered( cursor, column=ID, minDate=None, maxDate=None, atLeastMinDate=None, atLeastMaxDate=None, periodicity=None, categorical=None, limit_=None, order_=False): leftTable = 'T_DATA_SERIES' rightTable = 'T_DATA_SERIES_METADATA' joinCol = 'int_data_series_ID' sT = ['T_DATA_SERIES'] if column == ID: sC = ['int_data_series_ID'] elif column == TICKER: sC = ['txt_data_ticker'] else: raise NameError # Fill In Where Columns wT = [] wC = [] wV = [] wO = [] args = [minDate, maxDate, atLeastMinDate, atLeastMaxDate, periodicity, categorical] cols = ['dt_min_data_date', 'dt_max_data_date', 'dt_min_data_date', 'dt_max_data_date', 'code_local_periodicity', 'bool_data_is_categorical'] ops = ['>=', '<=', '<=', '>=', '=', '='] for (arg, col, op) in zip(args, cols, ops): if arg is not None: wT.append(DataColumnTableLink[col]) wC.append(col) wV.append(arg) wO.append(op) # Implement Ordering and Limits order_ = ([sC], 'ASC') if order_ else None # Generate and Execute Statement statement = DB_util.generateJoinedSelectStatement( leftTable, rightTable, joinCol, joinCol, sC, sT, wC, wV, wT, wO, order_=order_, limit_=limit_) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=1) return results # Don't care about success. If not successful, will fail with error
def retrieve_DataSeries_All(cursor, column=ID, limit_=None, order_=False): table = 'T_DATA_SERIES' # Find Select Column if column == ID: sC = ['int_data_series_ID'] elif column == TICKER: sC = ['txt_data_ticker'] else: raise NameError # Implement Ordering order_ = (sC, 'ASC') if order_ else None # Generate and Execute Statement statement = DB_util.generateSelectStatement(table, selectColumns=sC, order_=order_, limit_=limit_) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=1) return results # Don't care about success. If not successful, will fail with error
def __table_exists(conn, cursor, tableName):
'''
Checks for a table within a database
PARAMETERS:
conn <sqlite3 connection>
cursor <sqlite3 connection>
tableName <string> a table name
RETURNS:
<bool> whether a table exists
'''
statement = 'select name from sqlite_master where type="table" and name = "{0}";'.format(tableName)
(success, name) = retrieveDBStatement(cursor, statement, expectedColumnCount=1, expectedCount=1)
return success and (name == tableName) #Second part not strictly necessary, as select will fail if not found
def retrieve_WordSeriesID(conn, cursor, seriesName, insertIfNot=False):
'''
'''
statement = __get_retrieve_WordSeriesID_Statement(seriesName)
(success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=1, expectedCount=1)
if success:
return results
else:
if insertIfNot:
log.info('DATABASE: Word Series ID Not Found for %s... Creating.', seriesName)
statement = __get_insert_WordSeriesID_Statement(seriesName)
(success, rowID_or_Error) = DB_util.commitDBStatement(conn, cursor, statement)
if success:
return rowID_or_Error # a row ID
else:
log.error('DATABASE: Series ID Not Created for %s. Error:\n%s', seriesName, rowID_or_Error)
raise Exception('Word Series ID Failed to be Created')
else:
return None
def __table_exists(self, table_name):
statement = 'SHOW TABLES LIKE "{0}"'.format(table_name)
(success, name) = retrieveDBStatement(self.cursor, statement, expectedColumnCount=1, expectedCount=1)
return success and (name == table_name) #Second part not strictly necessary, as select will fail if not found
def retrieveAllStats_DataStatsTable(cursor, resp_data_ID): statement = __get_retrieveAllStats_DataStatsTable_Statement(resp_data_ID) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=2, expectedCount=None) return results # Don't care about success. If not successful, will fail with error
def retrieveStats_ModelStatsTable(cursor, resp_word_ID, model_ID): statement = __get_retrieve_Stat_ModelStatsTable_Statement(resp_word_ID, model_ID) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=3, expectedCount=None) return results # Don't care about success. If not successful, will fail with error
def getCompleteWordHistory_WordHistoryTable(cursor, seriesID): statement = __get_completeWordHistory_WordHistoryTable_Statement(seriesID) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=2, expectedCount=None) return results # Don't care about success. If not successful, will fail with error
def getCompleteDataHistory_DataHistoryTable(cursor, seriesID, selectCount=False): statement = __get_completeDataHistory_DataHistoryTable_Statement(seriesID, selectCount=selectCount) expectedCount = 1 if selectCount else None (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=2, expectedCount=expectedCount) return results # Don't care about success. If not successful, will fail with error
def retrieve_DataSeriesTicker(cursor, seriesID): statement = __get_retrieve_DataSeriesTicker_Statement(seriesID) (success, results) = DB_util.retrieveDBStatement(cursor, statement, expectedColumnCount=1, expectedCount=1) return results