Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated)
Date: Fri, 01 Nov 2024 11:17:47 +0100
Message-ID: 
 <20241101111747.Horde.GgVaeCJ_Ig6HsmcfjbNwguM@webmail.gelassene-pferde.biz>
From: thiemo@gelassene-pferde.biz
To: psycopg <psycopg@lists.postgresql.org>
Subject: TypeError: dict is not a sequence
Accept-Language: de
Content-Type: text/plain; charset=utf-8; format=flowed; DelSp=Yes
MIME-Version: 1.0
Content-Disposition: inline
Archived-At: 
 <https://www.postgresql.org/message-id/20241101111747.Horde.GgVaeCJ_Ig6HsmcfjbNwguM%40webmail.gelassene-pferde.biz>
Precedence: bulk

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