I am using this code in a notebook to save data row on table. And it is working perfectly. And now I am using the same function to save data from a chatbot in streamlit chatbot application of databricks and I am getting error for
ERROR ###################################
Failed to save to usdev_rnd.gme_luminai.feedback_tracker: [PARSE_SYNTAX_ERROR] Syntax error at or near '%'. SQLSTATE: 42601 (line 1, pos 149)
== SQL == INSERT INTO usdev_rnd.gme_luminai.feedback_tracker (Username, Email, UserID, Query, Response, ChatHistory, Timestamp, Feedback, Suggestions) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) -----------------------------------------------------------------------------------------------------------------------------------------------------^^^
ERROR#####################################
And when I changed the value of %S to "?" in the app code it is failing to save the data. So, please help me to correct it or give any reason for the same.
# Import necessary libraries import logging
import os
from databricks import sql
from datetime import datetime
# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
# Databricks connection configuration (Consider using Databricks Secrets)
DATABRICKS_SERVER_HOSTNAME = os.getenv("DATABRICKS_SERVER_HOSTNAME")
DATABRICKS_HTTP_PATH = os.getenv("DATABRICKS_HTTP_PATH")
DATABRICKS_ACCESS_TOKEN = os.getenv("DATABRICKS_ACCESS_TOKEN")
## Function to save data to the database
def save_to_database(table, data๐
try:
with sql.connect(server_hostname=DATABRICKS_SERVER_HOSTNAME,
http_path=DATABRICKS_HTTP_PATH,
access_token=DATABRICKS_ACCESS_TOKEN) as conn:
with conn.cursor() as cur:
columns = ", ".join(data.keys())
placeholders = ", ".join(["%s"] * len(data))
query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
logger.info(f"SQL Query: {query}")
logger.info(f"Data: {data}")
data_values = list(data.values())
cur.execute(query, tuple(data_values))
conn.commit()
logger.info(f"Data saved to {table} successfully.")
except Exception as e:
logger.error(f"Failed to save to {table}: {e}")
print(f"Failed to save to {table}: {e}")
# Example usage
if __name__ == "__main__":
feedback_data = {
"Username": "user123",
"Email": "user@example.com",
"UserID": "abc-456",
"Query": "What is the capital of France?",
"Response": "The capital of France is Paris.",
"ChatHistory": "[{'user': '...', 'bot': '...'}]",
"Timestamp": datetime.now(),
"Feedback": "1",
"Suggestions": "Test1"
}
table_name = "catalog_name.schema_name.feedback_tracker"
if DATABRICKS_SERVER_HOSTNAME and DATABRICKS_HTTP_PATH and DATABRICKS_ACCESS_TOKEN:
save_to_database(table_name, feedback_data)
else:
logger.warning("Databricks connection parameters not found. Cannot save to database.")
print("Databricks connection parameters not found. Cannot save to database.")
