python connect to mssql

[wailoonho]

Hi, I'm new to python
I'm currently converting my .net application to python.

I'm trying to call database to extract some data.
I manage to call to mssql database using pyodbc, but when I tried to use pandas, it failed with error

Error:
Exception has occurred: OperationalError
(pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]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)')
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Here is my pyodbc code

connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER="+server+";DATABASE="+database+";UID="+username+";PWD="+ password
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
cursor.execute(f"SELECT COUNT(*) FROM [98_PRODUCTS] WHERE SKU = ?", (sku,))
count = cursor.fetchone()[0]

this work fine

but when I want to try to use pandas

engine = create_engine('mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30')
data = pd.read_sql(query, engine)

it get error mentioned above.

May I know why this happened?? and how to solve this issue?


btw, I'm using Windows 11 running on Macbook M1 Parallel Desktop.

buran write Dec-06-2023, 08:00 AM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.

[buran]

Shouldn't be an f-string?

engine = create_engine(f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30')

That is assuming username, password, server and database used are actually names

[wailoonho]

Thanks for helping to add the tag.
I tried with and without f also same.
I assume f is formatting only and yes, username/password/etc are all string

Shouldn't be an f-string?

engine = create_engine(f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30')

That is assuming username, password, server and database used are actually names

[buran]

I assume f is formatting only

f-strings allows to interpolate the string, i.e. use values assign to names. without it the connection string is literally
mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30'
I would suggest that you construct the string outside of call and print it to check that is correct

I would try

conn_string = f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server'
engine = create_engine(conn_string, connect_args={'timeout': 30})

see the example in the docs

[wailoonho]

I tried, here is my code

                    strConnString = f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30'
                    print(strConnString)
                    engine = create_engine(strConnString)

here is the printed out connection string

mssql+pyodbc://sa:P@ssw0rd@myserver/LightStickers?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30

And here is the error this time

Error:
Exception has occurred: OperationalError
(pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]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)')
(Background on this error at: https://sqlalche.me/e/20/e3q8)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]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)')

I assume f is formatting only

f-strings allows to interpolate the string, i.e. use values assign to names. without it the connection string is literally
mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server&connect_timeout=30'
I would suggest that you construct the string outside of call and print it to check that is correct

I would try

conn_string = f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server'
engine = create_engine(conn_string, connect_args={'timeout': 30})

see the example in the docs

[buran]

Does your password indeed has @?
if yes, check Writing a connection string when password contains special characters

[wailoonho]

the password is P@ssw0rd

Does your password indeed has @?
if yes, check Writing a connection string when password contains special characters

[wailoonho]

Thanks Buran, it works now. technically just the special character on the password.

Does your password indeed has @?
if yes, check Writing a connection string when password contains special characters