Example #1
0
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
Example #2
0
	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)
Example #3
0
	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)
Example #4
0
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
Example #5
0
	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))
Example #6
0
	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()
Example #7
0
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)
Example #8
0
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)
Example #9
0
	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))
Example #10
0
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
Example #11
0
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
Example #12
0
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
Example #13
0
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
Example #14
0
def update_WordSeriesMetaData(conn, cursor, columnName, value, seriesID):
	'''

	'''
	statement = __get_update_WordSeriesMetaData_Statement(seriesID, columnName, value)
	DB_util.commitDBStatement(conn, cursor, statement)
Example #15
0
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
Example #16
0
	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)