Connecting Python to MS SQL Using PyODBC
From your DIFM Windows Server Virtual Machine
How to Connect Python Script to DIFM Database:
- Download & Install VS Code – (or any python development editor of your choice.)
- Install Python Version 3.11,
Choose Custom Installation
Click on Advanced
Check - Modify Path and Environmental Variables
- INSTALL PIP:
Using a Command Prompt:
py -m pip install --upgrade pip
- pip install pandas
pip install pyodbc
If Error: You must install C ++ Build Tools
https://visualstudio.microsoft.com/visual-cpp-build-tools/
- Create your Python File
In this case we will use SQL to view all products that are seeds in the products table.. The pyODBC connection string is below
_____________________________________________________
import pandas as pd
import pyodbc
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=132.145.129.240;PORT=1433;DATABASE=difm_clone;UID=dev;PWD=YourPassword;')
df = pd.read_sql_query("""SELECT ProductName, Type FROM products WHERE type='seed' ORDER BY ProductName ASC""", conn)
print(df)
print(type(df))
Developer Note:
Wrap Your SQL in Quotes!
Python does not care if you use single or double quotes, but SQL does. And when you use SQL within Python you need to wrap your query in quotes.
In Python, wrap your SQL string in three double quotes.
Use double quotes for any table or field in the database.
Use single quotes for any data you want to select/insert/update in the database.
This way, Python and SQL are happy.
