使用MySQL数据库初始化SQLAlchemy模型时出错

eiee3dmh  于 2022-10-31  发布在  Mysql
关注(0)|答案(1)|浏览(176)

“我的问题看起来很常见，但我检查过类似的堆栈溢出问题，还是不知道该怎么解决......所以我才不得已才来这里......”
我创建了一个MySQL数据库。如果我不做任何SQLAlchemy的事情，只是检查我是否连接到它，它就可以工作：

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>

SQLAlchem的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)

“我在网上找遍了，真的不知道该怎么解决这个问题。我看到有人在做《 flask 炼金》教程，但我不打算建网站......如果你知道我的问题的解决方法，请帮帮我！”

  • 谢谢-谢谢
mysql

1条答案

按热度按时间
cgh8pdjw

cgh8pdjw1#

pymysql后端无法解析主机名@localhost（而不是localhost）。
这意味着解析提供的URI失败，因为用户名或密码包含特殊字符，并且编码不正确。
请尝试：


# For older python versions try

# from urllib import quote_plus

from urllib.parse import quote_plus

mysql_username= quote_plus(mysql_username)
mysql_password= quote_plus(mysql_password)

# now we can connect

...
赞(0）分享  回复(0）举报  2022-10-31
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com