使用Python使用Windows身份验证连接到MS SQL Server吗?

问题描述

如何使用Windows身份验证和pyodbc库连接MS SQL Server?

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

我可以通过MS Access和SQL Server Management Studio进行连接，但是无法获得适用于Python的有效连接ODBC字符串.

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

这是我尝试过的方法(也没有'Trusted_Connection=yes'):

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]')

推荐答案

您可以将连接字符串指定为一个使用分号(;)作为参数分隔符的长字符串.

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

工作示例:

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.)

这篇关于使用Python使用Windows身份验证连接到MS SQL Server吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！