Flask SQLalchemy code doesn’t ‘see’ database entries after rerun the code-CodePudding

I created a small Database with one table and filled it with data. It worked perfectly. However, after I exited the VSCode and entered it again, the code recognizes the DB and makes requests, but gets an empty list as a result (as if it doesn’t ‘see’ DB entries). The DB is full of data; I can see it in DB browser. Moreover, a code in another file which imports the DBmodel from the first one successfully gets proper data. What might be wrong with the first file? The first file (app.py):

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///myDB.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # I tried also True

with app.app_context():
    db = SQLAlchemy(app)

    class Recipy(db.Model):
        id = db.Column(db.Integer, primary_key = True) 
        title = db.Column(db.String(100), index = True, unique = True) 
        …
    db.create_all()
    
@app.route('/')
def index():
    rows=Recipy.query.all()
    print(rows) # it is just to have results on terminal; results are an empty list [] both here and on the html-page
    return render_template("home.html", rows=rows, search_form=search_form)
…
if __name__ == '__main__':
    app.run()

>>> []

the second file which successfully gets the data:

from app import app, db, Recipy
with app.app_context():
    a=Recipy.query.get(2)
    rows=Recipy.query.order_by(Recipy.title).all()
    print(a, rows)

>>> <Recipy 2> [<Recipy 1>, <Recipy 2>, …]

Both files are in the same folder. Both times I run it within the same venv. I will be very grateful for a hint.

I tried app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True I also tried similar pieces of code created in another folder with another Database (which included two tables), but the results were the same.

Updated: According a comment, I added app.config["SQLALCHEMY_ECHO"] = "debug". The terminal output is as follows: For the first function:

023-01-10 17:23:27,458 DEBUG sqlalchemy.pool.impl.NullPool Created new connection <sqlite3.Connection object at 0x00000268DFA0DB40>
2023-01-10 17:23:27,459 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000268DFA0DB40> checked out from pool
2023-01-10 17:23:27,460 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-10 17:23:27,467 INFO sqlalchemy.engine.Engine SELECT recipy.id AS recipy_id, recipy.title AS recipy_title, recipy.author AS recipy_author, recipy.ingredients AS recipy_ingredients, recipy.instructions AS recipy_instructions     
FROM recipy
2023-01-10 17:23:27,468 INFO sqlalchemy.engine.Engine [generated in 0.00207s] ()
2023-01-10 17:23:27,470 DEBUG sqlalchemy.engine.Engine Col ('recipy_id', 'recipy_title', 'recipy_author', 'recipy_ingredients', 'recipy_instructions')
[]
2023-01-10 17:23:27,491 INFO sqlalchemy.engine.Engine ROLLBACK
2023-01-10 17:23:27,494 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000268DFA0DB40> being returned to pool
2023-01-10 17:23:27,501 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000268DFA0DB40> rollback-on-return
2023-01-10 17:23:27,504 DEBUG sqlalchemy.pool.impl.NullPool Closing connection <sqlite3.Connection object at 0x00000268DFA0DB40>

For the function that does return results:

