Connecting HANA DB using python Language

[sivakumar_kilari3]

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/39eca89d94ca464ca52385ad50...

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

 

 

 

 

 

[former_member757735]

Hi Siva,

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

Much Appreciated

 

Thanks

Srini

[former_member757729]

Hi,

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

I appreciate time you spent.

[former_member757711]

Insightful Siva . Thanks

[Cocquerel]

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

[sivakumar_kilari3]

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

[bhaskaranand_s]

Very useful document Siva.  

I Appreciate your efforts.

Bhaskar

[former_member605563]

Excellent work Siva.. Keep writing useful docs.

 

Sumit

[angeles804]

I have a question.

 

this I can to do in  Hana DB for SAP 4HANA?¿?.

[bharatvekariya2]

Thank you for informative blog. Have anyone tried to fetch data from T012K or any view using this method. I am not able to retrieve all records. Any suggestion?

[sivakumar_kilari3]

Yes , you can connect.

[former_member10505]

Thanks for the informative post, Just looking to run mini check SQL commands,so is there any way to pass the script and dump the result in txt file?

[rshekdar]

I created a new table ZEMPLOYEE using this method, but i am unable to see this table in SE16n, what could have gone wrong here? The table is visible in DB01, but not in SE16n.