Hi Thiemo,
I suspect the source of the problem is the commented-out line
-- ST_FromGDALRaster(pg_read_binary_file(%s)),
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)
> Traceback (most recent call last):
> 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 <thi...@gelassene-pferde.biz> wrote:
> 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
>
>
>
>
