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?

[mkemeter · Accepted solution]

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 · Accepted solution]

Thank you, it solved my problem.