Subject: TypeError: dict is not a sequence

From: [email-masked]
Date: Fri, 01 Nov 2024 11:17:47 +0100

Hi

I am trying to load data into a PostGIS table. For this purpose, I  
have the following function I tried to cough up analogously to  
https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries.

def process_files(data_directory, file_name_regexp, conn, source_id, logger):
     loaded_files =  
get_loaded_files(conn=conn,source_id=source_id,logger=logger,)
     existing_hashes = {file_hash for file_hash, _ in loaded_files}

     # "next" inhibits recursion, so only the top level is retrieved
     logger.info(f"Looking into '{data_directory}'")
     try:
         cur = conn.cursor()
         cur.execute("set postgis.gdal_enabled_drivers = 'ENABLE_ALL';")
         root, dirs, files = next(os.walk(data_directory))
         for file_name in files:
             if re.match(file_name_regexp, file_name):
                 file_path = os.path.join(root, file_name)
                 logger.info(f"Processing '{file_path}'")
                 file_hash = calculate_file_sha3_512_hash(file_path)
                 file_creation_time =  
datetime.fromtimestamp(os.path.getctime(file_path))

                 # If the hash is alread present, skip this file FIXME  
check on file names
                 if file_hash in existing_hashes:
                     continue

                 # Get the raster data
                 with open(file_path, 'rb') as f:
                     raster_data = f.read()

                 statement = """merge
into
     TOPO_FILES as TARGET
using
     ( values
         (
             -- ST_FromGDALRaster(pg_read_binary_file(%s)),
             ST_FromGDALRaster(%(TILE)s::bytea),
             %(FILE_NAME)s,
             %(FILE_CREATION_PIT)s,
             %(FILE_HASH)s,
             %(SOURCE_ID)s::uuid
         )
     ) as source ( TILE, FILE_NAME, FILE_CREATION_PIT, FILE_HASH, SOURCE_ID )
on
     TARGET.FILE_NAME = SOURCE.FILE_NAME
and TARGET.SOURCE_ID = SOURCE.SOURCE_ID
and TARGET.FILE_HASH != SOURCE.FILE_HASH
and TARGET.FILE_CREATION_PIT < SOURCE.FILE_CREATION_PIT
when matched
     then
update
set
     TILE = SOURCE.TILE,
     FILE_NAME = SOURCE.FILE_NAME,
     FILE_CREATION_PIT = SOURCE.FILE_CREATION_PIT,
     FILE_HASH = SOURCE.FILE_HASH,
     SOURCE_ID = SOURCE.SOURCE_ID
when not matched
     then
insert
     (
         TILE,
         FILE_NAME,
         FILE_CREATION_PIT,
         FILE_HASH,
         SOURCE_ID
     )
     values
     (
         SOURCE.TILE,
         SOURCE.FILE_NAME,
         SOURCE.FILE_CREATION_PIT,
         SOURCE.FILE_HASH,
         SOURCE.SOURCE_ID
     );"""
                 logger.debug("statement")
                 logger.debug(statement)
                 logger.debug("First 100 bytes of raster_data")
                 logger.debug(f"{raster_data[:100]}")
                 logger.debug(f"file_name:{file_name}")
                 logger.debug(f"file_creation_time:{file_creation_time}")
                 logger.debug(f"file_hash:{file_hash}")
                 logger.debug(f"source_id:{source_id}")
                 # params = (psycopg2.Binary(raster_data), file_name,  
