Accessing a database from python (pyodbc)

Below is a brief example demonstrating the use of the SQL/R ODBC driver from python to access a database SAMPLE.

The ODBC data source and driver is configured in unixODBC. The configuration file /etc/odbc.ini specifies a data source.

[sample]
driver=SQLR4
Server=...

The configuration file /etc/odbcinst.ini specifies the driver.

[SQLR4]
Description=SQL/R ODBC 4.x Driver
Driver=/opt/sqlr/4.2/lib/libsqlr3odbc.so
Driver64=/opt/sqlr/4.2/lib64/libsqlr3odbc64.so

The pyodbc example program:

import pyodbc

conn = pyodbc.connect('DSN=sample')
conn.setdecoding(pyodbc.SQL_CHAR, encoding='latin1')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='latin1')

cursor = conn.cursor()
cursor.execute('SELECT custno,matchcode,name1'
                ' FROM customers where matchcode like \'KE%\' limit 5')

for i in cursor:
     print(i)

output:

$ python3 ex.py
('33007', 'KELLER', 'KELLER, ERNST')
('23062', 'KELLER', 'Keller, Ihne & Tesch KG')
('11036', 'KELLER', 'OSKAR KELLER')
('17044', 'KEMET', 'KEMET ELECTRONICS CORP.')
('32031', 'KEMPCH', 'Kempchen & Co GmbH')