So you want to access Microsoft SQL Server from your Python script(s)?
After weeding out some long-abandoned and/or nonworking solutions, I discovered a very simple Python ODBC driver that works with virtually all SQL Servers since MSSQL 2005 called "pyodbc".
First, you will need to install this MSSQL ODBC (13.1 or 17 should work) component on your machine in addition to installing the pyodbc driver.
Next, get the pyodbc module for Python by running this from Windows command prompt:
pip install pyodbc
Then open up a python shell using 'py' or 'python' and enter the following after editing configuration values to match your development environment:
Running this code will result in the below if you have configured everything correctly (note this example makes use of the Microsoft SQL Server demo WorldWideImporters database):
Reference: https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-2017
After weeding out some long-abandoned and/or nonworking solutions, I discovered a very simple Python ODBC driver that works with virtually all SQL Servers since MSSQL 2005 called "pyodbc".
First, you will need to install this MSSQL ODBC (13.1 or 17 should work) component on your machine in addition to installing the pyodbc driver.
Next, get the pyodbc module for Python by running this from Windows command prompt:
pip install pyodbc
Then open up a python shell using 'py' or 'python' and enter the following after editing configuration values to match your development environment:
import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=WideWorldImporters;UID=DemoUser;PWD=123Password')
cursor = cnxn.cursor()
#Sample of a simple SELECT
cursor.execute("SELECT TOP (100) Comments, count(*) FROM WideWorldImporters.Sales.Orders GROUP BY Comments")
row = cursor.fetchone()
while row:
print(row[0] + ': ' + str(row[1]))
row = cursor.fetchone()
Running this code will result in the below if you have configured everything correctly (note this example makes use of the Microsoft SQL Server demo WorldWideImporters database):
