Proper connection string to pass to sqlalchemy create_engine() for mysql AWS RDS

Question

I don't know (and can't find examples) of a connection string to pass to sqlalchemy's create_engine() that allows me to connect to a mysql database on Amazon Web Service RDS. The documentation is straightforward, but I'm tripping up. Documentation States:

mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

let's say :

• my user is: hellothere
• password is: Comp<'/"/>{}]:["licated
• host is: sandbox.cbaspyqqzfab.us-west-2.rds.amazonaws.com
• port is: 3306
• dbname is: nameofdatabase

what's wrong with this?

    conn_str = 'mysql+mysqldb://<hellothere>:<Comp<'/"/>{}]:["licated>@<sandbox.cbaspyqqzfab.us-west-2.rds.amazonaws.com>[:<3306>]/<nameofdatabase>'
    engine = sqlalchemy.create_engine(conn_str, echo=True)

Error message

Traceback (most recent call last): File
 "C:/GitHub/blah/data/__init__.py", line 16, in <module> main() File
 "C:/GitHub/blah/data/__init__.py", line 12, in main init_db() File
 "C:/GitHub/blah/data/__init__.py", line 8, in init_db
 DbSessionFactory.global_init(db_file=dbfilepath) File
 "C:\GitHub\blah\data\dbsession.py", line 25, in global_init engine =
 sqlalchemy.create_engine(conn_str, echo=True) File
 "C:\Python\Anaconda3\envs\CE\lib\site-packages\sqlalchemy\engine\__init__.py",
 line 387, in create_engine return strategy.create(*args, **kwargs)
 File
 "C:\Python\Anaconda3\envs\CE\lib\site-packages\sqlalchemy\engine\strategies.py",
 line 80, in create dbapi = dialect_cls.dbapi(**dbapi_args) File
 "C:\Python\Anaconda3\envs\CE\lib\site-packages\sqlalchemy\dialects\mysql\mysqldb.py",
 line 110, in dbapi return __import__('MySQLdb') ModuleNotFoundError:
 No module named 'MySQLdb'

It works fine if I specify a local file and use sqlite so I think the rest of the code is fine.

    conn_str = 'sqlite:///' + 'dbfile.sqlite'

I've tried it quite a few different ways, with and without the <>, but I get the same error. I don't know if I need to escape the special characters in the password or if the problem is more fundamental and I'm connecting to RDS improperly. I connected using the RMySQL package in R and it works so I don't think my problem is network or firewall related. All the examples I see online substitute the documentation above for the connection string because (obviously) they don't want to expose their credentials, so it's hard for me to find something to mimic. Any ideas?

Answer 1

it worked after installed mysql-connector
pip install mysql-connector