SQLAlchemy suggests table is missing a column that it has: ProgrammingError: (psycopg2.ProgrammingError) column Canada.address does not exist

Question

I have four tables -- Users, Canada, France, Germany -- that are entirely independent. I have (I believe) added them to a database. But the columns of all the databases but Users seem to be unquery-able despite their being there. (One can query them but not use fliter() or all() or first() or iterate over the results of the query.) I'm wondering if the issue is that I need to use this declarative_base thing?

DATABASE_URL = os.environ["DATABASE_URL"]

app = Flask(__name__)      

app.secret_key = "real key will go here"

app.config.update(mail_settings)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config['SQLALCHEMY_DATABASE_URI'] = DATABASE_URL

db = SQLAlchemy(app)

class Users(db.Model):
    __tablename__ = "Users"
    id_ = db.Column(db.Integer, primary_key=True)
    name_ = db.Column(db.String(100))
    email_ = db.Column(db.String(100))
    date_ = db.Column(db.Date())
    count_ = db.Column(db.Integer)
    def __init__(self, email, name, date=datetime.datetime.now().date(), count=0):
        self.name_ = name
        self.email_ = email
        self.date_ = date
        self.count_ = count
    def __repr__(self):
        return '<User %r>' % self.email_

class Canada(db.Model):
    __tablename__ = 'Canada'
    id_ = db.Column(db.Integer, primary_key=True)
    address = db.Column(db.String(100))
    def __init__(self, address):
        self.address = address
    def __repr__(self):
        return "<Canadian address %r>" % self.address

class France(db.Model):
    __tablename__ = "France"
    id_ = db.Column(db.Integer, primary_key=True)
    address = db.Column(db.String(100))
    def __init__(self, address):
        self.address = address
    def __repr__(self):
        return "<French address %r>" % self.address

class Germany(db.Model):
    __tablename__ = "Germany"
    id_ = db.Column(db.Integer, primary_key=True)
    address = db.Column(db.String(100))
    def __init__(self, address):
        self.address = address
    def __repr__(self):
        return "<German address %r>" % self.address

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

I can add users and then query them using a web form without any issues.

