Flask : sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "users" does not exist

Question

I am working on a flask app based on http://code.tutsplus.com/tutorials/intro-to-flask-signing-in-and-out--net-29982.

As part of the tut I'm trying to connect to a postgres server, with a structure as in the screenshot. I've added a db 'flask' which you can see.

Based on the tut I have the following code in my main file ('routes.py'):

from flask.ext.sqlalchemy import SQLAlchemy

from flask import Flask

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql://postgres:123@localhost/flask"

db = SQLAlchemy(app)
from models import User
# db.init_app(app)
db.create_all()
db.session.commit()


admin = User('admin', '[email protected]', 'admin1', '[email protected]')
guest = User('admi2', '[email protected]', 'admin', '[email protected]')
# guest = User('guest', '[email protected]')
db.session.add(admin)
db.session.add(guest)
db.session.commit()

models.py:

from flask.ext.sqlalchemy import SQLAlchemy
from werkzeug import generate_password_hash, check_password_hash

db = SQLAlchemy()

class User(db.Model):
  __tablename__ = 'users'
  uid = db.Column(db.Integer, primary_key = True)
  firstname = db.Column(db.String(100))
  lastname = db.Column(db.String(100))
  email = db.Column(db.String(120), unique=True)
  pwdhash = db.Column(db.String(54))

  def __init__(self, firstname, lastname, email, password):
    self.firstname = firstname.title()
    self.lastname = lastname.title()
    self.email = email.lower()
    self.set_password(password)

  def set_password(self, password):
    self.pwdhash = generate_password_hash(password)

  def check_password(self, password):
    return check_password_hash(self.pwdhash, password)

When run the debugger gives:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "users" does not exist
LINE 1: INSERT INTO users (firstname, lastname, email, pwdhash) VALU...
                    ^
 [SQL: 'INSERT INTO users (firstname, lastname, email, pwdhash) VALUES (%(firstname)s, %(lastname)s, %(email)s, %(pwdhash)s) RETURNING users.uid'] [parameters: {'lastname': '[email protected]', 'firstname': 'Admin', 'pwdhash': 'pbkdf2:sha1:1000$eZvJNKHO$64f59c34364e3d6094d126fa3ca2b327ab39e302', 'email': 'admin1'}]

What am I doing wrong?

Answer 1

You're initializing your database twice.

I'd suggest taking a good look at this: http://flask.pocoo.org/docs/0.10/patterns/sqlalchemy/

Essentially, you'll want to split things up into a few more files to prevent import issues and make things a little more clean. I've done the below which seems to work. Note, I've used SQLite, since I do not have Postgres installed on this box.

app.py

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////test11.db'

models.py

from flask.ext.sqlalchemy import SQLAlchemy
from app import app
db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = 'users'
    uid = db.Column(db.Integer, primary_key = True)
    firstname = db.Column(db.String(100))
    lastname = db.Column(db.String(100))
    email = db.Column(db.String(120), unique=True)
    pwdhash = db.Column(db.String(54))

def __init__(self, firstname, lastname, email, password):
    self.firstname = firstname.title()
    self.lastname = lastname.title()
    self.email = email.lower()
    self.set_password(password)

def set_password(self, password):
    self.pwdhash = (password)

def check_password(self, password):
    return password

routes.py

from models import User, db

db.create_all()
db.session.commit()

admin = User('admin', '[email protected]', 'admin1', '[email protected]')
guest = User('admi2', '[email protected]', 'admin', '[email protected]')
db.session.add(admin)
db.session.add(guest)
db.session.commit()

I'd definitely suggest looking over some tutorials! You'll need it: you should learn about web vulnerabilities, best practices, and so on.

Answer 2

The tutorial you linked to has a section called "Create a User Model", under which it tells you how to use CREATE TABLE ... to create your users table. It gives some MySQL-specific syntax for this, although you are apparently using Postgres, so your command will be slightly different.

But the screenshot you posted (from pgAdmin?) clearly shows that the "public" schema in the flask database has zero tables, so obviously you have not created this table yet. You'll have to create this table, you should be able to do so through pgAdmin, something like:

CREATE TABLE users (
  uid serial PRIMARY KEY,
  firstname varchar(100) not null,
  ... follow the guide for the rest of the columns ...
);