Pages

Sunday, April 14, 2019

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)

No comments:

Post a Comment