#----------------------------------------------------------------------------------
# Class containing methods to perform MySQL database IO
# Methods are:  
#         execSQL(): execute incoming SQL and return results
#----------------------------------------------------------------------------------
import os
#import mysql.connector as mydb                     #Python 2x database driver
import pymysql          as mydb                     #Python 3x database driver
import cx_Oracle        as oradb                    #Oracle database driver

os.environ['ORACLE_HOME']='/u01/app/oracle/product/12.2.0/dbhome_1'

class DBio:
#----------------------------------------------------------------------------------
# __init__: Constructor, takes a username, password, DBname, optional DB type 
#----------------------------------------------------------------------------------
    def __init__(self,user,pswd,db,type='mysql'):
        self.user  = user
        self.pswd  = pswd
        self.db    = db
        self.type  = type

#=====================================================================================
# execSQL: Read data from a database
#=====================================================================================
    def execSQL(self, sql):

        DBresult = []                             #2dim array to capture DB result data

       #----------------------------------MySQL----------------------------------
        if self.type.lower() == 'mysql':        
            host = 'localhost'                        #the local host
            port = '3306'                             #MySql port number
            user = self.user                          #the userid
            pswd = self.pswd                          #the password
            db   = self.db                            #the name of the oracle database
     
            try:    
                conn  = mydb.connect(host=host,db=db,user=user,password=pswd)     #connect to db
     
                cursor = conn.cursor()                #create a cursor
                cursor.execute(sql)                   #execute the query
     
                row = cursor.fetchone()               #get row from cursor
                while row is not None:                #loop until no more rows
                    DBresult.append(row)              #append row to 2dim array
                    row = cursor.fetchone()           #get next row from cursor
 
            except mydb.Error as e:
                errorNum = e.args[0]
                errorMsg = e.args[1]
                error = 'Database Error - ' + str(errorNum) + errorMsg
                print(error)
                exit()

            cursor.close()                            #close the cursor/buffer
            conn.close()                              #close the connection

       #----------------------------------Oracle----------------------------------
        if self.type.lower() == 'oracle':        
            host = 'localhost'                        #the local host
            port = '1521'                             #Oracle port number
            user = self.user                          #the userid
            pswd = self.pswd                          #the password
            db   = self.db                            #the name of the oracle database

            connect_str = user+'/'+pswd+'@'+host+':'+port+'/'+db;    #connection string (user/pswd@host:port/db)    
     
            try:
                conn   = oradb.connect(connect_str)   #connect to db (oracle)             

                cursor = conn.cursor()                #create a cursor  
                cursor.execute(sql);                  #execute the query
     
                row = cursor.fetchone()               #get row from cursor
                while row is not None:                #loop until no more rows
                    DBresult.append(row)              #append row to 2dim array
                    row = cursor.fetchone()           #get next row from cursor
 
            except oradb.DatabaseError as e:
                error = 'Database Error - ' + str(e) 
                print(error)
                exit()

            cursor.close()                          #close the cursor/buffer
            conn.close()                            #close the connection

        return DBresult    

#=====================================================================================