Dropping Database Tables from python
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
Best Answers
-
Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,217 Dataiker
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()
-
CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭
@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!