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', 'admin@example.com', 'admin1', 'admin1@example.com')
guest = User('admi2', 'admin@ex1ample.com', 'admin', 'admin2@example.com')
# guest = User('guest', 'guest@example.com')
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': 'Admin@Example.Com', 'firstname': 'Admin', 'pwdhash': 'pbkdf2:sha1:1000$eZvJNKHO$64f59c34364e3d6094d126fa3ca2b327ab39e302', 'email': 'admin1'}]

What am I doing wrong?

score 10 · Accepted Answer · edited Sep 20 '22 at 14:04

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', 'admin@example.com', 'admin1', 'admin1@example.com')
guest = User('admi2', 'admin@ex1ample.com', 'admin', 'admin2@example.com')
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.

score 4 · Answer 2 · answered Jan 27 '16 at 21:09

4

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 ...
);

answered Jan 27 '16 at 21:09

Josh Kupershmidt

2,540
21
30

Hi Josh, I've never used SQLAlchemy before so trying to wrap my head around it. I had an earlier question http://stackoverflow.com/questions/35045952/setting-up-postgres-with-flask-on-win7/35046773#35046773 where Ryan seemed to imply that the table could be created automagically based on my model. Am I wrong about this? – user1592380 Jan 27 '16 at 21:17
Yes, there are several ways you can have SQLAlchemy or some third-party tool automagically create the database tables it needs; I won't attempt to go into them here, but definitely worth investigating as your app grows. – Josh Kupershmidt Jan 27 '16 at 21:37
Thanks Josh, I'm planning to deploy my app to an environment where my command line access may be limited/unwieldy, so I was hoping to have the tables auto created. I've been trying to follow http://stackoverflow.com/questions/20744277/sqlalchemy-create-all-does-not-create-tables/20749534#20749534 which has an example of this working, Any idea why mine does not? – user1592380 Jan 27 '16 at 23:16

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

2 Answers2

Linked