Web form that update and query an sqlite db using pyscript

hi, good evening, im a noob i m not good enough writing code and im trying to acomplish something i have a sqlite db on HA, i create a service using pyscript so it can query info from it and update it too, i have i test it and works flawless, i have use it with the notification system etc,

import sqlite3
import requests
from homeassistant.const import EVENT_CALL_SERVICE

@service
def get_sensor_web(search_term):
    sensorweb_db_path = '/config/testwebdb.db'  
    connection = sqlite3.connect(sensorweb_db_path)
    cursor = connection.cursor()
    cursor.execute("SELECT (location || ' y ' || sensor_value ) as searchTerm FROM websensor WHERE location LIKE ? LIMIT 1", ('%' + search_term + '%',))
    results = cursor.fetchall()
    cursor.close()
    connection.close()
    hass.bus.fire("get_data_address_event", {"wow": results})
    print(results)
    response = requests.post(url, headers=headers, json=data)
    print(response.json())
    return results if results else None

so now im trying to make a web form that call that service using rest api, it almos work… i can make the web form execute the script via api but it doent showme any responce/ result , but in the event lisen i can see the result so i dont know what im missing

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Search Form</title>
</head>
<body>
    <h1>Search Data</h1>
    <form id="dataForm">
        <label for="search_term">Search Term:</label>
        <input type="text" id="search_term" name="search_term" required>
        <button type="submit">Submit</button>
    </form>
    <div id="result"></div>

    <script>
        document.getElementById('dataForm').addEventListener('submit', function(e) {
            e.preventDefault(); // Prevent the default form submission

            const searchTerm = document.getElementById('search_term').value;

            fetch('/api/services/pyscript/get_sensor_web', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': 'Bearer MYTOKEN' 
                },
                body: JSON.stringify({ search_term: searchTerm })
            })
            .then(response => {
                if (!response.ok) {
                    throw new Error('Network response was not ok ' + response.statusText);
                }
                return response.json();
            })
            .then(data => {
                console.log(data); // Log the response for debugging
                document.getElementById('result').innerText = JSON.stringify(data, null, 2); // Display the result
            })
            .catch(error => {
                console.error('Error:', error);
                document.getElementById('result').innerText = 'Error: ' + error.message;
            });
        });
    </script>
</body>
</html>

i dont know whats wrong when i query it only return to me but in the event listener i see the query result/response/value, please helpme

These ‘debugging’ statements are probably messing up the response returned to the browser

Check what is being returned back to the browser, I bet your JSON.stringify(data, null, 2) is failing

hi i remove both print() items as you suggest but after i press the search button it still showme only square bracket “[” “]” but the script is executed and showme in the event lisent the result…any ideas?

You say you have created a Service in homeassistant using a pyscript (I did not know that was possible).

Are you able to add a console.log() and share the response you see in the debugging console in your browser?

.then(response => {
                // Lets add some debugging so we can actually see the response
                console.log('The response is: ', response)
                if (!response.ok) {
                    throw new Error('Network response was not ok ' + response.statusText);
                }
                return response.json();
            })

Or can you share what you are seeing in the event listener?

We’ll need to see what you’re seeing to be able to help

Reading this may also be of use - it appears that your service is not stating that it is going to send a response

Py script service with response

hi, ok i mod as you told me i hope i did right

import sqlite3
import requests
from homeassistant.const import EVENT_CALL_SERVICE

@service(supports_response="only")
def get_sensor_web(search_term, return_response=True):
    taxiamigo_db_path = '/config/testwebdb.db'  # Replace with the actual path
    connection = sqlite3.connect(taxiamigo_db_path)
    cursor = connection.cursor()
    cursor.execute("SELECT (location || ' y ' || sensor_value ) as searchTerm FROM websensor WHERE location LIKE ? LIMIT 1", ('%' + search_term + '%',))
    results = cursor.fetchall()
    cursor.close()
    connection.close()
    hass.bus.fire("get_sensor_web_event", {"wow": results})
    response = requests.post(url, headers=headers, json=data)
    return { "get_sensor_web": get_data_address}

this is what i receive in my event listener

event_type: get_sensor_web_event
data:
  wow:
    - - VIA A VALENCIA Y FUNCIONA
origin: LOCAL
time_fired: "2024-05-25T23:43:12.833534+00:00"
context:
  id: 01HYS263E1A86E1KATJVJ8XPPF
  parent_id: null
  user_id: null

and this is what it showme in the chrome console

