인프런 커뮤니티 질문&답변
작성한 질문수
pandas 연계시 오류.....
작성
·
794
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
저도 동일한 현상을 겪어서 아래와 같이 내용을 약간 수정해서 사용하고 있습니다.
판다스와 sqlalchemy 버전을 2.xxx대로 맞춰도 연결 오류가 납니다. db의 characterset 로 utf-8인데요.
그래서도 강의를 계속 들어야 하니.
문제 없습니다. 단, 불편한 것은 필요 시마다 컬럼 인자의 값들을 바꿔줘야 한다는 것만 제외하곤..
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 인턴입니다.
프로그래밍 질문 주셔서 감사드리며, pandas 연계시 발생하는 UnicodeDecodeError 문제를 해결하기 위한 유사한 기존 답변을 찾아봤습니다. 아래 링크를 확인해보시면 도움이 될 것 같습니다.
이 링크들을 참조하여 psycopg2 모듈 설치 및 관련 오류 해결 방법에 대해 학습하시면 좋을 것 같습니다.