file_creation_time, file_hash, source_id)
                 params = {'TILE': raster_data
                          ,'FILE_NAME': file_name
                          ,'FILE_CREATION_PIT': file_creation_time
                          ,'FILE_HASH': file_hash
                          ,'SOURCE_ID': source_id}
                 # logger.debug(f"params:{params}")
                 cur.execute(statement, params)
                 conn.commit()
                 cur.close()
     except StopIteration:
         logger.error(f"Error: '{data_directory}' could not be walked.  
Directory might be empty or inaccessible.")

However, I get the mentioned error.

2024-11-01 11:06:58 - root - DEBUG -  
source_id:4f68d890-a08c-4c06-8aa5-741ad36b6abe
Traceback (most recent call last):
   File  
"/home/thiemo/external_projects/svn/33/trunk/code_files/data_storage/load_OpenTopography_data.py", line 737, in  
<module>
     main()
   File  
"/home/thiemo/external_projects/svn/33/trunk/code_files/data_storage/load_OpenTopography_data.py", line 714, in  
main
     process_files(
   File  
"/home/thiemo/external_projects/svn/33/trunk/code_files/data_storage/load_OpenTopography_data.py", line 442, in  
process_files
     cur.execute(statement, params)
TypeError: dict is not a sequence

I would very much appreciate, if someone shed some light on the matter.

Kind regards

Thiemo

---

From: Jim Sizelove <[email-masked]>
Date: Fri, 1 Nov 2024 09:59:13 -0400

Hi Thiemo,

I suspect the source of the problem is the commented-out line

in the statement.

I get a similar error when I try to reproduce the problem.

>>> print(stmt)
> select -- %s
> %(ts)s
> >>> params
> {'ts': datetime.datetime(2024, 11, 1, 9, 21, 30, 283439)}
> >>> cur.execute(stmt, params)
>   File "<stdin>", line 1, in <module>
>   File
> "/home/jims/venvs/psycopg/lib/python3.12/site-packages/psycopg/cursor.py",
> line 97, in execute
>     raise ex.with_traceback(None)
> psycopg.ProgrammingError: positional and named placeholders cannot be mixed

Maybe the difference in the errors is due to a different version of
psycopg? This is what I am using:

>>> sys.version
> '3.12.3 (main, Sep 11 2024, 14:17:37) [GCC 13.2.0]'
> >>> psycopg.__version__
> '3.2.3'

Regards,
Jim

On Fri, Nov 1, 2024 at 6:18 AM <[email-masked]> wrote:

>                  logger.debug(f"file_name: {file_name} ")
>                  logger.debug(f"file_creation_time: {file_creation_time} ")
>                  logger.debug(f"file_hash: {file_hash} ")
>                  logger.debug(f"source_id: {source_id} ")
>                  # logger.debug(f"params: {params} ")

---

From: [email-masked]
Date: Fri, 01 Nov 2024 16:11:13 +0100

Hi Jim

>  -- ST_FromGDALRaster(pg_read_binary_file(%s)),

You are right. I was not expecting the comment to be relevant for the  
parameter replacement. Many thanks for the hint.

I now have an error, not related to psycopg, as far as I can tell.

psycopg2.errors.InternalError_: RASTER_fromGDALRaster: Could not open  
bytea with GDAL. Check that the bytea is of a GDAL supported format

>> '3.12.3 (main, Sep 11 2024, 14:17:37) [GCC 13.2.0]'

Probably.
>>> sys.version
'3.11.2 (main, Aug 26 2024, 07:20:54) [GCC 12.2.0]'
>>> psycopg.__version__
'3.1.7'

Many thanks for your support.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 1 Nov 2024 09:12:13 -0700

> parameter replacement. Many thanks for the hint.

That is an SQL(---) comment and I am guessing the parser sees the 
parameter before it gets to the comment.
> bytea with GDAL. Check that the bytea is of a GDAL supported format

This looks to be on the PostGIS end related to the ST_FromGDALRaster() 
function. Does:

select ST_FromGDALRaster(the_field);

work in psql?

-- 
Adrian Klaver
[email-masked]

---

From: [email-masked]
Date: Fri, 01 Nov 2024 17:49:05 +0100

Adrian Klaver <[email-masked]> escribió:

>> psycopg2.errors.InternalError_: RASTER_fromGDALRaster: Could not  
>> open bytea with GDAL. Check that the bytea is of a GDAL supported  
>> format
>
> This looks to be on the PostGIS end related to the  
> ST_FromGDALRaster() function. Does:
>

Thanks for looking into it. I am not sure, however, what you refer to  
with the_field. Be it as it may, the following might answer your  
question.

thiemo @ hotrod ~ % psql -p 5447 -d treintaytres -U treintaytres
psql (17.0 (Debian 17.0-1.pgdg120+1))
Type "help" for help.

treintaytres=> set postgis.gdal_enabled_drivers = 'ENABLE_ALL';
SET
treintaytres=> WITH foo AS (
     SELECT  
ST_AsPNG(ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(2, 2, 0,  
0, 0.1, -0.1, 0, 0, 4326), 1, '8BUI', 1, 0), 2, '8BUI', 2, 0), 3,  
'8BUI', 3, 0)) AS png
),
bar AS (
     SELECT 1 AS rid, ST_FromGDALRaster(png) AS rast FROM foo
     UNION ALL
     SELECT 2 AS rid, ST_FromGDALRaster(png, 3310) AS rast FROM foo
)
SELECT
     rid,
     ST_Metadata(rast) AS metadata,
     ST_SummaryStats(rast, 1) AS stats1,
     ST_SummaryStats(rast, 2) AS stats2,
     ST_SummaryStats(rast, 3) AS stats3
FROM bar
ORDER BY rid;
WARNING:  permission denied to set parameter "postgis.gdal_enabled_drivers"
ERROR:  rt_raster_to_gdal: Could not load the output GDAL driver
CONTEXT:  PL/pgSQL function st_aspng(raster,text[]) line 31 at RETURN

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 1 Nov 2024 09:57:55 -0700

On 11/1/24 09:49, [email-masked] wrote:
> with the_field. Be it as it may, the following might answer your question.

It was just a placeholder name to represent whatever field the function 
was working on.

The error messages spell out the problem. or at least an initial one, 
the GDAL drivers are not being loaded.  Looks like the connection user 
does not have sufficient privileges to load them.

> ST_AsPNG(ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(2, 2, 0, 0, 
> 0.1, -0.1, 0, 0, 4326), 1, '8BUI', 1, 0), 2, '8BUI', 2, 0), 3, '8BUI', 
> 3, 0)) AS png
> ),
> bar AS (

---

From: [email-masked]
Date: Fri, 01 Nov 2024 18:23:27 +0100

Adrian Klaver <[email-masked]> escribió:

> The error messages spell out the problem. or at least an initial  
> one, the GDAL drivers are not being loaded.  Looks like the  
> connection user does not have sufficient privileges to load them.

What I thought. I need to figure out why the owner of the database is  
not allowed to do so. Maybe because it was created from a template  
database where the PostGIS stuff was already installed but by the user  
postgres.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 1 Nov 2024 11:03:47 -0700

> database where the PostGIS stuff was already installed but by the user 

Yeah that is something you will need to sort out. My suggestion would be 
to do so using psql as it works closer to the database.