0

I'm on an EC2 instance Centos7. Using Python 3.9.10. Virtualenv activated with following libraries sqlalchemy, pandas, pymysql installed.

So this works fine:

import os
import pymysql
dw = {
    "host": os.environ.get("DW_HOST"),
    "database": os.environ.get("DW_DATABASE"),
    "user": os.environ.get("DW_USER"),
    "password": os.environ.get("DW_PASS"),
}
conn = pymysql.connect(**dw)
with conn.cursor() as cur:
    cur.execute("SELECT * FROM table LIMIT 10")
    data = cur.fetchall()
    for row in data:
        print(row)

This doesn't and I don't know why (works locally):

import sqlalchemy
import pandas as pd
import os
dw = {
    "host": os.environ.get("DW_HOST"),
    "database": os.environ.get("DW_DATABASE"),
    "user": os.environ.get("DW_USER"),
    "password": os.environ.get("DW_PASS"),
}
engine = sqlalchemy.create_engine(f'mysql+pymysql://{dw["user"]}:{dw["password"]}@{dw["host"]}/{dw["database"]}')
df = pd.read_sql("SELECT * FROM table LIMIT 10", engine)
df

Getting error:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'xyz@cluster-abc.region.rds.amazonaws.com' ([Errno -2] Name or service not known)")
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Also tried:

conn_string = f'mysql+pymysql://{dw["user"]}:{dw["password"]}@{dw["host"]}/{dw["database"]}'
df = pd.read_sql("SELECT * FROM table LIMIT 10", conn_string)

Also tried:

  • Adding port number 3306
  • Adding .connect() method onto engine
AK91
  • 671
  • 2
  • 13
  • 35
  • Using bog standard `pymysql.connect` object instead of `sqlalchemy` engine. That works fine for `read_sql` – AK91 Jun 22 '22 at 08:37
  • Is the actual host name `cluster-abc.region.rds.amazonaws.com` (without the `xyz@` bit at the beginning)? – Gord Thompson Jun 22 '22 at 10:09
  • 2
    Possibly related: https://stackoverflow.com/q/1423804/2144390 – Gord Thompson Jun 22 '22 at 10:23
  • @GordThompson no i replaced the actual one with that – AK91 Jun 22 '22 at 12:49
  • Did the host name in the error message really contain an `@` symbol? If so, that's probably your issue. (I'd bet that the password contains an `@`, too.) – Gord Thompson Jun 22 '22 at 13:13
  • 1
    @GordThompson absolute legend. It was a`%` actually. I used `quote_plus` from the related question by importing `from urllib.parse import quote_plus as urlquote` and it worked. Thanks – AK91 Jun 22 '22 at 14:03

1 Answers1

0

Try to pass port number explicitly.

mlader
  • 43
  • 6