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: Error Messages — SQLAlchemy 1.4 Documentation)
**
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?