Connecting to MS SQL Server with Windows Authentication using Python?

Question

How do I connect MS SQL Server using Windows Authentication, with the pyodbc library?

I can connect via MS Access and SQL Server Management Studio, but cannot get a working connection ODBC string for Python.

Here's what I've tried (also without 'Trusted_Connection=yes'):

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='[system_name]',
               database='[databasename]')

pyodbc.connect('Trusted_Connection=yes', uid='me',
               driver='{SQL Server}', server='localhost',
               database='[databasename]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='localhost',
               uid='me', pwd='[windows_pass]', database='[database_name]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='localhost',
               database='[server_name]\\[database_name]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}', server='localhost',
               database='[server_name]\[database_name]')

pyodbc.connect('Trusted_Connection=yes',
               driver='{SQL Server}',
               database='[server_name]\[database_name]')

Answer 1

You can specify the connection string as one long string that uses semi-colons (;) as the argument separator.

Working example:

import pyodbc
cnxn = pyodbc.connect(r'Driver=SQL Server;Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT LastName FROM myContacts")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.LastName)
cnxn.close()

For connection strings with lots of parameters, the following will accomplish the same thing but in a somewhat more readable way:

conn_str = (
    r'Driver=SQL Server;'
    r'Server=.\SQLEXPRESS;'
    r'Database=myDB;'
    r'Trusted_Connection=yes;'
    )
cnxn = pyodbc.connect(conn_str)

(Note that there are no commas between the individual string components.)

Answer 2

Windows Authentication can also be specified using a keyword. Nothing functionally different from the accepted answer, I think it makes code formatting a bit easier:

cnxn = connect(driver='{SQL Server}', server='localhost', database='test',               
               trusted_connection='yes')

Answer 3

Just wanted to add something as I see the solutions here using localhost; in my experience, SQL Server has issues with this, not sure if its the ODBC driver or the service itse, and prefers the use of (local) if you don't want to specify the local machines name.

cnxn = connect(driver='{SQL Server}', server='(local)', database='test',               
               trusted_connection='yes')

Answer 4

HI Use below clode to connect you data & export this data into excel

please use pip command to install dist in command line.

pip install pypyodbc
pip install pandas pip install xlsxwriter pip install requests

CODE__________________

import pypyodbc as odbc 
import pandas as pd
import xlsxwriter
import requests
import datetime

conn_str = (
    r'Driver=SQL Server;'
    r'Server=AJAYZORI\SQLEXPRESS;'
    r'Database=Production;'
    r'Trusted_Connection=yes;'
    )

try:
    conn =  odbc.connect(conn_str)
except:
    print("Error")
    exit()


try:
    with conn.cursor() as cursor:
        # Read data from database
        sql = "SELECT * FROM MAT_LISTING_MUMBAI_DATABASE_TBL"
        cursor.execute(sql)

        # Fetch all rows

        columns = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        #print(rows[0])
        # Print results
        #for row in rows:
        #    print(row)
        
        df = pd.DataFrame(list(rows), columns=columns)
        now = datetime.datetime.now()
        print(now)
        writer = pd.ExcelWriter( now.strftime("%B  %G HH_%I MM_%M  SS_%S %p") + " Output.xlsx", engine="xlsxwriter",)
        df.to_excel(writer, sheet_name='data')
        writer.close()

finally:
    conn.close()