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:root@127.0.0.1/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?

It might be missing the MySQL client libraries. The python libraries afaik are wrappers around the C implementation. — danblack, Feb 08 '19 at 01:45

score 4 · Accepted Answer · answered Feb 08 '19 at 12:38

4

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:root@127.0.0.1/scraper?charset=utf8')

Then use:

pip install mysql-connector --target my/dir

Create Zip and again upload to AWS Lambda.

answered Feb 08 '19 at 12:38

I Bajwa PHD

1,708
1
20
42

I normaly instatiate ‘db = SQLAlchemy()’ and then use db.session, any idea on how would that work in this case? – lowercase00 May 05 '19 at 16:43
1

Should this be included as a `layer`? or the function code itself? – Jinghui Niu May 08 '20 at 09:31

How to run SQLAlchemy on AWS Lambda in Python

1 Answers1

Linked