python 连接postgreSql 报错，使用的是sqlalchemy

G:\anaconda3\envs\scrapy\python.exe E:/project/python/ocr_project/ocr_model/db/PostgreSQLDB.py
Traceback (most recent call last):
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 3288, in raw_connection
    return self.pool.connect()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 1267, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 716, in checkout
    rec = pool._do_get()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\impl.py", line 170, in _do_get
    self._dec_overflow()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\impl.py", line 167, in _do_get
    return self._create_connection()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 678, in __init__
    self.__connect()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 903, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\default.py", line 615, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError

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

Traceback (most recent call last):
  File "E:\project\python\ocr_project\ocr_model\db\PostgreSQLDB.py", line 37, in <module>
    connection = engine.connect()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 3264, in connect
    return self._connection_cls(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 2426, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\base.py", line 3288, in raw_connection
    return self.pool.connect()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 1267, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 716, in checkout
    rec = pool._do_get()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\impl.py", line 170, in _do_get
    self._dec_overflow()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\impl.py", line 167, in _do_get
    return self._create_connection()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 678, in __init__
    self.__connect()
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 903, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\pool\base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\sqlalchemy\engine\default.py", line 615, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "G:\anaconda3\envs\scrapy\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) 
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Process finished with exit code 1

上面是错误信息，网络上找了半天也没有针对这个问题的解决方案，因为关于连接没有那么多弯弯绕绕的功能。这是一个很愚蠢的问题。

上原代码，下面是测试连接的源代码：

from sqlalchemy import create_engine, text

db_scheme = "postgresql+psycopg2"
user = "postgres"
password = "postgres@postgres"
host = "152.11.11.112"
port = "5432"
db = "face"

dns = "{db_scheme}://{user}:{password}@{host}:{port}/{db}".format(
    db_scheme=db_scheme,
    user=user,
    password=password,
    host=host,
    port=port,
    db=db
)

# 创建 engine
engine = create_engine(
    url=dns,
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1,  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    echo=True,  # 如果为真，引擎将记录所有语句以及 repr() 其参数列表的默认日志处理程序。
)

if __name__ == '__main__':
    connection = engine.connect()
    result = connection.execute(text('SELECT * FROM media_video'))
    print(result.fetchone())

提示的错误信息很明确，就是网络连接或者账号密码有问题。首先排除网络连接，因为我直接使用psycopg2是可以连接的。使用sqlalchemy就报错了

网上也没有解决方案。

但是一直怀疑是密码规范导致的

可以看到  dns中 是字符串拼接，用户名和密码是":"隔开，用户信息和ip端口是"@"符号隔开，恰巧我的密码中也包含了@符号，索性就修改密码为123456试试。

然后问题就解决了。

时间问题，没有去尝试 密码中包含 "//"或者 ":" 这些特殊符号会不会导致连接失败。

总结：

使用 sqlalchemy 连接 postgreSql时用户名和密码规范上不要使用特殊符号 “@”，“:”，“//“