Connecting to Azure PostgreSQL server from python psycopg2 client

Question

I have trouble connecting to the Azure postgres database from python. I am following the guide here - https://learn.microsoft.com/cs-cz/azure/postgresql/connect-python I have basically the same code for setting up the connection. But the psycopg2 and SQLalchemy throw me the same error:

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

I am able to connect to the instance by other client tools like dbeaver but from python it does not work.

When I investigate in Postgres logs I can see that the server actually authorized the connection but the next line says

 could not receive data from client: An existing connection was forcibly closed by the remote host.

Python is 3.7 psycopg's version is 2.8.5 Azure Postgres region is in West Europe

Does someone has any suggestion on what should I try to make it work?

Thank you!

EDIT: The issue resolved itself. I tried the same setup a few days later and it started working. Might have been something wrong with the Azure West Europe.

Facing the same issue intermittently. we have a load that runs every day for 8 hours. at least once a week this fails and we have to retrigger. painful. we have an ample amount of resources unused on our Postgres server, so that's not an issue. looks like it has something to do with maintenance mode. — Ojas Kale
– Ojas Kale, Commented Sep 11, 2020 at 15:24

Coffee and Code · Accepted Answer · 2025-07-17 12:11:59Z

3

I had this issue too. I think I read somewhere (I forget where) that Azure has an issue with the "@" you have to use for the username (user@serverName).

I created connection variables and used an f-string, then it worked OK.

import sqlalchemy    

username = 'user@server_name'
password = 'PassWord!'
host = 'server_name.postgres.database.azure.com'
database = 'your_database'

conn_str = f'postgresql+psycopg2://{username}:{password}@{host}/{database}'

After that:

engine = sqlalchemy.create_engine(conn_str, pool_pre_ping=True)
conn = engine.connect()

Test it with a simple SQL statement.

sql = 'SELECT * FROM public.some_table;'
results = conn.engine.execute(sql)

This was a connection in to Azure in UK South. Before that it did complain about the format of the username (having to use @), although the username was correct, as I tested it from the command line with PSQL and another SQL client.

edited Jul 17 at 12:11

answered Sep 25, 2021 at 12:50

Coffee and Code

1,09315 silver badges18 bronze badges

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

1 Comment

Alex Yu Over a year ago

I think you're correct. The critical point is that username is username@server. One strange quirk was when we created an instance copy and tried to connect to it with username@server when it was needed username@server_copy. Most strange was that we connected(!) but connected to a wrong instance. Not to one in host.

Collectives™ on Stack Overflow

Connecting to Azure PostgreSQL server from python psycopg2 client

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related