SQLAlchemy db.create_all() demo fails

Question

I am following the tutorial from this page:

https://pythonhosted.org/Flask-SQLAlchemy/quickstart.html

I believe I did it exactly line for line. But I get an error each time when I try to db.create_all()

Here is my code (I moved the actual db.create_all() to be part of the file for simplicity here, though I've tried it the way the tutorial shows as well at the command line.

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

db.create_all()

I found a similar question on Stack Overflow at Flask SQLAlchemy db.create_all() not creating database

and

SQLAlchemy create_all() does not create tables

But that response didn't help me. (In fact the answer to the second one seems to have a serious bug)

This is all in Python 3.4

The error I get is very long, but here it is:

Traceback (most recent call last):
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\strategies.py", line 90, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\default.py", line 377, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlite3.OperationalError: unable to open database file

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

Traceback (most recent call last):
  File "<pyshell#2>", line 1, in <module>
    import test_create_db.py
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\test_create_db.py", line 25, in <module>
    db.create_all()
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\flask_sqlalchemy\__init__.py", line 895, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\flask_sqlalchemy\__init__.py", line 887, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\sql\schema.py", line 3404, in create_all
    tables=tables)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\base.py", line 1615, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "C:\Python34\Lib\contextlib.py", line 59, in __enter__
    return next(self.gen)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\base.py", line 1608, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\base.py", line 1798, in contextual_connect
    self.pool.connect(),
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 338, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 644, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 440, in checkout
    rec = pool._do_get()
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get
    return self._create_connection()
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 285, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 411, in __init__
    self.connection = self.__connect()
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\pool.py", line 538, in __connect
    connection = self.__pool._creator()
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\strategies.py", line 96, in connect
    connection_invalidated=invalidated
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\util\compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\util\compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\strategies.py", line 90, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Programming\LearningFlask\FlaskCookbook\test_create_db\python\lib\site-packages\sqlalchemy\engine\default.py", line 377, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (OperationalError) unable to open database file None None

Answer 1

You are using Windows, and there is no /tmp temporary directory in Windows. The database connection fails because SQLite3 has no directory to create the database file in. Pick a different location for your database file.

In this case, you could use tempfile.gettempdir() to build a path:

import tempfile
import os.path

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(
    tempfile.gettempdir(), 'test.db')