SQLAlchemy 2.0 can't read SQLite table?

Question

I've tested the SQLite database with this script, and it works:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import text

db = SQLAlchemy()
app = Flask(__name__)
db_name = 'sockmarket.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_name
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db.init_app(app)

@app.route('/')
def testdb():
    try:
        db.session.query(text('1')).from_statement(text('SELECT 1')).all()
        return '<h1>It works.</h1>'
    except Exception as e:
        error_text = "<p>The error:<br>" + str(e) + "</p>"
        hed = '<h1>Something is broken.</h1>'
        return hed + error_text

if __name__ == '__main__':
    app.run(debug=True)

Then I try to use the new SQLAlchemy select command, and it fails:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import text

db = SQLAlchemy()
app = Flask(__name__)
db_name = 'sockmarket.db'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_name
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db.init_app(app)

class Sock(db.Model):
    __tablename__ = 'socks'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    style = db.Column(db.String)
    color = db.Column(db.String)
    quantity = db.Column(db.Integer)
    price = db.Column(db.Float)
    updated = db.Column(db.String)

@app.route('/')
def index():
    try:
        socks = db.session.execute(db.select(Sock)
        .filter_by(style='mini')
        .order_by(Sock.name)).scalars()
        
        sock_text = '<ul>'
        for sock in socks:
            sock_text += '<li>' + sock.name + ', ' + sock.color + '</li>'
        sock_text += '</ul>'
        return sock_text
    except Exception as e:
        # e holds description of the error
        error_text = "<p>The error:<br>" + str(e) + "</p>"
        hed = '<h1>Something is broken.</h1>'
        return hed + error_text

if __name__ == '__main__':
    # app.run(debug=True)

I've check the Model again and again. The script writes the UL tags but nothing in between them. It does not throw an error. I also ran SQLAlchemy commands in the Python interpreter to verify that, yes, the db is there and the table is readable. This is an already-existing database and table. Also, this setup worked perfectly with the old version of SQLAlchemy several months ago. Same Model. The select statement in the old version was this:

socks = Sock.query.filter_by(style='mini').order_by(Sock.name).all()

Answer 1

Solution: Even though the first script works fine without a path, and the second script used to work fine without a path, it turns out that now the second script requires a path for the SQLite database - even though the .db file is in the same directory with this Python script and there is an __init__.py file in the dir as well.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os.path

db = SQLAlchemy()
app = Flask(__name__)
db_name = 'sockmarket.db'
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(BASE_DIR, db_name)

I hope this saves someone else the hours it took me to solve it.