Question Re: inserting and retrieving python dataframe in hana database in Technology Q&A

inserting and retrieving python dataframe in hana database

hrshjrwl — Fri, 03 Jul 2020 06:12:45 GMT

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?

Re: inserting and retrieving python dataframe in hana database

dvankempen — Fri, 03 Jul 2020 07:05:06 GMT

Hi Harsh,

Does this concern the SAP Cloud Platform, SAP HANA Service?

I have posted a blog with tutorial videos how to set this up

https://blogs.sap.com/2019/01/17/secure-client-connections-for-the-sap-hana-service-by-the-sap-hana-academy/

For the SAP HANA Cloud service or on-premises, e.g. SAP HANA, express edition secure connections by default is not enforced. Are you the system administrator of the SAP HANA system as well?

Re: inserting and retrieving python dataframe in hana database

mkemeter — Fri, 03 Jul 2020 08:44:03 GMT

Hi Harsh,

you can also pass the encrypt parameter via the connection string, that is the parameter of create_engine. You can check the Jupyter notebook from this blog for an example.

The relevant part:

connection_string = 'hana://%s:%s@%s:%s/?encrypt=true&sslvalidatecertificate=false' % (hdb_user, hdb_password, hdb_host, hdb_port)

Regards,
Mathias

Re: inserting and retrieving python dataframe in hana database

LynnS1 — Fri, 03 Jul 2020 12:14:54 GMT

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

Re: inserting and retrieving python dataframe in hana database

former_member802800 — Sat, 07 May 2022 15:54:59 GMT

Thank you, it solved my problem.