JayDeBeApi
The JayDeBeApi module allows to connect from Python code to various databases using Java JDBC drivers and a JDBC URL. 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 from the Data Virtuality Driver download area.
To connect using a JDBC driver, a vendor-specific driver class and JDBC URL are needed.
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 following:
- <virtual database> - Name of the Virtual Database (VDB) to connect to. Unless Multiple VDBs are used, this 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. Per default the port for connections using SSL is 31001, 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 jclassname
- 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 a 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()
Comments
2 comments
its not working i am getting jpype.PyJPField' object has no attribute 'getStaticAttribute error
can some please tell me the solution
If JDBC is not working for you, use ODBC. Probably a version issue.
Here is something out of a jupyter notebook, but it should work. A DSN can be used instead also.
import pyodbc
usr = 'YourUser' # usr name
passwd = 'YourPassword' # the password for the user
connString_B= "driver={DataVirtuality Unicode(x64)}; server=YourServer; port=35433; database=datavirtuality; sslmode=require; uid="+usr+"; pwd="+passwd+";";
cnxn_B = pyodbc.connect(connString_B)
cursor_B = cnxn_B.cursor()
SQLCommand_B = ("select 1")
cursor_B.execute(SQLCommand_B)
row_B = cursor_B.fetchall()
print (row_B)
Please sign in to leave a comment.