CODEWITHSUNDEEP
python-3.xpostgresqlazurepsycopg2
Petr Švec
Petr Švec

Reputation: 63

Connecting to Azure PostgreSQL server from python psycopg2 client

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.

Upvotes: 3

Views: 2290

Answers (1)

Coffee and Code
Coffee and Code

Reputation: 1033

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

I created variables and an f-string and 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 UK South. Before that it did complain about the format of the username having to use @, although the username was correct, as tested from the command line with PSQL and another SQL client.

Upvotes: 2

Related Questions