Getting database info using cURL or another method?

Configuration
1

Hi all.
I’m trying to use the database for more than log storage. Namely, I want to use it to store and control a large number of users with different attributes to them, and later implement that list to check NFC card validity and some other stuff.
The problem I’m facing now is figuring out how to access the database in automations. I’m trying to use curl GET requests, possibly later implemented using python scripts, but I can’t find the API access to the database anywhere in the documentation.

Is it something that even exists? And if it doesn’t, is there another way to achieving this that I’m missing?

2

Have you looked at the SQL integration?

3

I have, but I’m either missing something or I’m just dumb. It shows queries but how do I use them for automations? I’ve been looking into command line sensors and python scripts to request the table but either way I’d need an API endpoint to access, which I can’t find anywhere, not even on the official developer documentation.

Again, this is probably me missing something due to lack of sleep/burnt neurons/divine retribution, but I seriously just can’t find it…

4

You go to Integrations in HA click “+Add Integration” choose SQL and create a sensor via the user interface.
That sensor can then be used in your automations.
I don’t think you can do anything more advanced than that in HA.
If you want to have direct access to the database you probably need to step outside HA and use something like Python.

5

You go to Integrations in HA click “+Add Integration” choose SQL and create a sensor via the user interface.

Tried that, output was a single value from a single row. I need the whole table to be ready, that means making queries dinamically.

If you want to have direct access to the database you probably need to step outside HA and use something like Python.

I thought of using an outside database, but that would introduce a slew of other problems.

If I can’t use a database, I’d be satisfied with just using a file and getting the info I need from there. But the file integration only returns the last line of the file, which I find really weird.

Guess my only option is scripting?

6

You are basically trying to do data processing, your best option is to go outside of HA since HA is not the best tool for that.
And since the default database engine is SQLite and stored in home-assistant_v2.db you could create a python script to accomplish that.