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时用户名和密码规范上不要使用特殊符号 “@”,“:”,“//“