cancel
Showing results for 
Search instead for 
Did you mean: 

inserting and retrieving python dataframe in hana database

hrshjrwl
Explorer
0 Kudos


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?

Accepted Solutions (1)

Accepted Solutions (1)

mkemeter
Product and Topic Expert
Product and Topic Expert

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

former_member802800
Discoverer
0 Kudos

Thank you, it solved my problem.

Answers (2)

Answers (2)

LynnS1
Advisor
Advisor

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()
dvankempen
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

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?