Can't connect to mysql DB with Python Pandas due to "@" in the password-CodePudding

I am trying to connect pandas to MySQL DB to write to the tables. My dummy database credentials are:

db_name="ai_db_dev"
db_user="ai_db_user_dev"
db_pwd="abassc@xasas12yz"
db_host="db.rds.amazonaws.com"
db_client="mysql"

I am connecting to the db with fake SSL flag like this:

db_connection_str=db_client "://" db_user ":" db_pwd "@" db_host "/" db_name
connect_args={'ssl':{'fake_flag_to_enable_tls': True},
         'port': 3306}
db_connection = create_engine(db_connection_str,connect_args= connect_args)

and I am trying to insert the db into the table like this:

df.to_sql(con=db_connection, name='ai_table', if_exists='append', index= False)

But due to the '@' in the password, it is not picking up the host properly and hence I am unable to connect to the DB. Any help in solving this issue will be highly appreciated!

CodePudding user response：

If you enclose your username and password by simple quotes?

db_connection_str = f"{db_client}://'{db_user}':'{db_pwd}'@{db_host}/{db_name}"

>>> db_connection_str
"mysql://'ai_db_user_dev':'abassc@xasas12yz'@db.rds.amazonaws.com/ai_db_dev"

Attempt 2

from urllib.parse import quote

db_connection_str = f"{db_client}://{db_user}:{quote(db_pwd)}@{db_host}/{db_name}"

Output:

>>> db_connection_str
"mysql://'ai_db_user_dev':'abassc@xasas12yz'@db.rds.amazonaws.com/ai_db_dev"

CodePudding user response：

SQLAlchemy has a built-in URL.create() method that will build a properly-escaped URL for you:

import sqlalchemy as sa

db_name="ai_db_dev"
db_user="ai_db_user_dev"
db_pwd="abassc@xasas12yz"
db_host="db.rds.amazonaws.com"
db_client="mysql"

connection_url = sa.engine.URL.create(
    drivername=db_client,
    username=db_user,
    password=db_pwd,
    host=db_host,
    database=db_name
)
print(connection_url)
# mysql://ai_db_user_dev:abassc@[email protected]/ai_db_dev