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

Time:12-01

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']

CodePudding user response:

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
                                  )
  • Related