0

I am trying to execute queries through Python.

Unfortunately, it takes forever. In fact, executing the same query in the DBeaver takes around 5 secs. In addition, queries such as create table, and alter table work perfectly. When it comes to this query it sticks:

def connection_db():
    """
    Establish connection to the DB
    :param db_name: Database name

    :return: cursor, engine

    """

    # connect to database
    conn = psycopg2.connect()
    conn.autocommit = True
    cursor = conn.cursor()
    
    engine = create_engine()

    
    return cursor, engine


cursor, engine = connection_db()

test = """
SELECT ST_DumpAsPolygons(rast.rast,1) band1, ST_DumpAsPolygons(rast.rast,2) band2,
ST_DumpAsPolygons(rast.rast,3) band3, ST_DumpAsPolygons(rast.rast,4) band4,
ST_DumpAsPolygons(rast.rast,5) band5, ST_DumpAsPolygons(rast.rast,6) band6,
ST_DumpAsPolygons(rast.rast,7) band7
FROM raster_grid g, public.t32ulc_entire_tile rast
WHERE rast.rid = 1
"""
print('here')
cursor.execute(test)
    # Fetch result
bbox = cursor.fetchone()[0]

What is wrong with my query?

The raster size is 2.3 GB.

4
  • can you provide whats is your database server config file ? If you are connecting using 'localhost' try replacing it with 127.0.0.1 instead.
    – Abhilshit
    Jul 31 at 10:15
  • @Abhilshit yeah, I tried both 127.0.0.1 and localhost Jul 31 at 16:51
  • Okay, may be you can start your database server in debug mode and check if the connection is established or not. Also if it is able to establish connection, how long it takes to establish. That will help you identify if it is a network configuration issue or not. If you identify that it's not a network issue try using another python package to connect to postgres like try the queries pypi package.
    – Abhilshit
    Aug 1 at 18:16
  • You're missing a semicolon at the end of your sql!
    – jbalk
    Aug 4 at 16:32

1 Answer 1

0

You haven't told the database where the end of the query is. It's waiting for more sql. You need to terminate your query with a semicolon:

test = """
SELECT ST_DumpAsPolygons(rast.rast,1) band1, ST_DumpAsPolygons(rast.rast,2) band2,
ST_DumpAsPolygons(rast.rast,3) band3, ST_DumpAsPolygons(rast.rast,4) band4,
ST_DumpAsPolygons(rast.rast,5) band5, ST_DumpAsPolygons(rast.rast,6) band6,
ST_DumpAsPolygons(rast.rast,7) band7
FROM raster_grid g, public.t32ulc_entire_tile rast
WHERE rast.rid = 1;
"""

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.