Connect to Oracle Database from Python or R

1. To connect to Oracle in Python

     1)  conda install -c anaconda cx_oracle 

2.1) Method 1:

    import pandas as pd
    import cx_Oracle

    con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')

    query = 'select * from departments order by department_id'     

    df_ora = pd.read_sql(query, con=con)

    df_ora.head()

    con.close()

2.2) Method 2:
    import cx_Oracle

    con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')

    cur = con.cursor()

    query = 'select * from departments order by department_id'
    cur.execute(query)
    for result in cur:
        print(result)

    cur.close()
    con.close()

2. To connect to Oracle in R






















1)  install.packages("ROracle")


























2) Method 1:




















   library("ROracle")

   drv = dbDriver("Oracle")

   host = "hostname"

   port = "1521"

   sid = "SID_NAME"

   connect.string = paste(
         "(DESCRIPTION=",

         "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=",port, "))",

         "(CONNECT_DATA=(SID=", sid, ")))", sep="")

   con = dbConnect(drv, username="uid", password="pwd", 

                   dbname=connect.string, prefetch=FALSE,

                   bulk_read=1000L, stmt_cache=0L,

                   external_credentials=FALSE, sysdba=FALSE)

   res = dbGetQuery(con, "select * from dual")

   print(res)

   dbDisconnect(con)