postgresql - 使用安装在不同 Postgres Schema 中的 Postgis 扩展

Question

我正在尝试设置我的第一个 Postgres 数据库，但我对为什么我在一个模式中启用 Postgis 然后在另一个模式中使用它时遇到问题感到非常困惑。

我将 Google Cloud SQL 与 Postgres v12 结合使用。我通过 Cloud SQL 代理连接，并通过 pgAdmin4 和 Jupyter Notebook (SQLAlchemy) 连接到服务器。

我的目标是通过 geopandas 将一个充满点的 shapefile 写入数据库中的一个表中。

基于 this thread我正在尝试将 postgis 安装到一个单独的模式中，同时在我的数据实际去向的地方创建几个不同的模式。我在 geopandas 中打开 shapefile，然后使用 .to_postgis() 尝试将 shapefile 内容保存到我的一个数据模式中的新表中。这总是失败。

但是，如果我尝试将 geodataframe 写入扩展模式，它工作得很好。这让我觉得 search_path 有问题，但不知道是什么。我正在按照建议设置搜索路径 here .我已经尝试了多种方法来设置 search_path，但我认为我对它的作用和工作原理的基本理解还不够。

工作流程如下:

• (在 Google Cloud SQL 上创建 postgres 服务器)
• (启动并运行代理)
• (连接到 pgAdmin)
• (在 pgAdmin 中创建一个名为“mydb”的数据库，相当于“CREATE DATABASE mydb”)
• (在 pgAdmin 中打开查询工具)

SQL:

CREATE SCHEMA extensions;
CREATE SCHEMA geodata;
ALTER DATABASE mydb SET search_path = geodata,extensions,public;
CREATE EXTENSION postgis SCHEMA extensions;

然后在 jupyter notebook 中:

# imports
from sqlalchemy import create_engine
import geopandas as gpd

# connect to database
dbschema = 'geodata' # note, if I change this to 'extensions' it works
engine = create_engine('postgresql+psycopg2://postgres:<password>@localhost:5432/mydb', connect_args={'options': '-csearch_path={}'.format(dbschema)})

# Open shapefile
gdf = gpd.read_file(r"C:\path\to\shapefile.shp")

# Attempt to write shapefile to postgis
gdf.to_postgis(name = 'geotable', con = engine)

这是我得到的错误:

