python连接sql server数据库windows认证_使用Python使用Windows身份验证连接到MS SQL Server吗？...

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

解决方案

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