print(engine) does not display the correct connection URL

Question

I am trying to use python in a virtual environment to write a dataframe to sql server. I can read from the server with my pyodbc connection, but can't write to it using that connection, so I'm using a sqlalchemy engine, and credentials stored in a .env file.

Printing the raw string returns:

'mssql+pyodbc://User:Password@Server/Database?trusted_connection=no&driver=ODBC+Driver+17+for+SQL+Server'

but when printing the engine it returns:

'mssql+pyodbc://User:***sword@Server/Database?trusted_connection=no&driver=ODBC+Driver+17+for+SQL+Server'

If I try to connect using the engine, the login timeout expires, which I'm assuming is because the engine isn't passing the correct credentials.

Is there something going on with my string formatting?

import os
from sqlalchemy import create_engine
from dotenv import load_dotenv

load_dotenv()

credentials = [os.getenv('UID'), os.getenv('PWD'), os.getenv('Server'), os.getenv('Database')]

engine = create_engine('mssql+pyodbc://{0}:{1}@{2}/{3}?trusted_connection=no&driver=ODBC+Driver+17+for+SQL+Server'.format(*credentials),fast_executemany=True)

print('mssql+pyodbc://{0}:{1}@{2}/{3}?trusted_connection=no&driver=ODBC+Driver+17+for+SQL+Server'.format(*credentials))
print(engine)

engine.connect()

Consider using [sqlalchemy.engine.URL.create()](https://docs.sqlalchemy.org/en/14/core/engines.html#sqlalchemy.engine.URL.create) and avoid having to build the string yourself. — Gord Thompson, Aug 18 '21 at 17:04
Please [Edit](https://stackoverflow.com/posts/68836464/edit) your question to include the full and complete error message you're getting, as text. "Login timeout expired" suggests a network issue, such as incorrect port, but the full error message may contain useful information to narrow down the issue. — AlwaysLearning, Aug 18 '21 at 21:38

score 0 · Answer 1 · edited Jun 01 '22 at 15:31

0

This is what you should do....

credentials = [username, password, server, database]

engine = create_engine('mssql+pyodbc://{0}:{1}@{2}/{3}?trusted_connection=no&driver=ODBC+Driver+17+for+SQL+Server'.format(*credentials),fast_executemany=True)

edited Jun 01 '22 at 15:31

Shmiel

1,201
10
25

answered May 28 '22 at 15:24

Kenneth Igili

1
1

https://stackoverflow.com/q/71398129/2144390 – Gord Thompson May 28 '22 at 23:19
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 29 '22 at 00:21

score 0 · Answer 2 · answered May 28 '22 at 20:12

The __repr__ method for Engine purposely obfuscates the password, replacing it with ***:

import sqlalchemy as sa

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="gord",
    password="p@ssword",
    host="192.168.0.199",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
    },
)
print(connection_url)
# mssql+pyodbc://gord:p%40ssword@192.168.0.199?driver=ODBC+Driver+17+for+SQL+Server
engine = sa.create_engine(connection_url)
print(engine)
# Engine(mssql+pyodbc://gord:***@192.168.0.199?driver=ODBC+Driver+17+for+SQL+Server)

Note that in this particular case the username and password arguments are irrelevant as Trusted_Connection=Yes is being used.

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    host="192.168.0.199",
    query={
        "driver": "ODBC Driver 17 for SQL Server",
        "Trusted_Connection": "Yes",
    },
)

print(engine) does not display the correct connection URL

2 Answers2