Databricks streamlit app for data ingestion in a table

manish_tanwar — Fri, 11 Apr 2025 07:06:37 GMT

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.")

Re: Databricks streamlit app for data ingestion in a table

manish_tanwar — Fri, 11 Apr 2025 09:47:56 GMT

Thanks for passing by I have solved the issue ....

Re: Databricks streamlit app for data ingestion in a table

jaideep — Sat, 12 Apr 2025 09:00:33 GMT

HI @manish_tanwar - Do you mind posting the code for others' reference? Thank you!

Re: Databricks streamlit app for data ingestion in a table

pradeepvatsvk — Tue, 15 Apr 2025 10:14:08 GMT

Hi @manish_tanwar how can we work with streamlit apps in databricks , i have a use case where i want to ingest data from different csv files and ingest it into delta tables

Re: Databricks streamlit app for data ingestion in a table

manish_tanwar — Tue, 15 Apr 2025 10:37:34 GMT

@pradeepvatsvk Well, it quite simple if you

1. Create tables with matching column names of csv files

2. Change all the csvs to dictionaries and

3. Run the save as above against corresponding to tables

That should work fine

Re: Databricks streamlit app for data ingestion in a table

pradeepvatsvk — Wed, 16 Apr 2025 16:05:20 GMT

Are you hosting/building your streamlit application in databricks apps??

topic Re: Databricks streamlit app for data ingestion in a table in Data Engineering

Databricks streamlit app for data ingestion in a table

Re: Databricks streamlit app for data ingestion in a table

Re: Databricks streamlit app for data ingestion in a table

Re: Databricks streamlit app for data ingestion in a table

Re: Databricks streamlit app for data ingestion in a table

Re: Databricks streamlit app for data ingestion in a table