python pyodbc:如何连接到特定实例 [英] python pyodbc : how to connect to a specific instance

问题描述

我正在尝试连接到 SQL Server 的特定实例并从系统表中获取一些数据.正在使用此代码段进行连接:

Am trying to connect to a specific instance of SQL Server and get some data from system tables. Am connecting using this code snippet:

connSqlServer = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=192.106.0.102;DATABASE=master;INSTANCE=instance1;UID=sql2008;PWD=password123;Trusted_Connection=yes')
...
cursorObj.execute("select * from sys.dm_os_sys_info")
row = cursorObj.fetchone()
print("rows from table ",row) 

但是我只能获取默认实例的值，但无法获取instance1"的值.因此，在 'INSTANCE=instance1' 中给出实例名称似乎真的没有效果.即使没有它(尝试提供实例的端口号 'PORT=1443')，我也只能获取默认 SQL Server 实例的值.如何强制它获取特定实例的值?

however am getting the values for the default instance only, but not able to get the value for 'instance1'. So, giving instance name in 'INSTANCE=instance1' really seems to have no effect. Even without it (tried giving 'PORT=1443', the instance's port number), am getting the values only for the default SQL Server instance. How to force it to get the values for the specific instance?

推荐答案

Authentication

首先，您提供 uid/pwd(SQL Server 身份验证)和 trusted_connection(Windows 身份验证).选一个，你不能两个都用.我将假设以下示例使用 SQL Server 身份验证.

Authentication

First, you're providing both uid/pwd (SQL Server authentication) and trusted_connection (Windows authentication). Pick one, you can't use both. I'll assume SQL Server authentication for the following examples.

使用实例名称连接到命名实例 instance1:

Connecting to named instance instance1 using the instance name:

connSqlServer = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=192.106.0.102\instance1;DATABASE=master;UID=sql2008;PWD=password123')

使用 TCP/IP 连接到命名实例，使用端口号 1443:

Connecting to named instance using TCP/IP using the port number 1443:

connSqlServer = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=192.106.0.102,1443;DATABASE=master;UID=sql2008;PWD=password123')

替代关键字

pyodbc.connect() 支持关键字，我认为这些是如果您将变量用于连接字符串属性，则更易于阅读，并且您不必进行任何字符串格式化:

Keyword alternative

pyodbc.connect() supports keywords, I think these are easier to read and you don't have to do any string formatting if you're using variables for connection string attributes:

命名实例:

connSqlServer = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                               server='192.106.0.102\instance1',
                               database='master',
                               uid='sql2008',pwd='password123')

TCP/IP 端口:

connSqlServer = pyodbc.connect(driver='{SQL Server Native Client 10.0}',
                               server='192.106.0.102,1443',
                               database='master',
                               uid='sql2008',pwd='password123')

这篇关于python pyodbc:如何连接到特定实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！