transform=True fails when you have an index on a column name with an underscore

[colinatkepler]

I'm using this project (sqlite_minutils) as a part of a FastHTML web app and came across a bug. I used python=3.12 and sqlite-minutils==3.37.0.post3. Here's a minimal reproduction:

#script.py

from fasthtml.common import Database
from datetime import datetime

db = Database('test.db')

class Request:
    id: str
    time: str
    # run with time_check and its index commented out first
    time_check: str

requests = db.create(Request, if_not_exists=True, transform=True)
requests.create_index(('time',), unique=True, if_not_exists=True)
# first run with this index creation commented out
requests.create_index(('time_check',), unique=True, if_not_exists=True)

for i in range(5):
    requests.insert(Request(time=datetime.now()))

To reproduce, simply first comment out time_check: str and the index on time_check, run python script.py. Then uncomment time_check and its index creation and re-run python script.py. You will get the following error, which is caused by the query returning nothing, and the index [0] trying to grab what isn't there.

File venv/lib/python3.12/site-packages/sqlite_minutils/db.py:1910, in Table.transform_sql(self, types, rename, drop, pk, not_null, defaults, drop_foreign_keys, add_foreign_keys, foreign_keys, column_order, tmp_suffix, keep_table)
   1908 for index in self.indexes:
   1909     if index.origin not in ("pk"):
-> 1910         index_sql = self.db.execute(
   1911             """SELECT sql FROM sqlite_master WHERE type = 'index' AND name = :index_name;""",
   1912             {"index_name": index.name},
   1913         ).fetchall()[0][0]
   1914         assert index_sql is not None, (
   1915             f"Index '{index}' on table '{self.name}' does not have a "
   1916             "CREATE INDEX statement. You must manually drop this index prior to running this "
   1917             "transformation and manually recreate the new index after running this transformation."
   1918         )
   1919         if keep_table:

IndexError: list index out of range

It looks like the parameter substitution fails when there is an extra underscore from the column name. If I do the same query manually using the ? substitution syntax it works, and if I do the query manually typing out the index name verbatim it also works.

[colinatkepler]

I'll add, in case anyone else has this problem, that my workaround is to try/except on IndexError for the db.create statement, and if it's raised, I manually drop the index, re-rung the db.create statement, and then run create_index again.

I also tried manually setting the index_name to something without underscores, so I guess underscores don't matter.