But when I try to add Canadian databases (I check to see if it's already in the before adding it) my query fails:

db.session.query(Canada).filter(Canada.address == address).first()

Here is the output:

2018-09-11T13:49:14.998235+00:00 app[web.1]: Traceback (most recent call last):
2018-09-11T13:49:14.998237+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/flask/app.py", line 2292, in wsgi_app
2018-09-11T13:49:14.998239+00:00 app[web.1]: response = self.full_dispatch_request()
2018-09-11T13:49:14.998241+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/flask/app.py", line 1815, in full_dispatch_request
2018-09-11T13:49:14.998242+00:00 app[web.1]: rv = self.handle_user_exception(e)
2018-09-11T13:49:14.998244+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/flask/app.py", line 1718, in handle_user_exception
2018-09-11T13:49:14.998246+00:00 app[web.1]: reraise(exc_type, exc_value, tb)
2018-09-11T13:49:14.998247+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/flask/app.py", line 1813, in full_dispatch_request
2018-09-11T13:49:14.998249+00:00 app[web.1]: rv = self.dispatch_request()
2018-09-11T13:49:14.998251+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/flask/app.py", line 1799, in dispatch_request
2018-09-11T13:49:14.998253+00:00 app[web.1]: return self.view_functions[rule.endpoint](**req.view_args)
2018-09-11T13:49:14.998255+00:00 app[web.1]: File "/app/app.py", line 111, in admin
2018-09-11T13:49:14.998256+00:00 app[web.1]: if db.session.query(Canada).filter(Canada.address == address).first() is None:
2018-09-11T13:49:14.998258+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2888, in first
2018-09-11T13:49:14.998260+00:00 app[web.1]: ret = list(self[0:1])
2018-09-11T13:49:14.998262+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2680, in __getitem__
2018-09-11T13:49:14.998263+00:00 app[web.1]: return list(res)
2018-09-11T13:49:14.998265+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__
2018-09-11T13:49:14.998267+00:00 app[web.1]: return self._execute_and_instances(context)
2018-09-11T13:49:14.998269+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3011, in _execute_and_instances
2018-09-11T13:49:14.998270+00:00 app[web.1]: result = conn.execute(querycontext.statement, self._params)
2018-09-11T13:49:14.998272+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
2018-09-11T13:49:14.998274+00:00 app[web.1]: return meth(self, multiparams, params)
2018-09-11T13:49:14.998276+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
2018-09-11T13:49:14.998278+00:00 app[web.1]: return connection._execute_clauseelement(self, multiparams, params)
2018-09-11T13:49:14.998279+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
2018-09-11T13:49:14.998281+00:00 app[web.1]: compiled_sql, distilled_params
2018-09-11T13:49:14.998282+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
2018-09-11T13:49:14.998284+00:00 app[web.1]: context)
2018-09-11T13:49:14.998297+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
2018-09-11T13:49:14.998299+00:00 app[web.1]: exc_info
2018-09-11T13:49:14.998300+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
2018-09-11T13:49:14.998302+00:00 app[web.1]: reraise(type(exception), exception, tb=exc_tb, cause=cause)
2018-09-11T13:49:14.998304+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
2018-09-11T13:49:14.998305+00:00 app[web.1]: context)
2018-09-11T13:49:14.998308+00:00 app[web.1]: File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
2018-09-11T13:49:14.998310+00:00 app[web.1]: cursor.execute(statement, parameters)
2018-09-11T13:49:14.998311+00:00 app[web.1]: ProgrammingError: (psycopg2.ProgrammingError) column Canada.address does not exist
2018-09-11T13:49:14.998313+00:00 app[web.1]: LINE 1: SELECT "Canada".id_ AS "Canada_id_", "Canada".address AS "Ca...
2018-09-11T13:49:14.998315+00:00 app[web.1]: ^
2018-09-11T13:49:14.998318+00:00 app[web.1]: [SQL: 'SELECT "Canada".id_ AS "Canada_id_", "Canada".address AS "Canada_address" \nFROM "Canada" \nWHERE "Canada".address = %(address_1)s \n LIMIT %(param_1)s'] [parameters: {'address_1': u'123 fake st. canada\r', 'param_1': 1}] (Background on this error at: http://sqlalche.me/e/f405)

I have tried dozens of things -- moving code into different files, adding some code involving an "engine", changing the names of the columns in Canada, changing the name of Canada -- but nothing seems to get at the problem. One thing I've noticed is that all the tutorials I've followed use only a single table in their database. I'm wondering if that is at the heart of the issue. I have been unable to find guidance for using multiple, independent tables.

It seems that the columns are there.

>>> for column in Users.__table__.columns:
...     print column
... 
Users.id_
Users.name_
Users.email_
Users.date_
Users.count_
>>> mapper = inspect(Users)
>>> for column in mapper.attrs:
...     print column
... 
Users.id_
Users.name_
Users.email_
Users.date_
Users.count_
>>> 

>>> from sqlalchemy import inspect
>>> mapper = inspect(Canada)
>>> for column in mapper.attrs:
...     print column
... 
Canada.id_
Canada.address
>>> for column in Canada.__table__.columns:
...     print column
... 
Canada.id_
Canada.address

I don't know why my script can query the columns of Users but not the columns of Canada.

So why is SQLAlchemy generating a query into it where the column cannot be found?

2018-09-11T13:49:14.998311+00:00 app[web.1]: ProgrammingError: (psycopg2.ProgrammingError) column Canada.address does not exist
2018-09-11T13:49:14.998313+00:00 app[web.1]: LINE 1: SELECT "Canada".id_ AS "Canada_id_", "Canada".address AS "Ca...
2018-09-11T13:49:14.998315+00:00 app[web.1]: ^
2018-09-11T13:49:14.998318+00:00 app[web.1]: [SQL: 'SELECT "Canada".id_ AS "Canada_id_", "Canada".address AS "Canada_address" \nFROM "Canada" \nWHERE "Canada".address = %(address_1)s \n LIMIT %(param_1)s'] [parameters: {'address_1': u'123 fake st. canada\r', 'param_1': 1}] (Background on this error at: http://sqlalche.me/e/f405)

When I do heroku run python I see the following:

`>>> from app import db, Users, Canada
>>> db.session.query(Users).all()
[<User u'mhd@example.com'>, <User u'md@example.edu'>]
>>> db.session.query(Canada).all()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2836, in all
    return list(self)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2988, in __iter__
    return self._execute_and_instances(context)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3011, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/app/.heroku/python/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column Canada.address does not exist
LINE 1: SELECT "Canada".id_ AS "Canada_id_", "Canada".address AS "Ca...
                                             ^
 [SQL: 'SELECT "Canada".id_ AS "Canada_id_", "Canada".address AS "Canada_address" \nFROM "Canada"'] (Background on this error at: http://sqlalche.me/e/f405)

There is something categorically different between Users and Canada.

No! Users works perfectly fine but not Canada. See update to the question. — Max D., Sep 11 '18 at 16:59
I believe I solved it by deleting my database entirely and running again. When my code seems correct but there appears to be an error like this, if I reinitialize the database it seems to work. — Max D., Jan 18 '21 at 13:21

score 0 · Answer 1 · answered Sep 11 '18 at 14:24

0

Maybe you need to check if "address" field exists in your table "Canada", because the log shows you a postgres database error:

(psycopg2.ProgrammingError) column Canada.address does not exist

If the field doesn't exists in your table you need to run migrations or add manually to your database with postgres command "add column" (alter table documentantion)

I hope this works :)

answered Sep 11 '18 at 14:24

pacocampo

66
1
4

`from sqlalchemy import inspect mapper = inspect(Canada); for column in mapper.attrs: print column.key` yields: `id_ address` – Max D. Sep 11 '18 at 15:32
`for column in Canada.__table__.columns: ... print column` yields: Canada.id_ Canada.address – Max D. Sep 11 '18 at 15:33
The field exists in the table so far as I can tell! – Max D. Sep 11 '18 at 15:36
Your model class thinks that such a column exists, because it has been told so, but does it really? Inspect the schema of the actual table in the database. – Ilja Everilä Sep 11 '18 at 18:55
postcardsabroad::DATABASE=> select * from "Users" where false; id_ | name_ | email_ | date_ | count_ -----+-------+--------+-------+-------- (0 rows) postcardsabroad::DATABASE=> select * from "Canada" where false; id_ ----- (0 rows) – Max D. Sep 12 '18 at 10:16
So when I search for the column it doesn't exist – Max D. Sep 12 '18 at 10:17
How can a discrepancy between a model and a table emerge, and how can it be resolved? – Max D. Sep 12 '18 at 10:17
1

I dropped the four tables by logging into heroku's postgres manager and re-ran it and it seems to be working – Max D. Sep 12 '18 at 10:36

SQLAlchemy suggests table is missing a column that it has: ProgrammingError: (psycopg2.ProgrammingError) column Canada.address does not exist

1 Answers1