import pyodbc
import ibm_db
import cx_Oracle
import collections

class DatabaseConnection(object):

    def __init__(self, connection_type, hostname_or_ip, port, database_or_sid, username, password):
        self.port = port
        self.connection_type = connection_type
        self.hostname_or_ip = hostname_or_ip
        self.database_or_sid = database_or_sid
        self.username = username
        self.password = password
        self.dsn = "GEMPROD"
        self.connection_string = ""
        self.conn = ""

    def __enter__(self):
        if self.connection_type == "Netezza":
            self.connection_string = "DRIVER={NetezzaSQL};SERVER=" + self.hostname_or_ip + ";PORT="+ self.port +
                                    ";DATABASE=" + self.database_or_sid + ";UID=" + self.username + ";PWD=" + self.password
            self.conn = pyodbc.connect(self.connection_string)
            return self.conn
        elif self.connection_type == "Oracle":
            dsn_tns = cx_Oracle.makedsn(self.hostname_or_ip, self.port, self.database_or_sid)
            self.conn = cx_Oracle.connect(user=self.username, password=self.password, dsn=dsn_tns)
            return self.conn
        elif self.connection_type == "DB2":
            self.connection_string = "Database=" + self.database_or_sid + ";HOSTNAME=" + self.hostname_or_ip +
                                     ";PORT=" + self.port + ";PROTOCOL=TCPIP;UID=" + self.username + ";PWD=" +
                                     self.password + ";"
            #self.conn = ibm_db.connect(self.connection_string, "", "")
            self.conn = ibm_db.connect('DSN=' + self.dsn, self.username, self.password)
            return self.conn
        pass

    def __exit__(self, type, value, traceback):
        if self.connection_type == "Netezza":
            self.conn.close()
        elif self.connection_type == "DB2":
            ibm_db.close(self.conn)
        elif self.connection_type == "Oracle":
            self.conn.close
        pass

    def __repr__(self):
            return '%s%s' % (self.__class__.__name__, self.dsn)

    def query(self, query, params):
        pass


#database_column_metadata = collections.namedtuple('DatabaseColumnMetadata','index column_name data_type')
#database_field = collections.namedtuple('', '')

table_list = ['BNR_CIF_25DAY_RPT', table2]
sort_column = None
with DatabaseConnection('Netezza', ip, port, database, username, pwd) as connection_one:
    print('Netezza Query:')
    for table in table_list:
        cursor = connection_one.cursor()
        netezza_rows = cursor.execute("SELECT * FROM BNR_CIF_25DAY_RPT LIMIT 1")
        column_list = netezza_rows.description
        sort_column = str(column_list[0][0])
        netezza_query = "SELECT * FROM BNR_CIF_25DAY_RPT ORDER BY " + sort_column + " ASC LIMIT 10"
        netezza_rows = cursor.execute(netezza_query)
        print(column_list)
        netezza_column_list = []
        for idx, column in enumerate(column_list):
            column_name, data_type, *rest = column
            netezza_column_list.append((idx, column_name, data_type))
        for row in netezza_rows:
            print(row, end='n')
        for tup in netezza_column_list:
            print(tup, end='n')
        print('Netezza row count:', str(netezza_rows.rowcount) + 'n')
        cursor.close()

with DatabaseConnection('Oracle', hostname, port, SID, username, pwd) as connection_two:
    print('Oracle Query:')
    for table in table_list:
        try:
            cursor = connection_two.cursor()
            oracle_rows = cursor.execute("SELECT * FROM BNR_CIF_25DAY_RPT WHERE ROWNUM <= 1")
            column_list = oracle_rows.description
            sort_column = column_list[0][0]
            oracle_query = "SELECT * FROM (SELECT * FROM BNR_CIF_25DAY_RPT ORDER BY " + sort_column + " ASC) WHERE ROWNUM <=10"
            oracle_rows = cursor.execute(oracle_query)
            print(column_list)
            oracle_column_list = []
            for idx, column in enumerate(column_list):
                column_name, data_type, *rest = column
                oracle_column_list.append((idx, column_name, data_type))
            for row in oracle_rows:
                print(row, end='n')
            for tup in oracle_column_list:
                print(tup, end='n')
            print('Oracle row count:', str(oracle_rows.rowcount) + 'n')
        except cx_Oracle.DatabaseError as e:
            print(str(e))
        finally:
            cursor.close()