• 카테고리

    질문 & 답변
  • 세부 분야

    데이터 분석

  • 해결 여부

    미해결

pandas 연계시 오류.....

23.12.21 15:21 작성 조회수 436

0

(아나콘다를 깔고나서 새 파일에서 뭘 수정하려고하면 바로 연결이 끊기는 문제가 생겨 주피터 노트북으로 설치하였습니다. 필요한 라이브러리들은 모두 따로 설치하였습니다.)

버전은 아래와 같습니다.

오류가 발생하는 부분은 이 부분인데

query문이 있는 셀의 전체 오류문은 아래와 같습니다

---------------------------------------------------------------------------
UnicodeDecodeError                        Traceback (most recent call last)
Cell In[12], 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 ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\sql.py:485, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
    482     dtype_backend = "numpy"  # type: ignore[assignment]
    483 assert dtype_backend is not lib.no_default
--> 485 with pandasSQL_builder(con) as pandas_sql:
    486     return pandas_sql.read_query(
    487         sql,
    488         index_col=index_col,
   (...)
    494         dtype_backend=dtype_backend,
    495     )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\sql.py:851, in pandasSQL_builder(con, schema, need_transaction)
    848     raise ImportError("Using URI string without sqlalchemy installed.")
    850 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 851     return SQLDatabase(con, schema, need_transaction)
    853 warnings.warn(
    854     "pandas only supports SQLAlchemy connectable (engine/connection) or "
    855     "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
   (...)
    858     stacklevel=find_stack_level(),
    859 )
    860 return SQLiteDatabase(con)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\io\sql.py:1576, in SQLDatabase.__init__(self, con, schema, need_transaction)
   1574     self.exit_stack.callback(con.dispose)
   1575 if isinstance(con, Engine):
-> 1576     con = self.exit_stack.enter_context(con.connect())
   1577 if need_transaction and not con.in_transaction():
   1578     self.exit_stack.enter_context(con.begin())

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py:3268, in Engine.connect(self)
   3245 def connect(self) -> Connection:
   3246     """Return a new :class:`_engine.Connection` object.
   3247 
   3248     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3265 
   3266     """
-> 3268     return self._connection_cls(self)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py:145, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    143 if connection is None:
    144     try:
--> 145         self._dbapi_connection = engine.raw_connection()
    146     except dialect.loaded_dbapi.Error as err:
    147         Connection._handle_dbapi_exception_noconnection(
    148             err, dialect, engine
    149         )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py:3292, in Engine.raw_connection(self)
   3270 def raw_connection(self) -> PoolProxiedConnection:
   3271     """Return a "raw" DBAPI connection from the connection pool.
   3272 
   3273     The returned object is a proxied version of the DBAPI
   (...)
   3290 
   3291     """
-> 3292     return self.pool.connect()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:452, in Pool.connect(self)
    444 def connect(self) -> PoolProxiedConnection:
    445     """Return a DBAPI connection from the pool.
    446 
    447     The connection is instrumented such that when its
   (...)
    450 
    451     """
--> 452     return _ConnectionFairy._checkout(self)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1261 @classmethod
   1262 def _checkout(
   1263     cls,
   (...)
   1266     fairy: Optional[_ConnectionFairy] = None,
   1267 ) -> _ConnectionFairy:
   1268     if not fairy:
-> 1269         fairy = _ConnectionRecord.checkout(pool)
   1271         if threadconns is not None:
   1272             threadconns.current = weakref.ref(fairy)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:716, in _ConnectionRecord.checkout(cls, pool)
    714     rec = cast(_ConnectionRecord, pool._do_get())
    715 else:
--> 716     rec = pool._do_get()
    718 try:
    719     dbapi_connection = rec.get_connection()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\impl.py:169, in QueuePool._do_get(self)
    167     return self._create_connection()
    168 except:
--> 169     with util.safe_reraise():
    170         self._dec_overflow()
    171     raise

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\util\langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
    144     assert exc_value is not None
    145     self._exc_info = None  # remove potential circular references
