Python Connect to AWS Aurora Serverless MySQL Using SQLAlchemy

Question

Is there a way to specify my connection with SQLAlchemy to an AWS RDS Aurora Serverless MySQL database instance without a Secrets Manager ARN? I have the database username, password, endpoint, ARN, etc., and ideally I would initialize an engine, then use df.to_sql() to load a DataFrame into a table on the Aurora instance.

...
else:
   engine = create_engine([WHAT DO I SPECIFY HERE?])
   with engine.connect() as conn:
      df.to_sql([CODE TO APPEND TO EXISTING TABLE HERE])...

Did you create a secret in aws secrets manager that contains the database username, password, etc? — committedandroider, Oct 21 '19 at 01:17

score 4 · Accepted Answer · answered Oct 21 '19 at 01:20

4

From Alchemy documentation - https://docs.sqlalchemy.org/en/13/dialects/mysql.html, this is what the connect string should look like - the parameter in create_engine - for MySQL

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

answered Oct 21 '19 at 01:20

committedandroider

8,711
14
71
126

1

Thanks for your reply -- in the context of an AWS RDS Aurora Serverless MySQL instance, how would this look? = database instance username, = database instance password, = database instance endpoint?, what are options? Why do I need pymysql in the prefix? – OJT Oct 22 '19 at 21:12
I think you need pymysql because sqlalchemy is a Python SQL toolkit and pymysql is the python mysql client – committedandroider Oct 22 '19 at 22:13

score 3 · Answer 2 · answered Nov 20 '19 at 19:54

I don't know if you can connect to Aurora without a secrets ARN, but if you are willing to use one, you could install a driver like this: https://github.com/koxudaxi/py-data-api

Which would allow you to do something like the following:

def example_driver_for_sqlalchemy():
    from sqlalchemy.engine import create_engine
    engine = create_engine(
        'mysql+pydataapi://',
        connect_args={
            'resource_arn': 'arn:aws:rds:us-east-1:123456789012:cluster:dummy',
            'secret_arn': 'arn:aws:secretsmanager:us-east-1:123456789012:secret:dummy',
            'database': 'test'
        }
    )
    result: ResultProxy = engine.execute("select * from pets")
    print(result.fetchall())

Python Connect to AWS Aurora Serverless MySQL Using SQLAlchemy

2 Answers2