Python Forum - python connect to mssql

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.

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

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

(Dec-06-2023, 08:09 AM)buran Wrote: [ -> ]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

(Dec-06-2023, 08:26 AM)wailoonho Wrote: [ -> ]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

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

Quote: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)')

(Dec-06-2023, 08:59 AM)buran Wrote: [ -> ]
(Dec-06-2023, 08:26 AM)wailoonho Wrote: [ -> ]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

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

the password is P@ssw0rd

(Dec-06-2023, 12:22 PM)buran Wrote: [ -> ]Does your password indeed has @?
if yes, check Writing a connection string when password contains special characters

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

(Dec-06-2023, 12:22 PM)buran Wrote: [ -> ]Does your password indeed has @?
if yes, check Writing a connection string when password contains special characters