HANA & Python Page 1
Table of Contents
1. Overview ............................................................................................................................................... 3
2. Prerequisites for HANA connectivity from Python ............................................................................... 3
3. HANA DB Connection parameters ........................................................................................................ 4
4. Table creation ....................................................................................................................................... 5
5. Data Insert operation ............................................................................................................................ 6
6. Delete Row from table .......................................................................................................................... 8
7. Select Particular row ............................................................................................................................. 9
8. Select all rows ....................................................................................................................................... 9
9. Table deletion ..................................................................................................................................... 10
HANA & Python Page 2
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
HANA & Python Page 3
3. HANA DB Connection parameters
For HANA tenant databases, use the port number 3**NN**13 (where NN is the SAP instance
number - e.g. 30013).
For HANA system databases in a multitenant system, the port number is 3**NN**13.
For HANA single-tenant databases, the port number is 3**NN**15.
#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()
Example:
#!Update data in table dyanamically
from hdbcli import dbapi
# Open database connection
conn = dbapi.connect(address="##.##.##.##", port=30215,user="SYSTEM", password="Test#123")
HANA & Python Page 4
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,
SEX 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, SEX CHAR(1), SALARY FLOAT)")
cursor.close()
conn.close()
######
HANA & Python Page 5
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, SEX, SALARY) VALUES (1,'Ram Kumar', 30, 'M', 20000)"
Example Program:
# !Update data in 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, SEX, 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 SEX :")
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()
HANA & Python Page 6
###: Below example will insert records in employee table dynamically
#!Update data in table dyanamically – Begind program
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'
#print(sql)
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. Sex, 4. Salaray: Enter No:")
#Write functions
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 Sex: ")
sql1= 'UPDATE SAPHANADB.ZEMPLOYEE SET SEX = :sex where SL_NO = :sl'
cursor.execute(sql1, {"sex": 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})
#conditions
if v == '1':
one()
elif v== '2':
two()
elif v== '3':
three()
elif v== '4':
four()
# print(v)
HANA & Python Page 7
sql2= 'SELECT * FROM SAPHANADB.ZEMPLOYEE WHERE SL_NO = :id'
cursor.execute(sql2, {"id": sn})
True
row = cursor.fetchone()
print(row)
###End
6. Delete Row from table
Delete single records using below program
#!Update 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()
HANA & Python Page 8
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]
sex = row[3]
sl = row[4]
# Now print fetched result
print(sn, nm, age, sex, sl)
Output:
HANA & Python Page 9
9. Table deletion
Using below program, you can fetch all records
#!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()
HANA & Python Page 10
