Python not connecting with MS SQL Server

[ashmitp869]

Hi All,

I have Power BI and Python install in my local machine , and the MS SQL server in install in Remote Desktop.

I install pypyodbc and pyodbc in local machine and trying to connect my db and tables by unable to do that.

code

mport pandas as pd
import numpy as np
import os

import datetime 

import pyodbc as odbc

DRIVER_NAME ='SQL SERVER'
SERVER_NAME= 'servername'
DATABASE_NAME = 'dbname'

connection_string =f"""
    DRIVER={{{DRIVER_NAME}}};
    SERVER={SERVER_NAME};
    DATABASE={DATABASE_NAME};
    Trust_Copnnection=yes;
"""

conn=odbc.connect(connection_string)
print(conn)

 getting this error :

PS C:\Users\sbhadra> & C:/Users/sbhadra/AppData/Local/Programs/Python/Python312/python.exe "c:/Users/sbhadra/AppData/Local/Programs/Python Scripts/DataManipulationWhile.py"
<pyodbc.Connection object at 0x0000020C9B21FB80>

Do I need to install MS SQL Server in my local machine and also the driver ?

[lbendlin · Accepted solution]

you don't need the server, and the ODBC driver should be installed by default. Check the 64 bit ODBC data sources control panel.

Now - why Python? Why not the standard SQL Server connector in Power Query?

[ashmitp869]

Thanks for the reply @lbendlin .

I am unable to achieve the output via power query or dax as I have two tables with M:M relationship.

I need to pull Timesheet Date from the DSR table and check the Qualification table for Date Start,Date End and Date Expiry to give output if that employee is Qualifed, Learner or Not Qualified for the hours he registered in DSR table with Timesheet data.

Will you have a look on the sample file - I have provided the details

https://community.fabric.microsoft.com/t5/Power-Query/Need-help-with-Dax-to-calculated-from-two-diff...

[ashmitp869]

Hi ,

I am using Python for data manipulation

[lbendlin]

You can still do that as part of the Powr Query script, AFTER you fetched the data from the SQL Server.

NOTE: As soon as you use Python or R scripts you will need to setup and maintain a Personal Gateway if you need scheduled refreshes.