Python

You are looking at an older version of the documentation. The latest version is found here.

This chapter describes several ways to enable interaction between Python scripts and Data Virtuality Server.

Access Data Virtuality Server from Python using JDBC Driver and JayDeBeApi

JayDeBeApi

The JayDeBeApi module allows connecting from Python code to various databases using Java JDBC drivers and connection string. It provides a Python DB-API v2.0 to the Data Virtuality Server.

The module can be installed via pip:

$ pip install jaydebeapi

Data Virtuality JDBC Driver

You can get the Data Virtuality JDBC Driver either from a Data Virtuality Server instance or the Data Virtuality Driver download area.

A vendor-specific driver class and JDBC URL are needed to connect using a JDBC driver. 

For the Data Virtuality Server, these are:

Driver class:
com.datavirtuality.dv.jdbc.Driver 

JDBC URL:
jdbc:datavirtuality:<Virtual Database>@mm[s]//<host>:<port>;

The components of the URL are as follows:

• < virtual database > - Name of the Virtual Database (VDB) to connect to. Unless Multiple VDBs are used, the name of the VDB is "datavirtuality".
• mm - Data Virtuality JDBC protocol for a connection not using SSL
  mms - Data Virtuality JDBC protocol for a connection using SSL
• <host> - Data Virtuality Server address. 
• <port> - Data Virtuality Server port incoming JDBC connections. By default, the port for connections using SSL is 31001, and the port for connections not using SSL is 31000.

Connect to Data Virtuality Server

jaydebeapi.connect()

The jaydebeapi.connect() method has the following signature:

jaydebeapi.connect(jclassname, url, driver_args, jars, libs)

Providing:

com.datavirtuality.dv.jdbc.Driver as jdbclassname

jdbc:datavirtuality:datavirtuality@mms://localhost:31001 as url

[admin, admin] as driver_args

"C:/datavirtuality/jdbc/datavirtuality-jdbc.jar" as jars,

the code to connect via jaydebapi to the Data Virtuality Server looks like that:

import jaydebeapi

