Database Connection

Executing a SQL statement- Updating Table

import cx_Oracle
connection = cx_Oracle.connect("hr", "redhat", "localhost/orcl")
cursor = connection.cursor()

sql = """select * from jobs where job_title='Programmer' """
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Fetch all the rows in a list of lists.
    results = cursor.fetchall()
    for row in results:
        print(row)
except:
    print ("Error: unable to fecth data")

# Prepare SQL query to UPDATE required records
sql = "UPDATE jobs SET min_salary = min_salary*1.1 WHERE job_title = 'Programmer'"
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    connection.commit()
except:
    # Rollback in case there is any error
    connection.rollback()

sql = """select * from jobs where job_title='Programmer'"""
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Fetch all the rows in a list of lists.
    results = cursor.fetchall()
    for row in results:
        print(row)
except:
    print ("Error: unable to fecth data")

connection.close()

"D:\Downloads\Softwares\python download\python-installed\python.exe" E:/software/python/Learning/Example/Demo/.idea/demo.py
('IT_PROG', 'Programmer', 4400, 10000)
('IT_PROG', 'Programmer', 4840, 10000)

 

Executing a SQL statement- Reading From Table

import cx_Oracle
connection = cx_Oracle.connect("hr", "redhat", "localhost/orcl")
cursor = connection.cursor()

sql = """SELECT * FROM JOBS"""
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Fetch all the rows in a list of lists.
    results = cursor.fetchall()
    for row in results:
        job_id = row[0]
        job_title = row[1]
        min_salary = row[2]
        max_salary = row[3]
        # Now print fetched result
        print("{} {} {} {}".format(job_id,job_title,min_salary,max_salary))
except:
    print ("Error: unable to fecth data")
# disconnect from server
connection.close()

"D:\Downloads\Softwares\python download\python-installed\python.exe" E:/software/python/Learning/Example/Demo/.idea/demo.py
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000

Executing a SQL statement- Inserting into Table

import cx_Oracle
connection = cx_Oracle.connect("hr", "redhat", "localhost/orcl")
cursor = connection.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """
    insert into persons (PersonID, LastName, FirstName, Address, City)
    values (101,'Gupta', 'Saurav', 'Some Random Address', 'Kolkata')
"""
try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the database
    connection.commit()
except:
    # Rollback in case there is any error
    connection.rollback()

cursor.execute("Select * from Persons")
for i in cursor:
    print(i)

# disconnect from server
connection.close()

(101, 'Gupta', 'Saurav', 'Some Random Address', 'Kolkata')

Executing a SQL statement- Creating a Table

import cx_Oracle
connection = cx_Oracle.connect("hr", "redhat", "localhost/orcl")
cursor = connection.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE Persons")

# Create table as per requirement
sql = """CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)"""

cursor.execute(sql)

sql = """
    insert into persons (PersonID, LastName, FirstName, Address, City)
    values (101,'Gupta', 'Saurav', '12 Some Random Address', 'Kolkata')
"""
cursor.execute(sql)
sql = "commit"
cursor.execute(sql)
cursor.execute("Select * from Persons")
for i in cursor:
    print(i)

# disconnect from server
connection.close()

(101, 'Gupta', 'Saurav', 'Some Random Address', 'Kolkata')

Writing Your connection String

#!/usr/bin/python
import cx_Oracle

# Connect as user "hr" with password "welcome" to the "oraclepdb" service running on this computer.
connection = cx_Oracle.connect("hr", "redhat", "localhost/orcl")

cursor = connection.cursor()
cursor.execute("""
    select * from tab""")
for x in cursor:
    print(x)

"D:\Downloads\Softwares\python download\python-installed\python.exe" E:/software/python/Learning/Example/Demo/.idea/demo.py
('COUNTRIES', 'TABLE', None)
('DEPARTMENTS', 'TABLE', None)
('EMPLOYEES', 'TABLE', None)
('EMP_DETAILS_VIEW', 'VIEW', None)
('JOBS', 'TABLE', None)
('JOB_HISTORY', 'TABLE', None)
('LOCATIONS', 'TABLE', None)
('PRODUCTS', 'TABLE', None)
('REGIONS', 'TABLE', None)
('SAURAV', 'TABLE', None)

install cx_Oracle

to connect to any database, you need its relevant API. cx_Oracle is the oracle used for connecting python code to oracle database

1. Include the following in your code, If this does not throws exception, then you have cx-Oracle installed in your system
#!/usr/bin/python
import import cx_Oracle


2. If you get an error, go to cmd line and install - python -m pip install cx_Oracle --upgrade
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\SouravG>python -m pip install cx_Oracle --upgrade
Collecting cx_Oracle
  Downloading https://files.pythonhosted.org/packages/ae/ee/5e9704b60f99540a6b6f527f355b5e7025ba3f637f31566c5021e6e337c5/cx_Oracle-6.4.1-cp3
6-cp36m-win_amd64.whl (164kB)
    100% |████████████████████████████████| 174kB 165kB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.4.1

C:\Users\SouravG>