How to specify url in sqlalchemy?

Question

I am trying to link a database table to a python class using sqlalchemy, and when creating the engine I am getting an error on the URL:

# Creating the declarative base class
Base = declarative_base()

class State(Base):
    """Class that links to the states table of the hbtn_0e_6_usa DB"""
    __tablename__ = 'states'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(128), nullable=False)

# Create the engine for the connection
Engine = create_engine(f"mysql+mysqldb://{user}:{passwd}@{host}:{port}/{db}")
Base.metadata.create_all(Engine)

I am geting an error:

Traceback (most recent call last):
...
MySQLdb.OperationalError: (2005, "Unknown MySQL server host '{part of my password}@{host}' (-2)")

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

Traceback (most recent call last):
...
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2005, "Unknown MySQL server host '{part of my password}@{host}' (-2)")
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Note: My password has a '@' character, and I think that is why this happening.

How can I solve this issue?

EDIT: I have tried to use the sqlalchemy.engine.URL.create(),but I get another error:

Engine = sqlalchemy.engine.URL.create(
drivername="mysql+mysqldb",
username="root",
password="la@1993#",
host="localhost",
port=3306,
database="hbtn_0e_6_usa"

) I get the error:

AttributeError: 'URL' object has no attribute '_run_ddl_visitor'

Use `connection_url = sqlalchemy.engine.URL.create(…)` followed by `engine = sqlalchemy.create_engine(connection_url)` — Gord Thompson, Dec 30 '22 at 21:28

score 0 · Answer 1 · answered Dec 30 '22 at 20:27

If your password contains special characters like you mentioned, you can try URL encoding the password before constructing your connection URL:

from urllib.parse import quote

pwd = "some@pwd!"
enc_pwd = quote(pwd)
print(enc_pwd)  # will print "some%40pwd%21"

For example: f"mysql+mysqldb://{user}:{quote(passwd)}@{host}:{port}/{db}".

How to specify url in sqlalchemy?

1 Answers1