CODEWITHSUNDEEP
pythonpandassqlalchemyjaydebeapi
user11756724
user11756724

Reputation:

Python - Insert from select - Fom Pandas using cursor

I'm trying to insert records from my dataframe on a existing table that's stored on a Hive cluster. I cannot use sqlalchemy engine since I am using a JDBC connection.

My code is:

cursor = conn.cursor()
engine = sqlalchemy.create_engine('driver://', creator=conn)
dff = pd.DataFrame([[1, 2], ['process 1', 'process 2']], columns=['id', 'description'])
sql_query = "INSERT INTO default.my_table SELECT * FROM {0}".format(dff)
#dff.to_sql(name='my_table', schema='default', con=engine.connect(), if_exists='append')#I also tried this but it gives me error: 'Connection' object is not callable
cursor.execute(sql_query)

I prefer to user INSERT INTO SELECT since INSERT INTO VALUES because the schema from my data can change over on future, and with this dependency, I just need to change my database tables.

With my current code I got this:

CAUSED BY: Exception: Syntax error

How can I solve my problem?

Upvotes: 1

Views: 622

Answers (1)

MertG
MertG

Reputation: 763

dff is an object, not string. You have to something like this cursor.executemany(your_sql_statement, column_1, column_2, column_n)

Upvotes: 1

Related Questions