Flask Web App: (psycopg2.OperationalError) server closed the connection unexpectedly

Question

I have a small application on Azure that runs as a web app with the following traits:

Python (Flask with SQLAlchemy)
PostgreSQL

I'm trying to create a table from my python code through SQLAlchemy. Here's my folder structure:

Here's my __init__.py file:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

POSTGRES = {
    'user': 'admin_user@pracap',
    'pw': 'password_goes_here',
    'db': 'apitest',
    'host': 'pracap.postgres.database.azure.com',
    'port': '5432',
}
URL = 'postgresql://%(user)s:\%(pw)s@%(host)s:%(port)s/%(db)s' % POSTGRES

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = URL
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

import apitestproject.index

Then I have my index.py file:

from flask import Flask
from flask_restful import Api
from apitestproject import app, db


@app.before_first_request
def create_tables():
    db.create_all()

@app.route('/')
@app.route('/home')
def home():
    return "I'm the default route"

And here's my usermodel file:

from ..apitestproject import db

class UserModel(db.Model):
    __tablename__ = 'users'

    id = db.column(db.string, primary_key=True)
    name = db.column(db.string(50))
    address = db.column(db.string(144))
    salary = db.column(db.numeric(12, 2))
    position = db.column(db.string(50))
    password = db.column(db.string(50))

When I run my project, I get the following error:

OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

I have disabled the requiredSSL from Azure just for testing purposes and allowed connections from every IP on the firewall like shown on the MSFT tutorial.

Anyone has had this error before?

score -1 · Answer 1 · answered Sep 12 '17 at 07:38

Per my experience, I think the issue was caused by using psycopg2 with the connection string like postgresql://user:password@hostname:port/dbname. For using psycopg2, you need to use the connection string in Python as the code below, please refer to the offical document here.

import psycopg2

# Update connection string information obtained from the portal
host = "mypgserver-20170401.postgres.database.azure.com"
user = "mylogin@mypgserver-20170401"
dbname = "mypgsqldb"
password = "<server_admin_password>"
sslmode = "require"

# Construct connection string
conn_string = "host={0} user={1} dbname={2} password={3} sslmode={4}".format(host, user, dbname, password, sslmode)
conn = psycopg2.connect(conn_string) 
print "Connection established"

And there is a SO thread Can't Figure Out DB URI Postgres Azure which explained some issues about connecting PostgreSQL on Azure using Python.

Hope it helps.

Flask Web App: (psycopg2.OperationalError) server closed the connection unexpectedly

1 Answers1