Running SQL statements with Python?

dev1 Registered Posts: 2 ✭✭✭

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
    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: 411 Neuron
    edited July 17 Answer ✓

    Hi @dev1

    There are several options:

    1. 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
    2. 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!


Setup Info
      Help me…