UNBOUND_SQL_PARAMETER with Runtime 15.4

Ouverte
#466 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
35/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
À l'abandon
Stack technique
python, sql, sqlalchemy
Domaine
backend-api-design, databases

Piste de recherche

Commencez par reproduire la requête avec Cursor.execute dans databricks/sql/client.py, puis suivez l’opération préparée et les paramètres jusqu’à ThriftBackend.execute_command dans databricks/sql/thrift_backend.py. Comparez ce chemin avec le comportement fonctionnel du notebook et avec l’issue 288 ; le travail est terminé lorsque le mapping :myparam est lié avec succès sans intégrer les valeurs directement.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

Hello, I have trouble using parameters with databricks-sql-python, using a Databricks cluster with Unity Catalog with Runtime 15.4.

This will fail:

from databricks import sql as databricks_sql

with databricks_sql.connect(
    server_hostname="...",
    http_path="...",
    auth_type="databricks-oauth",
    catalog="...",
    schema="...",
) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            "SELECT * FROM experimentation.time_series WHERE experimentation.time_series.name = :myparam",
            {"myparam": "myvalue"},
        )
        result = cursor.fetchall()
result

with the following exception: ServerOperationError: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: myparam. Please, fix args and provide a mapping of the parameter to a SQL literal.; line 1 pos 83

Stacktrace 
---------------------------------------------------------------------------
ServerOperationError                      Traceback (most recent call last)
Cell In[1], line 14
      6 with databricks_sql.connect(
      7     server_hostname="...",
      8     http_path="...",
   (...)
     11     schema="...",
     12 ) as connection:
     13     with connection.cursor() as cursor:
---> 14         cursor.execute(
     15             "SELECT * FROM experimentation.time_series WHERE experimentation.time_series.name = :myparam",
     16             {"myparam": "myvalue"},
     17         )
     18         result = cursor.fetchall()
     19 result

File ...\Lib\site-packages\databricks\sql\client.py:796, in Cursor.execute(self, operation, parameters)
    794 self._check_not_closed()
    795 self._close_and_clear_active_result_set()
--> 796 execute_response = self.thrift_backend.execute_command(
    797     operation=prepared_operation,
    798     session_handle=self.connection._session_handle,
    799     max_rows=self.arraysize,
    800     max_bytes=self.buffer_size_bytes,
    801     lz4_compression=self.connection.lz4_compression,
    802     cursor=self,
    803     use_cloud_fetch=self.connection.use_cloud_fetch,
    804     parameters=prepared_params,
    805 )
    806 self.active_result_set = ResultSet(
    807     self.connection,
    808     execute_response,
   (...)
    811     self.arraysize,
    812 )
    814 if execute_response.is_staging_operation:

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:849, in ThriftBackend.execute_command(self, operation, session_handle, max_rows, max_bytes, lz4_compression, cursor, use_cloud_fetch, parameters)
    831 req = ttypes.TExecuteStatementReq(
    832     sessionHandle=session_handle,
    833     statement=operation,
   (...)
    846     parameters=parameters,
    847 )
    848 resp = self.make_request(self._client.ExecuteStatement, req)
--> 849 return self._handle_execute_response(resp, cursor)

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:941, in ThriftBackend._handle_execute_response(self, resp, cursor)
    938 cursor.active_op_handle = resp.operationHandle
    939 self._check_direct_results_for_error(resp.directResults)
--> 941 final_operation_state = self._wait_until_command_done(
    942     resp.operationHandle,
    943     resp.directResults and resp.directResults.operationStatus,
    944 )
    946 return self._results_message_to_execute_response(resp, final_operation_state)

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:787, in ThriftBackend._wait_until_command_done(self, op_handle, initial_operation_status_resp)
    785     poll_resp = self._poll_for_status(op_handle)
    786     operation_state = poll_resp.operationState
--> 787     self._check_command_not_in_error_or_closed_state(op_handle, poll_resp)
    788 return operation_state

File ...\Lib\site-packages\databricks\sql\thrift_backend.py:572, in ThriftBackend._check_command_not_in_error_or_closed_state(self, op_handle, get_operations_resp)
    570 if get_operations_resp.operationState == ttypes.TOperationState.ERROR_STATE:
    571     if get_operations_resp.displayMessage:
--> 572         raise ServerOperationError(
    573             get_operations_resp.displayMessage,
    574             {
    575                 "operation-id": op_handle
    576                 and self.guid_to_hex_id(op_handle.operationId.guid),
    577                 "diagnostic-info": get_operations_resp.diagnosticInfo,
    578             },
    579         )
    580     else:
    581         raise ServerOperationError(
    582             get_operations_resp.errorMessage,
    583             {
   (...)
    587             },
    588         )
Dependencies 
sqlalchemy = "^2.0.36"
jupyter = "^1.1.1"
databricks-connect = "15.4.3"
databricks-sql-connector = {version = "^3.6.0", extras = ["sqlalchemy", "alembic"]}

However, this works using a Databricks notebook:

%python
myparam = 'myvalue'

%sql
SELECT * FROM experimentation.time_series 
WHERE experimentation.time_series.name = :myparam

Please note that this issue seems related to https://github.com/databricks/databricks-sql-python/issues/288.

As a temporary workaround, I can inline the parameters inside the query as documented here for SqlAlchemy: https://docs.sqlalchemy.org/en/20/faq/sqlexpressions.html#rendering-bound-parameters-inline However, this is not recommended for security reasons.

Langage dominant
Python
Étoiles
233
Forks
152
Merge moyen
21 h 5 min
PR mergées (30 j)
10

Guide de contribution

Ouvrir le guide de contribution

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de databricks/databricks-sql-python

Toutes les issues de databricks/databricks-sql-python

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.