Error initializing SQLAlchemy models with MySQL database

Question

My problem seems to be common, but I had checked these similar StackOverflow issues and still had no idea how to solve my problem... That's why I came here as last resort...

I created a MySQL database. If I didn't do any of the SQLAlchemy stuff and just checked if I connected to it, it works:

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)

OUTPUT:

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

And the create_engine() function of the SQLAlchem also works:

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

OUTPUT:

[BLANK STRING]

Process finished with exit code 0

However, very weirdly, as I just coded a little bit more (initializing the model) and do one instance adding to the model, issue arrises...:

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

ERROR:

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)

I have looked all over the Internet but I honestly don't know how to solve this issue. I see people with Flask Alchemy tutorials but I'm not planning to create a website...Please help me if you know the solution to my problem!

Thank you.

Georg Richter · Accepted Answer · 2022-10-26 05:40:43Z

1

The pymysql backend isn't able to resolve the hostname @localhost (not localhost).

That means parsing of the provided URI fails, since either username or password contain special chars and aren't encoded correctly.

Try:

# 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
...

edited Oct 26, 2022 at 5:40

answered Oct 25, 2022 at 4:00

Georg Richter

7,6442 gold badges19 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Upchanges Over a year ago

It worked! Thank you so much!! what's this thing about "escaping" correctly?

Georg Richter Over a year ago

escaped = encoded (edited answer)

Collectives™ on Stack Overflow

Error initializing SQLAlchemy models with MySQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related