SQL query escape colon

Configuration
1

I like to have sensor with latest game played from Discord sensor so I tried to make SQL sensor with this query but I don’t know ho to correctly escape colon

- platform: sql
  queries:
    - name: DimmuBoy#1658 Latest
      query: "SELECT * FROM states WHERE entity_id = 'sensor.dimmuboy_1658' AND attributes NOT LIKE '%\"game\"\:null%' ORDER BY state_id DESC LIMIT 1;"
      column: "state"

this returns syntax error found unknown escape character ':'

2

Why would you want to escape the colon in the first place?

3

Because without it I get error

Error executing query SELECT * FROM states WHERE entity_id = 'sensor.dimmuboy_1658' AND attributes NOT LIKE '%"game":null%' ORDER BY state_id DESC LIMIT 1;: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'null' [SQL: SELECT * FROM states WHERE entity_id = 'sensor.dimmuboy_1658' AND attributes NOT LIKE '%"game"?%' ORDER BY state_id DESC LIMIT 1;] [parameters: [{}]] (Background on this error at: https://sqlalche.me/e/14/cd3x)
1 Like
4

You shouldn’t need to escape a colon or the double quotes as it’s wrapped in the like and actually a part of the attribute data.

Try this:

- platform: sql
  queries:
    - name: DimmuBoy#1658 Latest
      query: "SELECT * FROM states WHERE entity_id = 'sensor.dimmuboy_1658' AND attributes NOT LIKE '%"game":null%' ORDER BY state_id DESC LIMIT 1;"
      column: "state"

I’ve tried this with the Xbox Live data, but if you can point me in the direction of the Discord Sensor you’re using happy to play around and help.

5

I tried that but config validator returns error

in "/config/sensors/discord.yaml", line 14, column 7
expected <block end>, but found '<scalar>'
in "/config/sensors/discord.yaml", line 15, column 104

Here is Discord sensor GitHub - LordBoos/discord_game: Home Assistant custom component to get online and game status of Discord users

6

Try replacing (without escaping) the colon with an underscore.

With “LIKE”, it should match any single character.

1 Like
7

Of course, you’re right!
It works with one replaced character but escaped quotes

- platform: sql
  queries:
    - name: DimmuBoy#1658 Latest
      query: "SELECT * FROM states WHERE entity_id = 'sensor.dimmuboy_1658' AND attributes NOT LIKE '%game\"_null%' ORDER BY state_id DESC LIMIT 1;"
      column: "state"