[python]Using cx_Oracle module

This is a try out for using cx_Oracle module maintained by Oracle.
The version I am trying is version 12.2
Here’s the same code:

import cx_Oracle


dsn = cx_Oracle.makedsn("dbhostname_or_fqdn", "1580", "student")
try:
    conn = cx_Oracle.connect("username", "password", dsn)
except BaseException as e:
    print(e)
    exit(1)
query = "select * from table_owner.table_name"
cur = conn.cursor()
try:
    cur.execute(query)
except BaseException as e:
    print(e)
    exit(1)
for result in cur:
    print(result)
quit()

The above is a test out code, to test on how to connect to the oracle database. Now i am expanding the code a little by doing two things:

• To list the tables which I can access.
• To list the tables I owned.
• Duplicate from another owner’s table to your own.
• Drop a table that belongs to you.

The username, password, service ID (db_sid) and the DB hostname will be obfuscated.

import cx_Oracle

# setting up the parameters
DSN = cx_Oracle.makedsn("db_hostname", "1580", "db_sid")
USERNAME = "db_username"
PASSWORD = "db_password"
QUERY = {
    '1': """select owner,table_name from all_tables 
            where owner = 'table_owner' or owner = 'db_username'
        """,
    '2': """select table_name
            from user_tables
        """
}


def drop_table(table_name):
    drop_string = "drop table {}".format(table_name)
    call_oracle(drop_string, USERNAME, PASSWORD, True)

def create_table(table_name, from_table):
    CREATE_TABLE = """
    CREATE TABLE {} AS
    SELECT * FROM {}
    """.format(table_name, from_table)
    # print(CREATE_TABLE)
    call_oracle(CREATE_TABLE, USERNAME, PASSWORD, True)


def call_oracle(query, username, password, is_create=False):
    try:
        conn = cx_Oracle.connect(username, password, DSN)
    except BaseException as e:
        print(e)
        exit(1)
    cur = conn.cursor()
    try:
        cur.execute(query)
        if is_create:
            conn.commit()
            print('Query executed successfully.')
    except BaseException as e:
        print(e)
        exit(1)
    if not is_create:
        for result in cur:
            print(result)
    cur.close()
    conn.close()
    quit()


def show_menu():
    menu = {
        '1': '[1] Show tables you are allowed to access.',
        '2': '[2] Show tables that you owned.',
        '3': '[3] Duplicate from another table.',
        '4': '[4] Drop a table.',
        '0': '[0] Quit'
    }
    options = menu.keys()
    sorted(options)
    for option in options:
        print(menu[option])
    select = input('Please choose an option: ')
    while True:
        if select is '1':
            call_oracle(QUERY[select], USERNAME, PASSWORD)
        elif select is '2':
            call_oracle(QUERY[select], USERNAME, PASSWORD)
        elif select is '3':
            table_name = input('Please enter table name: ')
            from_table = input('Type in the table you want to duplicate: ')
            create_table(table_name,from_table)
        elif select is '4':
            table_name = input('Type the table you wish to drop: ')
            drop_table(table_name)
        elif select is '0':
            break
        else:
            print('you have entered an invalid option.')
            break


if __name__ == "__main__":
    show_menu()

You do not need to put a semi colon at the end of every query, because the cx_Oracle will put that for you, if you put a semi colon you will receive an sql command not ended properly error from Oracle.