How to run SQLAlchemy on AWS Lambda in Python

Question

I preapre very simple file for connecting to external MySQL database server, like below:

from sqlalchemy import *

def run(event, context):
    sql = create_engine('mysql://root:[email protected]/scraper?charset=utf8');
    metadata = MetaData(sql)

    print(sql.execute('SHOW TABLES').fetchall())

Doesn't work on AWS, but localy on Windows works perfectly.

Next, I install by pip install sqlalchemy --target my/dir and prepare ZIP file to upload packages to AWS Lambda.

Run, but with failed message No module named 'MySQLdb': ModuleNotFoundError.

Then, I use pip install mysqlclient --target my/dir, create ZIP and again upload to AWS Lambda.

Run, but with new failed message cannot import name '_mysql': ImportError.

So, what I should doing now?

Answer 1

SQLAlchemy includes many Dialect implementations for various backends. Dialects for the most common databases are included with SQLAlchemy; a handful of others require an additional install of a separate dialect.

The MySQL dialect uses mysql-python as the default DBAPI. There are many MySQL DBAPIs available, including MySQL-connector-python and OurSQL

Instead of mysql you may use mysql+mysqlconnector

sql = create_engine('mysql+mysqlconnector://root:[email protected]/scraper?charset=utf8')

Then use:

pip install mysql-connector --target my/dir

Create Zip and again upload to AWS Lambda.