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

2022-01-16 00:00:00 pyodbc odbc sql-server sql-server-2008-r2 windows-authentication

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

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

相关文章