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

Gentlewoman

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. – Uri

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? – Apiarian

Answer 1

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

Gentlewoman

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. – Uri

Answer 2

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()

Braud