--> 146     raise exc_value.with_traceback(exc_tb)
    147 else:
    148     self._exc_info = None  # remove potential circular references

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\impl.py:167, in QueuePool._do_get(self)
    165 if self._inc_overflow():
    166     try:
--> 167         return self._create_connection()
    168     except:
    169         with util.safe_reraise():

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:393, in Pool._create_connection(self)
    390 def _create_connection(self) -> ConnectionPoolEntry:
    391     """Called by subclasses to create a new ConnectionRecord."""
--> 393     return _ConnectionRecord(self)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:678, in _ConnectionRecord.__init__(self, pool, connect)
    676 self.__pool = pool
    677 if connect:
--> 678     self.__connect()
    679 self.finalize_callback = deque()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:902, in _ConnectionRecord.__connect(self)
    900     self.fresh = True
    901 except BaseException as e:
--> 902     with util.safe_reraise():
    903         pool.logger.debug("Error on connect(): %s", e)
    904 else:
    905     # in SQLAlchemy 1.4 the first_connect event is not used by
    906     # the engine, so this will usually not be set

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\util\langhelpers.py:146, in safe_reraise.__exit__(self, type_, value, traceback)
    144     assert exc_value is not None
    145     self._exc_info = None  # remove potential circular references
--> 146     raise exc_value.with_traceback(exc_tb)
    147 else:
    148     self._exc_info = None  # remove potential circular references

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\pool\base.py:898, in _ConnectionRecord.__connect(self)
    896 try:
    897     self.starttime = time.time()
--> 898     self.dbapi_connection = connection = pool._invoke_creator(self)
    899     pool.logger.debug("Created new connection %r", connection)
    900     self.fresh = True

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\create.py:637, in create_engine.<locals>.connect(connection_record)
    634         if connection is not None:
    635             return connection
--> 637 return dialect.connect(*cargs, **cparams)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\sqlalchemy\engine\default.py:616, in DefaultDialect.connect(self, *cargs, **cparams)
    614 def connect(self, *cargs, **cparams):
    615     # inherits the docstring from interfaces.Dialect.connect
--> 616     return self.loaded_dbapi.connect(*cargs, **cparams)

File ~\AppData\Local\Programs\Python\Python312\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

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb8 in position 63: invalid start byte

 

답변 3

·

답변을 작성해보세요.

0

sangseo seo님의 프로필

sangseo seo

2024.05.11

저도 동일한 현상을 겪어서 아래와 같이 내용을 약간 수정해서 사용하고 있습니다.

판다스와 sqlalchemy 버전을 2.xxx대로 맞춰도 연결 오류가 납니다. db의 characterset 로 utf-8인데요.

그래서도 강의를 계속 들어야 하니.

문제 없습니다. 단, 불편한 것은 필요 시마다 컬럼 인자의 값들을 바꿔줘야 한다는 것만 제외하곤..

 

image

0

안녕하십니까,

오, 인프런 AI 인턴이 열일하고 있었군요.

제 생각엔 db의 기본 character set가 utf-8이어야 Pandas 하고 호환이 되는것 같습니다만..

일단 dbeaver에서 아래 SQL 을 수행하셔서 postgres db의 character set encoding이 무엇인지 확인 부탁 드립니다.

SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database a;

 

감사합니다.

0

인프런 AI 인턴님의 프로필

인프런 AI 인턴

2023.12.22

안녕하세요, 인프런 AI 인턴입니다.

프로그래밍 질문 주셔서 감사드리며, pandas 연계시 발생하는 UnicodeDecodeError 문제를 해결하기 위한 유사한 기존 답변을 찾아봤습니다. 아래 링크를 확인해보시면 도움이 될 것 같습니다.

이 링크들을 참조하여 psycopg2 모듈 설치 및 관련 오류 해결 방법에 대해 학습하시면 좋을 것 같습니다.