인프런 커뮤니티 질문&답변

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3280, in Engine._wrap_pool_connect(self, fn, connection)
   3279 try:
-> 3280     return fn()
   3281 except dialect.dbapi.Error as e:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:310, in Pool.connect(self)
    303 """Return a DBAPI connection from the pool.
    304 
    305 The connection is instrumented such that when its
   (...)
    308 
    309 """
--> 310 return _ConnectionFairy._checkout(self)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:868, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
    867 if not fairy:
--> 868     fairy = _ConnectionRecord.checkout(pool)
    870     fairy._pool = pool

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:476, in _ConnectionRecord.checkout(cls, pool)
    474 @classmethod
    475 def checkout(cls, pool):
--> 476     rec = pool._do_get()
    477     try:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\impl.py:145, in QueuePool._do_get(self)
    144 except:
--> 145     with util.safe_reraise():
    146         self._dec_overflow()

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\compat.py:208, in raise_(***failed resolving arguments***)
    207 try:
--> 208     raise exception
    209 finally:
    210     # credit to
    211     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    212     # as the __traceback__ object creates a cycle

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\impl.py:143, in QueuePool._do_get(self)
    142 try:
--> 143     return self._create_connection()
    144 except:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:256, in Pool._create_connection(self)
    254 """Called by subclasses to create a new ConnectionRecord."""
--> 256 return _ConnectionRecord(self)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:371, in _ConnectionRecord.__init__(self, pool, connect)
    370 if connect:
--> 371     self.__connect()
    372 self.finalize_callback = deque()

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:665, in _ConnectionRecord.__connect(self)
    664 except Exception as e:
--> 665     with util.safe_reraise():
    666         pool.logger.debug("Error on connect(): %s", e)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\compat.py:208, in raise_(***failed resolving arguments***)
    207 try:
--> 208     raise exception
    209 finally:
    210     # credit to
    211     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    212     # as the __traceback__ object creates a cycle

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:661, in _ConnectionRecord.__connect(self)
    660 self.starttime = time.time()
--> 661 self.dbapi_connection = connection = pool._invoke_creator(self)
    662 pool.logger.debug("Created new connection %r", connection)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\create.py:590, in create_engine.<locals>.connect(connection_record)
    589             return connection
--> 590 return dialect.connect(*cargs, **cparams)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\default.py:597, in DefaultDialect.connect(self, *cargs, **cparams)
    595 def connect(self, *cargs, **cparams):
    596     # inherits the docstring from interfaces.Dialect.connect
--> 597     return self.dbapi.connect(*cargs, **cparams)

File C:\ProgramData\anaconda3\Lib\site-packages\psycopg2\__init__.py:122, 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: 

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

OperationalError                          Traceback (most recent call last)
Cell In[62], line 4
      1 query = """
      2 select * from nw.customers
      3 """
----> 4 df = pd.read_sql_query(sql=query, con=postgres_engine)
      5 df.head(10)

File C:\ProgramData\anaconda3\Lib\site-packages\pandas\io\sql.py:397, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)
    339 """
    340 Read SQL query into a DataFrame.
    341 
   (...)
    394 parameter will be converted to UTC.
    395 """
    396 pandas_sql = pandasSQL_builder(con)
--> 397 return pandas_sql.read_query(
    398     sql,
    399     index_col=index_col,
    400     params=params,
    401     coerce_float=coerce_float,
    402     parse_dates=parse_dates,
    403     chunksize=chunksize,
    404     dtype=dtype,
    405 )

File C:\ProgramData\anaconda3\Lib\site-packages\pandas\io\sql.py:1560, in SQLDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)
   1512 """
   1513 Read SQL query into a DataFrame.
   1514 
   (...)
   1556 
   1557 """
   1558 args = _convert_params(sql, params)
-> 1560 result = self.execute(*args)
   1561 columns = result.keys()
   1563 if chunksize is not None:

File C:\ProgramData\anaconda3\Lib\site-packages\pandas\io\sql.py:1405, in SQLDatabase.execute(self, *args, **kwargs)
   1403 def execute(self, *args, **kwargs):
   1404     """Simple passthrough to SQLAlchemy connectable"""
-> 1405     return self.connectable.execution_options().execute(*args, **kwargs)

File <string>:2, in execute(self, statement, *multiparams, **params)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\deprecations.py:402, in _decorate_with_warning.<locals>.warned(fn, *args, **kwargs)
    400 if not skip_warning:
    401     _warn_with_version(message, version, wtype, stacklevel=3)
--> 402 return fn(*args, **kwargs)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3175, in Engine.execute(self, statement, *multiparams, **params)
   3149 @util.deprecated_20(
   3150     ":meth:`_engine.Engine.execute`",
   3151     alternative="All statement execution in SQLAlchemy 2.0 is performed "
   (...)
   3156 )
   3157 def execute(self, statement, *multiparams, **params):
   3158     """Executes the given construct and returns a
   3159     :class:`_engine.CursorResult`.
   3160 
   (...)
   3173 
   3174     """
