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

ljmc · Accepted Answer · 2023-01-19T10:32:13.863

0

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')

edited Jan 19 '23 at 10:32

answered Jan 17 '23 at 09:56

ljmc

4,830
2
7
26

Thank you for your answer, but I still get the same error – Jan 18 '23 at 02:04
Are there special characters in your password ? – ljmc Jan 18 '23 at 08:30
yeah, there is ! and @ – Jan 19 '23 at 01:41
@Reganda then you should look at this [answer](https://stackoverflow.com/a/1424009/17676984). – ljmc Jan 19 '23 at 10:32

SQLAlchemy PyMySQL Error, Can't Query Data From MySQL

1 Answers1