4

I am having issues with connecting Amazon AWS MySQL with SQLAlchemy. According to the instruction, I have connected.

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://<user>:<password>@<host>/<dbname>

But there is an error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/ec2-user/venv/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 514, in __get__
    return type.query_class(mapper, session=self.sa.session())
  File "/home/ec2-user/venv/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 74, in __call__
    return self.registry()
  File "/home/ec2-user/venv/lib/python3.7/site-packages/sqlalchemy/util/_collections.py", line 1001, in __call__
    return self.registry.setdefault(key, self.createfunc())
  File "/home/ec2-user/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2950, in __call__
    return self.class_(**local_kw)
  File "/home/ec2-user/venv/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 143, in __init__
    bind = options.pop('bind', None) or db.engine
  File "/home/ec2-user/venv/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 877, in engine
    return self.get_engine()
  File "/home/ec2-user/venv/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 896, in get_engine
    return connector.get_engine()
  File "/home/ec2-user/venv/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 559, in get_engine
    self._engine = rv = sqlalchemy.create_engine(info, **options)
  File "/home/ec2-user/venv/lib/python3.7/site-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine
    return strategy.create(*args, **kwargs)
  File "/home/ec2-user/venv/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 81, in create
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/home/ec2-user/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 102, in dbapi
    return __import__('MySQLdb')
ModuleNotFoundError: No module named 'MySQLdb'

I am using Python3.7 version. Even according to the stackoverflow, I have installed pymysql, but still facing the problem.

Thank you for your reply.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
3Mcollab
  • 111
  • 1
  • 4

3 Answers3

5

If you use the PyMySQL client to connect to MySQL, your SQLAlchemy connection string needs to start with

 mysql+pymysql://

instead of mysql://.

If you connect with mysql:// then you will need to install the MySQLdb library as shown in the Traceback.

Matt Healy
  • 18,033
  • 4
  • 56
  • 56
  • Solves the problem, but would someone add some explanation of what's happening? (I would ask a question; but I'm not sure yet what the question would be.) – Calaf Dec 21 '18 at 12:55
2

Step 1 If you want to use MySQLDB you need to use one of the following commands. Which one depends on what OS and software you have and use.

easy_install mysql-python (mix os)

pip install mysql-python (mix os/ python 2)

pip install mysqlclient (mix os/ python 3)

apt-get install python-mysqldb (Linux Ubuntu, ...)

cd /usr/ports/databases/py-MySQLdb && make install clean (FreeBSD)

yum install MySQL-python (Linux Fedora, CentOS ...)

For Windows, see this answer: Install mysql-python (Windows)

Step 2:

  1. Create Engine

engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)

use the create_engine.pool_recycle option which ensures that a connection will be discarded and replaced with a new one if it has been present in the pool for a fixed number of seconds:

  1. Create Connection object

conn = engine.connect()

  1. Execute SQL queries

conn.execute("SELECT * From table;")

Christos Karapapas
  • 1,018
  • 3
  • 19
  • 40
Jasir
  • 21
  • 2
2

Above issues has been solved with the following:

import pymysql

pymysql.install_as_MySQLdb()

Nothing to change anywhere.

Blue
  • 22,608
  • 7
  • 62
  • 92
3Mcollab
  • 111
  • 1
  • 4