sqlalchemy.exc.OperationalError: (mariadb.OperationalError) Unknown server host '@ip_address'

ip address가 맞음에도 다음과 같이 unknown server host 에러가 발생한다.

원인: ip주소를 읽을때 앞 패스워드 입력시 reserved word가 있어서 인터프리터 입장에서 ip주소를 오해함에서 문제가 발생

해결 방법: 

password = urllib.parse.quote_plus(f"{dbinfo['MARIADB_PWD']}")

 

다음과 같이 password자체를 connection string에 포함하지 말고 구문분석해주는 urllib라이브러리를 이용하여 password를 connection string에 포함한다.

아래는 예제코드

 

def dbcon():
    with open("dbinfo.json") as fp:
        dbinfo = json.loads(fp.read())
    #     dbconnector://(user):(password)@(host):(port)/(db)
    # need to use urllib lib because password include reserved character like @
    password = urllib.parse.quote_plus(f"{dbinfo['MARIADB_PWD']}")
    snow_str = f"snowflake://{dbinfo['SF_USER']}:{dbinfo['SF_PWD']}@{dbinfo['SF_ACCOUNT']}/{dbinfo['SF_DB']}/{dbinfo['SF_SCHEMA']}?warehouse={dbinfo['SF_WH']}"
    mysql_str = f"mysql+pymysql://{dbinfo['MYSQL_USER']}:{dbinfo['MYSQL_PWD']}'@'{dbinfo['MYSQL_HOST']}/{dbinfo['MYSQL_DB']}?charset=utf8mb4"
    maria_str = f"mariadb+mariadbconnector://{dbinfo['MARIADB_USER']}:{password}@{dbinfo['MARIADB_HOST']}:{dbinfo['MARIADB_PORT']}/{dbinfo['MARIADB_DB']}"
    postgre_str = f"postgresql://{dbinfo['POSTGRE_USER']}:{dbinfo['POSTGRE_PWD']}@{dbinfo['POSTGRE_HOST']}/{dbinfo['POSTGRE_DB']}"
    mssql_str = f"mssql+pymssql://{dbinfo['MSSQL_USER']}:{dbinfo['MSSQL_PWD']}@{dbinfo['MSSQL_HOST']}:{dbinfo['MSSQL_PORT']}/{dbinfo['MSSQL_DB']}"
    print(maria_str)
    connection = create_engine(maria_str)
    connection.execute("create table tbl(col1 int, col2 varchar(8));")
    connection.execute("insert into tbl values(1, 'test');")
    result = connection.execute("select * from tbl;").fetchone()
    print(result)
    connection.execute("drop table tbl;")

 

 

 

실행결과
mariadb+mariadbconnector://user:password%21%40@127.0.0.1:3306/dbname
(1, 'test')

 

references.

https://stackoverflow.com/questions/58661569/password-with-cant-connect-the-database

Password with @ can't connect the database

https://docs.sqlalchemy.org/en/14/core/engines.html

Engine Configuration — SQLAlchemy 1.4 Documentation