UndefinedObject                           Traceback (most recent call last)
~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1275                 if not evt_handled:
-> 1276                     self.dialect.do_execute(
   1277                         cursor, statement, parameters, context

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
    592     def do_execute(self, cursor, statement, parameters, context=None):
--> 593         cursor.execute(statement, parameters)
    594 

UndefinedObject: type "geometry" does not exist
LINE 7:  geometry geometry(POINT,28354)
                  ^


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-6-a0dc69a1128e> in <module>
      1 from geoalchemy2 import Geometry
----> 2 gdf.to_postgis(name = 'vines', con = engine, if_exists = 'replace')

~\Anaconda3\envs\rasterio\lib\site-packages\geopandas\geodataframe.py in to_postgis(self, name, con, schema, if_exists, index, index_label, chunksize, dtype)
   1095         >>> gdf.to_postgis("my_table", engine)
   1096         """
-> 1097         geopandas.io.sql._write_postgis(
   1098             self, name, con, schema, if_exists, index, index_label, chunksize, dtype
   1099         )

~\Anaconda3\envs\rasterio\lib\site-packages\geopandas\io\sql.py in _write_postgis(gdf, name, con, schema, if_exists, index, index_label, chunksize, dtype)
    390     with con.begin() as connection:
    391 
--> 392         gdf.to_sql(
    393             name,
    394             connection,

~\Anaconda3\envs\rasterio\lib\site-packages\pandas\core\generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2651         from pandas.io import sql
   2652 
-> 2653         sql.to_sql(
   2654             self,
   2655             name,

~\Anaconda3\envs\rasterio\lib\site-packages\pandas\io\sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    510         )
    511 
--> 512     pandas_sql.to_sql(
    513         frame,
    514         name,

~\Anaconda3\envs\rasterio\lib\site-packages\pandas\io\sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1314             dtype=dtype,
   1315         )
-> 1316         table.create()
   1317         table.insert(chunksize, method=method)
   1318         if not name.isdigit() and not name.islower():

~\Anaconda3\envs\rasterio\lib\site-packages\pandas\io\sql.py in create(self)
    653                 raise ValueError(f"'{self.if_exists}' is not valid for if_exists")
    654         else:
--> 655             self._execute_create()
    656 
    657     def _execute_insert(self, conn, keys, data_iter):

~\Anaconda3\envs\rasterio\lib\site-packages\pandas\io\sql.py in _execute_create(self)
    639         # Inserting table into database, add to MetaData object
    640         self.table = self.table.tometadata(self.pd_sql.meta)
--> 641         self.table.create()
    642 
    643     def create(self):

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\sql\schema.py in create(self, bind, checkfirst)
    925         if bind is None:
    926             bind = _bind_or_error(self)
--> 927         bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
    928 
    929     def drop(self, bind=None, checkfirst=False):

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in _run_visitor(self, visitorcallable, element, **kwargs)
   1654 
   1655     def _run_visitor(self, visitorcallable, element, **kwargs):
-> 1656         visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
   1657 
   1658 

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\sql\visitors.py in traverse_single(self, obj, **kw)
    143             meth = getattr(v, "visit_%s" % obj.__visit_name__, None)
    144             if meth:
--> 145                 return meth(obj, **kw)
    146 
    147     def iterate(self, obj):

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\sql\ddl.py in visit_table(self, table, create_ok, include_foreign_key_constraints, _is_metadata_operation)
    825             include_foreign_key_constraints = None
    826 
--> 827         self.connection.execute(
    828             # fmt: off
    829             CreateTable(

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in execute(self, object_, *multiparams, **params)
   1009             )
   1010         else:
-> 1011             return meth(self, multiparams, params)
   1012 
   1013     def _execute_function(self, func, multiparams, params):

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\sql\ddl.py in _execute_on_connection(self, connection, multiparams, params)
     70 
     71     def _execute_on_connection(self, connection, multiparams, params):
---> 72         return connection._execute_ddl(self, multiparams, params)
     73 
     74     def execute(self, bind=None, target=None):

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in _execute_ddl(self, ddl, multiparams, params)
   1066             else None,
   1067         )
-> 1068         ret = self._execute_context(
   1069             dialect,
   1070             dialect.execution_ctx_cls._init_ddl,

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1314 
   1315         except BaseException as e:
-> 1316             self._handle_dbapi_exception(
   1317                 e, statement, parameters, cursor, context
   1318             )

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1508                 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   1509             elif should_wrap:
-> 1510                 util.raise_(
   1511                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   1512                 )

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\util\compat.py in raise_(***failed resolving arguments***)
    180 
    181         try:
--> 182             raise exception
    183         finally:
    184             # credit to

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1274                             break
   1275                 if not evt_handled:
-> 1276                     self.dialect.do_execute(
   1277                         cursor, statement, parameters, context
   1278                     )

~\Anaconda3\envs\rasterio\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
    591 
    592     def do_execute(self, cursor, statement, parameters, context=None):
--> 593         cursor.execute(statement, parameters)
    594 
    595     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (psycopg2.errors.UndefinedObject) type "geometry" does not exist
LINE 7:  geometry geometry(POINT,28354)
                  ^

[SQL: 
CREATE TABLE geotable (
    id BIGINT, 
    geometry geometry(POINT,28354)
)

]
(Background on this error at: http://sqlalche.me/e/13/f405)

Answer 1

问题是您在连接到 PostgreSQL 时显式设置了 search_path。这会覆盖使用 ALTER DATABASE 设置的值。

你有两个选择:

• 正确设置search_path:

  dbschema = 'geodata,extensions,public'
  

• 不在 create_engine 中设置 options 参数以保留默认设置。

既然您费心设置了一个好的默认值，我会选择第二个选项。

我个人使用 public 模式进行扩展(在撤销 PUBLIC 的 CREATE 特权之后)，但您可能会选择删除该模式完全。