-> 3175     connection = self.connect(close_with_result=True)
   3176     return connection.execute(statement, *multiparams, **params)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3234, in Engine.connect(self, close_with_result)
   3219 def connect(self, close_with_result=False):
   3220     """Return a new :class:`_engine.Connection` object.
   3221 
   3222     The :class:`_engine.Connection` object is a facade that uses a DBAPI
   (...)
   3231 
   3232     """
-> 3234     return self._connection_cls(self, close_with_result=close_with_result)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:96, in Connection.__init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events, _allow_revalidate)
     91     self._has_events = _branch_from._has_events
     92 else:
     93     self._dbapi_connection = (
     94         connection
     95         if connection is not None
---> 96         else engine.raw_connection()
     97     )
     99     self._transaction = self._nested_transaction = None
    100     self.__savepoint_seq = 0

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3313, in Engine.raw_connection(self, _connection)
   3291 def raw_connection(self, _connection=None):
   3292     """Return a "raw" DBAPI connection from the connection pool.
   3293 
   3294     The returned object is a proxied version of the DBAPI
   (...)
   3311 
   3312     """
-> 3313     return self._wrap_pool_connect(self.pool.connect, _connection)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3283, in Engine._wrap_pool_connect(self, fn, connection)
   3281 except dialect.dbapi.Error as e:
   3282     if connection is None:
-> 3283         Connection._handle_dbapi_exception_noconnection(
   3284             e, dialect, self
   3285         )
   3286     else:
   3287         util.raise_(
   3288             sys.exc_info()[1], with_traceback=sys.exc_info()[2]
   3289         )

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:2117, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine)
   2115     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2116 elif should_wrap:
-> 2117     util.raise_(
   2118         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2119     )
   2120 else:
   2121     util.raise_(exc_info[1], with_traceback=exc_info[2])

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\compat.py:208, in raise_(***failed resolving arguments***)
    205     exception.__cause__ = replace_context
    207 try:
--> 208     raise exception
    209 finally:
    210     # credit to
    211     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    212     # as the __traceback__ object creates a cycle
    213     del exception, replace_context, from_, with_traceback

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3280, in Engine._wrap_pool_connect(self, fn, connection)
   3278 dialect = self.dialect
   3279 try:
-> 3280     return fn()
   3281 except dialect.dbapi.Error as e:
   3282     if connection is None:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:310, in Pool.connect(self)
    302 def connect(self):
    303     """Return a DBAPI connection from the pool.
    304 
    305     The connection is instrumented such that when its
   (...)
    308 
    309     """
--> 310     return _ConnectionFairy._checkout(self)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:868, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
    865 @classmethod
    866 def _checkout(cls, pool, threadconns=None, fairy=None):
    867     if not fairy:
--> 868         fairy = _ConnectionRecord.checkout(pool)
    870         fairy._pool = pool
    871         fairy._counter = 0

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:476, in _ConnectionRecord.checkout(cls, pool)
    474 @classmethod
    475 def checkout(cls, pool):
--> 476     rec = pool._do_get()
    477     try:
    478         dbapi_connection = rec.get_connection()

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\impl.py:145, in QueuePool._do_get(self)
    143         return self._create_connection()
    144     except:
--> 145         with util.safe_reraise():
    146             self._dec_overflow()
    147 else:

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     68     self._exc_info = None  # remove potential circular references
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:
     75     if not compat.py3k and self._exc_info and self._exc_info[1]:
     76         # emulate Py3K's behavior of telling us when an exception
     77         # occurs in an exception handler.

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\compat.py:208, in raise_(***failed resolving arguments***)
    205     exception.__cause__ = replace_context
    207 try:
--> 208     raise exception
    209 finally:
    210     # credit to
    211     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    212     # as the __traceback__ object creates a cycle
    213     del exception, replace_context, from_, with_traceback

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\impl.py:143, in QueuePool._do_get(self)
    141 if self._inc_overflow():
    142     try:
--> 143         return self._create_connection()
    144     except:
    145         with util.safe_reraise():

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:256, in Pool._create_connection(self)
    253 def _create_connection(self):
    254     """Called by subclasses to create a new ConnectionRecord."""
--> 256     return _ConnectionRecord(self)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:371, in _ConnectionRecord.__init__(self, pool, connect)
    369 self.__pool = pool
    370 if connect:
--> 371     self.__connect()
    372 self.finalize_callback = deque()

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:665, in _ConnectionRecord.__connect(self)
    663     self.fresh = True
    664 except Exception as e:
--> 665     with util.safe_reraise():
    666         pool.logger.debug("Error on connect(): %s", e)
    667 else:
    668     # in SQLAlchemy 1.4 the first_connect event is not used by
    669     # the engine, so this will usually not be set

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     68     self._exc_info = None  # remove potential circular references
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:
     75     if not compat.py3k and self._exc_info and self._exc_info[1]:
     76         # emulate Py3K's behavior of telling us when an exception
     77         # occurs in an exception handler.

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\util\compat.py:208, in raise_(***failed resolving arguments***)
    205     exception.__cause__ = replace_context
    207 try:
