Accessing Virtuoso using PyODBC on macOS or Linux

Virtuoso Universal Server Tips, Tricks, and HowTos
, , , ,
1

What?

You are a Data Scientist, working primarily with Python, with a need for data access that’s independent of specific Database Management Systems (DBMS).

Why?

You don’t want to duplicate effort (e.g., re-writing code) per each target DBMS.

How?

PyODBC is an example of a generic data access layer for Python that supports the ODBC Standard, with built-in binding to the unixODBC Driver Manager for platforms beyond Windows.

Here are the steps for using PyODBC with the Virtuoso ODBC Driver using unixODBC on either Linux or macOS.

  1. Install the Virtuoso ODBC Driver for macOS or Linux
  2. Install your Python runtime – ideally Python3 using the pip package installer
  3. Install PyODBC – also using the pip package installer
  4. Create an ODBC Data Source Name (DSN) for the Virtuoso ODBC Driver using the ‘odbc.ini’ file bound to unixODBC – on macOS this is situated in the folder: /usr/local/etc/odbc.ini while on Linux/Unix it is /etc/odbc.ini 
[Local Virtuoso Demo PyODBC]
Driver             = /opt/virtuoso/lib/virtodbcu_r.so
Address            = localhost:21118
PWDClearText       = 0
LastUser           = dba
DayLight           = Yes
RoundRobin         = No
NoSystemTables     = No
TreatViewsAsTables = No
WideAsUTF16        = Yes
  1. Connect to your ODBC DSN using PyODBC

Sample Python Script saved to file ‘pyodbc-test3.py’

import pyodbc
import pandas as pd
cnxn = pyodbc.connect('DSN=Local Virtuoso Demo PyODBC;UID=demo;PWD=demo')
q = "SELECT CustomerID, ContactName FROM Demo..Customers"
df = pd.read_sql_query(q,cnxn)
print (df)

Sample output when run from your macOS or Linux/Unix command-line using: python3 pyodbc-test3.py .

                  CustomerID   ContactName
0                 ALFKI             Maria Anders
1                 ANATR             Ana Trujillo
2                 ANTON           Antonio Moreno
3                 AROUT             Thomas Hardy
4                 BERGS       Christina Berglund
5                 BLAUS               Hanna Moos
6                 BLONP       Frédérique Citeaux
7                 BOLID            Martín Sommer
8                 BONAP         Laurence Lebihan
9                 BOTTM        Elizabeth Lincoln
10                BSBEV        Victoria Ashworth
11                CACTU         Patricio Simpson
12                CENTC          Francisco Chang
13                CHOPS                Yang Wang
14                COMMI             Pedro Afonso
15                CONSH          Elizabeth Brown
16                DRACD             Sven Ottlieb
17                DUMON           Janine Labrune
18                EASTC                Ann Devon
19                ERNSH            Roland Mendel
20                FAMIA                Aria Cruz
21                FISSA               Diego Roel
22                FOLIG            Martine Rancé
23                FOLKO            Maria Larsson
24                FRANK            Peter Franken
25                FRANR           Carine Schmitt
26                FRANS            Paolo Accorti
27                FURIB          Lino Rodriguez
28                GALED         Eduardo Saavedra
29                GODOS        José Pedro Freyre
..                  ...                      ...
61                QUEEN           Lúcia Carvalho
62                QUICK              Horst Kloss
63                RANCH         Sergio Gutiérrez
64                RATTC             Paula Wilson
65                REGGC          Maurizio Moroni

You can obtain Virtuoso ODBC Driver installers directly from the following links.

Related