Tuesday 20 July 2021

Connecting HANA DB using python Language

1. Overview

This document will explain you, how to connect HANA database and table operations using python language.

2.  Prerequisites for HANA connectivity from Python

◉ Python latest software installation

SAP HANA DB, SAP HANA Exam Prep, SAP HANA Learning, SAP HANA Certification, SAP HANA Career, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning

◉ Install HANA client

SAP HANA DB, SAP HANA Exam Prep, SAP HANA Learning, SAP HANA Certification, SAP HANA Career, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning

◉ Install Python wheel package

https://pypi.org/project/hdbcli/2.9.23/#files

SAP HANA DB, SAP HANA Exam Prep, SAP HANA Learning, SAP HANA Certification, SAP HANA Career, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning

SAP HANA DB, SAP HANA Exam Prep, SAP HANA Learning, SAP HANA Certification, SAP HANA Career, SAP HANA Guides, SAP HANA Tutorial and Material, SAP HANA Learning

3. HANA DB Connection parameters


#Following is the example of connecting to database  
#Import module
from hdbcli import dbapi
#Open the database conenciton 
conn = dbapi.connect(address="<hostname/IP>", port=3<NN>MM,user="<username>", password="<password>"
)
# prepare a cursor object using cursor() method
cursor = conn.cursor()

# disconnect from server
conn.close()
#------------------END--------------

Example:
#!Update data in table dyanamically 
from hdbcli import dbapi
# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")

4. Table creation


#!Let us create Database table EMPLOYEE:

# Create table statement 
tabdef= "CREATE TABLE EMPLOYEE (SL_NO AS  INTEGER PRIMARY KEY,NAME CHAR(20) NOT NULL, ), AGE  INT, GENDER CHAR(1), SALARY FLOAT )”

Example:

#!Zemployee table
from hdbcli import dbapi
# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")

# prepare a cursor object using cursor() method
cursor = conn.cursor()

cursor.execute("CREATE TABLE SAPHANADB.ZEMPLOYEE (SL_NO INTEGER PRIMARY KEY,NAME CHAR(20) NOT NULL, AGE INT, GENDER CHAR(1), SALARY FLOAT)")

cursor.close()
conn.close()

5. Data Insert operation


Using below program you can update the records in to table

Below example statements will insert records in to zemployee table
   sql = "INSERT INTO SAPHANADB.ZEMPLOYEE (SL_NO, NAME, AGE, GENDER, SALARY) VALUES (1,'Ram Kumar', 30, 'M', 20000)"
 
Example Program:

# !Update data in to table dyanamically

from hdbcli import dbapi

# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")
# prepare a cursor object using cursor() method
cursor = conn.cursor()

# Prepare SQL query to INSERT a record into the database.

sql= 'INSERT INTO SAPHANADB.ZEMPLOYEE (SL_NO, NAME, AGE, GENDER, SALARY) VALUES (:sn, :nm, :ag, :sx, :sl )

sn = input("Enter your Serial No :")
nm = input("Enter your Name :")
ag = input("Enter your Age :")
sx = input("Enter your Gender :")
sl = input("Enter your Salary :")

# Execute the SQL command
cursor.execute(sql, {"sn": sn, "nm": nm, "ag": ag, "sx": sx, "sl": sl})

# Commit your changes in the database
conn.commit()
# disconnect database 
conn.close()

Below example will change/modify records in table

#!Update / modify data in table dyanamically
from hdbcli import dbapi

# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")

cursor = conn.cursor()
sql= 'SELECT * FROM SAPHANADB.ZEMPLOYEE WHERE SL_NO = :id'
sn=input("Enter Serial No to update : ")
cursor.execute(sql, {"id": sn})
True
row = cursor.fetchone()
print(row)
v=input("which fields do you want to update 1.Name, 2. Age, 3. Gender, 4. Salaray: Enter No:")

#Functions begin

def one():
    name=input("Enter correct name: ") 
    sql1= 'UPDATE SAPHANADB.ZEMPLOYEE SET NAME = :name where SL_NO = :sl'
    cursor.execute(sql1, {"name": name, "sl":sn})

def two():
    ag=input("Enter correct Age: ")
    sql1= 'UPDATE SAPHANADB.ZEMPLOYEE SET AGE = :age where SL_NO = :sl'
    cursor.execute(sql1, {"age": ag, "sl":sn})

def three():
    sx=input("Enter correct Gender: ")
    sql1= 'UPDATE SAPHANADB.ZEMPLOYEE SET GENDER = :sx where SL_NO = :sl'
    cursor.execute(sql1, {"sx": sx, "sl":sn})

def four():
    sal=input("Enter correct Salary: ")
    sql1= 'UPDATE SAPHANADB.ZEMPLOYEE SET SALARY = :salary where SL_NO = :sl'
    cursor.execute(sql1, {"salary": sal, "sl":sn})
    
#Functions close

#conditions start, if below conditions pass will call particular function 

if v == '1':
 one()
elif v== '2':
 two()
elif v== '3':
 three()
elif v== '4':
 four()
  #  print(v)

sql2= 'SELECT * FROM SAPHANADB.ZEMPLOYEE WHERE SL_NO = :id'
cursor.execute(sql2, {"id": sn})
True
rw = cursor.fetchone()

print(row)

6.  Delete Row from table


Delete single records using below program

#!Delete data in table dyanamically 
from hdbcli import dbapi
# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")
cursor = conn.cursor()
sql= 'DELETE  FROM SAPHANADB.ZEMPLOYEE WHERE SL_NO = :id'
#print(sql)
sn=input("Enter Serial No to Delete record : ")
cursor.execute(sql, {"id": sn})
True
cursor.close()
 

7. Select Particular row


Using below program, you can select particular records

#!Select particular record
from hdbcli import dbapi

# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")

# prepare a cursor object using cursor() method
cursor = conn.cursor()

sql= 'SELECT * FROM SAPHANADB.ZEMPLOYEE WHERE SL_NO = :id'

sn=input("Enter Serial No to update : ")
cursor.execute(sql, {"id": sn})
True
row = cursor.fetchone()
print(row)

8.  Select all rows


Using below program, you can fetch all records

#!Select all records
from hdbcli import dbapi

# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")

sql = 'SELECT * FROM SAPHANADB.ZEMPLOYEE'
cursor = conn.cursor()
cursor.execute(sql)
True
result = cursor.fetchall()

for row in result:
    sn = row[0]
    nm = row[1]
    age = row[2]
    gender = row[3]
    sl = row[4]

# Now print fetched result
    print(sn, nm, age, gender, sl)

9. Table deletion


Using below program, you can delete table

#!Drop zemployee table
from hdbcli import dbapi

# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")

# prepare a cursor object using cursor() method
cursor = conn.cursor()
# Dropping tables 
cursor.execute("DROP TABLE SAPHANADB.ZEMPLOYEE")

cursor.close()

conn.close()

No comments:

Post a Comment