0

I have installed my Python app with Heroku and am moving over from my SQLite db to the Postgres one. However, I am encountering the following error which does not occur with SQLite:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) argument of WHERE must be type boolean, not type character varying
LINE 3: WHERE site_clearance."CreatedOn"
              ^

[SQL: SELECT id 
FROM site_clearance 
WHERE site_clearance."CreatedOn"]
(Background on this error at: http://sqlalche.me/e/f405)

The corresponding Python code for the query is for the generation of a PDF file and it is this query that is generating the error:

# MAKE THE DB QUERIES HERE THAT ARE NEEDED TO BE ENTERED INTO EACH RECORD SHEET
for i in range(0, len(dictionary)):
    query = [r[0] for r in db.session.query(model).filter(model.CreatedOn).values(keys[i])]
    queries.append(query)

As I said, this error does not happen with SQLite and the trouble started with the psycopg2 package. Stack trace from the server logs below. Any assistance appreciated. Thanks!

2020-08-10T17:54:25.630676+00:00 heroku[router]: at=info method=GET path="/makepdf_site_clearance" host=hidden-meadow-44024.herokuapp.com request_id=6bb8550d-afd0-415f-9e8b-bf0a86e63ab4 fwd="88.98.244.118" dyno=web.1 connect=1ms service=37ms status=500 bytes=484 protocol=https
2020-08-10T17:54:25.627181+00:00 app[web.1]: [2020-08-10 17:54:25,620] ERROR in app: Exception on /makepdf_site_clearance [GET]
2020-08-10T17:54:25.627193+00:00 app[web.1]: Traceback (most recent call last):
2020-08-10T17:54:25.627194+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
2020-08-10T17:54:25.627194+00:00 app[web.1]: cursor, statement, parameters, context
2020-08-10T17:54:25.627195+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
2020-08-10T17:54:25.627196+00:00 app[web.1]: cursor.execute(statement, parameters)
2020-08-10T17:54:25.627196+00:00 app[web.1]: psycopg2.errors.DatatypeMismatch: argument of WHERE must be type boolean, not type character varying
2020-08-10T17:54:25.627196+00:00 app[web.1]: LINE 3: WHERE site_clearance."CreatedOn"
2020-08-10T17:54:25.627197+00:00 app[web.1]: ^
2020-08-10T17:54:25.627198+00:00 app[web.1]:
2020-08-10T17:54:25.627198+00:00 app[web.1]:
2020-08-10T17:54:25.627199+00:00 app[web.1]: The above exception was the direct cause of the following exception:
2020-08-10T17:54:25.627199+00:00 app[web.1]:
2020-08-10T17:54:25.627200+00:00 app[web.1]: Traceback (most recent call last):
2020-08-10T17:54:25.627200+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 2447, in wsgi_app
2020-08-10T17:54:25.627201+00:00 app[web.1]: response = self.full_dispatch_request()
2020-08-10T17:54:25.627201+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1952, in full_dispatch_request
2020-08-10T17:54:25.627202+00:00 app[web.1]: rv = self.handle_user_exception(e)
2020-08-10T17:54:25.627202+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1821, in handle_user_exception
2020-08-10T17:54:25.627203+00:00 app[web.1]: reraise(exc_type, exc_value, tb)
2020-08-10T17:54:25.627203+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
2020-08-10T17:54:25.627203+00:00 app[web.1]: raise value
2020-08-10T17:54:25.627204+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1950, in full_dispatch_request
2020-08-10T17:54:25.627204+00:00 app[web.1]: rv = self.dispatch_request()
2020-08-10T17:54:25.627205+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1936, in dispatch_request
2020-08-10T17:54:25.627205+00:00 app[web.1]: return self.view_functions[rule.endpoint](**req.view_args)
2020-08-10T17:54:25.627206+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask_login/utils.py", line 272, in decorated_view
2020-08-10T17:54:25.627206+00:00 app[web.1]: return func(*args, **kwargs)
2020-08-10T17:54:25.627207+00:00 app[web.1]: File "/app/constructaerp02/routes.py", line 1191, in makepdf_site_clearance
2020-08-10T17:54:25.627207+00:00 app[web.1]: p = pdf_make(model=SiteClearance, dictionary=SiteClearance_dictionary, title='Site Clearance')
2020-08-10T17:54:25.627208+00:00 app[web.1]: File "/app/constructaerp02/pdf_creator.py", line 23, in pdf_make
2020-08-10T17:54:25.627208+00:00 app[web.1]: query = [r[0] for r in db.session.query(model).filter(model.CreatedOn).values(keys[i])]
2020-08-10T17:54:25.627209+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 1530, in values
2020-08-10T17:54:25.627209+00:00 app[web.1]: return iter(q)
2020-08-10T17:54:25.627210+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3481, in __iter__
2020-08-10T17:54:25.627210+00:00 app[web.1]: return self._execute_and_instances(context)
2020-08-10T17:54:25.627211+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3506, in _execute_and_instances
2020-08-10T17:54:25.627211+00:00 app[web.1]: result = conn.execute(querycontext.statement, self._params)
2020-08-10T17:54:25.627212+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1020, in execute
2020-08-10T17:54:25.627212+00:00 app[web.1]: return meth(self, multiparams, params)
2020-08-10T17:54:25.627212+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
2020-08-10T17:54:25.627213+00:00 app[web.1]: return connection._execute_clauseelement(self, multiparams, params)
2020-08-10T17:54:25.627213+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_clauseelement
2020-08-10T17:54:25.627214+00:00 app[web.1]: distilled_params,
2020-08-10T17:54:25.627215+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1324, in _execute_context
2020-08-10T17:54:25.627215+00:00 app[web.1]: e, statement, parameters, cursor, context
2020-08-10T17:54:25.627216+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1518, in _handle_dbapi_exception
2020-08-10T17:54:25.627216+00:00 app[web.1]: sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2020-08-10T17:54:25.627216+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
2020-08-10T17:54:25.627217+00:00 app[web.1]: raise exception
2020-08-10T17:54:25.627217+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
2020-08-10T17:54:25.627218+00:00 app[web.1]: cursor, statement, parameters, context
2020-08-10T17:54:25.627218+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
2020-08-10T17:54:25.627219+00:00 app[web.1]: cursor.execute(statement, parameters)
2020-08-10T17:54:25.627223+00:00 app[web.1]: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) argument of WHERE must be type boolean, not type character varying
2020-08-10T17:54:25.627224+00:00 app[web.1]: LINE 3: WHERE site_clearance."CreatedOn"
2020-08-10T17:54:25.627224+00:00 app[web.1]: ^
2020-08-10T17:54:25.627224+00:00 app[web.1]:
2020-08-10T17:54:25.627225+00:00 app[web.1]: [SQL: SELECT id
2020-08-10T17:54:25.627225+00:00 app[web.1]: FROM site_clearance
2020-08-10T17:54:25.627226+00:00 app[web.1]: WHERE site_clearance."CreatedOn"]
2020-08-10T17:54:25.627233+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/f405)
2020-08-10T17:54:25.631141+00:00 app[web.1]: 10.63.174.10 - - [10/Aug/2020:17:54:25 +0000] "GET
Improve this question
1
  • Please clean up your logs in the future, it will help people identify your question as answerable. That means removing the timestamps, since they make it hard to read Commented Aug 10, 2020 at 20:08

1 Answer 1

Reset to default
1

The error says exactly what it's going on; you're using .filter(model.createdOn), but createdOn what?

One option is .filter(model.createdOn > datetime.now() - timedelta(days=7)) would work, since it generates the SQL "createdOn" > "2020-08-03 12:00:00" or something similar.

Another option would be .filter(model.createdOn.isnot(None)) or .filter(~model.createdOn.is_(None)), both of which generate "createdOn" IS NOT NULL.

In such a situation, it's often a good idea to print the query, put in some dummy values for the variables, and see if it runs if you execute the SQL directly. That helps a lot with debugging!

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

1 Comment

Thanks. That solved it. I had other issues afterwards too that didn't show up in SQLite but came up in Postgres. I narrowed them down with the python console, entering queries directly there until I found the bogey parameter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.