Why is SQLALCHEMY_DATABASE_URI set to "sqlite:///:memory:" when I set it to a path in my Config?

Question

I am learning Flask by following Miguel Ginsberg mega tutorial chapter 4. When I run any Flask command from the Anaconda command panel I get an error that includes "Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set." and as a result an SQLite database is created in memory.

But I have created a Config object that sets SQLALCHEMY_DATABASE_URI, SECRET_KEY and SQLALCHEMY_TRACK_MODIFICATIONS, and have tested the python separately, and it all works.

I have tried everything I can think of including testing snippets of code separately, at least 8 hours searching the web, and trawling though Ginsberg's posts, nothing works. One person Graham (post #29) seems to have had the same problem but Ginsberg does not give a useful answer.

Here is my app init code

__init__

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from config import Config

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import routes, models

Here is my config, it works when run separately.

import os
basedir = os.path.abspath(os.path.dirname(__file__))


class Config(object):
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

For completeness here are my routes and models

from flask import render_template, flash, redirect, url_for
from app import app
from app.forms import LoginForm


@app.route('/')
@app.route('/index')
def index():
    user = {'username': 'Miguel'}
    posts = [
        {
            'author': {'username': 'John'},
            'body': 'Beautiful day in Portland!'
        },
        {
            'author': {'username': 'Susan'},
            'body': 'The Avengers movie was so cool!'
        }
    ]
    return render_template('index.html', title='Home', user=user, posts=posts)


@app.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        flash('Login requested for user {}, remember_me={}'.format(
            form.username.data, form.remember_me.data))
        return redirect(url_for('index'))
    return render_template('login.html',  title='Sign In', form=form)

and

from datetime import datetime
from app import db


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def __repr__(self):
        return '<User {}>'.format(self.username)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    def __repr__(self):
        return '<Post {}>'.format(self.body)

What should happen is that when I run a command like

> flask db init

or

> flask db migrate -m "users table"

the command should complete successfully because SQLALCHEMY_DATABASE_URI should equal the path of the app and the SQLite database should be app.db.

Instead I get error messages stating SQLALCHEMY_DATABASE_URI is not set and that therefore SQLALCHEMY_DATABASE_URI has been set to "sqlite:///:memory:"

My app needs a persistent database! Why isn't SQLALCHEMY_DATABASE_URI and SQLALCHEMY_TRACK_MODIFICATIONS being set?

Answer 1

this problem has gone away by itself, but since others may experience it I decided to describe the work-around I used to save them some frustration. I think the original problem may have been due to the sequence in which I was importing packages/modules and initiating classes/objects into my __init__ method.

The workaround is to comment out the original config statement and directly set the config variables, including the SQLite database, in __init__.

### app.config.from_object(Config)
app.config["SECRET_KEY"] = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
app.config["SQLALCHEMY_DATABASE_URI"] = os.environ.get('DATABASE_URL') or \
     'sqlite:///' + 'C:\\...path...\\app.db'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
...

The workaround can probably be backed off a little by using

import os
basedir = os.path.abspath(os.path.dirname(__file__))

...

SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'app.db')
...