github IAsDoubleYou/homeassistant-mysql_query v2.1.0
v2.1.0 - Parameterized queries

latest releases: v4.2.1, v4.2.0, v4.1.0...
3 months ago

v2.1.0 - Parameterized queries - What's new

mysql_query.query and mysql_query.execute now accept an optional values list, so you can write parameterized queries instead of building SQL strings with templates.

Put a %s placeholder in the statement for every value and pass the values in the same order. The values travel to MySQL separately from the statement, and the driver quotes and escapes each one according to its type — a quote, a semicolon or a backslash in your data can no longer change what the statement does.

actions:
  - action: mysql_query.execute
    data:
      query: >-
        INSERT INTO ha_measurements (entity_id, value, measured_at)
        VALUES (%s, %s, %s)
      values:
        - sensor.power_consumption
        - "{{ states('sensor.power_consumption') | float(0) }}"
        - "{{ now().strftime('%Y-%m-%d %H:%M:%S') }}"
    response_variable: insert_result

Templates keep their data type

Every value is rendered through the Home Assistant template engine with native typing, so numeric and boolean columns get real numbers and booleans, and none becomes a real SQL NULL:

┌──────────────────────────────────────────────┬──────────────────┬─────────────┐
│               Value in values                │     Bound as     │ Python type │
├──────────────────────────────────────────────┼──────────────────┼─────────────┤
│ "{{ states('sensor.temperature') | float }}" │ 21.5             │ float       │
├──────────────────────────────────────────────┼──────────────────┼─────────────┤
│ "{{ now().year }}"                           │ 2026             │ int         │
├──────────────────────────────────────────────┼──────────────────┼─────────────┤
│ "{{ is_state('light.kitchen', 'on') }}"      │ 1                │ bool        │
├──────────────────────────────────────────────┼──────────────────┼─────────────┤
│ "{{ none }}"                                 │ NULL             │ None        │
├──────────────────────────────────────────────┼──────────────────┼─────────────┤
│ "sensor.kitchen"                             │ 'sensor.kitchen' │ str         │
└──────────────────────────────────────────────┴──────────────────┴─────────────┘

A plain value without {{ ... }} is passed through untouched and is never reinterpreted: "42" stays the string "42", and a value like "0612345678" keeps its leading zero.

Things to know

- One placeholder, one value. %s never expands a list, so WHERE id IN (%s) with a list does not work — write one placeholder per value. Identifiers (table and column names) cannot be parameterized either.
- Percent signs. As soon as you pass values, a literal % in the statement has to be doubled (LIKE '%%kitchen%%'), or better, passed as a value: LIKE %s with "%kitchen%". Without values, nothing in the statement is interpreted and LIKE '%kitchen%' keeps working as before.
- Failing templates are handled like any other failure: query raises and stops the automation, execute returns succeeded: false with the details in error. The statement is not sent to the database in that case.

Upgrading

Fully backward compatible — no changes needed. Leave values out and every statement is sent exactly as it was before.

Installation

Install through HACS, or download homeassistant-mysql_query.zip below and extract it straight into custom_components/mysql_query. The archive has no top-level folder, so __init__.py, manifest.json and the translations folder must end up directly in that directory. Restart Home Assistant afterwards.

Full changelog: https://github.com/IAsDoubleYou/homeassistant-mysql_query/compare/v2.0.1...v2.1.0

Don't miss a new homeassistant-mysql_query release

NewReleases is sending notifications on new releases.