--> 208     raise exception
    209 finally:
    210     # credit to
    211     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    212     # as the __traceback__ object creates a cycle
    213     del exception, replace_context, from_, with_traceback

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:661, in _ConnectionRecord.__connect(self)
    659 try:
    660     self.starttime = time.time()
--> 661     self.dbapi_connection = connection = pool._invoke_creator(self)
    662     pool.logger.debug("Created new connection %r", connection)
    663     self.fresh = True

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\create.py:590, in create_engine.<locals>.connect(connection_record)
    588         if connection is not None:
    589             return connection
--> 590 return dialect.connect(*cargs, **cparams)

File C:\ProgramData\anaconda3\Lib\site-packages\sqlalchemy\engine\default.py:597, in DefaultDialect.connect(self, *cargs, **cparams)
    595 def connect(self, *cargs, **cparams):
    596     # inherits the docstring from interfaces.Dialect.connect
--> 597     return self.dbapi.connect(*cargs, **cparams)

File C:\ProgramData\anaconda3\Lib\site-packages\psycopg2\__init__.py:122, in connect(dsn, connection_factory, cursor_factory, **kwargs)
    119     kwasync['async_'] = kwargs.pop('async_')
    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) 
(Background on this error at: https://sqlalche.me/e/14/e3q8)

안녕하세요, 오류 텍스트 다음과 같습니다! 감사합니다

섹션 1의 <공지>sqlalchemy 버전 업그레이드에 따른 실습 환경 변경 영상을 참조해서 pandas와 sql alchemy 버전을 변경했는데도 여전히 오류가 발생해서 아래 캡처를 올리신건지요?

만약 그렇다면 어느 셀에서 발생한 오류인지 셀 내용과 오류를 적어 주십시요.

가령, 아래 셀 인지...

conn_string =....

postgres_engine = create_engine(conn_string)

추가적으로 pandas 버전과 sqlalchemy 버전이 어떤 건지도 부탁드립니다.

네, 해당 강의 확인했습니다. 현재 pandas, sqlalchemy 버전은 아래와 같습니다.

pandas 1.5.3

sqlalchemy 1.4.39

conn_string, postgres_engine 셀에서는 문제가 발생하지 않고, 그 아래 [SQL을 호출하여 결과를 pandas의 DataFrame으로 로딩] 과정의 아래 셀에서 위와 같은 문제가 발생합니다.

query = """

select * from nw.customers

"""

df = pd.read_sql_query(sql=query, con=postgres_engine)

df.head(10)

postgresql 이 떠있는지 확인 부탁드리며(이미 하신것 같지만) 아래와 같이 pyscopg2 가 import 되었는지 확인 부탁드립니다.

import pandas as pd

from sqlalchemy import create_engine

import psycopg2

그리고 섹션 1의 <공지>sqlalchemy 버전 업그레이드에 따른 실습 환경 변경 영상대로 pandas와 sqlalchemy를 2.대로 version up 해주신 뒤에 다시 수행 부탁드립니다.

말씀해주신대로 이행했고, sqlalchemy, pandas 모두 2대로 버전업도 완료했습니다. 그래도 동일한 오류가 발생하며 실행이 되지 않습니다..

오류로 봐서는 db connection을 생성하지 못하는것 같은데, 원격이다보니, 제가 환경에 어떤 문제가 있는지 오류를 잡아내는데 한계가 있군요.

PC를 재기동 해보시고

1. PostgreSQL 이 제대로 5432 port에 떠있는지 확인해 보시고

2. 주피터 노트북을 기동한 후 해당 커널에서 pandas와 sqlalchemy가 앞에서 말씀드린 대로 버전업 되어 있는지 확인해 보시고, 다시 한번 수행해 보시기 바랍니다.

그래도 안되면 저도 더 이상 방법이 없을 것 같습니다. 좀 더 환경을 테스트 해보시고 여전히 안된다면 강의를 더 이상 듣지 마시고, 환불을 받으셔야 할 것 같습니다.

만약 강의 자료를 다운로드 받아서 환불을 받으실 수 없다면, 다시 저한테 말씀해주십시요. 제가 인프런에 전달해서 환불 절차를 받도록 하겠습니다.

저도 버전을 동일하게 맞췄는데 커넥션 오류가 나서 위와 같이 꼼수를 써서 강의 수강하고있습니다.문제없이 잘 활용하고 있습니다.

createEngine을 안쓰고 ,,,,,직접 psycog2를 이요해서 커넥션을 맺고, 쿼리 실행한 결과를 바로 데이터 프레임으로 리턴 가능하도록 컬럼정보만 그때 그때 바꿔서 인자로 던져주면 가능합니다.