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

Final conclusion :

In this blog total 8 simple programs are there, each program  is different purpose. Using this python code you can create tables, insert, edit, select and delete data operations against HANA DB tables. I

Find below links for information:

https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.02/en-US/39eca89d94ca464ca52385ad50fc7dea.html

https://pypi.org/project/hdbcli/