python - Python SQLAlchemy create_Engine 错误“连接超时”
问题描述
我已经阅读了很多关于此的帖子，但找不到解决方案。我可以从 Postgres 读取，但在使用 create_engine 字符串时出现连接超时错误。我知道凭据是正确的，因为我可以从 PostGres 中读取。
我的目标是直接从 Python 将数据帧（df）写入 postgres，然后继续将行附加到该表中。我在下面的示例中更改了密码和 IP 地址。不确定这是否相关，但我正在使用 Putty 中的 Tunnel 连接到远程服务器并启动 Jupyter 笔记本，然后尝试写入 Postgres DB。
我的代码
from sqlalchemy import create_engine
import psycopg2
import io
password = 'Pas@$tk$@a' #This is just an example. MY password has special characters
engine = create_engine('postgresql+psycopg2://admin:password@1.12.11.1:5432/DEV_Sach_D', connect_args={'sslmode':'require'}, echo=True).connect() # I have changed the IP here for security reasons
conn = engine.connect()
df.to_sql('py_stg_test', con=conn, if_exists='replace',index=False)
Error I get
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
3211 try:
-> 3212 return fn()
3213 except dialect.dbapi.Error as e:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in connect(self)
306 """
--> 307 return _ConnectionFairy._checkout(self)
308
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in _checkout(cls, pool, threadconns, fairy)
766 if not fairy:
--> 767 fairy = _ConnectionRecord.checkout(pool)
768
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in checkout(cls, pool)
424 def checkout(cls, pool):
--> 425 rec = pool._do_get()
426 try:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/impl.py in _do_get(self)
145 with util.safe_reraise():
--> 146 self._dec_overflow()
147 else:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
71 exc_value,
---> 72 with_traceback=exc_tb,
73 )
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
206 try:
--> 207 raise exception
208 finally:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/impl.py in _do_get(self)
142 try:
--> 143 return self._create_connection()
144 except:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in _create_connection(self)
252
--> 253 return _ConnectionRecord(self)
254
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in __init__(self, pool, connect)
367 if connect:
--> 368 self.__connect()
369 self.finalize_callback = deque()
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in __connect(self)
610 with util.safe_reraise():
--> 611 pool.logger.debug("Error on connect(): %s", e)
612 else:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
71 exc_value,
---> 72 with_traceback=exc_tb,
73 )
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
206 try:
--> 207 raise exception
208 finally:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in __connect(self)
604 self.starttime = time.time()
--> 605 connection = pool._invoke_creator(self)
606 pool.logger.debug("Created new connection %r", connection)
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/create.py in connect(connection_record)
577 return connection
--> 578 return dialect.connect(*cargs, **cparams)
579
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
583 # inherits the docstring from interfaces.Dialect.connect
--> 584 return self.dbapi.connect(*cargs, **cparams)
585
/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py in connect(dsn, connection_factory, cursor_factory, **kwargs)
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
OperationalError: could not connect to server: Connection timed out
Is the server running on host "1.12.11.1" and accepting
TCP/IP connections on port 5432?
The above exception was the direct cause of the following exception:
OperationalError Traceback (most recent call last)
<ipython-input-5-28cdb6a0c387> in <module>()
4 password = Pas@$tk$@a'
5
----> 6 engine = create_engine('postgresql+psycopg2://libertypassageadminuser@libertypassage:password@1.12.11.1:5432/Dev_Sach_D, connect_args={'sslmode':'require'}, echo=True).connect()
7 conn = engine.connect()
8 df.to_sql('py_stg_test', con=conn, if_exists='replace',index=False)
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in connect(self, close_with_result)
3164 """
3165
-> 3166 return self._connection_cls(self, close_with_result=close_with_result)
3167
3168 @util.deprecated(
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events, _allow_revalidate)
94 connection
95 if connection is not None
---> 96 else engine.raw_connection()
97 )
98
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
3243
3244 """
-> 3245 return self._wrap_pool_connect(self.pool.connect, _connection)
3246
3247
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
3214 if connection is None:
3215 Connection._handle_dbapi_exception_noconnection(
-> 3216 e, dialect, self
3217 )
3218 else:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
2068 elif should_wrap:
2069 util.raise_(
-> 2070 sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2071 )
2072 else:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
205
206 try:
--> 207 raise exception
208 finally:
209 # credit to
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
3210 dialect = self.dialect
3211 try:
-> 3212 return fn()
3213 except dialect.dbapi.Error as e:
3214 if connection is None:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in connect(self)
305
306 """
--> 307 return _ConnectionFairy._checkout(self)
308
309 def _return_conn(self, record):
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in _checkout(cls, pool, threadconns, fairy)
765 def _checkout(cls, pool, threadconns=None, fairy=None):
766 if not fairy:
--> 767 fairy = _ConnectionRecord.checkout(pool)
768
769 fairy._pool = pool
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in checkout(cls, pool)
423 @classmethod
424 def checkout(cls, pool):
--> 425 rec = pool._do_get()
426 try:
427 dbapi_connection = rec.get_connection()
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/impl.py in _do_get(self)
144 except:
145 with util.safe_reraise():
--> 146 self._dec_overflow()
147 else:
148 return self._do_get()
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
70 compat.raise_(
71 exc_value,
---> 72 with_traceback=exc_tb,
73 )
74 else:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
205
206 try:
--> 207 raise exception
208 finally:
209 # credit to
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/impl.py in _do_get(self)
141 if self._inc_overflow():
142 try:
--> 143 return self._create_connection()
144 except:
145 with util.safe_reraise():
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in _create_connection(self)
251 """Called by subclasses to create a new ConnectionRecord."""
252
--> 253 return _ConnectionRecord(self)
254
255 def _invalidate(self, connection, exception=None, _checkin=True):
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in __init__(self, pool, connect)
366 self.__pool = pool
367 if connect:
--> 368 self.__connect()
369 self.finalize_callback = deque()
370
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in __connect(self)
609 except Exception as e:
610 with util.safe_reraise():
--> 611 pool.logger.debug("Error on connect(): %s", e)
612 else:
613 # in SQLAlchemy 1.4 the first_connect event is not used by
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
70 compat.raise_(
71 exc_value,
---> 72 with_traceback=exc_tb,
73 )
74 else:
/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
205
206 try:
--> 207 raise exception
208 finally:
209 # credit to
/usr/local/lib/python3.6/dist-packages/sqlalchemy/pool/base.py in __connect(self)
603 try:
604 self.starttime = time.time()
--> 605 connection = pool._invoke_creator(self)
606 pool.logger.debug("Created new connection %r", connection)
607 self.connection = connection
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/create.py in connect(connection_record)
576 if connection is not None:
577 return connection
--> 578 return dialect.connect(*cargs, **cparams)
579
580 creator = pop_kwarg("creator", connect)
/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
582 def connect(self, *cargs, **cparams):
583 # inherits the docstring from interfaces.Dialect.connect
--> 584 return self.dbapi.connect(*cargs, **cparams)
585
586 def create_connect_args(self, url):
/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py in connect(dsn, connection_factory, cursor_factory, **kwargs)
120
121 dsn = _ext.make_dsn(dsn, **kwargs)
--> 122 conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
123 if cursor_factory is not None:
124 conn.cursor_factory = cursor_factory
OperationalError: (psycopg2.OperationalError) could not connect to server: Connection timed out
Is the server running on host "1.12.11.1" and accepting
TCP/IP connections on port 5432?
(Background on this error at: https://sqlalche.me/e/14/e3q8)
解决方案
我很傻。我在字符串中使用了错误的 IP。我没有使用 POSTGRES 数据库名称，而是使用机器本身的 IP。
感谢所有回答的人。
推荐阅读
- javascript - 完整的日历 4.0 fc-more(2+ more) popup override
- python - Django中的OnetoOne（primary_key = Tue）到ForeignKey
- testing - Spock 中 TestNG Reporter.getOutput() 的模拟
- python - TypeError：'int' 对象不可下标。我应该怎么办？
- javascript - ReactJS：更新值而不重新加载页面
- linux - 管道命令返回没有 bash&PIPESTATUS 的代码
- javascript - 如何隐藏 webpack-dev-server 日志？
- javascript - Firebase auth().signInWithEmailAndPassword 使用 try-catch async-await 未捕获错误
- c# - 用于点云中寻路的 DataStructure 算法
- mongodb - 如何将 MongoDb 地图集数据库与 Jaspersoft Studio 连接