Oracle Using Java and Python from Delta

Connecting to Oracle server from Delta using jdbc

JDBC Connection string

In the connection strings below, replace "userid" with your UWinID, and "password" with your campus password.

Code to test your connection:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class JDBCVersion
{
  public static void main (String args[]) throws SQLException
  {
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:thin:userid/password@oracle.cs.uwindsor.ca:1521:cs01");
    Connection conn = ods.getConnection();

    // Create Oracle DatabaseMetaData object
    DatabaseMetaData meta = conn.getMetaData();

    // gets driver info:
    System.out.println("JDBC driver version is " + meta.getDriverVersion());
  }
}

The classpath for the Oracle JDBC archive will vary depending on the operating system and which version of the Oracle client you installed. The classpath in the following commands are those required to compile and run from a terminal on cs.uwindsor.ca.

javac -classpath /usr/lib/oracle/12.1/client64/lib/ojdbc7.jar  JDBCVersion.java 
java -classpath /usr/lib/oracle/12.1/client64/lib/ojdbc7.jar:.  JDBCVersion

Connecting to Oracle server from Delta using Python

• Create a Python Virtual Environment
  •  mkdir oraclepy
  •  cd oraclepy/
  •  python -m venv ./venv
• Pip install cx_oracle
  • pip install cx_oracle --upgrade
• Use the following code db_config.py

  import cx_Oracle
  import sys
  import os
  import getpass
  
  user = os.getlogin()
  
  dsn = os.environ.get("PYTHON_CONNECT_STRING", "oracle.cs.uwindsor.ca/cs01")
  
  pw = os.environ.get("PYTHON_PASSWORD")
  if pw is None:
      pw = getpass.getpass("Enter password for %s: " % user)
  
  

  
  
• Using the following code, you connect to the database

  import cx_Oracle
  import db_config
  
  con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)
  print("Database version:", con.version)
  

• Run the Code: before running the code, set the environment variables using export 
• Check the following URL for more information: Tutorial cx_Oracle
  
• You can embed the export variables in shell script and call your Python code from the same script

  export ORACLE_HOME=/opt/oracle/instantclient_21_8
  export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_8:$PATH
  export TNS_ADMIN=/opt/oracle/instantclient_21_8/network/admin/

• To exit the Python virtual env, type deactivate and press enter. you will be back at the Linux prompt