Running SQL statements with Python?

dev1 · June 2022

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')

Ignacio_Toledo · June 2022

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!