jaydebeapi.connect(com.datavirtuality.dv.jdbc.Driver, jdbc:datavirtuality:datavirtuality@mms://localhost:31001, [admin, admin], "C:/datavirtuality/jdbc/datavirtuality-jdbc.jar")
 
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

Full sample

import jaydebeapi
 
# Data Virtuality Server Details
host = "localhost"
port = "31001"
database = "datavirtuality"
use_ssl = True
uid = "admin"
pwd = "admin"
 
# Data Virtuality JDBC class name 
driver_class = "com.datavirtuality.dv.jdbc.Driver"
 
# Data Virtuality Server driver file (full path)
driver_file = "C:/datavirtuality/jdbc/datavirtuality-jdbc.jar"
 
# JDBC connection string
if (use_ssl):
    connection_string="jdbc:datavirtuality:{}@mms://{}:{}".format(database, host, port)
else:
    connection_string="jdbc:datavirtuality:{}@mm://{}:{}".format(database, host, port)
 
# Establish JDBC connection
con = jaydebeapi.connect(driver_class, connection_string, [uid, pwd], driver_file,)

cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall() 

Access Data Virtuality Server from Python Using Data Virtuality ODBC Driver and pyodbc

pyodbc

The pyodbc module allows connecting from Python code to various databases using ODBC drivers. It provides a Python DB-API v2.0 to the Data Virtuality Server.

The module can be installed via pip:

$ pip install pyodbc

Binary builds are released for most versions of Windows and macOS.

When installing pyodbc on Linux,  pip  will download and compile the pyodbc source code. This requires that some related components and source files be available for the compilation to succeed. Please consider the most recent pyodbc documentation regarding the needs of your particular Linux distribution.

Data Virtuality ODBC Driver 

The Data Virtuality ODBC Driver must be installed on the system running the Python program, which shall connect to the Data Virtuality Server.

The driver can be obtained from a Data Virtuality Server instance. You can also get the Windows installer for the driver via the Data Virtuality Driver download area.

Detailed driver installation instructions for Windows, Linux, and macOS can be found in the Data Virtuality Administration Guide.

Connect to Data Virtuality Server

pyodbc.connect()

import pyodbc

con = pyodbc.connect("driver={DataVirtuality Unicode(x64)}; server=localhost; port=31001; database=datavirtuality;sslMode=require; uid=admin; pwd=admin;")
 
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

Full sample

import pyodbc

# Data Virtuality Server Details
host = "localhost"
port = "35433"
database = "datavirtuality"
sslmode = "require"
uid = "admin"
pwd = "admin"
 
# Data Virtuality ODBC driver name
driver = "DataVirtuality Unicode(x64)" 

# ODBC connection string
con_string = "driver={}; server={}; port={}; database={};sslMode={}; uid={}; pwd={};".format(driver, host, port, database, sslmode, uid, pwd)

# Establish ODBC connection
con = pyodbc.connect(con_string)

cur  = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

Access Data Virtuality Server from Python Using psycopg2

Another way to connect from Python code to the Data Virtuality Server is the psycopg2 module. It uses the PostgreSQL protocol and has been specially designed for heavily multi-threaded applications.

For development and testing purposes, you can obtain a stand-alone package not requiring a compiler or external libraries by installing the psycopg2-binary package from PyPI:

$ pip install psycopg2-binary

In production, we recommend using the package built from sources, as described in detail in the Installation section of the psycopg2 documentation.

Connect to Data Virtuality Server

As the Data Virtuality Server and PostgreSQL share some part in their language, psycopg2 can be used for easy-to-set-up access to Data Virtuality Server. It can be used with plain SQL statements to be sent to Data Virtuality Server.

psycopg2.connect()

import psycopg2

con = psycopg2.connect("dbname=datavirtuality user=admin host=localhost password=admin port=35433 sslmode=require")
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

import psycopg2 

# Data Virtuality Server Details
host = "localhost"
port = "35433"
database = "datavirtuality"
sslmode = "require"
uid = "admin"
pwd = "admin"

# psycopg2 connection string
con_string = "dbname={} user={} host={} password={} port={} sslmode={}".format(database, uid, host, pwd, port, sslmode)

# Establish connection
con = psycopg2.connect(con_string)
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()

Limitations

Using psycopg2 has some limitations. Let's consider the following example where something has gone wrong - note that we're employing a very helpful mogrify method. Basically, all you need to do is replace execute with mogrify, and it'll return the exact string that would be sent to the database by execute.

In: import datetime
   ...: import psycopg2
   ...:
   ...: host = "localhost"
   ...: port = "35433"
   ...: database = "datavirtuality"
   ...: sslmode = "require"
   ...: uid = "admin"
   ...: pwd = "admin"
   ...:
   ...: dt = datetime.datetime.now()
   ...:
   ...:
   ...: conn_string = "dbname={} user={} host={} password={} port={} sslmode={}".format(database, uid, host, pwd, port, sslmode)
   ...:
   ...: conn = psycopg2.connect(conn_string)
   ...: cur = conn.cursor()
   ...:
   ...: cur.mogrify("SELECT %s, %s, %s;", (dt, dt.date(), dt.time()))
Out: b"SELECT '2019-11-08T17:58:59.910677'::timestamp, '2019-11-08'::date, '17:58:59.910677'::time;"

In the statement "SELECT %s, %s, %s;", (dt, dt.date(), dt.time()), the placeholders are filled by Python via (dt, dt.date(), dt.time()) . As a result, ::date and ::time are sent to the Data Virtuality Server (or, in our example, printed out by mogrify) and prevent the statement from being parsed and understood. Thus, such statements cannot be used with psycopg2. But the following statement will work all right:

cur.mogrify("SELECT cast(now() as timestamp), cast(now() as date), cast(now() as time);"