index.html:34 The response is:  Response {type: 'basic', url: 'https://xxxxxxxxxxxxxxxxxxxx/api/services/pyscript/get_sensor_web', redirected: false, status: 200, ok: true, …}
body: (…)
bodyUsed: true
headers: Headers
[[Prototype]]: Headersappend: ƒ append()delete: ƒ delete()entries: ƒ entries()forEach: ƒ forEach()get: ƒ ()getSetCookie: ƒ getSetCookie()has: ƒ has()keys: ƒ keys()set: ƒ ()values: ƒ values()constructor: ƒ Headers()Symbol(Symbol.iterator): ƒ entries()Symbol(Symbol.toStringTag): "Headers"
[[Prototype]]: Object
ok: true
redirected: false
status: 200
statusText: "OK"
type: "basic"url: "https://xxxxxxxxxxxxxxxxxxx/api/services/pyscript/get_sensor_web"[[Prototype]]: Response
index.html:47 []length: 0[[Prototype]]: Array(0)at: ƒ at()concat: ƒ concat()constructor: ƒ Array()copyWithin: ƒ copyWithin()entries: ƒ entries()every: ƒ every()fill: ƒ fill()filter: ƒ filter()find: ƒ find()findIndex: ƒ findIndex()findLast: ƒ findLast()findLastIndex: ƒ findLastIndex()flat: ƒ flat()flatMap: ƒ flatMap()forEach: ƒ forEach()includes: ƒ includes()indexOf: ƒ indexOf()join: ƒ join()keys: ƒ keys()lastIndexOf: ƒ lastIndexOf()length: 0map: ƒ map()pop: ƒ pop()push: ƒ push()reduce: ƒ reduce()reduceRight: ƒ reduceRight()reverse: ƒ reverse()shift: ƒ shift()slice: ƒ slice()some: ƒ some()sort: ƒ sort()splice: ƒ splice()toLocaleString: ƒ toLocaleString()toReversed: ƒ toReversed()toSorted: ƒ toSorted()toSpliced: ƒ toSpliced()toString: ƒ toString()unshift: ƒ unshift()values: ƒ values()with: ƒ with()Symbol(Symbol.iterator): ƒ values()Symbol(Symbol.unscopables): {at: true, copyWithin: true, entries: true, fill: true, find: true, …}
[[Prototype]]: Object


i forgot something from the console

 index.html:1 Uncaught (in promise) Error: A listener indicated an asynchronous response by returning true, but the message channel closed before a response was received

You have so many issues in your code that you are not going to even get a response delivered back to your web page that the JavaScript can respond to.

It appears you may be quite out of your depth with this code, and may have copied it from multiple examples from other sources.

It might be a good idea to communicate to this forum what is your goal. instead of trying to get this code working. For instance what are you hoping to have in this

<div id="result"></div>

It seems that all you are trying to achieve is return a single string from the first matching row in a database table and show it on a page.

You can achieve this in so many more simple ways in homeassistant without having to create a separate HTML web page with a search input and custom Javascript.

Please let us know what you are hoping to achieve so we can help more easily.

hi, good morning, im sorry for my delay ansering im from Ecuador and took some medicines that make sleep, im sorry just now its arround 10 am in the mornng.

hass.bus.fire("get_sensor_web_event", {"wow": results})

i use that line for debuggin, that line helpme to see in the the menu Develeper Tools-Events-Lisent to Events the result or return of my script in this case the search for now, dont know if its the best or right way but it works i use it with other scripts that i use and works cool for me.

It appears you may be quite out of your depth with this code, and may have copied it from multiple examples from other sources.

yes you are right, as i said in the begining of the post im not good enough writing code im learning at the same time that im trying to acomplish something in HA, i read test read again test, all my other scripts work fine and helpme a lot, but this one the part of call that script from web and now that it show the result in the same web form has surpassed me, i run out of ideas even ask many IA but didnt find a way so thats why its a mess that code and thats why im asking for advice or help :frowning:

It seems that all you are trying to achieve is return a single string from the first matching row in a database table and show it on a page.

Please let us know what you are hoping to achieve so we can help more easily.

Okey i will explain :slight_smile: , for now im trying to only have the return of a single string from the first maching row as you said, but the next objetive its to have 2 fields that use database-autocomplete, the first field will be the search_term that will look in the location column and the second field/search_term2 will show the few options from the sensor column that mach what i chooose in the first field and then search to show the result that will be some extra colums and later update those fields or even insert new ones.

i want to access that page from the outside and it works, without the need of logging in my HA, but for security i dont want to leave the code or the access to db,query,tables or the access to the HA in the code of the html so thats why im trying to use the pyscript as a service to make the background job(it that called backend?)
i hope you can helpme , thanks

im sorry for my bad english