DataError: (DataError) invalid input syntax for integer: "None"

Question

Background

We're using Flask to develop a fairly simple application to "scratch an itch" we realized we had while working on a previous project. Session management is handled through the Flask-Login extension, coupled with the Github-Flask extension which we use for user authentication. The app uses Flask-SQLAlchemy and Psycopg2 (2.6.3) to connect to a PostgreSQL 9.1 database where user data is stored.

Issue

When the app is run locally (or deployed to a remote server) for testing we're seeing a strange condition during the first login attempt. The first time I log in I successfully get through the Github authentication sets, but immediately see this error.

DataError: (DataError) invalid input syntax for integer: "None"
LINE 3: WHERE "user".id = 'None'
                      ^
'SELECT "user".id AS user_id, "user".date_created AS user_date_created,
"user".date_modified AS user_date_modified, "user".nickname AS user_nickname,
"user".email AS user_email, "user".about_me AS user_about_me,
"user".github_access_token AS user_github_access_token, "user".github_id AS
user_github_id \nFROM "user" \nWHERE "user".id = %(param_1)s' {'param_1': u'None'}

I can close the browser tab and then revisit the application URL and see the same message. However, if I close the browser window, completely clear my browser's cache, and then attempt to log back in, it works as expected and I'm able to use the application. From that point on I can log out, switch browsers, clear browser cache again, etc., and there don't appear to be any problems -- it's only that first login until browser cache is cleared that this happens.

My testing shows that this happens on any user and two different users can be in two different states at the same time (one has cleared cache and now isn't having problems, the other who hasn't taken those steps is still stuck in the error state.

Originally we used a local SQLite database instead of Postgres. The issue does not exist there. It only has occurred since the switch to Postgres.

I'm at a loss for what steps I can take to remedy this situation. So far the questions I know to ask haven't been able to lead to the right answers when I search online.

For reference, I'll include the full stack trace as well.

Full Stack Trace

Traceback (most recent call last):
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask_debugtoolbar/__init__.py", line 124, in dispatch_request
    return view_func(**req.view_args)
  File "/Users/dev/PRODUCT/app/modules/mod_profile/controllers.py", line 26, in profile
    title='PRODUCT')
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/templating.py", line 126, in render_template
    ctx.app.update_template_context(context)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask/app.py", line 716, in update_template_context
    context.update(func())
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask_login.py", line 825, in _user_context_processor
    return dict(current_user=_get_user())
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask_login.py", line 794, in _get_user
    current_app.login_manager._load_user()
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask_login.py", line 363, in _load_user
    return self.reload_user()
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/flask_login.py", line 325, in reload_user
    user = self.user_callback(user_id)
  File "/Users/dev/PRODUCT/app/modules/mod_auth/controllers.py", line 31, in load_user
    return User.query.get(id)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 840, in get
    return loading.load_on_ident(self, key)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 231, in load_on_ident
    return q.one()
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2395, in one
    ret = list(self)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2438, in __iter__
    return self._execute_and_instances(context)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2453, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/Users/dev/PRODUCT/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
DataError: (DataError) invalid input syntax for integer: "None"
LINE 3: WHERE "user".id = 'None'
                          ^
 'SELECT "user".id AS user_id, "user".date_created AS user_date_created, "user".date_modified AS user_date_modified, "user".nickname AS user_nickname, "user".email AS user_email, "user".about_me AS user_about_me, "user".github_access_token AS user_github_access_token, "user".github_id AS user_github_id \nFROM "user" \nWHERE "user".id = %(param_1)s' {'param_1': u'None'}

Answer 1

Resolved!

This was a race condition. During the login process our application redirects an authenticated user to their profile page. This was getting triggered correctly, but for new users it was getting called before the user was actually committed to the database.

Visually it happened at the same time, so you could confirm that the callback was supplied with the "right" information, but it wasn't until I deconstructed and then reconstructed the login steps that I realized that it was hitting the route just before the record was actually committed to the database. Since the function that added the record wasn't interrupted by the redirect it did what it was supposed to do, but just a hair late.

Perhaps someone else will find this extract of my code helpful when troubleshooting a similar issue:

## from mod_auth/controllers.py
@mod_auth.before_app_request
def before_request():
    g.user = current_user

@mod_auth.route('/login', methods=['GET'])
def login():
    if g.user.is_authenticated():
        redirect(url_for('mod_home.index'))
    return github.authorize()

@login_manager.user_loader
def load_user(id):
    return User.query.get(id)

@github.access_token_getter
def token_getter():
    user = g.user
    if user is not None:
        return user.github_access_token

@mod_auth.route('/github')
@github.authorized_handler
def authorized(oauth_token):

    next_url = request.args.get('next') or url_for('mod_home.index')
    if oauth_token is None:
        flash("Authorization failed.")
        return redirect(next_url)

    user = User.query.filter_by(github_access_token=oauth_token).first()
    if user is None:
        user = User(github_access_token=oauth_token)
        db.session.add(user)
        db.session.commit() ## <-- adding a commit here fixed the issue

    user.github_access_token = oauth_token

    login_user(user) 
    ## login_user called, but because the oauth_token exist and had been
    ## added to the session, the user was flagged as "is_authenticated"
    ## and the redirect was triggered

    ghinfo = github.get('user')

    if 'login' in ghinfo:
        user.nickname = ghinfo['login'].lower()
    else:
        user.nickname = 'nameless'

    db.session.commit() ## this was the only commit, originally

    return redirect(url_for('mod_profile.profile', username=user.nickname))