Cannot create database with SQLAlchemy

Question

I am trying to run my create_db.py file to create the posts.db database, but it will not get created in my project directory. And when I run the main file for the blog and try to login in I get the error below.

I have looked up this error and seen that other people have gotten it as well and asked about it here on Stackoverflow, but none of them seems to help me. I have read that this could be because something in my blog.py file is running main before the database gets created. But, I am thinking that it has something to do with the configuration. Mainly the PATH of the database could be getting mixed up with the app.config['SQLAlCHEMY_DATABASE_URI'] = 'sqlite:///' line.

Any ideas?

Here is the error

sqlalchemy.exc.OperationalError

OperationalError: (OperationalError) no such table: posts u'SELECT posts.id AS posts_id, posts.title AS posts_title, posts.post AS posts_post \nFROM posts' ()

OperationalError: (OperationalError) no such table: posts u'SELECT posts.id AS posts_id, posts.title AS posts_title, posts.post AS posts_post \nFROM posts' ()

Here is my code. There are three files here: blog.py, models.py, create_db.py

blog.py # controller of blog app

from flask import Flask, render_template, request, session,\
    flash, redirect, url_for, g
from flask.ext.sqlalchemy import SQLAlchemy
import sqlite3
from functools import wraps


# create the application object
app = Flask(__name__)

# configuration
app.secret_key = 'x13xa8xf5}[xfexd4Zxb8+x07=7xc9xe1Bxcfxbdt.ox87oxc9'
app.config['SQLAlCHEMY_DATABASE_URI'] = 'sqlite:///'

# create sqlalchemy object

db = SQLAlchemy(app)

from models import *

# login required decorator
def login_required(test):
    @wraps(test)
    def wrap(*args, **kwargs):
        if 'logged_in' in session:
            return test(*args, **kwargs)
        else:
            flash('You need to login first.')
            return redirect(url_for('login'))
    return wrap

@app.route('/', methods = ['GET','POST'])
def login():
    error = None
    if request.method == 'POST':
        if request.form['username'] != 'admin' or request.form['password'] != 'admin':
            error = 'Invalid Credentils. Please try again.'
        else:
            session['logged_in'] = True
            return redirect(url_for('main'))
    return render_template('login.html', error=error)

@app.route('/main')
@login_required
def main():
    posts = db.session.query(BlogPost).all()
    return render_template('main.html', posts=posts)

@app.route('/add', methods=['POST'])
@login_required
def add():
    title = request.form['title']
    post = request.form['post']
    if not title or not post:
        flash("All fields are required. Please try again.")
        return redirect(url_for('main'))
    else:
        db.session.add(title)
        db.session.add(post)
        db.session.commit()
        db.session.close()
        flash('New entry was successfully posted!')
        return redirect(url_for('main'))


@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    flash('You were logged out')
    return redirect(url_for('login'))

def connect_db():
    return sqlite.connect('posts.db')

if __name__ == '__main__':
    app.run(debug = True)

models.py:

from blog import db

class BlogPost(db.Model):

    __tablename__ = "posts"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)
    post = db.Column(db.String, nullable=False)

    def __init__(self, title, post):
        self.title = title
        self.post = post

    def __repr__(self):
        return '<title {}'.format(self.title)

create_db.py

from blog import db
from models import BlogPost

# create the database and the db tables
db.create_all()

# insert
db.session.add(BlogPost("Good","I\'m good."))
db.session.add(BlogPost("Well","I\'m well."))
db.session.add(BlogPost("Post","I\'m a post."))

# commit the changes

db.session.commit()

Answer 1

There's a typo in SQLAlCHEMY_DATABASE_URI, should be SQLALCHEMY_DATABASE_URI, the 2nd l.

When running from blog import db some statements in blog.py get executed, including the one with sqlite:/// which is where the path is set. Modifying this line

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///posts.db'

and then after running create_db.py should create a database in app's root directory. There doesn't seem to be another place in the code where the db path is set.

It looks like you may also run into some circular import problems (will throw ImportError: cannot import name [name]). Some solutions are putting the app init stuff like db = SQLAlchemy.. into a separate file or importing at the end of the file.