fusetools.db_etl_tools.Oracle¶

class fusetools.db_etl_tools.Oracle[source]¶

Bases: object

Generic functions for Oracle SQL queries and ETL.

Methods

`get_oracle_date`	Converts a date to an Oracle date of format “DD-MMM-YYY”
`get_orcl_date`	Converts a date to an Oracle date of format “DD-MMM-YYY”.
`insert_exec`	Executes a provided SQL statement.
`insert_tbl`	Executes an INSERT INTO statement for a given Pandas DataFrame.
`make_tbl`	Provides a CREATE TABLE SQL statement for a given Pandas DataFrame.
`make_tbl_complete`	Executes a series of SQL statements to CREATE and INSERT into a table from a Pandas DataFrame.
`make_tbl_complete_force`	Executes a series of SQL statements to CREATE and INSERT into a table from a Pandas DataFrame.
`orcl_tbl_varchar_convert`	Converts a set of columns to VARCHAR(300) for a given Oracle table.

classmethod get_oracle_date(date)[source]¶

Converts a date to an Oracle date of format “DD-MMM-YYY”

Parameters: date – A provided date.
Returns: An Oracle database date.

classmethod get_orcl_date(dat)[source]¶

Converts a date to an Oracle date of format “DD-MMM-YYY”.

Parameters: dat – A provided date column of a Pandas Series.
Returns: An Oracle database date.

classmethod insert_exec(sql, conn, df)[source]¶

Executes a provided SQL statement.

Parameters

sql – A provided SQL query.
conn – A database connection.
df – A Pandas DataFrame.

Returns

Nothing.

classmethod insert_tbl(df, tbl_name)[source]¶

Executes an INSERT INTO statement for a given Pandas DataFrame.

Parameters

df – A Pandas DataFrame with values to be inserted.
tbl_name – An Oracle table for Pandas DataFrame to be inserted into.

Returns

SQL for INSERT INTO statement.

classmethod make_tbl(df, tbl_name)[source]¶

Provides a CREATE TABLE SQL statement for a given Pandas DataFrame.

Parameters

df – A Pandas DataFrame to be added as an Oracle table.
tbl_name – Oracle table name to be created.

Returns

CREATE TABLE SQL statement.

classmethod make_tbl_complete(df, tbl_name, eng, conn, subcols=False, chunks=False, chunks_delay=False)[source]¶

Executes a series of SQL statements to CREATE and INSERT into a table from a Pandas DataFrame.

Parameters

df – Pandas DataFrame to create a table from.
tbl_name – Name of table to be created.
eng – Oracle database engine object.
conn – Oracle database connection object.
subcols – A list of columns of the Pandas DataFrame to apply operations on.
chunks – Number of chunks to split Pandas DataFrame into.
chunks_delay – Delay between chunk’s INSERT statement.

Returns

Print statements outline sequential SQL statements executed.

classmethod make_tbl_complete_force(df, tbl_name, eng, conn, attempt_n, subcols=False, chunks=False, chunks_delay=False)[source]¶

Executes a series of SQL statements to CREATE and INSERT into a table from a Pandas DataFrame.

Parameters

df – Pandas DataFrame to create a table from.
tbl_name – Name of table to be created.
eng – Oracle database engine object.
conn – Oracle database connection object.
attempt_n – Number of times to attempt to run INSERT statement.
subcols – A list of columns of the Pandas DataFrame to apply operations on.
chunks – Number of chunks to split Pandas DataFrame into.
chunks_delay – Delay between chunk’s INSERT statement.

Returns

Print statements outline sequential SQL statements executed.

classmethod orcl_tbl_varchar_convert(tbl_name, convert_cols, engine)[source]¶

Converts a set of columns to VARCHAR(300) for a given Oracle table.

Parameters

tbl_name – Oracle table name.
convert_cols – List of columns to convert.
engine – Oracle database engine.

Returns

Printed ALTER table statements for each column.