Running SQL statements with Python?
![dev1](https://us.v-cdn.net/6038231/uploads/Dataiku/nAvatar1.png)
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')
Best Answer
-
Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 412 Neuron
Hi @dev1
,There are several options:
- Use a library like pyscopg2, or cx_oracle, or whatever python library that allows you to connect to the database you are using for your datasets, and execute SQL code
- Use dataiku's SQLExecutor2 to execute SQL code, without needing to care about your database kind.
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!
Answers
-
Can you link to the documentation on how to use SQLExecutor2?
-
Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 412 Neuron
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!