CODEWITHSUNDEEP
pythonhana
Shalini Baranwal
Shalini Baranwal

Reputation: 2998

Python Dataframe to Hana Table

I have my dataframe in python environment, which i want to push it to Hana from python environment. I am trying to do line by line pushing to Hana, that's not happening.However if there a way to push full dataframe to hana in one go, thats the best.

However, for now i am not able to push dynamically the values of dataframe ,line by line:

Here is the python code i tried, so far in best But unfortunately not working :( :

 cursor = conn.cursor() 

 cursor.execute('CREATE TABLE DS.BM_TEXT("Var_ID" VARCHAR (255),"Start_Date" 
 varchar(255),"End_Date" varchar(255),"ID" varchar(255),"Text" varchar(255))') 

 conn = dbapi.connect(address="hana1345.lab1.abc.com", port=30015, user='SHEEMAZ', 
 password='Hello1')

 sql_insert_query = """ INSERT INTO DS.BM_TEXT VALUES (%s,%s,%s,%s,%s)"""

 insert_tuple_2 = ("2", "Emma", "2019-05-19", "9500","22")
 cursor.execute(sql_insert_query, insert_tuple_2)

Error i am getting is :

ProgrammingError: (257, 'sql syntax error: incorrect syntax near "%": line 1 col 53 (at pos 53)')

Appreciate all help.

Upvotes: 1

Views: 1393

Answers (1)

Robert Kearns
Robert Kearns

Reputation: 1706

I am not positive on what module you are using for your db api. But usually ? is the placeholder. Without explicitly calling .format on your string it may not actually insert your sql_insert_query into the string. I could be wrong but I am guessing that is the problem.

As for sending everything at once; it can be done with executemany(). You need an iterable structured like this:

insert_list = [("2", "Emma", "2019-05-19", "9500","22"),("3", "Smith", "2019-05-19", "9500","22")]

To send it to the database use this query:

cursor.executemany("""INSERT INTO DS.BM_TEXT VALUES (?,?,?,?,?);""", insert_list)

This will put the whole iterable into the table. It still does it line by line I believe, but does the heavy lifting for you. If your dataframe is not structured like this you could create an iterable class/function that yields the data in that format from your df.

Upvotes: 2

Related Questions