db.perform_query fails when called with a string: sqlalchemy not an executable object/no attribute '_execute_on_connection'

[rct]

Calling db.perform_query() with a query in a string results in the following errors:

• sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: <query string>
• AttributeError: 'str' object has no attribute '_execute_on_connection'

Tested current master with sqlalchemy==2.0.7 (version from requirements.txt). See #157.

A work around identified in #157 appears to be to use sqlalchemy.text() to convert the query string.

Here is a pytest case test_query.py. The first test succeeds, the 2nd falls.

import detective.core as detective
import detective.functions as functions
import pandas as pd
import sqlalchemy

db_url = "sqlite:///tests/test.db"

def test_query():
    db = detective.HassDatabase(db_url)
    assert db is not None
    assert len(db.entities) > 0

    # Temporary: show perform_query works with sqlalchemy.txt
    df = db.perform_query(sqlalchemy.text("SELECT 1;"));
    assert df is not None

    # a raw string currently doesn't work
    df = db.perform_query("SELECT 1;");
    assert df is not None

If db.perform_query() should accept a string, then it should do what's necessary to prepare it for sqlalchemy's execution.

[rct]

There are reports that the breaking change started in SQLAlchemy 2.0 and impacted other projects (like pandas).