Connecting to remote MySql instance with Google Co-lab

Question

Following the guide :

https://pythondata.com/quick-tip-sqlalchemy-for-mysql-and-pandas/

I'm attempting to connect to a remote MySql DB using Google co-lab. Here is connection code :

import pandas as pd
import sqlalchemy as sql

connect_string = 'my_connection_string'

sql_engine = sql.create_engine(connect_string)

But error is returned :

ModuleNotFoundError                       Traceback (most recent call last)

<ipython-input-2-8e070e8c6206> in <module>()
      1 connect_string = 'my_connection_string'
----> 2 sql_engine = sql.create_engine(connect_string)
      3 

2 frames

/usr/local/lib/python3.6/dist-packages/sqlalchemy/dialects/mysql/mysqldb.py in dbapi(cls)
    116     @classmethod
    117     def dbapi(cls):
--> 118         return __import__("MySQLdb")
    119 
    120     def on_connect(self):

ModuleNotFoundError: No module named 'MySQLdb'


-----------------------------------------------------

From answers suggested on No module named MySQLdb

I tried installing MySQLdb using pip install mysql-python and pip install mysqlclient

But receive error for both :

Collecting mysqlclient
  Using cached https://files.pythonhosted.org/packages/4d/38/c5f8bac9c50f3042c8f05615f84206f77f03db79781db841898fde1bb284/mysqlclient-1.4.4.tar.gz
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

and :

Collecting mysql-python
  Downloading https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip (108kB)
     |████████████████████████████████| 112kB 4.8MB/s 
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

How to connect to remote MySql DB using SQLAlchemy ?

@Poojan thanks, but I'm not trying to use Google cloud SQL. I'm trying to connect a MySql instance. — blue-sky, Oct 02 '19 at 14:39

score 4 · Answer 1 · edited Apr 27 '20 at 02:16

4

Here's what works

!pip install PyMySQL

And use connection string like "mysql+pymysql://scott:tiger@localhost/foo"

edited Apr 27 '20 at 02:16

Poojan

3,366
2
17
33

answered Oct 03 '19 at 04:31

korakot

37,818
16
123
144

score 2 · Accepted Answer · answered Oct 02 '19 at 14:56

it looks like its failing because some missing lib.
first run following command and then do pip install mysqlclient

sudo apt-get install python3-dev default-libmysqlclient-dev
pip install mysqlclient

Source: https://github.com/PyMySQL/mysqlclient-python

tested in colab with python3

score 0 · Answer 3 · answered Sep 12 '20 at 15:31

This process worked for me:

Install following two packages on Collab.

 !pip install PyMySQL
 !pip install mysql-connector-python

Initiate connection in the following format:

 from sqlalchemy import  create_engine
 engine = create_engine("mysql+pymysql://<dbuser>:<pwd>@<server_ip>/<db_instance>")

Connecting to remote MySql instance with Google Co-lab

3 Answers3