Testing the connection of Postgres-DB

Question

I would like to put a button on the GUI if the software connects to a specific Postgre-DB. I wrote a small test-function: if it can connect to the DB it returns True, if not it returns False.

The code works, but there is an issue: if there is no connection (I just pull out the internet cable, nothing else changes), it simply takes too much time. Could you help me to make the code faster if there is no connection?

Here is my simple test-function:

import psycopg2

def postgres_test():

    try:
        conn = psycopg2.connect("dbname='mydb' user='myuser' host='my_ip' password='mypassword'")
        conn.close()
        return True
    except:
        return False

If timeout is a possible reason for absence of connection, I'm afraid you can't do any better, you have to wait until the timeout ends. — Jérôme
– Jérôme, Commented Jan 30, 2017 at 15:49
It appears you can pass a timeout time in seconds to your call to connect() as described here postgresql.org/docs/current/static/…. Would that meet what you're looking for? — hobenkr
– hobenkr, Commented Jan 30, 2017 at 15:52

picibucor · Accepted Answer · 2017-01-30 16:08:16Z

8

Thanks for the comments. And yes, it was timeout related.

Here is my faster code:

import psycopg2

def postgres_test():

    try:
        conn = psycopg2.connect("dbname='mydb' user='myuser' host='my_ip' password='mypassword' connect_timeout=1 ")
        conn.close()
        return True
    except:
        return False

answered Jan 30, 2017 at 16:08

picibucor

7831 gold badge8 silver badges26 bronze badges

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

1 Comment

Jérôme Over a year ago

This might yield a false positive if the connection lags some day. In any case, the user can push that button again to double-check. In practice, if the DB is local, the answer should be fast anyway, so a false positive is unlikely. You may accept your own answer.

hassanzadeh.sd · Accepted Answer · 2022-03-05 15:32:48Z

5

For test postgres connection with python first you have to install this package :

pip install psycopg2-binary

and try this code :

import psycopg2

conn = psycopg2.connect(dbname="db_name",
                        user="user_name",
                        host="127.0.0.1",
                        password="******",
                        port="5432")
cursor = conn.cursor()
cursor.execute('SELECT * FROM information_schema.tables')
rows = cursor.fetchall()
for table in rows:
    print(table)
conn.close()

answered Mar 5, 2022 at 15:32

hassanzadeh.sd

3,5811 gold badge23 silver badges29 bronze badges

Collectives™ on Stack Overflow

Testing the connection of Postgres-DB

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related