时间戳

首页 > 解决方案 > Postgres 或 Python 脚本在尝试访问数据库时引发错误

python - Postgres 或 Python 脚本在尝试访问数据库时引发错误

问题描述

我一直在关注本教程。

我正在开发一个虚拟机，由于公司的政策无法连接到互联网，所以我在加载数据部分修改了步骤Shapefile 下载。我将所需的 .zip 档案（在 中指定）下载到不同的机器并通过 pscp 复制它。我还将 Python 脚本修改为仅解压缩文件，而不是尝试下载它们。external-data.ymlget-external-data.py

预期的行为是将 shapefile 加载到准备好的 postgres 数据库中。

实际行为如下：

ubuadmin@klab-osm:~/src/openstreetmap-carto$ ls
antarctica-icesheet-outlines-3857.zip  Dockerfile.db      ne_110m_admin_0_boundary_lines_land.zip  road-colors.yaml
antarctica-icesheet-polygons-3857.zip  Dockerfile.import  openstreetmap-carto.lua                  scripts
CARTOGRAPHY.md                         DOCKER.md          openstreetmap-carto.style                simplified-water-polygons-split-3857.zip
CHANGELOG.md                           external-data.yml  package-lock.json                        style
CODE_OF_CONDUCT.md                     indexes.sql        patterns                                 symbols
CONTRIBUTING.md                        indexes.yml        preview.png                              USECASES.md
data                                   INSTALL.md         project.mml                              water-polygons-split-3857.zip
docker-compose.yml                     LICENSE.txt        README.md
Dockerfile                             mapnik.xml         RELEASES.md

一切似乎都在工作 - 所有 4 个 .zip 档案都已到位。所以我执行下一步：

ubuadmin@klab-osm:~/src/openstreetmap-carto$ sudo python3 ./scripts/get-external-data.py
[sudo] password for ubuadmin:
INFO:root:Starting load of external data into database
INFO:root:Checking table simplified_water_polygons
INFO:root:  Decompressing files
INFO:root:  Water Polygons
INFO:root:  Antarctica Icesheet Polygons
INFO:root:  Antarctica Icesheet Polygons
INFO:root:  Admin Boundary Lines
INFO:root:  Decompressing done
INFO:root:  Importing into database
CRITICAL:root:ogr2ogr returned 1 with layer simplified_water_polygons
CRITICAL:root:Command line was ogr2ogr -f PostgreSQL -lco GEOMETRY_NAME=way -lco SPATIAL_INDEX=FALSE -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=YES -nln loading.simplified_water_polygons "PG:dbname=gis port=5432 user=user host=localhost password=password" data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp
CRITICAL:root:Output was

