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

Connecting to MS SQL Server with Windows Authentication using Python?(使用 Python 通过 Windows 身份验证连接到 MS SQL Server?)

问题描述

如何使用 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.)

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