inserting and retrieving python dataframe in hana database

[hrshjrwl]

I have a python dataframe that I want to insert directly into HANA database and also wants to read it from database.

I have tried this code :

from sqlalchemy import create_engine
engine = create_engine('hana+pyhdb://username:password@example.com:port')
my_df = pd.DataFrame([[1,2],[3,4],[5,6],[7,8]], columns=["A","B"])
my_df.to_sql('table_name', con = engine, index =False, if_exists ='replace')

Error: DBAPIError: (hdbcli.dbapi.Error) (4321, 'only secure connections are allowed') (Background on this error at:http://sqlalche.me/e/dbapi)*

Is this about adding ssl certificate? How to add it in the engine ?

However, I'm able to connect OK via the Python API using the encrypt option:

conn = dbapi.connect(
    address="host",
    port=portnr,
    encrypt="true",
    user="user",
    password="pwd")

But, if I pass this connection object here:

my_df.to_sql('table_name', con = conn, index =False, if_exists ='replace')

I'll still get an error. How to fix this?

[LynnS1]

Hi Harsh,

We also have another method with SAP developed python API in HANA_ML, specifically with the connection context object. After installing HANA_ML, you can set up a connection such as the following example. You will notice like the examples above, you need to provide encrypt and sslvalidatecertificate. This is probably what caused you original problem.

from hana_ml.dataframe import ConnectionContext
with open(os.path.join(os.getcwd(), 'env_cloud.json')) as f: 
    hana_env = json.load(f)
    port = hana_env['port'] 
    user = hana_env['user'] 
    url = hana_env['url']
    pwd = hana_env['pwd']

cc = ConnectionContext(url, port, user, pwd, autocommit=True, encrypt='true', sslValidateCertificate='false')
cur = cc.connection.cursor()