How to read a table from MySQL work bench in colab

[Ruhul786]

I have referred various articles in stackoverflow and external sources but somehow unable to get answer for this. I want to read a table from MySQL workbench database into a dataframe in colab.

1st Method

In this method, first line of code is successfully executed. Note: I have hidden database, table and password name for security reasons.

Source -

USER = 'root'
PASSWORD = 'PASSWORD'
DATABASE = 'DATABASE'
TABLE = 'TABLE'

connection_string = f'mysql+pymysql://root:PASSWORD/DATABASE' 

engine = sqlalchemy.create_engine(connection_string) 

I am getting error for second line of code. Is it because my password ends with @786 or some other reasons.

query = f"SELECT * FROM DATABASE.TABLE"
import pandas as pd
df = pd.read_sql_query(query, engine)  

OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '786@3306' ([Errno -2] Name or service not known)") (Background on this error at: https://sqlalche.me/e/14/e3q8)

2nd Method

In this method, first line of code is successfully executed. Note: I have hidden database, table and password name for security reasons.

connection_string = 'mysql+pymysql://root:PASSWORD@3306/DATABASE'
connect_args = {'ssl': {'ca': '/content/rds-ca-2015-root.pem'}}

db = create_engine(connection_string, connect_args=connect_args)

I am getting error for second line of code.

query = """SELECT * FROM DATABASE.TABLE"""

events_df = pd.read_sql(query, con=db) 

FileNotFoundError: [Errno 2] No such file or directory

My Queries: 1) Why I am getting error for 2nd line of code in both methods? 2) Is there any workaround or alternative approach / codes where I can successfully connect colab with MySQL database and read table?

[welcome[bot]]

Thank you for opening your first issue in this project! Engagement like this is essential for open source projects! :hugs:
If you haven't done so already, check out Jupyter's Code of Conduct. Also, please try to follow the issue template as it helps other other community members to contribute more effectively. You can meet the other Jovyans by joining our Discourse forum. There is also an intro thread there where you can stop by and say Hi! :wave:
Welcome to the Jupyter community! :tada:

[consideRatio]

Hi @Ruhul786!

Please help us organize discussion by posting this on the http://discourse.jupyter.org/ forum. If it's your first time posting please read https://discourse.jupyter.org/t/getting-good-answers-to-your-questions/1825.

The more information you provide the more likely we can help you. Our goal is to sustain a positive experience for both users and developers. We use GitHub issues for specific discussions related to changing a repository's content, and let the forum be where we can more generally help and inspire each other.

Thanks you for being an active member of our community! :heart: