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

◉ Install HANA client

◉ Install Python wheel package

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

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()