peewee.OperationalError: no such column:...

[WolfgangFahl]

see #31

it would be great if sqlite_web would react more robust on such errors so that the database can still be inspected.

Traceback (most recent call last):
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/peewee.py", line 3322, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: no such column: Amt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/wf/Library/Python/3.12/bin/sqlite_web", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/sqlite_web/sqlite_web.py", line 1395, in main
    initialize_app(args[0], options.read_only, password, options.url_prefix,
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/sqlite_web/sqlite_web.py", line 1348, in initialize_app
    dataset = SqliteDataSet(db, bare_fields=True, **dataset_kw)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/playhouse/dataset.py", line 49, in __init__
    self._models = self._introspector.generate_models(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/playhouse/reflection.py", line 679, in generate_models
    database = self.introspect(table_names, literal_column_names,
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/playhouse/reflection.py", line 581, in introspect
    table_columns = self.metadata.get_columns(table, self.schema)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/playhouse/reflection.py", line 170, in get_columns
    for metadata in self.database.get_columns(table, schema))
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/peewee.py", line 3895, in get_columns
    cursor = self.execute_sql('PRAGMA "%s".table_info("%s")' %
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/peewee.py", line 3320, in execute_sql
    with __exception_wrapper__:
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/peewee.py", line 3088, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/peewee.py", line 196, in reraise
    raise value.with_traceback(tb)
  File "/Users/wf/Library/Python/3.12/lib/python/site-packages/peewee.py", line 3322, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: no such column: Amt

[coleifer]

Can you please dump the entire schema of the database? e.g. echo ".schema" | sqlite3 /path/to/file.db

Or attach the problematic database?

Or provide steps to reproduce?

This is basically nothing for me to work with.

[WolfgangFahl]

@coleifer - certainly - this is a database with invalid views which happens since it is in development. I would love to use sqlite-web to drop the invalid views and create working new ones.

echo ".schema" | sqlite3 BITPlan.db 
CREATE TABLE Posting(Buchungstag TEXT,Konto TEXT,Vorgang TEXT,Empfänger TEXT,Verwendungszweck TEXT,Kategorie TEXT,Klasse TEXT,Betrag TEXT,isodate TIMESTAMP);
CREATE VIEW BuchungF AS
SELECT
   isodate,
   Betrag as amount,
   Konto as account,
   Kategorie as category,
   Verwendungszweck as purpose,
   'Empfänger' as receiver,
   Vorgang as action,
   Klasse as class
FROM Posting
ORDER BY isodate DESC
/* BuchungF(isodate,amount,account,category,purpose,receiver,"action",class) */;
CREATE VIEW Buchung AS
SELECT
 isodate,
 Amt as amount,
 AmtCcy as currency,
 OwnrAcctNo as account,
 Category as category,
 RmtInf as purpose,
 RmtdNm as receiver,
 BookgTxt as action,
 Notes as class
FROM Posting
ORDER BY isodate DESC;

[coleifer]

Thank you for the extra info, this definitely helps me understand. Unfortunately, I don't think there's much I can offer in the way of help. What am I supposed to show for a totally broken view/schema? You'll need to drop those views from the sqlite shell.

[WolfgangFahl]

In mediawiki there is the concept of a "red link" that is the View would be shown in red with the textual info about the schema and the error message but no details. A drop would be offered as a remedy. This is exactly what i had hoped this issue would turn into as a feature request. The difference is that i would still be able to query the valid table and not be left in a state where i can no start the software at all.