Check database status using python

Check database status using python

save this file as dbstatus.py




import cx_Oracle


db=cx_Oracle.connect("hr","hr","192.168.43.68/ind")

cursor=db.cursor()
cursor.execute(""" select name,open_mode from v$database """)

for count in cursor:
    print(count)










Output:

"C:\Program Files\Python37\python.exe" C:/Users/ANURAG/PycharmProjects/phython_master/conn.py
('IND', 'READ WRITE')

Process finished with exit code 0

How can I access Oracle database from Python language using unique way ?

How can I access Oracle database from Python language?

we usually get issue while working with oracle database using python langaugae . The big issue how to connect with database .

There are many articles are available but when you run inti issue very few helps on out . 

if you are here then you issue is solved to how to connect with oracle database using python langaugae ?

* First you need to install python (in my case python version is  3.7.2)

* Install Oracle client (in my case oracle 18c)

* Update network configuration file tnanames.ora using db details 

* Stop firewall on linux (db install on linux in my case )

* Install oracle cx_Oracle  module in python using pip command as below 

* Start listener (db service) on oracle server

 

save the file as oracle_test.py

from __future__ import print_function

import cx_Oracle

# Connect as user "hr" with password "hr" to the "ind" service running on this server.
connection = cx_Oracle.connect("hr", "hr", "192.168.43.68/ind")

cursor = connection.cursor()
cursor.execute("""
    SELECT first_name, last_name
    FROM employees
    WHERE department_id = :did AND employee_id > :eid""",
    did = 55,
    eid = 180)
for fname, lname in cursor:
    print("Values:", fname, lname)


* you can upgrade pip module using below snapshot:

 

Oracle database network configuration files - listener.ora and tnsnames.ora

DB Network Configuration file 

tnsnames.ora

[oracle@pri admin]$ pwd
/u01/app/oracle/product/18.0.0/db/network/admin


[oracle@pri admin]$

[oracle@pri admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/18.0.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

IND =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.68)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = IND)
    )
  )

##LISTENER_IND =
##  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.68)(PORT = 1521))

USA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.69)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = USA)
    )
  )

listener.ora

[oracle@pri admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/18.0.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.68)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = IND)
      (ORACLE_HOME = /u01/app/oracle/product/18.0.0/db)
      (SID_NAME = IND)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = USA)
      (ORACLE_HOME = /u01/app/oracle/product/18.0.0/db)
      (SID_NAME = USA)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle




[oracle@pri admin]$

[oracle@pri ~]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 10-APR-2019 22:46:01

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.43.68)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production

Start Date                10-APR-2019 17:03:30

Uptime                    0 days 5 hr. 42 min. 30 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/18.0.0/db/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.68)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "IND" has 1 instance(s).

  Instance "IND", status UNKNOWN, has 1 handler(s) for this service...

Service "USA" has 1 instance(s).

  Instance "USA", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@pri ~]$

Oracle 18c client installation

Oracle 18c client installation 

*  Download oracle client software .

* unzip the software 

follow below steps for installation :-

setup run by administrator 

you can use different user as you requirement 

Add database details in tnanames.ora

C:\Users\ANURAG>
C:\Users\ANURAG>sqlplus hr/hr@ind

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 10 22:42:30 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Wed Apr 10 2019 20:48:43 +05:30

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
IND       READ WRITE


SQL>