1

In development (so sqlite3) I'm getting this error on any database access:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: ujs ...

I got here by saying

export FLASK_ENV=development
export FLASK_APP=my_app.py
flask db init
flask db migrate
flask db upgrade
flask run

and then doing an HTTP GET against that dev server.

I believe the migration workflow succeeded, because when I use the sqlite3 commandline client, I can see the (empty) table with a believably correct schema.

╭╴ (get-db-working *%=)╶╮
╰ jeff@starshine:TN_flask_web $ sqlite3 dev.db 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .table
alembic_version    ujs
sqlite> .quit
╭╴ (get-db-working *%=)╶╮
╰ jeff@starshine:TN_flask_web $ 

I therefore believe I've made a coding error. But I'm not seeing it.

I have this code (pared down to what I believe is the essential bits):

my_app.py:

from app import create_app, db, cli
from app.models import UJS

app = create_app()
cli.register(app)

@app.shell_context_processor
def make_shell_context():
    return {'db': db,
            'UJS': UJS}

app/models.py:

from app import db
import time

def now_in_microseconds():
    """Return the current time in microseconds since the epoch.
    """
    return time.time() * 1000 * 1000

class UJS(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    timestamp_microseconds = db.Column(db.BigInteger, default=now_in_microseconds)
    ip_hash = db.column(db.String(40))

    # And then some more columns, all quite boring.

    def __repr__(self):
        return '<[{tag}]/[{ip}] {microsec}/{city}>'.format(
            tag=self.tag, ip=self.ip_hash,
            microsec=self.timestamp_microseconds, city=self.city)

app/__init__.py:

from flask import Flask, request, current_app
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from config import Config

db = SQLAlchemy()
migrate = Migrate()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)
    try:
        app.config.from_pyfile("../config_local.py")
    except FileNotFoundError:
        print('No local config found.')
    except:
        print('Unexpected error on app.config.from_pyfile()')

    db.init_app(app)
    migrate.init_app(app, db)
    ...
    return app

from app import models

and app/main/routes.py:

from flask import request, g, current_app, session
from app import db
from app.main import bp
from app.models import UJS

@bp.before_app_request
def before_request():
    if 'static' == request.endpoint:
        # This should only happen in dev.  Otherwise, nginx handles static routes directly.
        return
    # I expect this to return an empty list, but it throws a 500.
    print(UJS.query.all())

Any suggestions what I'm missing?

4
  • Can you please get into the DB with sqlite3 command line tool (not Flask shell) and show us what tables exactly do you have here? Commented Jan 1, 2020 at 19:29
  • are you sure this DB is the one actually used by Flask? Can you please show the app.config contents on app init? Just to be sure. Commented Jan 2, 2020 at 9:49
  • @MikhailSavushkin I will when back at keyboard. In the mean time, note that (1) it's the db created by flask db init and migrate, which read this same config, and (2) how can I verify this in flask shell, where I can also reproduce the error? I've tried to understand the error further by exploring the db object I get in that shell. Commented Jan 2, 2020 at 13:10
  • @MikhailSavushkin print(app.config) was not very instructive: the db path value was correct. But find . -name dev.db indeed yielded two paths. I'm unclear why db migrate used one and run the other, but making sure that SQLALCHEMY_DATABASE_URI calls os.path.abspath removed the discrepancy. Thanks! Commented Jan 3, 2020 at 20:18

1 Answer 1

2

For anyone who might find this question later on: the problem was about having the right absolute path to your DB in your SQLALCHEMY_DATABASE_URI config value.

Also (this wasnt the case here, but it might possibly gotcha with the same symptoms) - if you omit __tablename__ on Model declaration, SQLAlchemy might autogenerate something you wont expect. Just a thing to keep in mind, if you're working with an existing DB with some schema already in place.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.