How to connect to a MSSQL database on a remote (windows) server from python?

Question

I have the following information about the remote server.

IP address
Database user name
Database password
Database name

I can even connect to the remote server using azure data studio, running on my Laptop, which is running on Ubuntu 20.04.

However, this is my requirement

Connect to the MSSQL database programmatically from python
Simply write a pandas dataframe as a table.

I tried using pyodbc, but whenever I try pyodbc.connect(), I get an error saying

InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Is there some other library I should use instead of pyodbc? Here is my sample code.

#!/usr/bin/env python3
# encoding: utf-8    
import pyodbc
credential='DRIVER=ODBC Driver 18 for SQL Server;SERVER=192.168.101.56;DATABASE=DEMAND_FORECAST;ENCRYPT=yes;UID=della;PWD=strong;Trusted_Connection=yes;'
pyodbc.connect(str=credential) # Throwing error
# InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default 
# driver specified (0) (SQLDriverConnect)')

pyodbc.drivers()
# ['ODBC Driver 18 for SQL Server']

Sounds like you haven't installed the drivers; have you followed the documentation? — Thom A
– Thom A ♦, Commented Dec 1, 2022 at 9:53
That connection string doesn't look right; you can't provide a username and password and use a trusted connection; it's either SQL Authentication or Windows Authentication, not both. — Thom A
– Thom A ♦, Commented Dec 1, 2022 at 10:06
Also, per the documentation, it should be DRIVER={ODBC Driver 18 for SQL Server}. — Thom A
– Thom A ♦, Commented Dec 1, 2022 at 10:08
"So can the password be called windows authentication then?" I don't understand this sentence. — Thom A
– Thom A ♦, Commented Dec 1, 2022 at 10:18
Parametrising a connection string is possible, yes, just concatenate your parameters to the connection string (like shown in the documentation). How you parametrise those parts is up to you and a very different question to what you're asking; which appears to be typographical errors (the missing braces ({}).) — Thom A
– Thom A ♦, Commented Dec 1, 2022 at 10:20

Orfeas Bourchas · Accepted Answer · 2022-12-01 09:48:54Z

1

Usually you need to do something like: Porbably you are missing the "Driver={SQL Server} or you need to change it to something else

cnxn = pyodbc.connect("Driver={SQL Server};" 
                                  "Server=" your server + ";"
                                  "Database=" your db+ ";"
                                  "UID=" +your uid + ";"
                                  "PWD=" + your PWD
                                  )

answered Dec 1, 2022 at 9:48

Orfeas Bourchas

4282 silver badges7 bronze badges

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

Collectives™ on Stack Overflow

How to connect to a MSSQL database on a remote (windows) server from python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related