EC2 Linux2上Python3.10连接PostgreSQL时出现Sqlite3模块缺失错误

问题原因 #

你的Python 3.10是手动编译安装的，编译时系统缺失sqlite-devel依赖，导致Python未内置_sqlite3模块。尽管你连接的是PostgreSQL，但pandas的to_sql在通过SQLAlchemy处理数据库连接时，会加载通用的数据库适配模块，其中包含对SQLite的引用，缺失该模块就会触发错误。

解决方案 #

1. 安装系统层面的SQLite开发依赖 #

在Amazon Linux 2终端执行：

sudo yum install sqlite-devel -y

2. 重新编译Python 3.10 #

进入你之前编译Python 3.10的源码目录，重新编译并安装：

./configure --enable-optimizations
make && sudo make install

注意：如果你的虚拟环境是基于这个Python版本创建的，需要重新创建虚拟环境，确保新编译的Python被正确引用。

3. 验证_sqlite3模块是否可用 #

激活虚拟环境后，启动Python交互模式执行：

import _sqlite3

若没有报错，说明模块已正常安装。

4. 强制重装相关依赖（可选） #

激活虚拟环境后，重新安装pandas、SQLAlchemy和psycopg2，确保依赖与新编译的Python兼容：

pip install --force-reinstall pandas sqlalchemy psycopg2-binary

推荐使用psycopg2-binary，避免手动编译psycopg2时的依赖问题。

用户提供的相关信息 #

依赖清单 #

pandas==2.1.0
sqlalchemy==2.0.20
psycopg2-binary==2.9.7

核心代码 #

import pandas as pd
from sqlalchemy import create_engine

# 读取Parquet数据
df = pd.read_parquet('data.parquet')

# 初始化PostgreSQL连接引擎
engine = create_engine('postgresql://<用户名>:<密码>@<主机>:<端口>/<数据库名>')

# 将数据写入PostgreSQL表
df.to_sql('target_table', engine, if_exists='replace', index=False)

完整报错栈 #

Traceback (most recent call last):
  File "insert_data.py", line 10, in <module>
    df.to_sql('target_table', engine, if_exists='replace', index=False)
  File "/path/to/venv/lib/python3.10/site-packages/pandas/core/generic.py", line 2987, in to_sql
    return sql.to_sql(
  File "/path/to/venv/lib/python3.10/site-packages/pandas/io/sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "/path/to/venv/lib/python3.10/site-packages/pandas/io/sql.py", line 1725, in to_sql
    table = self.prep_table(
  File "/path/to/venv/lib/python3.10/site-packages/pandas/io/sql.py", line 1638, in prep_table
    self._execute_create(table)
  File "/path/to/venv/lib/python3.10/site-packages/pandas/io/sql.py", line 1772, in _execute_create
    conn.execute(create_stmt)
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1960, in _exec_single_context
    self.dialect.do_execute(
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/dialects/sqlite/__init__.py", line 117, in <module>
    from sqlalchemy.dialects.sqlite import base, pysqlite
  File "/path/to/venv/lib/python3.10/site-packages/sqlalchemy/dialects/sqlite/pysqlite.py", line 75, in <module>
    import sqlite3 as dbapi2
  File "/usr/local/lib/python3.10/sqlite3/__init__.py", line 57, in <module>
    from sqlite3.dbapi2 import *
  File "/usr/local/lib/python3.10/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ModuleNotFoundError: No module named '_sqlite3'

内容的提问来源于stack exchange，提问作者Bec Wilson