public inbox for [email protected]
help / color / mirror / Atom feedTypeError: dict is not a sequence
8+ messages / 3 participants
[nested] [flat]
* TypeError: dict is not a sequence
@ 2024-11-01 10:17 [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: [email protected] @ 2024-11-01 10:17 UTC (permalink / raw)
To: psycopg <[email protected]>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
@ 2024-11-01 13:59 ` Jim Sizelove <[email protected]>
2024-11-01 15:11 ` Re: TypeError: dict is not a sequence [email protected]
0 siblings, 1 reply; 8+ messages in thread
From: Jim Sizelove @ 2024-11-01 13:59 UTC (permalink / raw)
To: [email protected]; +Cc: psycopg <[email protected]>
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 <[email protected]> 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
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
@ 2024-11-01 15:11 ` [email protected]
2024-11-01 16:12 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: [email protected] @ 2024-11-01 15:11 UTC (permalink / raw)
To: psycopg <[email protected]>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
2024-11-01 15:11 ` Re: TypeError: dict is not a sequence [email protected]
@ 2024-11-01 16:12 ` Adrian Klaver <[email protected]>
2024-11-01 16:49 ` Re: TypeError: dict is not a sequence [email protected]
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-11-01 16:12 UTC (permalink / raw)
To: [email protected]; psycopg <[email protected]>
On 11/1/24 08:11, [email protected] 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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
2024-11-01 15:11 ` Re: TypeError: dict is not a sequence [email protected]
2024-11-01 16:12 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
@ 2024-11-01 16:49 ` [email protected]
2024-11-01 16:57 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: [email protected] @ 2024-11-01 16:49 UTC (permalink / raw)
To: psycopg <[email protected]>
Adrian Klaver <[email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
2024-11-01 15:11 ` Re: TypeError: dict is not a sequence [email protected]
2024-11-01 16:12 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
2024-11-01 16:49 ` Re: TypeError: dict is not a sequence [email protected]
@ 2024-11-01 16:57 ` Adrian Klaver <[email protected]>
2024-11-01 17:23 ` Re: TypeError: dict is not a sequence [email protected]
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-11-01 16:57 UTC (permalink / raw)
To: [email protected]; psycopg <[email protected]>
On 11/1/24 09:49, [email protected] wrote:
>
> Adrian Klaver <[email protected]> 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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
2024-11-01 15:11 ` Re: TypeError: dict is not a sequence [email protected]
2024-11-01 16:12 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
2024-11-01 16:49 ` Re: TypeError: dict is not a sequence [email protected]
2024-11-01 16:57 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
@ 2024-11-01 17:23 ` [email protected]
2024-11-01 18:03 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: [email protected] @ 2024-11-01 17:23 UTC (permalink / raw)
To: psycopg <[email protected]>
Adrian Klaver <[email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TypeError: dict is not a sequence
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Re: TypeError: dict is not a sequence Jim Sizelove <[email protected]>
2024-11-01 15:11 ` Re: TypeError: dict is not a sequence [email protected]
2024-11-01 16:12 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
2024-11-01 16:49 ` Re: TypeError: dict is not a sequence [email protected]
2024-11-01 16:57 ` Re: TypeError: dict is not a sequence Adrian Klaver <[email protected]>
2024-11-01 17:23 ` Re: TypeError: dict is not a sequence [email protected]
@ 2024-11-01 18:03 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2024-11-01 18:03 UTC (permalink / raw)
To: [email protected]; psycopg <[email protected]>
On 11/1/24 10:23 AM, [email protected] wrote:
>
> Adrian Klaver <[email protected]> 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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-11-01 18:03 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-01 10:17 TypeError: dict is not a sequence [email protected]
2024-11-01 13:59 ` Jim Sizelove <[email protected]>
2024-11-01 15:11 ` [email protected]
2024-11-01 16:12 ` Adrian Klaver <[email protected]>
2024-11-01 16:49 ` [email protected]
2024-11-01 16:57 ` Adrian Klaver <[email protected]>
2024-11-01 17:23 ` [email protected]
2024-11-01 18:03 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox