Skip to Content
Technical Articles
Author's profile photo SivaKumar Kilari

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

 

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/

 

 

 

 

 

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Srinivasa Rao
      Srinivasa Rao

      Hi Siva,

      I just go through the document and it is very useful. Thanks for your continues help and support

      Much Appreciated

       

      Thanks

      Srini

      Author's profile photo CHANDAN DAS
      CHANDAN DAS

      Hi,

      Thanks for the information you provided about connecting hana db using Python is very helpful 

      I appreciate time you spent.

      Author's profile photo Jayanth Venkata Pelluri
      Jayanth Venkata Pelluri

      Insightful Siva . Thanks

      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      I would suggest not to put user and password in the python script but to refer to an hdbuserstore key instead.

      Author's profile photo SivaKumar Kilari
      SivaKumar Kilari
      Blog Post Author

      Yes , we can use hdbuserstore and we can pass credentials as a arguments.

      Author's profile photo Bhaskar Anand Sathuluri
      Bhaskar Anand Sathuluri

      Very useful document Siva.  

      I Appreciate your efforts.

      Bhaskar

      Author's profile photo Sumit Maheshwari
      Sumit Maheshwari

      Excellent work Siva.. Keep writing useful docs.

       

      Sumit