Dropping Database Tables from python

[stainlessteelra]

Trying to use dataiku's python environment to operationalize some tasks.

Specifically here it is needing to drop a table from a Redshift connection. I can successfully get the code to read the data but if i change the query to drop the table, then it fails.

api_key = personal_api_key

local_host = "host_name"

client = dataikuapi.DSSClient(local_host, local_api_key)
client._session.verify = False

CONNECTION = "nonprod_redshift"
QUERY = '''drop table schema.table_name;'''

# Using remote client, query against Redshift util table containing info
results = client.sql_query(connection=CONNECTION, query=QUERY)

---

Operating system used: Windows

[AlexT · Accepted solution]

Hi @stainlessteelra ,

With sql_query()  you can only result as an iterator and will not be able to use used to drop tables. 

However, if you want to be able to use the DSS connections details for the database one you can use a SQL Script Recipe.  Create the SQL script recipe with a dummy output and place your drop statement there and then call their recipe from your python code. YOu can if needed update the SQL statement from the created recipe later on. 

import dataikuapi

api_key = '<replace-api-key>'
local_host = "http://localhost:11000"
client = dataikuapi.DSSClient(local_host, api_key)
client._session.verify = False

project = client.get_project('PYTHON_TESTS')

my_script_recipe = project.get_recipe("compute_test_dummy_dataset")
my_script_recipe.run()

[AlexT · Accepted solution]

Hi @stainlessteelra ,

With sql_query()  you can only result as an iterator and will not be able to use used to drop tables. 

However, if you want to be able to use the DSS connections details for the database one you can use a SQL Script Recipe.  Create the SQL script recipe with a dummy output and place your drop statement there and then call their recipe from your python code. YOu can if needed update the SQL statement from the created recipe later on. 

import dataikuapi

api_key = '<replace-api-key>'
local_host = "http://localhost:11000"
client = dataikuapi.DSSClient(local_host, api_key)
client._session.verify = False

project = client.get_project('PYTHON_TESTS')

my_script_recipe = project.get_recipe("compute_test_dummy_dataset")
my_script_recipe.run()

[stainlessteelra]

Thanks @AlexT !

That got me to a solution, still not elegant as I would have hoped for.

@Dataiku Team, Please add a python call that allows direct SQL execution for other purposes other than iterating over rows

[CoreyS · Accepted solution]

@stainlessteelra sounds like an interesting idea! Why not submit it on the Product Ideas Board?  The Product Ideas board is here to let you share and exchange your ideas on how to improve Dataiku!