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

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
                                  )