[FIXED] Flask-SQLalchemy returns None when attempting to execute SELECT query

Issue

I have a DB connected to the Flask App.

from flask import Flask, render_template, request, session, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

DB contains table Names with 40 rows

Here's the model of the table:

class Names(db.Model):
    __tablename__ = 'names'
    ID = db.Column(db.Integer, primary_key=True, nullable=False)
    name = db.Column(db.Text, nullable=False)
    gender = db.Column(db.Text, nullable=False)

Simple select-queries like this

with app.app_context():
    db.create_all()
    print(db.session.connection())
    names = Names.query.all()

return None.

What am I doing wrong?

At the same time, when I read DB with a standard SQLAlchemy instead of Flask SQLAlchemy, it works:

from sqlalchemy.orm import sessionmaker, scoped_session
engine = create_engine('sqlite:///db.db') 
session_factory = sessionmaker(bind=engine)
session = scoped_session(session_factory)

name = session.query(Names).get(1) //returns row
print(name.name)
with app.app_context():
    name_flask = db.session.query(Names).get(1) //returns None
    print(name_flask)

Solution

The issue occurred because of the database location. When a database is called only by name specified in the SQLALCHEMY_DATABASE_URI parameter of the app, Flask attempts to detect the database in the Instance folder of the project. If it does not exist in that folder, Flask-SQLAlchemy creates a new one. While my database was located in the main folder of the project, the app created a new DB with the same name in the Instances folder and after tried to read it whereas it was obviously empty.

So far, check the database location.

Answered By - Mr_Palenoff