1

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!

Asim
  • 1,430
  • 1
  • 22
  • 43

2 Answers2

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%40xasas12yz@db.rds.amazonaws.com/ai_db_dev
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
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"
Corralien
  • 109,409
  • 8
  • 28
  • 52