使用pandas.read_sql_query连接PostgreSQL时遇编码及连接错误

操作步骤 #

创建PostgreSQL数据库 #

CREATE DATABASE "new_db"
WITH 
OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

创建数据表 #

CREATE TABLE public.rounds
(
    rounds_id bigint NOT NULL,
    rounds_bot_date date,
    rounds_bot_time time without time zone,
    PRIMARY KEY (rounds_id)
)

Python中创建SQLAlchemy引擎 #

db_config = {'user': 'postgres',
             'pwd': '****',
             'host': 'localhost',
             'port': 5432,
             'db': 'new_db'}

connection_string = f"postgresql://{db_config['user']}:{db_config['pwd']}@{db_config['host']}:{db_config['port']}/{db_config['db']}"
engine = create_engine(connection_string)

报错情况 #

执行查询与追加数据时的编码错误 #

执行pd.read_sql_query查询，以及尝试向表中追加数据时，均出现如下错误：

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xc2 in position 61: invalid continuation byte

怀疑是数据库编码或连接字符串存在问题。

更换驱动后的连接错误 #

• 使用postgresql+pg8000驱动时，报错：

ProgrammingError: (pg8000.dbapi.ProgrammingError) {'S': '�����', 'V': 'FATAL', 'C': '28P01',

• 使用postgresql+psycopg驱动时，报错：

OperationalError: (psycopg.OperationalError) connection failed: ������������ "postgres" ��

添加SSL上下文后的错误 #

尝试添加SSL上下文连接：

import ssl
ssl_context = ssl.create_default_context()
engine = create_engine(
    connection_string,
    connect_args={"ssl_context": ssl_context},
)

得到错误：

InterfaceError: (pg8000.exceptions.InterfaceError) Server refuses SSL

补充信息 #

• 数据库仅包含英文内容
• 相同查询操作在DBeaver中可正常执行

环境版本 #

• Python 3.11.7
• Pandas 2.2.1
• SQLAlchemy 2.0.25
• PostgreSQL 16.2（64位，Visual C++编译）

内容的提问来源于stack exchange，提问作者John Doe