def load(self, tname, df, pk=None):
"""
===================================================================
Description: Load Data Frame into Oracle Table.
===================================================================
Arguments:
-------------------------------------------------------------------
1. tname : str (Table Name to Create).
2. df : DataFrame.
3. pk : str (Cols Names of Primary Key).
===================================================================
"""
signature_cols = Ora.df_to_signature_cols(df)
res_create = self.create_table(tname, signature_cols, pk)
if not res_create:
return res_create
str_parameters = Ora.str_parameters(len(df.columns))
command = f'insert into {tname} values({str_parameters})'
cur = self.con.cursor()
try:
cur.executemany(command, df.values.tolist())
self.con.commit()
except Exception as e:
msg = '\n* Error in Oracle Execute Many:\n** {0}\n*** {1}\n'.format(
e, command)
return Res(msg, False)
return Res()
def close(self):
"""
===================================================================
Description: Close Connection to Oracle.
=======================================================================
Return: Res (bool).
=======================================================================
"""
try:
self.con.close()
except Exception as e:
return Res(str(e), False)
return Res()
def count(self, tname):
"""
===================================================================
Description: Count rows in Oracle Table.
===================================================================
Arguments:
-------------------------------------------------------------------
1. tname : str (Table Name).
===================================================================
Return: Res (int).
===================================================================
"""
query = 'select count(*) from {0}'.format(tname)
cur = self.con.cursor()
try:
cur.execute(query)
return Res(cur.fetchone()[0])
except Exception as e:
return Res(str(e), False)
def run(self, command):
"""
===================================================================
Description: Run an Command on Oralce.
===================================================================
Arguments:
-------------------------------------------------------------------
1. command : str (Command to Run).
===================================================================
Return: Res (bool).
===================================================================
"""
cur = self.con.cursor()
try:
cur.execute(command)
except Exception as e:
msg = '\n* Error in Oracle running command:\n** {0}\n*** {1}\n'.format(
e, command)
return Res(msg, False)
return Res()
def open(self):
"""
=======================================================================
Description: Open Connection to Oracle.
=======================================================================
Return: Res (bool).
=======================================================================
"""
dsn = cx_Oracle.makedsn('poramolam', '1522', service_name='olam')
dsn = cx_Oracle.makedsn('localhost', '1521', service_name='oranew')
try:
self.con = cx_Oracle.connect(self.user,
self.password,
dsn,
mode=cx_Oracle.SYSDBA,
encoding='iso-8859-1',
nencoding='iso-8859-1')
except Exception as e:
return Res(str(e), False)
return Res()
def select(self, query):
"""
===================================================================
Description: Select query into DataFrame.
===================================================================
Arguments:
-------------------------------------------------------------------
1. query : str (SQL Query or Table Name to select all table.
===================================================================
Return: Res (DataFrame).
===================================================================
"""
# if it is a table name
if ' ' not in query:
query = 'select * from {0}'.format(query)
try:
df = pd.read_sql(query, self.con)
return Res(df)
except Exception as e:
return Res(str(e), False)
def description(self, tname):
"""
===================================================================
Description: Return Description of the Table's Columns.
===================================================================
Arguments:
-------------------------------------------------------------------
1. tname : str (Table Name).
===================================================================
Return: Res (list).
===================================================================
"""
query = 'select * from {0} where rownum=0'.format(tname)
cur = self.con.cursor()
try:
cur.execute(query)
return Res(cur.description)
except Exception as e:
msg = '\n* Error in Oracle running command:\n** '
msg += '{0}\n*** {1}\n'.format(e, query)
return Res(msg, False)
def get_empty_cols(tname, user='******'):
empty_cols = set()
ora = Ora(user)
res_open = ora.open()
if not res_open:
return res_open
res_desc = ora.description(tname)
if not res_desc:
ora.close()
return res_desc
for col in res_desc.val:
query = 'select count(*) from {0} where {1} is not null'.format(
tname, col)
res_count = ora.select(query)
if not res_count:
ora.close()
return res_count
values = res_count.val.iloc[0][0]
if not values:
empty_cols.add(col)
ora.close()
return Res(empty_cols)
def create_table(self, tname, signature_cols, pk=None):
"""
===================================================================
Description: Create Oracle Table by Signature Cols.
===================================================================
Arguments:
-------------------------------------------------------------------
1. tname : str (Table Name).
2. signature_cols : str (Signature of Col-Name and Col-DType).
3. pk : str (Primary Key, contains the names of the columns).
===================================================================
Return: Res(bool).
===================================================================
"""
self.drop_table(tname)
str_command = f'create table {tname}({signature_cols})'
res_create = self.run(str_command)
if not res_create:
return res_create
if pk:
return self.add_primary_key(tname, pk)
else:
return Res()
def create_table_as(self, tname, select, pk=None):
"""
===================================================================
Description: Create Table as Select from another Table.
===================================================================
Arguments:
-------------------------------------------------------------------
1. tname : str (Table Name).
2. select : str (Select Query).
3. pk : str (Column Names separated by comma).
===================================================================
Return: Res (bool).
===================================================================
"""
self.drop_table(tname)
query = 'create table {0} as {1}'
res_create = self.run(query.format(tname, select))
if not res_create:
return res_create
if pk:
return self.add_primary_key(tname, pk)
else:
return Res()
def from_sas(self, tname_sas, tname_ora, pk=None):
"""
===================================================================
Description: Canonize Table from SAS into Oracle format.
===================================================================
Arguments:
-------------------------------------------------------------------
1. tname_sas : str (Table Name from SAS).
2. tname_ora : str (Table Name to Create).
===================================================================
Return: Res (bool).
===================================================================
"""
res_desc = self.description(tname_sas)
if not res_desc:
return Res(res_desc.msg, False)
li_desc = [(col[0], col[1]) for col in res_desc.val]
query = 'select '
li_cols = list()
for name, dtype in li_desc:
dtype = str(dtype).lower()
if 'char' in dtype or 'string' in dtype or 'clob' in dtype:
li_cols.append("nvl(to_char({0}),'NULL') as {0}".format(name))
elif 'date' in dtype or 'time' in dtype:
li_cols.append(Ora.to_date_to_char(name))
else:
li_cols.append('nvl({0},-1) as {0}'.format(name))
query += ','.join(li_cols)
query += ' from {0} order by 1'.format(tname_sas)
res_create = self.create_table_as(tname_ora, query)
if not res_create:
return res_create
if pk:
return self.add_primary_key(tname_ora, pk)
else:
return res_create