Python

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

Access CData 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 CData Virtuality Server.

The module can be installed via pip:

BASH

$ pip install jaydebeapi

CData Virtuality JDBC Driver

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

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

For the CData Virtuality Server, these are:

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

JDBC URL:
jdbc:cdatavirtuality:<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 - CData Virtuality JDBC protocol for a connection not using SSL
mms - CData Virtuality JDBC protocol for a connection using SSL
<host> - CData Virtuality Server address.
<port> - CData 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 CData Virtuality Server

jaydebeapi.connect()

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

CODE

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

Providing:

com.datavirtuality.dv.jdbc.Driver as jdbclassname

jdbc:cdatavirtuality: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 CData Virtuality Server looks like that:

PY

import jaydebeapi

jaydebeapi.connect(com.datavirtuality.dv.jdbc.Driver, jdbc:cdatavirtuality: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

PY

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:cdatavirtuality:{}@mms://{}:{}".format(database, host, port)
else:
    connection_string="jdbc:cdatavirtuality:{}@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 CData Virtuality Server from Python Using CData 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 CData Virtuality Server.

The module can be installed via pip:

BASH

$ 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.

CData Virtuality ODBC Driver

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

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

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

Connect to CData Virtuality Server

pyodbc.connect()

PY

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

python code

PY

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 CData Virtuality Server from Python Using psycopg2

Another way to connect from Python code to the CData 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:

BASH

$ 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 CData Virtuality Server

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

psycopg2.connect()

PY

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()

Full Example

PY

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.

PY

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 CData 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:

PY

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

JDBC connection string changed in v4.10: both jdbc:datavirtuality and jdbc:cdatavirtuality are acceptable