-1

Im currently taking the cs50web course and i have to connect my flask app to heroku postgres database, i have already created the tables using pgadmin 4 but im not able to access the data.

    import os

    from flask import Flask, session
    from flask_session import Session
    from sqlalchemy import create_engine
    from sqlalchemy.orm import scoped_session, sessionmaker
    from dotenv import load_dotenv

    load_dotenv()


    # Check for environment variable
    if not os.getenv("DATABASE_URL"):
        raise RuntimeError("DATABASE_URL is not set")


    # Set up database
    engine = create_engine(os.getenv("DATABASE_URL"))
    db = scoped_session(sessionmaker(bind=engine))

    rows = db.execute("SELECT * FROM users;")
    print(rows)

This is the error message i got back

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

Traceback (most recent call last):
  File "/Users/Bryan/Downloads/project1/tester.py", line 21, in <module>
    rows = db.execute("SELECT * FROM users;")
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1268, in execute
    clause, params or {}
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/Users/Bryan/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "users" does not exist
LINE 1: SELECT * FROM users;
                      ^

[SQL: SELECT * FROM users;]
(Background on this error at: http://sqlalche.me/e/f405)
[Finished in 6.69s]

pgadmin4 screenshot

Improve this question
2
  • 2
    In your screenshot the table is user, but your code is trying to select from users. Commented Nov 10, 2019 at 5:41
  • 1
    And for user table to work, you need to quote the table name in double quotes... As user is a reserved word. Commented Nov 10, 2019 at 5:45

2 Answers 2

Reset to default
2

It is just saying that there is no such thing as a "users" table.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

In SQL, the table name should be in double quotes, since Python also passes the string to execute function. You need to send users inside double quotes. Hence, try this :

rows = db.execute("SELECT * FROM "Users"")

Improve this answer

2 Comments

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
In the question: rows = db.execute("SELECT * FROM users;"). So what you're saying is that they should remove the semicolon and add double quotes to users? Please explain your answer (and please reformat it so that the code appears properly).