Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
I want to execute raw SQL statements in Python. The raw SQL statements would not return data, they are just inserting data into a database. The existing write_to_dataframe method rewrites the whole table, and using a SQL recipe or scenario is not ideal. These statements would typically be running from a dataiku webapp or notebook.
The following query is what I need to run from python (webapp or notebook).
INSERT INTO db.db_table (col_one, col_two) VALUES('value_one', 'value_two')
Hi @dev1,
There are several options:
For the second option, and assuming that your output dataset is called "output_table", and is stored in with the same name in your dabatase and schema "public", this would work:
from dataiku import SQLExecutor2
sqlcon = SQLExecutor2(dataset="output_table")
sql_str = """INSERT INTO "public"."output_table" (col_one, col_two) VALUES('value_one', 'value_two')"""
sqlcon.query_to_df(sql_str, post_queries=['COMMIT'])
Hope this helps!
Hi @dev1,
There are several options:
For the second option, and assuming that your output dataset is called "output_table", and is stored in with the same name in your dabatase and schema "public", this would work:
from dataiku import SQLExecutor2
sqlcon = SQLExecutor2(dataset="output_table")
sql_str = """INSERT INTO "public"."output_table" (col_one, col_two) VALUES('value_one', 'value_two')"""
sqlcon.query_to_df(sql_str, post_queries=['COMMIT'])
Hope this helps!
Can you link to the documentation on how to use SQLExecutor2?
Hi @dev1,
There is no much documentation on the method:
https://doc.dataiku.com/dss/latest/python-api/sql.html?highlight=sqlexecutor2#dataiku.SQLExecutor2
https://doc.dataiku.com/dss/latest/python-api/sql.html?highlight=sqlexecutor2#executing-queries
But there is a whole excellent section (first time I see it!) in the knowledge base:
even when it doesn't touch the topic of executing statements other than SELECT.
Cheers!