Python pyodbc: how to connect to a specific instance

Question

I am trying to connect to a specific instance of SQL Server and get some data from system tables. Connection using this piece of code:

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) 

however, I only get the values ​​for the default instance, but cannot get the value for "instance1". Thus, providing the instance name in "INSTANCE = instance1" really has no effect. Even without it (having tried to give "PORT = 1443", the port number of the instance), I get the values ​​only for the default SQL Server instance. How to make it get values ​​for a specific instance?

Answer 1

Authentication

First, you provide both uid / pwd (SQL Server authentication) and trusted_connection (Windows authentication). Choose one, you cannot use both. I assume SQL Server authentication for the following examples.

Connection strings

Connect to a 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') 

Connecting to a named instance using TCP / IP using 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') 

Keyword Alternative

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

Named instance:

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

TCP / IP Port:

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