db.py

"""
PythonDBAGraphs: Graphs to help with Oracle Database Tuning
Copyright (C) 2016  Robert Taft Durrett (Bobby Durrett)

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.

Contact:

bobby@bobbydurrettdba.com

db.py

Oracle database related code

"""

import cx_Oracle
import sys

# Flag to show SQL statements as they are executed or not.
# Either N or Y.

showsql='N'

# Flag to show data returned from queries or not.
# Either N or Y.

showdata='N'

# connection is single connection to database
# single open cursor

class connection:
    def __init__(self,username,password,database):
        """ Login to database and open cursor """
        connect_string = username+'/'+password+'@'+database
        try:
            self.con = cx_Oracle.connect(connect_string)
        except cx_Oracle.DatabaseError as e:
            print("Error logging in: "+str(e.args[0]))
            print("Username: "+username)
            print("Database: "+database)
            sys.exit(-1)
        self.cur = self.con.cursor()
        self.column_names=[]
    
    def __del__(self):
        """ Close cursor and connection """
        try:
            self.cur.close()
            self.con.close()
        except:
            return

    def print_data(self,column_names,data_list):
        """ 
        Print a nicely formatted version of
        the data returned by the query.
        """

# Get the maximum length of each column as a string

        num_columns = len(column_names)

# Initialize lengths to column lengths

        max_lengths=[]
        for cn in range(num_columns):
            max_lengths.append(len(column_names[cn]))
            
# Loop through entire list

        for d in data_list:
            for cn in range(num_columns):
                data_length = len(str(d[cn]))
                if data_length > max_lengths[cn]:
                   max_lengths[cn] = data_length
                   
# Print column names padding for max lengths 

        column_name_header=""
        for cn in range(num_columns):
            column_name_header += column_names[cn].rjust(max_lengths[cn]) + " "
        print(column_name_header)
            
# Print data with same padding
        for d in data_list:
            data_line=""
            for cn in range(num_columns):
                data_line += str(d[cn]).rjust(max_lengths[cn]) + " "
            print(data_line)
        
    def run_return_all_results(self,query):
        """ 
        Run the SQL Query and return all of the results.
        Returns a list of tuples.
        Each tuple contains one row of query output.
        """
        if showsql == 'Y':
            print(query)
            
        self.cur.execute(query)
        
        returned_list = []
       
        for result in self.cur:
            returned_list.append(result)
        
        self.column_names=[]
        for d in self.cur.description:
            self.column_names.append(d[0])
            
        if showdata == 'Y':
            self.print_data(self.column_names,returned_list)

        return returned_list
 
    def get_column_names(self):
        """ 
        Return a list of the names of the
        columns returned by the query.
        """
        return self.column_names

    def run_return_no_results(self,non_query):
        """ 
        Execute a SQL statement that does not
        return any results such as DDL, DML.
        In other words run something that is not
        a query.
        """
        if showsql == 'Y':
            print(non_query)

        self.cur.execute(non_query)
        return

    def commit(self):
        """ Commit all current changes to the database """
        self.con.commit()
        
    def run_return_flipped_results(self,query):
        """
        Method run_return_all_results returns the query results as a 
        list of tuples. For graphing it can help to have each column's 
        values in a list by itself. So this method returns the query 
        results as a list of lists of column values.
        
        r is a list of tuples each tuple is (c1,c2,...)
        want a list of lists of columns
        [[c1 c1 c1 ...][c2 c2 c2 ...]...]
        
        """
        r = self.run_return_all_results(query)
        
        if len(r) == 0:
            return None
        
        number_columns = len(r[0])

        # create a list of empty lists
    
        list_output = []

        for i in range(number_columns):
            list_output.append([])
    
        # append each column to its list

        for curr_row in r:
            for i in range(number_columns):
               list_output[i].append(curr_row[i])

        return list_output
    def run_return_no_results_catch_error(self,non_query):
        """ 
        Catch error in run_return_no_results
        """
        try:
            self.run_return_no_results(non_query)
        except cx_Oracle.DatabaseError:
            pass
        
        return