Skip to content

linuxfabrik.lfops.sqlite_query

Run a read-only SQLite query

Synopsis

  • Connects to a SQLite database file and runs a single SELECT query against it.
  • The query is parameterized via named_args using SQLite's named placeholders (e.g. :my_arg), so user-provided values do not have to be string-concatenated into the SQL.
  • The connection is opened with a 1-second busy timeout, returns rows as dictionaries by default, and registers a Python-backed REGEXP function so that WHERE col REGEXP '...' works (SQLite has no built-in regex implementation).
  • The module is read-only by design - query_type currently only accepts select, no transaction is committed, and the connection is closed (without commit) at the end. As a result, the module always reports changed=false.

Available since LFOps 2.0.0.

Mandatory Parameters

db

  • Filename of the SQLite database file. Combined with path using os.path.join to form the full path.
  • Type: String.

path

  • Directory the database file lives in. Combined with db to form the full path.
  • Type: String.

query

  • The SQL SELECT statement to run. Use named placeholders (:name) for any user-provided values and pass them through named_args.
  • Type: String.

Optional Parameters

as_dict

  • When true (the default), each result row is returned as a dictionary keyed by column name. When false, rows are returned as positional tuples.
  • Type: Bool.
  • Default: true

fetch_one

  • When true, return only the first row of the result set instead of all rows. If there are no rows, an empty list is returned.
  • Type: Bool.
  • Default: false

named_args

  • Dictionary of values bound to SQLite named placeholders (:name) inside query. Use this instead of formatting values into the SQL string to avoid injection.
  • Type: Dictionary.

query_type

  • Type of SQL query to run. Currently only select is implemented; the option exists to leave room for future write-capable variants.
  • Type: String. One of select.
  • Default: select

Examples

- name: 'Simple select query'
  linuxfabrik.lfops.sqlite_query:
    db: 'acme.db'
    path: '{{ role_path }}/library'
    query: 'SELECT sqlite_version();'

- name: 'Parameterized select query'
  linuxfabrik.lfops.sqlite_query:
    db: 'stig.db'
    path: '{{ role_path }}/library'
    query: 'SELECT control_id FROM profile WHERE profile_name = :profile_name AND enabled = :enabled'
    named_args:
      profile_name: 'CIS CentOS 7'
      enabled: 1
  delegate_to: 'localhost'

Return Values

changed

  • Always false. The module never modifies the database.
  • Type: Bool.
  • Returned: always.
  • Sample: False

query

  • The SQL query that was executed, echoed back unchanged.
  • Type: String.
  • Returned: always.
  • Sample: SELECT control_id FROM profile WHERE enabled = :enabled

query_result

  • Rows returned by the query. With as_dict=true (default), a list of dicts keyed by column name; with as_dict=false, a list of positional tuples. With fetch_one=true, only the first row is returned (as a single dict / tuple), or an empty list if the query produced no rows.
  • Type: List.
  • Returned: always.

query_type

  • The query type that was executed.
  • Type: String.
  • Returned: always.
  • Sample: select

rowcount

  • Number of rows in query_result. With fetch_one=true this is 0 when no rows were returned and 1 (or the field count of the row) otherwise, since len() is taken over the returned object.
  • Type: Number.
  • Returned: always.
  • Sample: 42

Authors

  • Linuxfabrik GmbH, Zurich, Switzerland