Creating database with SQLAlchemy in Flask

Question

I'd like to know, what I need to do to create SQLAlchemy database in Flask. According to documentation I should create model in my Flask app and then go to the Python shell and just create this by using db.create_all(). But that doesn't work.

My Flask app:

import os
import flask
import settings
from flask_sqlalchemy import SQLAlchemy

app = flask.Flask(__name__)
app.config['SESSION_TYPE'] = 'filesystem'
app.secret_key = os.urandom(24)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////database.db'
db = SQLAlchemy(app)
(...)

Model:

from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(15), unique = True)
    password = db.Column(db.String(15), unique = True)
    tasks = db.relationship('Task', backref='author', lazy='dynamic')

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

class Task(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    scene = db.Column(db.String(140), nullable = False)
    state = db.Column(db.Integer(1), nullable = False)
    progress = db.Column(db.Integer(3), nullable = False)
    add_date = db.Column(db.DateTime, nullable = False)
    start_date = db.Column(db.DateTime, nullable = False)
    finish_date = db.Column(db.DateTime, nullable = False)
    rendered_scene = db.Column(db.String(140))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Error code:

>>> from app import db
>>> db.create_all()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python27\lib\site-packages\flask_sqlalchemy\__init__.py", line 895, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "C:\Python27\lib\site-packages\flask_sqlalchemy\__init__.py", line 887, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "C:\Python27\lib\site-packages\sqlalchemy\sql\schema.py", line 3420, in create_all
    tables=tables)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1727, in_run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "C:\Python27\lib\contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1720, in_optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1910, incontextual_connect
    self.pool.connect(),
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 338, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 645, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 440, in checkout

    rec = pool._do_get()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 1058, in _do_get

    return self._create_connection()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 285, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 411, in __init__

    self.connection = self.__connect()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 539, in __connect
    connection = self.__pool._creator()
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 96, in connect
    connection_invalidated=invalidated
  File "C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 90, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python27\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 have one too many / in the database uri. The format is dialect+driver://user:pass@host:port/db_name. With SQLite, db_name is the path to the database. You've specified the absolute path /database.db, which means you're trying to create the database in the filesystem's root directory.

Using sqlite:///database.db (a relative path) will create the database in (relative to) the current working directory.

You probably want to specify an absolute path, but build it based on the project location, since you could run the application from another folder. Assuming you want to create the db in the same directory as the app setup code, build a path relative to __file__.

db_path = os.path.join(os.path.dirname(__file__), 'app.db')
db_uri = 'sqlite:///{}'.format(db_path)
app.config['SQLALCHEMY_DATABASE_URI'] = db_uri