I am writing a Flask API that needs to communicate with many datastores, Postgres on AWS RDS being one of them. I want to avoid Flask-SQLAlchemy (trying to reduce package dependence), and given that my use case is pretty simple, I think the standard SQLAlchemy library should suffice. This is my models.py file:
class DriverPostgres:
def __init__(self):
self.connector = PostgresCredentials()
self.connection_str = 'postgresql+psycopg2://{}:{}@{}/{}'.format(...)
self.engine = create_engine(self.connection_str)
self.Session = scoped_session(
sessionmaker(autocommit=False, autoflush=True, bind=self.engine))
Base.metadata.create_all(bind=self.engine)
def get_user_by_email(self, email):
session = self.Session()
result = session.query(User).filter_by(email=email).first()
self.Session.remove()
return result
def register_user(self, ...):
user = User(...)
session = self.Session()
session.add(user)
try:
session.commit()
except IntegrityError:
return False
finally:
self.Session.remove()
return True
In my Flask app server.py file, I import DriverPostgres at the top of the app and instantiate driver_postgres = DriverPostgres(), and when handling requests I make calls like driver_postgres.get_user_by_email(). This logic works perfectly when the app is first loaded. I can send as many requests through as I want with no issues. However, if I wait for a few minutes and hit the app again, I get the following error:
Traceback (most recent call last):
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 2309, in __call__
return self.wsgi_app(environ, start_response)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 2295, in wsgi_app
response = self.handle_exception(e)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask_cors/extension.py", line 161, in wrapped_function
return cors_after_request(app.make_response(f(*args, **kwargs)))
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 1741, in handle_exception
reraise(exc_type, exc_value, tb)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
raise value
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
response = self.full_dispatch_request()
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask_cors/extension.py", line 161, in wrapped_function
return cors_after_request(app.make_response(f(*args, **kwargs)))
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
raise value
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
rv = self.dispatch_request()
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/Users/userserver.py", line 118, in login
response = driver_postgres.check_password(email, password)
File "/Users/usermodels.py", line 125, in check_password
user_to_check = self.get_user_by_email(email)
File "/Users/usermodels.py", line 154, in get_user_by_email
result = session.query(User).filter_by(email=email).first()
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2835, in first
ret = list(self[0:1])
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2627, in __getitem__
return list(res)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2935, in __iter__
return self._execute_and_instances(context)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2958, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/Users/user/.virtualenvs/demo/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[SQL: ...] (Background on this error at: http://sqlalche.me/e/e3q8)
This is rather crippling. I have spent a lot of time going through SO and many tutorials, and this seems to be a standard valid pattern, so I am not sure what is going wrong. Should I not be removing the session? Should I be using the @app.teardown_appcontext decorator (it seems that if I just remove the sessions myself as I am doing now, I shouldn't have to)? Should I be using a connection pool instead (it seems that QueuePool is enabled under the hood by default anyway)? If it helps, I am using the latest version of all packages, Python 3.6.5, and Postman to send the requests to the server, which is running at localhost:5000. Any advice would be much appreciated - thanks in advance!
