Thread: TypeError: dict is not a sequence

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

Hi Jim

> I suspect the source of the problem is the commented-out line
>
>  -- ST_FromGDALRaster(pg_read_binary_file(%s)),
>
> in the statement.

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

> 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'

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.

On 11/1/24 08:11, thiemo@gelassene-pferde.biz wrote:
> Hi Jim
> 
>> I suspect the source of the problem is the commented-out line
>>
>>  -- ST_FromGDALRaster(pg_read_binary_file(%s)),
>>
>> in the statement.
> 
> You are right. I was not expecting the comment to be relevant for the 
> 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.
> 
> 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

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

select ST_FromGDALRaster(the_field);

work in psql?

> 
>> 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'
> 
> 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.
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> 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:
>
> select ST_FromGDALRaster(the_field);
>
> work in psql?

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



Many thanks for your support.

On 11/1/24 09:49, thiemo@gelassene-pferde.biz wrote:
> 
> Adrian Klaver <adrian.klaver@aklaver.com> 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:
>>
>> select ST_FromGDALRaster(the_field);
>>
>> work in psql?
> 
> 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.

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.

> 
> 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
> 
> 
> 
> Many thanks for your support.
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> 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.

On 11/1/24 10:23 AM, thiemo@gelassene-pferde.biz wrote:
> 
> Adrian Klaver <adrian.klaver@aklaver.com> 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.

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.

-- 
Adrian Klaver
adrian.klaver@aklaver.com