easyo2p

Easy, quick and simply Oracle to PostgreSQL schema migration tool.

Migrates a schema’s tables, data, constraints, indexes, comments, sequences and (some) triggers.

Connect to both Oracle and PostgreSQL databases simultaneously to migrate in realtime, create sql script files from the Oracle schema to run repeatedly on PostgreSQL, or both.

class easyo2p.__init__.O2P(**kwargs)

Oracle to PostgreSQL migration tools class

create_run_script()

Creates a sql script _run_.sql to run all of the scripts created on PostgreSQL.

do_etl()

Run the etl process, ensure parameters are all set.

file_read_to_string(filename: str, parameter_map=None) → str

Reads a text file and returns the file content as a string.

Lines beginning “REM ” are ignored

Parameters:

  • filename – the filename including path.

  • parameter_map – list of strings to replace in the file. Encase strings within the file with %%.

Returns:

the file content as a string.

file_write_from_string(filename: str, content: str, mode: str = 'a')

Wrapper method for writing a file using the encoding parameter.

Parameters:

  • filename – the filename including path

  • content – the string to writeString to write

  • mode – ‘a’ppend, or ‘w’rite

get_oracle_connection() → Connection

Gets the cx_oracle connection object

Returns:

oracle connection object

get_parameter(name: str) → Any

Get a named parameter value

Parameters:

name – the name of the parameter

Returns:

the parameter value if it exists or None

get_pls2pgs() → Dict

Gets the current pls2pgs mappings

get_postgres_connection(conn_number: int = 0) → connect

Creates the psycopg2 connection to PostgreSQL

Returns:

psycopg2 connection object

is_excluded(object_type: str, object_name: str) → bool

Checks to see if object is in the exclude list

log(comment: str)

Log a comment to the log file and console if console parameter True

oracle_query(query: str, inc_cols: bool = False) → Tuple[List, List] | List

Executes a query on the Oracle database

Parameters:

  • query – the query string

  • inc_cols – should a tuple be returned which also includes the columns and positions

Returns:

data or tuple of data and column names

postgresql_cmd(cmd: str, filename: str = '')

Executes a PostgreSQL DDL or DML command. Either to a file, postgresql database, or both.

Parameters:

  • cmd – the command string.

  • filename – the ETL filename to write the command to.

postgresql_file(file_path)

Executes the command file on PostgreSQL and/or outputs command to ETL file.

Parameters:

file_path – the path and name of the file to execute

postgresql_init_schema()

Creates PostgreSQL schema, dropping it first if it already exists

rename_column(table_name: str, column_name: str)

rename_object wrapper for columns

rename_object(object_type: str, object_name: str)

Overrides the object name with the rename value, if one exists

set_parameter(name: str, value: Any)

Sets a single parameter with the specified value Settable parameters: EXCLUDE, RENAME, SEQUENCES, TABLES, TABLESPACE_MAP, TRIGGERS

Parameters:

  • name – the name of the parameter

  • value – the value of the parameter

set_pls2pgs(pls2pgs: Dict, update: bool = True)

Sets multiple pls2pgs mappings with the specified values

Parameters:

  • pls2pgs – the names and values of the pls2pgs mappings

  • update – should the mappings be updated or not, replaced.

tablespace_map(object_type: str, object_name: str, tablespace_name: str, preamble: str = 'TABLESPACE ') → str

Maps existing tablespaces to target tablespace

Parameters:

  • object_type – either TABLE or INDEX

  • object_name – the name of the object

  • tablespace_name – the tablespace name to map

  • preamble – text to place before the tablespace name if being used.

Returns:

the target tablespace name