Connect to SQL Server via JayDeBeApi in Python

Python JayDeBeApi module allows you to connect from Python to databases using Java JDBC drivers. In my previous article about 

Connect to SQL Server in Spark (PySpark), I showed how to use py4j (JDBC), pyodbc, pymssql to connect to SQL Server. This article demos how to use this JayDeBeApi.

About JayDeBeApi

Find more details about JayDeBeApi from the this pypy index page: https://pypi.org/project/JayDeBeApi/.

Make sure Python and pip is installed in your machine. 

Run the following command to install this package.

pip install JayDeBeApi

The command will print the following text:

Successfully built JPype1
Installing collected packages: JPype1, JayDeBeApi
Successfully installed JPype1-0.7.2 JayDeBeApi-1.1.1

Code snippet

import jaydebeapi

database = "test"
table = "dbo.Employees"
user = "zeppelin"
password = "zeppelin"

conn = jaydebeapi.connect("com.microsoft.sqlserver.jdbc.SQLServerDriver",
                          f"""jdbc:sqlserver://localhost:1433;
                          databaseName={database}""",
                          [user, password],
                          "sqljdbc_7.2/enu/mssql-jdbc-7.2.1.jre8.jar")
curs = conn.cursor()

curs.execute('create table dbo.test_jaydebeapi'
             '("CID" INTEGER not null,'
             ' "NAME" VARCHAR not null,'
             ' primary key ("ID"))'
             )
curs.execute("insert into dbo.test_jaydebeapi values (1, 'Raymond')")
curs.execute("select * from dbo.test_jaydebeapi")
curs.fetchall()
curs.close()
conn.close()

Python and JVM mismatch

You may encounter the following error if your python is 32 bit while JVM is 64 bit, vice versa. 

jpype._jvmfinder.JVMNotSupportedException: JVM mismatch, python is 32 bit and JVM is 64 bit

To address the issue, make sure you they are aligned. 

AttributeError: type object 'java.sql.Types' has no attribute '__javaclass__'

To fix this error, refer to the following article:

AttributeError: type object 'java.sql.Types' has no attribute '__javaclass__'

Other databases

This package supports the following databases with JDBC drivers:

• SQLite
• Hypersonic SQL (HSQLDB)
• IBM DB2
• IBM DB2 for mainframes
• Oracle
• Teradata DB
• Netezza
• Mimer DB
• Microsoft SQL Server
• MySQL
• PostgreSQL
• many more…

The connection string might be different but the usage is similar.