SQLAlchemy初始化MySQL模型时出现连接失败错误求助

一、正常连接情况 #

1. 使用mysql.connector可正常连接 #

from mysql.connector import connect, Error
import getpass
try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass.getpass("Enter password: "),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

输出：

<mysql.connector.connection_cext.CMySQLConnection object at 0x1026a3790>

2. SQLAlchemy create_engine可正常执行 #

mysql_user_name = getpass.getpass(prompt="Enter mysql username:")
mysql_password = getpass.getpass(prompt="Enter mysql password:")
ssl_key = getpass.getpass(prompt="Enter ssl key:")

database_string = f"mysql+pymysql://{mysql_user_name}:{mysql_password}@localhost/database"


engine = create_engine(
    database_string,
    connect_args={"ssl": {"key": ssl_key}},
    echo=True,
)

输出：

[BLANK STRING]
Process finished with exit code 0

二、报错场景及错误信息 #

执行模型初始化和添加实例代码时出错：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, Float, Date, and_, or_
import getpass

Base = declarative_base()
mysql_user_name = getpass.getpass(prompt="Enter mysql username:")
mysql_password = getpass.getpass(prompt="Enter mysql password:")
ssl_key = getpass.getpass(prompt="Enter ssl key:")

database_string = f"mysql+pymysql://{mysql_user_name}:{mysql_password}@localhost/database"


engine = create_engine(
    database_string,
    connect_args={"ssl": {"key": ssl_key}},
    echo=True,
)


class Database(Base):
    __tablename__ = "database"
    id = Column(Integer, primary_key=True, autoincrement=True)
    #y values:
    original_product_released_date = Column(Date)

    #x values:
    product_name = Column(String)

    def __init__(self, original_product_released_date, product_name):
        self.original_product_released_date = original_product_released_date
        self.product_name = product_name

Base.metadata.create_all(engine)

session = sessionmaker()
session.configure(bind=engine)
db = session()
product1=Database(product_name="SHFIronman",original_product_released_date='2021-08-05')
db.add(product1)
db.commit()
db.close()

错误信息：

Traceback (most recent call last):
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/pymysql/connections.py", line 613, in connect
    sock = socket.create_connection(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/socket.py", line 824, in create_connection
    for res in getaddrinfo(host, port, 0, SOCK_STREAM):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/socket.py", line 955, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno 8] nodename nor servname provided, or not known

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 320, in connect
    return _ConnectionFairy._checkout(self)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 884, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 486, in checkout
    rec = pool._do_get()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 266, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 381, in __init__
    self.__connect()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 677, in __connect
    with util.safe_reraise():
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 673, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '@localhost' ([Errno 8] nodename nor servname provided, or not known)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/kienletrung/Desktop/AI Project/legend_AI/database.py", line 52, in <module>
    Base.metadata.create_all(engine)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4917, in create_all
    bind._run_ddl_visitor(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3227, in _run_ddl_visitor
    with self.begin() as conn:
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3143, in begin
    conn = self.connect(close_with_result=close_with_result)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3315, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
    return fn()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 320, in connect
    return _ConnectionFairy._checkout(self)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 884, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 486, in checkout
    rec = pool._do_get()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
    with util.safe_reraise():
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 266, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 381, in __init__
    self.__connect()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 677, in __connect
    with util.safe_reraise():
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 673, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 598, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/Users/kienletrung/Desktop/AI Project/venv/interpreter/lib/python3.10/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '@localhost' ([Errno 8] nodename nor servname provided, or not known)")
(Background on this error at: https://sqlalche.me/e/14/e3q8)

三、解决方案 #

1. 编码用户名和密码 #

错误提示中出现@localhost，大概率是用户名或密码包含特殊字符（如@、:）导致URL解析错误，需用urllib.parse.quote_plus编码：

from urllib.parse import quote_plus

mysql_user_name = getpass.getpass(prompt="Enter mysql username:")
mysql_password = getpass.getpass(prompt="Enter mysql password:")
encoded_user = quote_plus(mysql_user_name)
encoded_pass = quote_plus(mysql_password)

database_string = f"mysql+pymysql://{encoded_user}:{encoded_pass}@localhost/database"

2. 替换localhost为127.0.0.1 #

部分环境下localhost解析异常，直接使用IP地址规避：

database_string = f"mysql+pymysql://{encoded_user}:{encoded_pass}@127.0.0.1/database"

3. 调整SSL配置 #

如果本地MySQL未启用SSL，删除connect_args中的SSL参数：

engine = create_engine(
    database_string,
    echo=True,
)

若确实需要SSL，确保ssl_key路径正确，或补充完整SSL参数：

connect_args={"ssl": {"key": ssl_key, "cert": ssl_cert, "ca": ssl_ca}}

4. 确认目标数据库存在 #

URL中的database是目标库名，需提前在MySQL中创建：

# 用mysql.connector连接后创建数据库
with connect(
    host="localhost",
    user=mysql_user_name,
    password=mysql_password,
) as connection:
    create_db_query = "CREATE DATABASE IF NOT EXISTS database;"
    with connection.cursor() as cursor:
        cursor.execute(create_db_query)

内容的提问来源于stack exchange，提问作者Upchanges