0
$\begingroup$

I have referred to various articles on Stack Overflow and external sources but somehow am unable to get answer for this. I would like to read a table from MySQL workbench database into a dataframe in colab.

$1^{st}$ 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)  

Error:

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)

$2^{nd}$ Method:

In this method, the 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) 

Error:

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?

$\endgroup$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.