Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

python pyodbc : how to connect to a specific instance

Tags:

python

sql

pyodbc

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) 

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?

like image 691
Jean Avatar asked Aug 26 '14 11:08

Jean


People also ask

What is DSN in Pyodbc?

More Information. It is the name that applications use to request a connection to an ODBC Data Source. In other words, it is a symbolic name that represents the ODBC connection. It stores the connection details like database name, directory, database driver, UserID, password, etc.


1 Answers

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.

Connection strings

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

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

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:

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')
like image 193
Bryan Avatar answered Oct 13 '22 12:10

Bryan