Virtuoso-SQLAlchemy

A Virtuoso DBMS dialect for SQLAlchemy.

The latest version of this dialect requires SQLAlchemy 1.4 or later.

Objectives

Co-requisites

This SQLAlchemy dialect depends on pyodbc (which depends on a working unixODBC environment). This dialect and its dependencies require installation using pip oif they are not already in place:

pip install pyodbc 
pip install sqlalchemy 
pip install virtuoso-sqlalchemy or pip install git+https://github.com/OpenLinkSoftware/Virtuoso-SQLAlchemy.git

Getting Started

Create an ODBC DSN (Data Source Name)_ that points to your target Virtuoso multi-model DBMS instance via ~/.odbc.ini as exemplified by the following sample snippet:

; Data Source Name and associated Driver Section
VOS          = OpenLink Virtuoso ODBC Driver (Unicode)

; Data Source Name and associated Driver Library section
[VOS]
Description = Open Virtuoso
Driver      = /usr/local/virtuoso-opensource/lib/virtodbcu_r.so
Database    = Demo
Address     = localhost:1111
WideAsUTF16 = Yes

NOTE: WideAsUTF16 = Yes is mandatory attribute, it is used to transform unicode methods and data in Virtuoso ODBC to UTF16 charset, that is required by unixODBC Driver Manager.

Most parameters depend on your installation, but be sure to use virtodbcu_r.so which comprises OpenLink Virtuoso 7.2 ODBC driver or Virtuoso 8.x ODBC driver functionality.

Data Source Name (DSN) binding, via SQLAlchemy, occurs via a virtuoso+pyodbc scheme URI.

    from sqlalchemy import create_engine
    engine = create_engine("virtuoso+pyodbc://uid:pwd@your_dsn")

Example

    engine = create_engine("virtuoso+pyodbc://dba:dba@VOS")

The SQLAlchemy Project

SQLAlchemy-access is part of the SQLAlchemy Project https://www.sqlalchemy.org and adheres to the same standards and conventions as the core project.

Authors

2023, OpenLink Software
The package based on project https://github.com/maparent/virtuoso-python from Marc-Antoine Parent

Error trying to connect to local Virtuoso instance running via Docker on Fedora

Issue Description

When trying to connect to connect to Virtuoso via SQLAlchemy I get the error [IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect).
I've tried multiple workarounds to configure the driver properly on Fedora, but have not been able to get over this particular error.

The test consists of initiating Virtuoso via Docker Compose and running a python script equivalent to the following:

from sqlalchemy import create_engine

metadata = MetaData()
...
# create multiple tables associated to metadata
...

engine = create_engine("virtuoso+pyodbc://dba:dba@VOS")

with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
    with engine.begin():
        metadata.drop_all(conn, checkfirst=True)
        metadata.create_all(conn, checkfirst=False)
    conn.commit()

The error is being raised the line with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:, which indicates that is unlikely associated to the specific tables I'm trying to create.

Also, after searching this error I found this StackOverflow post whose answer indicates that the issue is likely related to some problem with the ODBC driver installation/configuration.

Would you be able to provide further detail on how the driver could be installed/configured on Fedora and how I might solve this particular issue?

End of Error Stack Trace

... lib64/python3.11/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/20/rvf5)

Test Environment:

Virtual Machine (VM) Setup

VM OS: Fedora release 39 (Thirty Nine)
VM CPU / RAM / Disk: 2 / 4GB / 32 GB
Hypervisor: Oracle VirtualBox 7.0.4
Host OS: MacOS 14.2.1 (Sonoma)

Python Setup

Python 3.11.7
pip 24.0 from /home/eltons/virtualenvs/dbms-benchmarking-framework/lib64/python3.11/site-packages/pip (python 3.11)

# dependencies
virtuoso-sqlalchemy @ git+https://github.com/OpenLinkSoftware/Virtuoso-SQLAlchemy.git@1f88bad4142dfbb030934be3a6fc87d9b6374529
SQLAlchemy==2.0.25
pyodbc==5.1.0

docker-compose.yaml

version: '3.1'

services:

  db:
    image: openlink/virtuoso-opensource-7:7.2.11
    ports:
      - '1111:1111'
      - '8890:8890'
    shm_size: 4g
    environment:
      - DBA_PASSWORD=dba

ODBC

unixODBC 2.3.11

Installed using sudo dnf install unixODBC-devel

~/.odbc.ini

; Data Source Name and associated Driver Section
VOS          = OpenLink Virtuoso ODBC Driver (Unicode)

; Data Source Name and associated Driver Library section
[VOS]
Description = Open Virtuoso
Driver      = /usr/local/virtuoso-opensource/lib/virtodbcu_r.so
Database    = Demo
Address     = localhost:1111
WideAsUTF16 = Yes

/etc/odbcinst.ini

Manually added the following lines to the end of the file:

# Driver from Virtuoso-SQLAlchemy
# Setup from the unixODBC pacakge
[VOS]
Description     = Open Virtuoso
Driver              = /usr/local/virtuoso-opensource/lib/virtodbcu_r.so
Database        = Demo
Address          = localhost:1111
WideAsUTF16     = Yes

virtodbcu_r.so

Copied from the virtuoso container to /usr/local/virtuoso-opensource/lib/ via docker cp (since we could not find it anywhere else):

sudo docker cp virtuoso_db_1:/opt/virtuoso-opensource/lib/virtodbcu_r.so /usr/local/virtuoso-opensource/lib/virtodbcu_r.so

openlinksoftware / virtuoso-sqlalchemy Goto Github PK