请求排查：AttributeError: module 'sqlalchemy.dialects' 无postgresql属性问题

我正在编写一个连接PostgreSQL数据库的基础Python程序，代码如下：

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine=create_engine("postgresql+psycopg2://sidrules:password@localhost:5432/first")
db=scoped_session(sessionmaker(bind=engine))
def main():
    flights=db.execute("select origin, destination, duration from flights").fetchall()
    for flight in flights:
        print(f"from {flight.origin} to {flight.destination} in {flight.duration} min")
if __name__ == "__main__":
    main()

运行上述代码时出现了错误，重点需要关注最后一行的错误信息：

AttributeError: module 'sqlalchemy.dialects' has no attribute 'postgresql'

完整的错误回溯如下：

Traceback (most recent call last):
  File "select.py", line 6, in <module>
    engine=create_engine("postgres+psycopg2://sidrules:secret@localhost:5432/first")
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\__init__.py", line 479, in create_engine
    return strategy.create(*args, **kwargs)
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\strategies.py", line 61, in create
    entrypoint = u._get_entrypoint()
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\url.py", line 172, in _get_entrypoint
    cls = registry.load(name)
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\util\langhelpers.py", line 253, in load
    loader = self.auto_fn(name)
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\dialects\__init__.py", line 45, in _auto_fn
    module = __import__("sqlalchemy.dialects.%s" % (dialect,)).dialects
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\dialects\postgresql\__init__.py", line 8, in <module>
    from . import base
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\dialects\postgresql\base.py", line 954, in <module>
    from uuid import UUID as _python_UUID  # noqa
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\uuid.py", line 57, in <module>
    _AIX = platform.system() == 'AIX'
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\platform.py", line 891, in system
    return uname().system
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\platform.py", line 779, in uname
    node = _node()
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\platform.py", line 582, in _node
    import socket
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\socket.py", line 52, in <module>
    import os, sys, io, selectors
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\selectors.py", line 12, in <module>
    import select
  File "D:\HTML\SQL\select.py", line 6, in <module>
    engine=create_engine("postgres+psycopg2://sidrules:sidhhant@localhost:5432/first")
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\__init__.py", line 479, in create_engine
    return strategy.create(*args, **kwargs)
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\strategies.py", line 61, in create
    entrypoint = u._get_entrypoint()
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\url.py", line 172, in _get_entrypoint
    cls = registry.load(name)
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\util\langhelpers.py", line 253, in load
    loader = self.auto_fn(name)
  File "C:\Users\Home\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\dialects\__init__.py", line 49, in _auto_fn
    module = getattr(module, dialect)
AttributeError: module 'sqlalchemy.dialects' has no attribute 'postgresql'

问题根源分析 #

从错误回溯里能看到关键的一环：当Python尝试导入标准库的select模块时，它加载的是你当前目录下的select.py（也就是你自己写的脚本），而不是系统标准库中的select模块。这种命名冲突导致了模块加载的循环依赖，最终使得SQLAlchemy无法正确加载PostgreSQL的方言模块，抛出了AttributeError。

解决方案 #

1. 重命名你的脚本文件
   把你的脚本从select.py改成其他不会和标准库模块重名的名字，比如flight_query.py。这是最关键的一步，直接解决命名冲突问题。

2. 清理缓存文件
   删除当前目录下的__pycache__文件夹（如果存在的话），避免之前的缓存文件影响程序运行。

3. 确认依赖安装正确
   确保你已经安装了SQLAlchemy和PostgreSQL的适配驱动，运行以下命令重新安装（如果需要的话）：

   pip install --upgrade sqlalchemy psycopg2-binary
   

   （psycopg2-binary是预编译版本，安装更简单，适合快速开发；如果是生产环境可以考虑安装标准的psycopg2）

4. 检查数据库连接URL
   确保你的连接URL格式正确，SQLAlchemy中PostgreSQL的URL格式是：

   postgresql+psycopg2://username:password@host:port/dbname
   

   你代码里写的postgresql+psycopg2://...是正确的，错误信息里的postgres+psycopg2://...其实也能被识别，但建议保持格式统一。

验证步骤 #

完成以上操作后，重新运行你的脚本，应该就能正常连接数据库并查询航班数据了。

内容的提问来源于stack exchange，提问作者Siddhant Gupta