CODEWITHSUNDEEP
pythonflasksqlitesqlalchemy
Connor D
Connor D

Reputation: 105

How to fix 'Error: unable to open database file' in Flask app?

I'm creating a Flask server that will display information queried from an sqlite3 database. However, when I try to access the database file after running the localhost, it returns the following error.

File "C:\Users\Connor\Documents\Parking\app\routes.py", line 13, in index
    con = sqlite3.connect(app.config['SQLALCHEMY_DATABASE_URI'])
sqlite3.OperationalError: unable to open database file
127.0.0.1 - - [26/Mar/2019 20:30:57] "GET / HTTP/1.1" 500 -

I'm almost positive that the problem stems from sqlite:///, but I can't figure it out. None of the solutions suggested seem to have the answer either.

routes.py

from app import app
from flask import Flask, flash, redirect, request, render_template, 
session, url_for
import sqlite3

app.secret_key = app.config['SECRET_KEY']

@app.route('/')
@app.route('/index')
def index():
    con = sqlite3.connect(app.config['SQLALCHEMY_DATABASE_URI'])
    cur = con.cursor()
    cur.execute("SELECT * FROM Lot")
    data = cur.fetchall()
    return render_template('index.html', data=data)

config.py

import os

PROJECT_ROOT = os.path.dirname(os.path.realpath(__file__))

class Config(object):
    SQLALCHEMY_DATABASE_URI = "sqlite:///" + os.path.join(PROJECT_ROOT, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    DEBUG = True
    SECRET_KEY = 'development key'

init.py

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

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

Printing app.config['SQLALCHEMY_DATABASE_URI'] returns sqlite:///C:\Users\Connor\Documents\Parking\app.db. I would assume the forward slashes are the root of my problem. I've tried several variants of os.path but to no avail.

Oddly enough, when I manually type in my path, the database displays just fine AND its data can be manipulated in the admin portal. When I use os.path.join(PROJECT_ROOT, 'app.db'), the database displays, but I can't manipulate its data in the admin portal. When I use "sqlite:///" + os.path.join(PROJECT_ROOT, 'app.db'), I can't access the database at all.

I believe I'm using sqlite:/// correctly as per this document, so maybe I'm just missing something?

Upvotes: 1

Views: 3158

Answers (1)

SuperShoot
SuperShoot

Reputation: 10861

The issue here is that you are using the sqlalchemy connection url, but trying to connect directly through the sqlite3 api.

This line of code from within your index() route:

con = sqlite3.connect(app.config['SQLALCHEMY_DATABASE_URI'])

...calls the sqllite3.connect() function, and you are passing that your sqlalchemy connection url: SQLALCHEMY_DATABASE_URI = "sqlite:///" + os.path.join(PROJECT_ROOT, 'app.db').

Here is the function signature of sqlite3.connect():

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

And here is an excerpt form the documentation about what can be passed as the database parameter:

database is a path-like object giving the pathname (absolute or relative to the current working directory) of the database file to be opened.

sqllite:///some/path/to/app.db is not a valid pathname and that's why the error is raised.

You've gone to all the trouble of configuring flask_sqlalchemy, so you may as well use it!

from app import app, db  # <== notice import of db here
from flask import (Flask, flash, redirect, request, render_template,
                   session, url_for)

app.secret_key = app.config['SECRET_KEY']

@app.route('/')
@app.route('/index')
def index():
    data = db.session.execute("SELECT * FROM Lot").fetchall()
    return render_template('index.html', data=data)

This has the added bonus of keys included session management that comes with Flask-SQLAlchemy.

Upvotes: 2

Related Questions