Flask SQL no such table

Question

I've tried this before and it worked, now I'm doing it again but it doesn't. I use a virtual env.

The db file is in the same directory as the init and models. I've also executed the db.create_all() in the python shell after importing db. The error occurs when I execute Students.query.all(). I'm really confused now. I compared my codes with my existing projects and it is the same. I don't understand why it doesn't work.

Traceback (most recent call last):
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: students

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\orm\query.py", line 3211, in all
    return list(self)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\orm\query.py", line 3367, in __iter__
    return self._execute_and_instances(context)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\orm\query.py", line 3392, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\base.py", line 982, in execute
    return meth(self, multiparams, params)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1101, in _execute_clauseelement
    distilled_params,
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1246, in _execute_context
    cursor, statement, parameters, context
  File "C:\Users\Martin\Desktop\regtry\venv\lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: students
[SQL: SELECT students.id AS students_id, students.last_name AS students_last_name, students.college AS students_college, students.email AS students_email
FROM students]
(Background on this error at: http://sqlalche.me/e/e3q8)

_ _ init _ _.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

main = Flask(__name__)
main.config['SECRET_KEY'] = '3bce79429ad6b79670e4e800fb4a57b9'
main.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

db = SQLAlchemy(main)

from reg import routes

models.py:

from reg import db

class Students(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    last_name = db.Column(db.String(60), nullable=False)
    college = db.Column(db.String(60), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return f"Student('{self.last_name}','{self.college}','{self.email}')"

first you could use database editor/viewer like [DBeaver](https://dbeaver.io/) to see what you have in database. Maybe code created database but not tables. OR it created it in different folder and you use some old database. — furas, Dec 26 '19 at 17:23

Lucas Scott · Answer 1 · 2019-12-27T12:38:17.257

It probably has to do with where you are calling db.create_all(). You have to call it after you have defined all of your models. Here is a working example in one module:

import flask
from flask_sqlalchemy import SQLAlchemy

app = flask.Flask(__name__)
app.config['SECRET_KEY'] = '3bce79429ad6b79670e4e800fb4a57b9'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

db = SQLAlchemy(app)


class Students(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    last_name = db.Column(db.String(60), nullable=False)
    college = db.Column(db.String(60), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return f"Student('{self.last_name}','{self.college}','{self.email}')"

db.create_all()


@app.route("/students", methods=["GET"])
def all_students():
    students = Students.query.all()
    resp = []
    for student in students:
        resp.append(dict(
            id=student.id,
            last_name=student.last_name,
            college=student.college,
            email=student.email
        ))
    return flask.jsonify(resp), 200


@app.route("/students/<_id>", methods=["GET"])
def student_by_id(_id):
    student = Students.query.get(_id)
    if student:
        resp = dict(
            id=student.id,
            last_name=student.last_name,
            college=student.college,
            email=student.email
        )
        return flask.jsonify(resp), 200
    else:
        return "", 404


@app.route("/students", methods=["POST"])
def add_student():
    req = flask.request.get_json()
    student = Students(**req)
    db.session.add(student)
    db.session.commit()
    resp = flask.make_response()
    resp.headers["Location"] = flask.url_for("student_by_id", _id=student.id)
    return resp, 201

In your example I did not see a call to db.create_all() but I suspect that it is in the wrong spot. Using your example, I believe it will work if you move the call from where you have it to the end of your __init__.py:

__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

main = Flask(__name__)
main.config['SECRET_KEY'] = '3bce79429ad6b79670e4e800fb4a57b9'
main.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

db = SQLAlchemy(main)

from reg import routes
db.create_all()

score 1 · Accepted Answer · answered Dec 29 '19 at 12:54

In the shell, import the database and the models first before executing db.create_all(). Apparently, error occurs when create you create database without importing the models.

My mistake was:

>>from APPFOLDER import db
>>db.create_all()
>>from APPFOLDER.models import MODELNAME
>>MODELNAME.query.all()
***ERROR MSG***

What you should do:

>>from APPFOLDER import db
>>from APPFOLDER.models import MODELNAME
>>db.create_all()
>>MODELNAME.query.all()
[]

Flask SQL no such table

2 Answers2