pola-rs/polars · error · UnsuitableSQLError

{query_type} statements are not valid 'read' queries

Error message

{query_type} statements are not valid 'read' queries

What it means

Because read_database is a read API, ConnectionExecutor.execute guards it when select_queries_only=True (the default): it strips /* */ comments, finds the first word of 3+ letters via regex, and if it is one of _INVALID_QUERY_TYPES (ALTER, ANALYZE, CREATE, DELETE, DROP, GRANT, INSERT, REPLACE, REVOKE, UPDATE, UPSERT, USE, VACUUM) it raises UnsuitableSQLError - a polars exception. This is a footgun guard against accidental writes, not a security boundary; it is purely lexical (comment stripping plus first-token match), so it can both miss obfuscated writes and false-positive on statements merely starting with such a word.

Source

Thrown at py-polars/src/polars/io/database/_executor.py:545

        if cursor_execute is None:
            cursor_execute = (
                self._sqlalchemy_async_execute if is_async else self.cursor.execute
            )
        return cursor_execute, options, query

    def execute(
        self,
        query: str | TextClause | Selectable,
        *,
        options: dict[str, Any] | None = None,
        select_queries_only: bool = True,
    ) -> Self:
        """Execute a query and reference the result set."""
        if select_queries_only and isinstance(query, str):
            q = re.search(r"\w{3,}", re.sub(r"/\*(.|[\r\n])*?\*/", "", query))
            if (query_type := "" if not q else q.group(0)) in _INVALID_QUERY_TYPES:
                msg = f"{query_type} statements are not valid 'read' queries"
                raise UnsuitableSQLError(msg)

        options = options or {}

        if self._is_alchemy_object(self.cursor):
            cursor_execute, options, query = self._sqlalchemy_setup(query, options)
        else:
            cursor_execute = self.cursor.execute

        # note: some cursor execute methods (eg: sqlite3) only take positional
        # params, hence the slightly convoluted resolution of the 'options' dict
        try:
            params = signature(cursor_execute).parameters
        except ValueError:
            params = {}  # type: ignore[assignment]

        if not options or any(
            p.kind in (Parameter.KEYWORD_ONLY, Parameter.POSITIONAL_OR_KEYWORD)
            for p in params.values()

View on GitHub (pinned to df599052da)

Solutions

1. Move write/DDL statements to the driver's own execute method (conn.execute / cursor.execute), keeping read_database for SELECTs
2. If you deliberately need a non-SELECT through this API, pass select_queries_only=False - accepting that you are bypassing the read-only guard
3. Run setup statements (CREATE TEMP TABLE, USE) on a separate cursor before calling read_database with the SELECT

Example fix

# before
pl.read_database('INSERT INTO audit VALUES (1)', connection=conn)

# after - write via the driver, read via polars
conn.cursor().execute('INSERT INTO audit VALUES (1)')
df = pl.read_database('SELECT * FROM audit', connection=conn)

# after - or explicitly opt out of the read-only guard
pl.read_database('INSERT INTO audit VALUES (1)', connection=conn,
                 select_queries_only=False)

import re

INVALID = {'ALTER','ANALYZE','CREATE','DELETE','DROP','GRANT','INSERT',
           'REPLACE','REVOKE','UPDATE','UPSERT','USE','VACUUM'}

def is_read_query(sql: str) -> bool:
    stripped = re.sub(r'/\*(.|[\r\n])*?\*/', '', sql)
    first = re.search(r'\w{3,}', stripped)
    return not first or first.group(0).upper() not in INVALID

if not is_read_query(sql):
    raise ValueError(f'read_database is read-only; got: {sql[:40]!r}')
df = pl.read_database(sql, connection=conn)

from polars.exceptions import UnsuitableSQLError

try:
    df = pl.read_database(sql, connection=conn)
except UnsuitableSQLError:
    # route the statement to the driver's own writer path
    conn.cursor().execute(sql)
    df = None

When it happens

Trigger: pl.read_database('INSERT INTO t ...', connection=conn); 'USE schema_x' as a preamble; 'UPDATE ...' passed for side-effectful fetches; CTE wrappers do NOT trigger it (WITH ... UPDATE does not start with an invalid word), and a leading /* comment */ is stripped before the first-word check.

Common situations: Reusing a query-building helper that defaults to writing; executing temp-table setup (CREATE TEMP ...) alongside reads through one code path; migrating scripts from a generic execute() utility to read_database; users surprised that DDL/DML is blocked in a read API.

Related errors

• column {nm!r} appears more than once in the query/result cur
• Event loop stopped before Future completed.
• Can't patch loop of type %s
• {result}
• `read_database` queries require at least {module_name} versi

AI-assisted analysis of pola-rs/polars@df599052da (2026-08-16). Data as JSON: /api/errors/69a3e3a8ed74187a. Report an issue: GitHub.