Traceback (most recent call last):
  File "./scripts/get-external-data.py", line 290, in main
    subprocess.check_output(
  File "/usr/lib/python3.8/subprocess.py", line 415, in check_output
    return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
  File "/usr/lib/python3.8/subprocess.py", line 516, in run
    raise CalledProcessError(retcode, process.args,
subprocess.CalledProcessError: Command '['ogr2ogr', '-f', 'PostgreSQL', '-lco', 'GEOMETRY_NAME=way', '-lco', 'SPATIAL_INDEX=FALSE', '-lco', 'EXTRACT_SCHEMA_FROM_LAYER_NAME=YES', '-nln', 'loading.simplified_water_polygons', 'PG:dbname=gis port=5432 user=ubuadmin host=localhost password=ubuadmin', 'data/simplified_water_polygons/simplified-water-polygons-split-3857/simplified_water_polygons.shp']' returned non-zero exit status 1.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./scripts/get-external-data.py", line 313, in <module>
    main()
  File "./scripts/get-external-data.py", line 299, in main
    raise RuntimeError(
RuntimeError: ogr2ogr error when loading table simplified_water_polygons

我在用：

脚本的修改部分get-external-data.py如下所示：

if "archive" in source and source["archive"]["format"] == "zip":
    logging.info("  Decompressing files")

# strange archive name - was manually added, because of no Internet connection
logging.info("  Water Polygons")
with zipfile.ZipFile("water-polygons-split-3857.zip", 'r') as zip_ref:
    zip_ref.extractall(workingdir)
logging.info("  Antarctica Icesheet Polygons")
with zipfile.ZipFile("antarctica-icesheet-polygons-3857.zip", 'r') as zip_ref:
    zip_ref.extractall(workingdir)
logging.info("  Antarctica Icesheet Polygons")
with zipfile.ZipFile("antarctica-icesheet-outlines-3857.zip", 'r') as zip_ref:
    zip_ref.extractall(workingdir)
logging.info("  Admin Boundary Lines")
with zipfile.ZipFile("ne_110m_admin_0_boundary_lines_land.zip", 'r') as zip_ref:
    zip_ref.extractall(workingdir)

logging.info("  Decompressing done")

ogrpg = "PG:dbname={}".format(database)

if port is not None:
    ogrpg = ogrpg + " port={}".format(port)
if user is not None:
    ogrpg = ogrpg + " user={}".format(user)
if host is not None:
    ogrpg = ogrpg + " host={}".format(host)
if password is not None:
    ogrpg = ogrpg + " password={}".format(password)

ogrcommand = ["ogr2ogr",
              '-f', 'PostgreSQL',
              '-lco', 'GEOMETRY_NAME=way',
              '-lco', 'SPATIAL_INDEX=FALSE',
              '-lco', 'EXTRACT_SCHEMA_FROM_LAYER_NAME=YES',
              '-nln', "{}.{}".format(config["settings"]["temp_schema"], name)]

if "ogropts" in source:
    ogrcommand += source["ogropts"]

if DEBUG:
    logging.info(f"Config: \n{config}")
    logging.info(f"Source: \n{source}")

ogrcommand += [ogrpg,
               os.path.join(workingdir, source["file"])]

logging.info("  Importing into database")
logging.debug("running {}".format(
    subprocess.list2cmdline(ogrcommand)))

# ogr2ogr can raise errors here, so they need to be caught
try:
    subprocess.check_output(
        ogrcommand, stderr=subprocess.PIPE, universal_newlines=True)
except subprocess.CalledProcessError as e:
    # Add more detail on stdout for the logs
    logging.critical(
        "ogr2ogr returned {} with layer {}".format(e.returncode, name))
    logging.critical("Command line was {}".format(
        subprocess.list2cmdline(e.cmd)))
    logging.critical("Output was\n{}".format(e.output))
    raise RuntimeError(
        "ogr2ogr error when loading table {}".format(name))

logging.info("  Import complete")

似乎解压缩文件工作得很好，但 postgres db 以某种方式阻止访问指定的表。

标签： pythonpostgresqlzipopenstreetmapunzip

解决方案

谢谢@Tomerikoo

我找到了答案，就在这里。显然，提出问题有助于找到解决方案，即使您没有得到回答。错误出现在 Python 脚本中 - .zip 档案之一没有解压缩。脚本的固定部分如下：

if "archive" in source and source["archive"]["format"] == "zip":
    logging.info("  Decompressing files")
    # strange archive name - was manually added, because of no Internet connection
    logging.info("  Simplified Water Polygons")
    with zipfile.ZipFile("simplified-water-polygons-split-3857.zip", 'r') as zip_ref:
        zip_ref.extractall(workingdir)
    logging.info("  Water Polygons")
    with zipfile.ZipFile("water-polygons-split-3857.zip", 'r') as zip_ref:
        zip_ref.extractall(workingdir)
    logging.info("  Antarctica Icesheet Polygons")
    with zipfile.ZipFile("antarctica-icesheet-polygons-3857.zip", 'r') as zip_ref:
        zip_ref.extractall(workingdir)
    logging.info("  Antarctica Icesheet Polygons")
    with zipfile.ZipFile("antarctica-icesheet-outlines-3857.zip", 'r') as zip_ref:
        zip_ref.extractall(workingdir)
    logging.info("  Admin Boundary Lines")
    with zipfile.ZipFile("ne_110m_admin_0_boundary_lines_land.zip", 'r') as zip_ref:
        zip_ref.extractall(workingdir)

推荐阅读