Python) Database connection with Oracle

You must install cx-oracle library in pycharm to demonstrate this.

To test Python with Oracle

import cx_Oracle

try:
    # con = cx_Oracle.connect('ID/PASSWORD@IP:1521/INSTANCE NAME')
    con = cx_Oracle.connect('scott/tiger@localhost:1521/xe')
    
    cursor = con.cursor()
    
    cursor.execute('select * from dept')
    # cursor.execute('select * from board0')

    row = cursor.fetchone()             
    print(type(row))                    # <class 'tuple'>
    print(row)                          # (10, 'ACCOUNTING', 'NEW YORK')

    rows = cursor.fetchall()           
    print(type(rows))                   # <class 'list'>
    print(rows)

except Exception as err:
    print(err)
finally:
    con.close()

To insert data

import cx_Oracle

try:
    con = cx_Oracle.connect('scott/tiger@localhost:1521/xe')
    cursor = con.cursor()

    no = int(input('Insert DEPT NO.'))
    name = input('Insert DEPT NAME.')
    local = input('Insert LOCATION.')

    sql = "insert into dept values(:no, :name, :local)"
    cursor.execute(sql, no=no, name=name, local=local)
    con.commit()
    print('Your data has been successfully inserted.')

except Exception as err:
    print(err)
finally:
    con.close()

To search data

import cx_Oracle

try:
    con = cx_Oracle.connect('scott/tiger@localhost:1521/xe')
    cursor = con.cursor()

    no = input('Enter DEPT NO to search.')

    sql = "select * from dept where deptno = :no"

    cursor.execute(sql, no = no)
    rows = cursor.fetchall()          
    for r in rows:
        print(r[0], r[1], r[2])

except Exception as err:
    print(err)
finally:
    con.close()

To update data

import cx_Oracle

try:
    con = cx_Oracle.connect('scott/tiger@localhost:1521/xe')
    cursor = con.cursor()

    no = input('Insert DEPT NO to update')
    name = input('Insert DEPT NAME to update')
    local = input('Insert LOCATION to update')

    sql = "update dept set dname=:name, loc=:local where deptno=:no"
    cursor.execute(sql, name=name, local=local, no=no)
    con.commit()
    print('Successfully updated')

except Exception as err:
    print(err)
finally:
    con.close()

To delete

import cx_Oracle

try:
    con = cx_Oracle.connect('scott/tiger@localhost:1521/xe')

    cursor = con.cursor()

    no = input('Insert DEPT NO to delete.')

    sql = "delete from dept where deptno = :no"

    cursor.execute(sql, no=no)
    con.commit()

    print('Successfully deleted.')

except Exception as err:
    print(err)
finally:
    con.close()