Skip to Content

inserting and retrieving python dataframe in hana database


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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Jul 03 at 08:44 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 03 at 12:14 PM

    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()
    
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 03 at 07:05 AM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.