Python SQL INSERT statement query with variables
I pulled this example from an old post, and also tried following the example from
This example without variables works fine!
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'])
I tried to insert a value as a variable instead
from dataiku import SQLExecutor2 sqlcon = SQLExecutor2(dataset="output_table") sqlcon.query_to_df(""" INSERT INTO "public"."output_table" (col_one, col_two) VALUES(%s, %s); """ % (variable_one, variable_two), post_queries=['COMMIT'])
However i get an error on the post_queries=['COMMIT'] line. Any ideas?
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,138 Neuron
Can you please post the full error trace?
-
Sure! I used the generic example above, this is specific to my use case but same result
--------------------------------------------------------------------------- Exception Traceback (most recent call last) <ipython-input-12-ecc7922dcc07> in <module> 7 """ 8 % (test_sso, 'Jane Doe', '123', 'Test Pym', 'Control Title Holder', 'CTH'), ----> 9 post_queries=['COMMIT']) 10 print("Data updated Successfully") /u01/dataiku/dataiku-dss-12.5.1/python/dataiku/core/sql.py in query_to_df(self, query, pre_queries, post_queries, extra_conf, infer_from_schema, parse_dates, bool_as_str, dtypes, script_steps, script_input_schema, script_output_schema) 242 if not self._iconn: 243 raise ValueError("SQLExecutor2 not configured: either 'connection' or 'dataset' are required (in SQLExecutor2 constructor)") --> 244 return _streamed_query_to_df(self._iconn, query, pre_queries, post_queries, self._find_connection_from_dataset, "sql", extra_conf, infer_from_schema=infer_from_schema, parse_dates=parse_dates, bool_as_str=bool_as_str, dtypes=dtypes, script_steps=script_steps, script_input_schema=script_input_schema, script_output_schema=script_output_schema) 245 246 def query_to_iter(self, query, pre_queries=None, post_queries=None, extra_conf={}, script_steps=None, script_input_schema=None, script_output_schema=None): /u01/dataiku/dataiku-dss-12.5.1/python/dataiku/core/sql.py in _streamed_query_to_df(connection, query, pre_queries, post_queries, find_connection_from_dataset, db_type, extra_conf, infer_from_schema, parse_dates, bool_as_str, dtypes, script_steps, script_input_schema, script_output_schema) 32 # initiate the streaming (blocks until the database says it's ready to return values) 33 streamingSession = jek_or_backend_json_call("sql-queries/start-streaming", ---> 34 data = data) 35 36 logger.info("Got initial SQL query response") /u01/dataiku/dataiku-dss-12.5.1/python/dataiku/core/intercom.py in jek_or_backend_json_call(path, data, err_msg, **kwargs) 470 return jek_json_call(path, data, err_msg, **kwargs) 471 else: --> 472 return backend_json_call(path, data, err_msg, **kwargs) 473 474 def lambda_or_backend_json_call(path, data=None, err_msg=None, **kwargs): /u01/dataiku/dataiku-dss-12.5.1/python/dataiku/core/intercom.py in backend_json_call(path, data, err_msg, **kwargs) 458 459 def backend_json_call(path, data=None, err_msg=None, **kwargs): --> 460 return _handle_json_resp(backend_api_post_call(path, data, **kwargs), err_msg = err_msg) 461 462 def jek_json_call(path, data=None, err_msg=None, **kwargs): /u01/dataiku/dataiku-dss-12.5.1/python/dataiku/core/intercom.py in _handle_json_resp(resp, err_msg) 557 err_data = resp.text 558 if err_data: --> 559 raise Exception("%s: %s" % (err_msg, _get_error_message(err_data).encode("utf8"))) 560 else: 561 raise Exception("%s: %s" % (err_msg, "No details")) Exception: None: b'During query \'\n INSERT INTO eng_analytics_eng.etcr_pyrm_updated (candidate_sso, full_name_per, time_on_job_mo, primary_pyramid_name, cth_level_name, cth_level_short)\n VALUES (98745, Jane Doe, 123, Test Pym, Control Title Holder, CTH);\n \': ERROR: syntax error at or near "Doe"\n Position: 180, caused by: RedshiftException: ERROR: syntax error at or near "Doe"\n Position: 180'
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,138 Neuron
You are mising quotes or double quotes in your string columns, whatever Redshift requires. You can test your insert in a Dataiku SQL Notebook first to be sure of the synthax.
-
Hm, not sure if its a quote issue. I can use this with psycopg and it works no problem... i was converting this over to use the SQLexecutor.
Here is the code I'm trying to execute, if you think there needs to be a change can you help be identify it?
test_sso = 98745 sqlcon.query_to_df(""" INSERT INTO eng_analytics_eng.etcr_pyrm_updated (candidate_sso, full_name_per, time_on_job_mo, primary_pyramid_name, cth_level_name, cth_level_short) VALUES (%s, %s, %s, %s, %s, %s); """ % (test_sso, 'Jane Doe', '123', 'Test Pym', 'Control Title Holder', 'CTH'), post_queries=['COMMIT']) print("Data updated Successfully")