Can't connect to mysql DB with Python Pandas due to "@" in the password [duplicate]

Question

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!

What if you url encode your password like what was suggested here ? — Martin Tovmassian
– Martin Tovmassian, Commented Mar 8, 2022 at 16:21

Gord Thompson · Accepted Answer · 2022-03-08 20:35:43Z

2

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

answered Mar 8, 2022 at 20:35

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Corralien · Accepted Answer · 2022-03-08 20:02:28Z

1

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%40xasas12yz'@db.rds.amazonaws.com/ai_db_dev"

edited Mar 8, 2022 at 20:02

answered Mar 8, 2022 at 16:25

Corralien

121k8 gold badges44 silver badges69 bronze badges

3 Comments

Asim Over a year ago

Thanks for the help but I am getting the error "Access denied for user". Maybe the quotes changed the password or something?

Corralien Over a year ago

I updated my answer. Try to use quote from urllib.parse.

Asim Over a year ago

thanks! it also solved the issue.

Collectives™ on Stack Overflow

Can't connect to mysql DB with Python Pandas due to "@" in the password [duplicate]

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related