使用Geopandas+Psycopg/SQLAlchemy读写PostgreSQL数据库遇错求助

1. to_postgis 报 ValueError: Unknown Connectable #

geopandas的to_postgis目前不直接兼容psycopg3的Connection对象，它只支持SQLAlchemy的Engine/Connection或psycopg2的原生连接实例。

2. SQLAlchemy连接被拒绝 #

• 参数拼写错误：你代码里写的db_users是笔误，实际变量名应为db_user，会导致用户名验证失败。
• Dialect指定错误：如果用的是psycopg3，drivername要设为postgresql+psycopg，而非默认适配psycopg2的postgresql。
• 冗余参数：query=None完全可以省略，没必要传入。

3. pandas to_sql 报SQLite相关查询错误 #

当传入原生DBAPI连接（比如psycopg3的Connection）时，pandas无法自动识别数据库类型，默认按SQLite处理，因此执行了SQLite的系统表查询，和PostgreSQL不兼容。

正确实现代码 #

方式一：SQLAlchemy + psycopg3（推荐） #

先确保依赖安装完整：

pip install sqlalchemy psycopg pandas geopandas

代码示例：

import geopandas as gpd
from sqlalchemy import create_engine, URL

# 你的服务器凭证
db = "db"
host_db = "url_to_server"
db_port = 5432
db_user = "username"
db_password = "pwd"

# 构造适配psycopg3的SQLAlchemy连接URL
params = {
    "drivername": "postgresql+psycopg",
    "host": host_db,
    "port": db_port,
    "database": db,
    "username": db_user,
    "password": db_password
}
url = URL.create(**params)
engine = create_engine(url)

# 读取PostGIS数据
with engine.connect() as con:
    sql = 'select * from schema.table1'
    gdf1 = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom')
    gdf2 = gdf1.to_crs(3857)

# 写入GeoDataFrame到PostGIS
gdf2.to_postgis(
    name="your_target_table",  # 替换成你要创建的表名
    con=engine,
    schema="public",
    if_exists="replace"  # 根据需求选：fail/replace/append
)

# 写入普通DataFrame
df1 = gdf2[['id','area']]
df1.to_sql(
    name="your_df_table",
    con=engine,
    schema="public",
    if_exists="replace",
    index=False  # 避免把pandas索引写入数据库
)

方式二：psycopg2原生连接（备选） #

如果偏好原生连接，先安装psycopg2：

pip install psycopg2-binary pandas geopandas

代码示例：

import geopandas as gpd
import psycopg2
from sqlalchemy import create_engine

# 服务器凭证
db = "db"
host_db = "url_to_server"
db_port = 5432
db_user = "username"
db_password = "pwd"

# 用psycopg2读取数据
con = psycopg2.connect(
    dbname=db,
    user=db_user,
    password=db_password,
    host=host_db,
    port=db_port
)

sql = 'select * from schema.table1'
gdf1 = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom')
gdf2 = gdf1.to_crs(3857)

# 写入时转用SQLAlchemy引擎（to_postgis兼容）
engine = create_engine(f"postgresql+psycopg2://{db_user}:{db_password}@{host_db}:{db_port}/{db}")
gdf2.to_postgis("your_target_table", con=engine, schema="public", if_exists="replace")

# 关闭原生连接
con.close()

关键注意点 #

• 依赖版本要匹配：psycopg3对应postgresql+psycopg dialect，psycopg2对应postgresql+psycopg2。
• 仔细检查变量名拼写，避免笔误导致的验证失败。
• 写入时务必指定if_exists参数，防止表已存在的报错。
• 普通DataFrame写入建议加index=False，避免冗余索引列写入数据库。

内容的提问来源于stack exchange，提问作者Michelle Li