How do I use SQLAlchemy create_engine() with password that includes an @

Question

This works:

db = pyodbc.connect('driver={SQL Server Native Client 11.0}; server=172.30.0.194; database=db;uid=someuser; pwd=fancy@password')

This doesn't

cn_string = "mssql+pyodbc://someuser:&quot;fancy&amp;password&quot;@172.30.0.194/db?driver=SQL+Server+Native+Client+11.0"
return create_engine(cn_string)

This doesn't either:

driver = "SQL Server Native Client 11.0"
server = "192.30.0.194"
database = "EPM_Dashboard"
uid = "someuser"
pwd = "fancy@password"
params = f'DRIVER={{{driver}}};SERVER={server};DATABASE={database};UID={uid};PWD={{{pwd}}};'

connection_string = 'mssql+pyodbc:///?odbc_connect=%s' % urllib.parse.quote_plus(params)

return create_engine(connection_string)

I get something like:

Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)

which would be more believable if the pyodbc item failed.

Here's another failure:

return create_engine(urllib.parse.quote_plus('driver={SQL Server Native Client 11.0}; server=172.30.0.194; database=EPM_Dashboard;uid=someuser; pwd=fancy@password'))

I'm sure there's a tricky character somewhere I'm missing.

Here are some resources

https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-databases

Special character in SQL password

SqlAlchemy equivalent of pyodbc connect string using FreeTDS

That's not an authentication error, it's a timeout. Check the firewall is open, SQL Server is enabled for remote connection and TCP listening is enabled — Charlieface
– Charlieface, Commented Feb 25, 2021 at 15:52
The reason why I think all of that is fine is because on the same computer the pyodbc works. It's just the sqlalchemy create_engine that isn't working — Henrietta Martingale
– Henrietta Martingale, Commented Feb 25, 2021 at 16:00
What does urllib.parse.quote_plus do, is that the correct method for creating a connection string for sqlalchemy? (No idea, never used it myself) — Charlieface
– Charlieface, Commented Feb 25, 2021 at 16:03
I imagine it puts in html entities or something. This is what the first link used. I figured its the same encoding. Looks like sa may have their own different encoder? All these languages, This is why we need a monopoly in software — Henrietta Martingale
– Henrietta Martingale, Commented Feb 25, 2021 at 19:30

Gord Thompson · Accepted Answer · 2023-11-02 13:54:31Z

19

If you need to construct a connection URL that may have "funny characters" in it then you can use URL.create() to build it for you:

import sqlalchemy as sa

connection_url = sa.URL.create(
    "mssql+pyodbc",
    username="someuser",
    password="fancy@password",
    host="192.30.0.194",
    database="EPM_Dashboard",
    query={"driver": "SQL Server Native Client 11.0"},
)
engine = sa.create_engine(connection_url)

For those who are curious, the stringified version of the URL is

print(connection_url.render_as_string(hide_password=False))
# mssql+pyodbc://someuser:fancy%[email protected]/EPM_Dashboard?driver=SQL+Server+Native+Client+11.0

but note that it is NOT necessary to convert the URL object to string before passing it to create_engine().

edited Nov 2, 2023 at 13:54

answered Feb 25, 2021 at 17:10

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

2 Comments

Be Chiller Too Over a year ago

if you need to specify a port, you can do host="192.30.0.194,1433"

Gord Thompson Over a year ago

Note: That syntax is specific to SQL Server. URL.create() will also construct the correct connection string if we use host="192.30.0.194", port=49242

Divyank · Accepted Answer · 2025-06-17 13:05:21Z

0

Aleternate way to Handle Special Character such as @ in DB Password is using urllib.parse

from sqlalchemy import create_engine
import pymysql
import urllib.parse
db_password = "abc@123"
db_password = urllib.parse.quote_plus(db_password) # Parsing the password to avoid error: pwd: Jane@123, Error: '123@localhost' ([Errno -2] Name or service not known)")
db_user = "test_user"
db_name = "xyz"
db_host = "127.0.0.1"  # or 127.0.0.1
# Create SQLAlchemy engine
engine = create_engine(f'mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}', pool_pre_ping=True, pool_recycle=300)

answered Jun 17 at 13:05

Divyank

1,0772 gold badges16 silver badges42 bronze badges

Collectives™ on Stack Overflow

How do I use SQLAlchemy create_engine() with password that includes an @

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related