2023-01-10 18:15:46,205 DEBUG sqlalchemy.pool.impl.NullPool Created new connection <sqlite3.Connection object at 0x00000286178E9B40>
2023-01-10 18:15:46,205 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000286178E9B40> checked out from pool
2023-01-10 18:15:46,216 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-10 18:15:46,217 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("recipy")
2023-01-10 18:15:46,218 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-01-10 18:15:46,219 DEBUG sqlalchemy.engine.Engine Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk')     
2023-01-10 18:15:46,220 DEBUG sqlalchemy.engine.Engine Row (0, 'id', 'INTEGER', 1, None, 1)
2023-01-10 18:15:46,220 DEBUG sqlalchemy.engine.Engine Row (1, 'title', 'VARCHAR(100)', 0, None, 0)
2023-01-10 18:15:46,221 DEBUG sqlalchemy.engine.Engine Row (2, 'author', 'VARCHAR(40)', 0, None, 0)
2023-01-10 18:15:46,222 DEBUG sqlalchemy.engine.Engine Row (3, 'ingredients', 'VARCHAR(500)', 0, None, 0)
2023-01-10 18:15:46,223 DEBUG sqlalchemy.engine.Engine Row (4, 'instructions', 'VARCHAR(4000)', 0, None, 0)
2023-01-10 18:15:46,224 INFO sqlalchemy.engine.Engine COMMIT
2023-01-10 18:15:46,226 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000286178E9B40> being returned to pool
2023-01-10 18:15:46,227 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000286178E9B40> rollback-on-return
2023-01-10 18:15:46,230 DEBUG sqlalchemy.pool.impl.NullPool Closing connection <sqlite3.Connection object at 0x00000286178E9B40>
2023-01-10 18:15:46,239 DEBUG sqlalchemy.pool.impl.NullPool Created new connection <sqlite3.Connection object at 0x00000286178E9940>
2023-01-10 18:15:46,240 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000286178E9940> checked out from pool
2023-01-10 18:15:46,241 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-01-10 18:15:46,245 INFO sqlalchemy.engine.Engine SELECT recipy.id AS recipy_id, recipy.title AS recipy_title, recipy.author AS recipy_author, recipy.ingredients AS recipy_ingredients, recipy.instructions AS recipy_instructions     
FROM recipy
WHERE recipy.id = ?
2023-01-10 18:15:46,246 INFO sqlalchemy.engine.Engine [generated in 0.00101s] (2,)
2023-01-10 18:15:46,248 DEBUG sqlalchemy.engine.Engine Col ('recipy_id', 'recipy_title', 'recipy_author', 'recipy_ingredients', 'recipy_instructions')
2023-01-10 18:15:46,249 DEBUG sqlalchemy.engine.Engine Row (2, 'Omelet', 'Serge', '2 eggs,\nmilk, \r\nolive oil', 'Mix it and fry on the pan')
2023-01-10 18:15:46,253 INFO sqlalchemy.engine.Engine SELECT recipy.id AS recipy_id, recipy.title AS recipy_title, recipy.author AS recipy_author, recipy.ingredients AS recipy_ingredients, recipy.instructions AS recipy_instructions     
FROM recipy ORDER BY recipy.title
2023-01-10 18:15:46,254 INFO sqlalchemy.engine.Engine [generated in 0.00130s] ()
2023-01-10 18:15:46,255 DEBUG sqlalchemy.engine.Engine Col ('recipy_id', 'recipy_title', 'recipy_author', 'recipy_ingredients', 'recipy_instructions')
2023-01-10 18:15:46,256 DEBUG sqlalchemy.engine.Engine Row (1, 'Baked apple', 'Serge', 'apple', 'Just bake it')       
2023-01-10 18:15:46,256 DEBUG sqlalchemy.engine.Engine Row (2, 'Omelet', 'Serge', '2 eggs,\nmilk, \r\nolive oil', 'Mix it and fry on the pan')
… (10 more similar rows with the content)
<Recipy 2> [<Recipy 1>, <Recipy 2>, <Recipy 4>, <Recipy 10>, <Recipy 12>, <Recipy 14>, <Recipy 13>, <Recipy 9>, <Recipy 8>, <Recipy 7>, <Recipy 5>]
2023-01-10 18:15:46,268 INFO sqlalchemy.engine.Engine ROLLBACK
2023-01-10 18:15:46,269 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000286178E9940> being returned to pool
2023-01-10 18:15:46,269 DEBUG sqlalchemy.pool.impl.NullPool Connection <sqlite3.Connection object at 0x00000286178E9940> rollback-on-return
2023-01-10 18:15:46,270 DEBUG sqlalchemy.pool.impl.NullPool Closing connection <sqlite3.Connection object at 0x00000286178E9940>

CodePudding user response：

You wrote that the dbURI is 'sqlite:///myDB.db', which depends on os.getcwd(), current working directory.

Ensure that CWD is always the same, or use an absolute pathname.