SQLAlchemy PyMySQL Error, Can't Query Data From MySQL

Question

I try to connect my jupyter notebook to MySQL with SQLAlchemy, I think the connection is work but I can't read data from my database to dataframe.

from sqlalchemy import create_engine
import mysql.connector as sql
import pymysql

db_connection_str = 'mysql+pymysql://user:password@localhost/my_db'
db_connection = create_engine(db_connection_str)
db_connection

import pandas as pd
query = ('SELECT * FROM my_table')
df = pd.read_sql(query,con=db_connection)
df

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

How I can solve this error

Answer 1

This is a connectivity issue, looks like when the URL gets parsed, something goes wrong as SQLAlchemy thinks word@localhost is the server.

You could try to use a manual URL:

from sqlalchemy.engine import URL

url = URL(
    drivername="mysql+pymysql",
    host="localhost",
    username="user",
    password="password",
    database="my_db",
)

Or to validate how your current url parses into a URL with make_url.

from sqlalchemy.engine import make_url

make_url('mysql+pymysql://user:password@localhost/my_db')