I tried to run a Python program which query our database.

But unfortunately any query i run with psycopg2 is very very very slow.

As an exemple you can see in the picture that the same query took 47ms in Dbeaver and take more than 3 minutes in Python ! In the past i tried to move from dbever to oracle client. But all my queries in oracle were so slow so i decided to stay on dBeaver.

But scripting and make queries on the database is a need for my project.

Here an exemple of table I am querying "bex" :

Here is the code I am using in Python :

import psycopg2

try:
    conn = psycopg2.connect(
            host="xxxxx.sogate-pacy.xxxxxx.fr",
            dbname="xxxxxx",
            user="xxxxxxx",
            password="\<xxxxx\>",
            port="5432",
            options="-c search_path=xxx",
            sslmode = "disable"
    )

    cursor = conn.cursor()
    postgreSQL_select_Query = "SELECT * FROM bex"
    
    cursor.execute(postgreSQL_select_Query)
    ouvrage = cursor.fetchone()
    
    print("Print each row and it's columns values")
    print(cursor.fetchone())

except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from PostgreSQL", error)

finally:
    # closing database connection.
    if conn:
        cursor.close()
        conn.close()
        print("PostgreSQL connection is closed")

I tried to make a Python script to get data from the database

CodePudding user response：

Your python code fetches the entire bex table into memory in your python process memory space, and then processes the first row and throws the rest away. While pgAdmin4 and DBeaver both uses cursors (or something equivalent to them) to fetch only a small number of rows until you do something which calls for more. You can use a psycopg2 "named cursor" to get the same behavior in your own python code as you get with pgAdmin4.

CodePudding user response：

To be notes that this table has only 10 rows at total.

and this happen even if do a select to return me only one row