Support for pyodbc Python ODBC Bridge

pyodbc is an open source Python module that implements the DB API 2.0 specification (PEP 249 -- Python Database API Specification v2.0Opens in a new tab), leveraging ODBC to access the underlying database. InterSystems supports use of pyodbc as a way to access the database from Python using the relational paradigm. This module can also be used with earlier versions of InterSystems IRIS. For general information, see the pyodbc GitHub siteOpens in a new tab.

Note:

An InterSystems-specific implementation of DB API 2.0 was introduced with InterSystems IRIS 2022.1 (see “Using the Python DB-API” in Using the Native SDK for Python), and is recommended for all new Python development. The pyodbc implementation is still supported, and may be required for client applications that connect to older versions of InterSystems IRIS.

Installation

There are several sites with installation information, both for Windows and for Linux and related operating systems:

pyodbc GitHub site: pyodbc Python ODBC bridgeOpens in a new tab
pyodbc Wiki: Wiki HomeOpens in a new tab
Microsoft pyodbc installation: Python SQL Driver - pyodbcOpens in a new tab
General Python documentation: PythonOpens in a new tab

The installation process is simple:

Install Python 2 or 3 (which supports Unicode) via the Python downloadOpens in a new tab:
From a console with Python in the path:
```
   pip install pyodbc
```

macOS X Installation

macOS X installation is similar to UNIX® platforms (see Python Releases for Mac OS XOpens in a new tab):

install homebrewOpens in a new tab
install unixODBCOpens in a new tab

run pip install:

   pip install --upgrade --global-option=build_ext 
   --global-option="-I/usr/local/include"  --global-option="-L/usr/local/lib"

Test Program

The following test program demonstrates using pyodbc to access an InterSystems IRIS database. See “Structure of the ODBC Initialization File” for an example listing the connection keywords supported by the InterSystems ODBC driver.

test.py

import pyodbc 
import time

input("Hit any key to start")

dsn = 'IRIS Samples'
server = '127.0.0.1' 
database = 'USER' 
username = '_SYSTEM' 
password = 'SYS' 
#cnxn = pyodbc.connect('DRIVER={InterSystems ODBC35};SERVER='+server+'; 
#   PORT='+port+'; DATABASE='+database+';UID='+username+';PWD='+ password)
cnxn = pyodbc.connect('DSN='+dsn+';')
lowptr=cnxn.getinfo(127)
highptr=cnxn.getinfo(136)
#value = PyLong_FromUnsignedLongLong(lowptr)
#print("%#5.8x"% (value))

print ("Connection high pointer: ")
print (format(highptr, '02x'))
print ("Connection high pointer: ")
print("%#5.8x"% (highptr))
print ("Connection low pointer: ")
print("%#5.8x"% (lowptr))
cursor = cnxn.cursor()
start= time.perf_counter()


#Sample select query
cursor.execute("SELECT * from Sample.Person") 
row = cursor.fetchone() 
#while row: 
#    print(row) 
#    row = cursor.fetchone()

end= time.perf_counter()
print ("Total elapsed time: ")
print (end-start)
input("Hit any key to end")

The following changes avoid returning Unicode data specifically and just directly return UTF-8 data.

   cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='latin1')
   cnxn.setencoding(str, encoding='latin1')

This uses the narrow driver, which avoids driver managers using UCS-2 or UCS-4 Unicode and the complications of providing a driver that matches how a particular driver manager was built. For other Unicode options, see the Unicode section of the pyodbc WikiOpens in a new tab.

Support for Node.js Relational Access