Flask how do you use sqlalchemy declaratively with init_db()?

Question

this is my database.py

engine = create_engine('sqlite:///:memory:', echo=True)
session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()
Base.query = session.query_property()

def init_db():
  # import all modules here that might define models so that
  # they will be registered properly on the metadata.  Otherwise
  # you will have to import them first before calling init_db()
  import models
  Base.metadata.create_all(engine)

and this is my backend.py

from flask import Flask, session, g, request, render_template
from database import init_db, session
from models import *

app = Flask(__name__)
app.debug = True
app.config.from_object(__name__)

# Serve static file during debug 
if app.config['DEBUG']:
  from werkzeug import SharedDataMiddleware
  import os
  app.wsgi_app = SharedDataMiddleware(app.wsgi_app, {
    '/': os.path.join(os.path.dirname(__file__), 'static')
  })

@app.route('/')
def foo():
  session.add(User())
  session.commit()
  return "NOTHING HERE."

if __name__ == "__main__":
  init_db()
  app.run(port=8888)

I am noticing a couple of weird things:

1. When I do python backend.py I am seeing the tables being created twice. The same create table statements are executed
2. When I visit '/', I am getting the following error even when I am 100% sure that the tables have been created. Why?

cursor.execute(statement, parameters) OperationalError: (OperationalError) no such table: users u'INSERT INTO users DEFAULT VALUES' ()

Answer 1

When you create a SQLite database in memory it is only accessible to the particular thread that created it - change create_engine('sqlite:///:memory:') to create_engine('sqlite:////some/file/path/db.sqlite' and your tables will exist.

As to why you are seeing the tables created twice - Flask in debug mode by default runs with a server that reloads every time you change your code. In order to do when it starts it spawns a new process that actually runs the server - so your init_db function is called before you start the server, then it is called again when the server creates a child process to serve requests.