I am trying to build a streamlit application and trying to connect to sql DB.
When I try to run:
import streamlit as st

conn = st.connection("reports_db", type = "sql") 

I get:
(MySQLdb._exceptions.OperationalError) (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/anaconda3/envs/llm/lib/plugin/caching_sha2_password.so, 0x0002): tried: '/usr/local/anaconda3/envs/llm/lib/plugin/caching_sha2_password.so' (no such file)

There has been an older SO page: Authentication plugin ‘caching_sha2_password’ cannot be loaded, which helps me understand that the plugin mysql_native_password has been deprecated and removed as of 9.0.0. My current sql version is 9.1.0.

There is another solution I tried which would have added the caching_sha2_password.so file

pip install mariadb

But still getting the error.
What is the correct way to use the new plugin for the new version of mysql?