버전을 맞추었는데도 오류가 발생합니다. (pd ver: 2.0.3, sqlalchemy: 2.0.0)

2024-02-23T01:43:41.940Z

0

text 까지 import 후에 text를 입혀도 비슷하게 나옵니다.

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

File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:468, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype, dtype_backend)
    465 if dtype_backend is lib.no_default:
    466     dtype_backend = "numpy"  # type: ignore[assignment]
--> 468 with pandasSQL_builder(con) as pandas_sql:
    469     return pandas_sql.read_query(
    470         sql,
    471         index_col=index_col,
   (...)
    477         dtype_backend=dtype_backend,
    478     )

File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:832, in pandasSQL_builder(con, schema, need_transaction)
    829     raise ImportError("Using URI string without sqlalchemy installed.")
    831 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
--> 832     return SQLDatabase(con, schema, need_transaction)
    834 warnings.warn(
    835     "pandas only supports SQLAlchemy connectable (engine/connection) or "
    836     "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
   (...)
    839     stacklevel=find_stack_level(),
    840 )
    841 return SQLiteDatabase(con)

File ~\anaconda3\Lib\site-packages\pandas\io\sql.py:1539, in SQLDatabase.__init__(self, con, schema, need_transaction)
   1537     self.exit_stack.callback(con.dispose)
   1538 if isinstance(con, Engine):
-> 1539     con = self.exit_stack.enter_context(con.connect())
   1540 if need_transaction and not con.in_transaction():
   1541     self.exit_stack.enter_context(con.begin())

File ~\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3245, in Engine.connect(self)
   3222 def connect(self) -> Connection:
   3223     """Return a new :class:`_engine.Connection` object.
   3224 
   3225     The :class:`_engine.Connection` acts as a Python context manager, so
   (...)
   3242 
   3243     """
-> 3245     return self._connection_cls(self)

File ~\anaconda3\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 ~\anaconda3\Lib\site-packages\sqlalchemy\engine\base.py:3269, in Engine.raw_connection(self)
   3247 def raw_connection(self) -> PoolProxiedConnection:
   3248     """Return a "raw" DBAPI connection from the connection pool.
   3249 
   3250     The returned object is a proxied version of the DBAPI
   (...)
   3267 
   3268     """
-> 3269     return self.pool.connect()

File ~\anaconda3\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 ~\anaconda3\Lib\site-packages\sqlalchemy\pool\base.py:1255, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1247 @classmethod
   1248 def _checkout(
   1249     cls,
   (...)
   1252     fairy: Optional[_ConnectionFairy] = None,
   1253 ) -> _ConnectionFairy:
   1254     if not fairy:
-> 1255         fairy = _ConnectionRecord.checkout(pool)
   1257         if threadconns is not None:
   1258             threadconns.current = weakref.ref(fairy)

File ~\anaconda3\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 ~\anaconda3\Lib\site-packages\sqlalchemy\pool\impl.py:168, in QueuePool._do_get(self)
    166     return self._create_connection()
    167 except:
--> 168     with util.safe_reraise():
    169         self._dec_overflow()
    170     raise

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

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

File ~\anaconda3\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 ~\anaconda3\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 ~\anaconda3\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 ~\anaconda3\Lib\site-packages\sqlalchemy\util\langhelpers.py:147, in safe_reraise.__exit__(self, type_, value, traceback)
    145     assert exc_value is not None
    146     self._exc_info = None  # remove potential circular references
--> 147     raise exc_value.with_traceback(exc_tb)
    148 else:
    149     self._exc_info = None  # remove potential circular references

File ~\anaconda3\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 ~\anaconda3\Lib\site-packages\sqlalchemy\engine\create.py:640, in create_engine.<locals>.connect(connection_record)
    638         if connection is not None:
    639             return connection
--> 640 return dialect.connect(*cargs, **cparams)

File ~\anaconda3\Lib\site-packages\sqlalchemy\engine\default.py:580, in DefaultDialect.connect(self, *cargs, **cparams)
    578 def connect(self, *cargs, **cparams):
    579     # inherits the docstring from interfaces.Dialect.connect
--> 580     return self.loaded_dbapi.connect(*cargs, **cparams)

File ~\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

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

0

쿼리 실행 전에 아래 수행하셔서 db connection이 제대로 되는지 확인 부탁드립니다.

connection = postgres_engine.connect()

print(type(connection))

0

혹시 기존 sqlalchemy와 pandas 버전은 어떻게 되나요? 필요하면 다운 그레이드 해볼 예정입니다.

이거 이후 제가 찾으면서 한 시도입니다.

1. 로컬로 utf-8 설정

import locale

locale.setlocale(locale.LC_ALL, 'en_US.UTF-8')

2. encoding=utf8 및 , connect_args={'options': '-c client_encoding=utf8'} 추가

conn_string = "postgresql://postgres:password@localhost:5432/postgres?client_encoding=utf8"

postgres_engine = create_engine(conn_string, connect_args={'options': '-c client_encoding=utf8'})

0

connection = postgres_engine.connect()

print(type(connection))

여기에서도 UnicodeDecodeError가 나옵니다

0

음. 이게 connection 접속 부터 문제 인것 같군요.

sqlalchemy engine 만드는 맨 처음 코드도 여기에 올려봐 주시겠습니까? 패스워드는 xxx 처리해 주시고요.

0

동일한 문제로 고민하다가 이렇게 꼼수를 써서 하고 있습니다.

환불을 하기엔 너무 많은 시간이 지나서 그리고 오기가 생겨서..

db에서 접속하기 위한 라이브러리는 임포트 하고 함수를 하나 만들어 호출하도록 하고, 컬럼 정보는 그때 필요에 따라 변수로 받아서 처리하도록 하고 하니까 엔진을 사용 하지 않고도 가능은 합니다. 실습 하는데 문제는 없네요. 도움 되길 바랍니다.

유