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 __del__(self):
if self._deleteDB:
statement = 'SET FOREIGN_KEY_CHECKS = 0;'
commitDBStatement(self.conn, self.cursor, statement)
statement = 'DROP DATABASE IF EXISTS `{0}`;'.format(self._dbName)
commitDBStatement(self.conn, self.cursor, statement)
log.warning('DB_HANDLE: Database %s deleted.', self._dbName)
self._cursor.close()
self._conn.close()
log.info('DB_HANDLE: Database %s closed.', self._dbName)
def write_tables(self): # Create Tables / Temporarily Turn Off Foreign Key Checks statement = 'SET FOREIGN_KEY_CHECKS = 0;' commitDBStatement(self.conn, self.cursor, statement) # Create Tables / Create Tables table_creation_instr = lib_CreateDB.create_instr_MySQL for (table_name, create_instr) in table_creation_instr.iteritems(): self.__create_table(table_name, create_instr) # Create Tables / Temporarily Turn Off Foreign Key Checks statement = 'SET FOREIGN_KEY_CHECKS = 1;' commitDBStatement(self.conn, self.cursor, statement)
def update_DataSeriesMetaData(conn, cursor, columnName, value, seriesID): ''' ''' statement = __get_update_DataSeriesMetaData_Statement(seriesID, columnName, value) (success, rowID_or_Error) = DB_util.commitDBStatement(conn, cursor, statement) return success
def __drop_table(self, table_name):
statement = 'DROP TABLE `{0}`'.format(table_name)
(success, error) = commitDBStatement(self.conn, self.cursor, statement)
if not success:
log.warning('DB_HANDLE: Failed to drop {0}'.format(table_name))
log.warning('DB_HANDLE: ' + error)
else:
log.warning('DB_HANDLE: Dropped {0}'.format(table_name))
def create_DB(self):
# Create MySQL Connection (Without database)
try:
self._conn = mysq.connect( host=lib_DB.host,
user=lib_DB.username,
password=lib_DB.password,
raise_on_warnings=True)
self._cursor = self._conn.cursor(buffered=True)
except mysq.Error as err:
if err.errno == mysq.errorcode.ER_ACCESS_DENIED_ERROR:
log.error('DB_HANDLE: Database Access Denied.')
log.error('DB_HANDLE: '+err)
raise err
# Create Database
statement = 'CREATE DATABASE IF NOT EXISTS `{0}`;'.format(self._dbName)
commitDBStatement(self.conn, self.cursor, statement)
log.info('DB_HANDLE: Database %s created (or found).', self._dbName)
# Assign Database to MySQL Connection
self._conn.database = self._dbName
self.write_tables()
def __create_table(conn, cursor, tableName, instruction, force=False):
if __table_exists(conn, cursor, tableName):
if force:
log.warning('Dropping Table %s for overwrite.', tableName)
__drop_table(conn, cursor, tableName)
else:
log.error('Table %s already exists', tableName)
log.error('Databases already exist. If overwriting was your intent, use -mode-TEMP flag to force creation.')
return
log.info("Table %s created.", tableName)
(success, error) = commitDBStatement(conn, cursor, instruction)
def __drop_table(conn, cursor, tableName):
'''
Drops a table from a database
PARAMETERS:
conn <sqlite3 connection>
cursor <sqlite3 connection>
tableName <string> a table name
RETURNS:
(success, error) <(bool, string)> tuple of whether successful and what error if not
'''
statement = 'drop table if exists {0};'.format(tableName)
return commitDBStatement(conn, cursor, statement)
def __create_table(self, table_name, create_instr, force=False):
'''
TODO:
force is not being used (nothing passing it in)
'''
if self.__table_exists(table_name):
if force:
log.warning('DB_HANDLE: Dropping Table %s for overwrite.', table_name)
self.__drop_table(table_name)
else:
log.error('DB_HANDLE: Table {0} Exists.'.format(table_name))
log.error('DB_HANDLE: Failed to create {0}. (Force disabled).'.format(table_name))
return
log.info('DB_HANDLE: Creating Table {0}.'.format(table_name))
(success, error) = commitDBStatement(self.conn, self.cursor, create_instr)
if not success:
log.error('DB_HANDLE: Failed to create {0}'.format(table_name))
log.error('DB_HANDLE: ' + error)
else:
log.warning('DB_HANDLE: Created Table {0}.'.format(table_name))
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 replaceStats_DataStatsTable(conn, cursor, resp_data_ID, pred_data_ID, pred_feature_importance, stat_variance, stat_count): insert_statement = __get_insertStat_DataStatsTable_Statement(resp_data_ID, pred_data_ID, pred_feature_importance, stat_variance, stat_count) delete_statement = __get_deleteStats_DataStatsTable_Statement(resp_data_ID, pred_data_ID) statement = [delete_statement, insert_statement] (success, err) = DB_util.commitDBStatement(conn, cursor, statement, failSilently=True) return success
def insertStat_DataStatsTable(conn, cursor, resp_data_ID, pred_data_ID, pred_feature_importance, stat_variance=0, stat_count=1): statement = __get_insertStat_DataStatsTable_Statement(resp_data_ID, pred_data_ID, pred_feature_importance, stat_variance, stat_count) (success, err) = DB_util.commitDBStatement(conn, cursor, statement, failSilently=True) return success
def insertWordPoint_WordHistoryTable(conn, cursor, seriesID, date, value): statement = __get_insertWordPoint_WordHistoryTable_Statement(seriesID, date, value) (success, err) = DB_util.commitDBStatement(conn, cursor, statement, failSilently=True) return success
def update_WordSeriesMetaData(conn, cursor, columnName, value, seriesID): ''' ''' statement = __get_update_WordSeriesMetaData_Statement(seriesID, columnName, value) DB_util.commitDBStatement(conn, cursor, statement)
def insertDataPoint_DataHistoryTable_SQLite(conn, cursor, seriesID, date, value, interpolated=False, forecast=False): statement = __get_insertDataPoint_DataHistoryTable_Statement(seriesID, date, value, interpolated, forecast) (success, err) = DB_util.commitDBStatement(conn, cursor, statement, failSilently=True) return success
def write_tables(self):
log.info('DB_HANDLE: Performing table creation')
for (table_name, instruction) in create_instr_SQLite.iteritems():
(success, error) = commitDBStatement(self.conn, self.cursor, instruction)