So I am trying to use Neon as my db, for reading and updating a simple table. It was working wonders on local, but I am struggling to make it work on cloud. I’ve tried downgrading psycopg2 to 2.9.6, which made also to downgrade to python 3.11 but the error is the same. According to copilot and the sqlalchemy info, it is because the connection needs channel binding. Neon uses SCRAM-SHA-256 and it may work with MD5.
Note that since I need to update, I am making changes via using the Session directly with SQLAlchemy.
Relevant info:
https://steam-review.streamlit.app/
The error:
────────────────────── Traceback (most recent call last) ───────────────────────
/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/scriptru
nner/exec_code.py:128 in exec_func_with_error_handling
/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/scriptru
nner/script_runner.py:669 in code_to_exec
/mount/src/steam-reviews-ai/main.py:300 in <module>
297 │ with col_analysis:
298 │ │ if st.button("Generate Review Analysis"):
299 │ │ │ appid = df[df["name"]==appname].index[0]
❱ 300 │ │ │ content_raw = manage_summary_by_appid(str(appid), total_re
301 │ │ │ content = json.loads(content_raw)
302 │ │ │ content = trim_factors(content, summary['total_positive']/
303 │ │ │ with col_banner:
/mount/src/steam-reviews-ai/main.py:37 in manage_summary_by_appid
34 def manage_summary_by_appid(target_appid: str, total_reviews: int):
35 │ conn = st.connection("neon", type="sql")
36 │ session = conn.session
❱ 37 │ result = session.get(Summary, target_appid)
38 │ json_summary = None
39 │ if result is not None:
40 │ │ if check_fresh_summary(result, total_reviews):
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:
3694 in get
3691 │ │ :return: The object instance, or ``None``.
3692 │ │
3693 │ │ """ # noqa: E501
❱ 3694 │ │ return self._get_impl(
3695 │ │ │ entity,
3696 │ │ │ ident,
3697 │ │ │ loading.load_on_pk_identity,
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:
3873 in _get_impl
3870 │ │
3871 │ │ if options:
3872 │ │ │ statement = statement.options(*options)
❱ 3873 │ │ return db_load_fn(
3874 │ │ │ self,
3875 │ │ │ statement,
3876 │ │ │ primary_key_identity,
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/loading.py:
694 in load_on_pk_identity
691 │ │ execution_options, {"_sa_orm_load_options": load_options}
692 │ )
693 │ result = (
❱ 694 │ │ session.execute(
695 │ │ │ q,
696 │ │ │ params=params,
697 │ │ │ execution_options=execution_options,
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:
2365 in execute
2362 │ │
2363 │ │
2364 │ │ """
❱ 2365 │ │ return self._execute_internal(
2366 │ │ │ statement,
2367 │ │ │ params,
2368 │ │ │ execution_options=execution_options,
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:
2241 in _execute_internal
2238 │ │
2239 │ │ bind = self.get_bind(**bind_arguments)
2240 │ │
❱ 2241 │ │ conn = self._connection_for_bind(bind)
2242 │ │
2243 │ │ if _scalar_result and not compile_state_cls:
2244 │ │ │ if TYPE_CHECKING:
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:
2110 in _connection_for_bind
2107 │ │ trans = self._transaction
2108 │ │ if trans is None:
2109 │ │ │ trans = self._autobegin_t()
❱ 2110 │ │ return trans._connection_for_bind(engine, execution_options)
2111 │
2112 │ @overload
2113 │ def _execute_internal(
in _connection_for_bind:2
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/state_chang
es.py:139 in _go
136 │ │ │ self._current_fn = fn
137 │ │ │ self._next_state = _StateChangeStates.CHANGE_IN_PROGRESS
138 │ │ │ try:
❱ 139 │ │ │ │ ret_value = fn(self, *arg, **kw)
140 │ │ │ except:
141 │ │ │ │ raise
142 │ │ │ else:
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py:
1189 in _connection_for_bind
1186 │ │ │ │ │ │ │ "for the given Connection's Engine"
1187 │ │ │ │ │ │ )
1188 │ │ │ │ else:
❱ 1189 │ │ │ │ │ conn = bind.connect()
1190 │ │ │ │ │ local_connect = True
1191 │ │ │
1192 │ │ │ try:
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:
3273 in connect
3270 │ │
3271 │ │ """
3272 │ │
❱ 3273 │ │ return self._connection_cls(self)
3274 │
3275 │ def raw_connection(self) -> PoolProxiedConnection:
3276 │ │ """Return a "raw" DBAPI connection from the connection pool.
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:
147 in __init__
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 │ │ │ │ )
150 │ │ │ │ raise
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:
2436 in _handle_dbapi_exception_noconnection
2433 │ │ │ raise newraise.with_traceback(exc_info[2]) from e
2434 │ │ elif should_wrap:
2435 │ │ │ assert sqlalchemy_exception is not None
❱ 2436 │ │ │ raise sqlalchemy_exception.with_traceback(exc_info[2]) fr
2437 │ │ else:
2438 │ │ │ assert exc_info[1] is not None
2439 │ │ │ raise exc_info[1].with_traceback(exc_info[2])
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:
145 in __init__
142 │ │
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
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:
3297 in raw_connection
3294 │ │ │ :ref:`dbapi_connections`
3295 │ │
3296 │ │ """
❱ 3297 │ │ return self.pool.connect()
3298
3299
3300 class OptionEngineMixin(log.Identified):
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:44
9 in connect
446 │ │ the pool.
447 │ │
448 │ │ """
❱ 449 │ │ return _ConnectionFairy._checkout(self)
450 │
451 │ def _return_conn(self, record: ConnectionPoolEntry) -> None:
452 │ │ """Given a _ConnectionRecord, return it to the :class:`_pool.
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:12
64 in _checkout
1261 │ │ fairy: Optional[_ConnectionFairy] = None,
1262 │ ) -> _ConnectionFairy:
1263 │ │ if not fairy:
❱ 1264 │ │ │ fairy = _ConnectionRecord.checkout(pool)
1265 │ │ │
1266 │ │ │ if threadconns is not None:
1267 │ │ │ │ threadconns.current = weakref.ref(fairy)
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:71
3 in checkout
710 │ │ if TYPE_CHECKING:
711 │ │ │ rec = cast(_ConnectionRecord, pool._do_get())
712 │ │ else:
❱ 713 │ │ │ rec = pool._do_get()
714 │ │
715 │ │ try:
716 │ │ │ dbapi_connection = rec.get_connection()
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py:17
9 in _do_get
176 │ │ │ try:
177 │ │ │ │ return self._create_connection()
178 │ │ │ except:
❱ 179 │ │ │ │ with util.safe_reraise():
180 │ │ │ │ │ self._dec_overflow()
181 │ │ │ │ raise
182 │ │ else:
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/util/langhelper
s.py:224 in __exit__
221 │ │ │ exc_type, exc_value, exc_tb = self._exc_info
222 │ │ │ assert exc_value is not None
223 │ │ │ self._exc_info = None # remove potential circular refere
❱ 224 │ │ │ raise exc_value.with_traceback(exc_tb)
225 │ │ else:
226 │ │ │ self._exc_info = None # remove potential circular refere
227 │ │ │ assert value is not None
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py:17
7 in _do_get
174 │ │
175 │ │ if self._inc_overflow():
176 │ │ │ try:
❱ 177 │ │ │ │ return self._create_connection()
178 │ │ │ except:
179 │ │ │ │ with util.safe_reraise():
180 │ │ │ │ │ self._dec_overflow()
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:39
0 in _create_connection
387 │ def _create_connection(self) -> ConnectionPoolEntry:
388 │ │ """Called by subclasses to create a new ConnectionRecord."""
389 │ │
❱ 390 │ │ return _ConnectionRecord(self)
391 │
392 │ def _invalidate(
393 │ │ self,
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:67
5 in __init__
672 │ │
673 │ │ self.__pool = pool
674 │ │ if connect:
❱ 675 │ │ │ self.__connect()
676 │ │ self.finalize_callback = deque()
677 │
678 │ dbapi_connection: Optional[DBAPIConnection]
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:90
1 in __connect
898 │ │ │ pool.logger.debug("Created new connection %r", connection
899 │ │ │ self.fresh = True
900 │ │ except BaseException as e:
❱ 901 │ │ │ with util.safe_reraise():
902 │ │ │ │ pool.logger.debug("Error on connect(): %s", e)
903 │ │ else:
904 │ │ │ # in SQLAlchemy 1.4 the first_connect event is not used b
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/util/langhelper
s.py:224 in __exit__
221 │ │ │ exc_type, exc_value, exc_tb = self._exc_info
222 │ │ │ assert exc_value is not None
223 │ │ │ self._exc_info = None # remove potential circular refere
❱ 224 │ │ │ raise exc_value.with_traceback(exc_tb)
225 │ │ else:
226 │ │ │ self._exc_info = None # remove potential circular refere
227 │ │ │ assert value is not None
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py:89
7 in __connect
894 │ │ self.dbapi_connection = None
895 │ │ try:
896 │ │ │ self.starttime = time.time()
❱ 897 │ │ │ self.dbapi_connection = connection = pool._invoke_creator
898 │ │ │ pool.logger.debug("Created new connection %r", connection
899 │ │ │ self.fresh = True
900 │ │ except BaseException as e:
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/create.p
y:646 in connect
643 │ │ │ │ │ if connection is not None:
644 │ │ │ │ │ │ return connection
645 │ │ │
❱ 646 │ │ │ return dialect.connect(*cargs, **cparams)
647 │ │
648 │ │ creator = pop_kwarg("creator", connect)
649
/home/adminuser/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.
py:625 in connect
622 │
623 │ def connect(self, *cargs: Any, **cparams: Any) -> DBAPIConnection
624 │ │ # inherits the docstring from interfaces.Dialect.connect
❱ 625 │ │ return self.loaded_dbapi.connect(*cargs, **cparams) # type:
626 │
627 │ def create_connect_args(self, url: URL) -> ConnectArgsType:
628 │ │ # inherits the docstring from interfaces.Dialect.create_conne
/home/adminuser/venv/lib/python3.11/site-packages/psycopg2/__init__.py:122
in connect
119 │ │ kwasync['async_'] = kwargs.pop('async_')
120 │
121 │ dsn = _ext.make_dsn(dsn, **kwargs)
❱ 122 │ conn = _connect(dsn, connection_factory=connection_factory, **kwas
123 │ if cursor_factory is not None:
124 │ │ conn.cursor_factory = cursor_factory
125
────────────────────────────────────────────────────────────────────────────────
OperationalError: (psycopg2.OperationalError) channel binding required but not
supported by server's authentication request
(Background on this error at: https://sqlalche.me/e/20/e3q8)