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. 

info Last modified by Raymond at 3 months ago * This page is subject to Site terms.

More from Kontext

local_offer teradata local_offer python

visibility 623
thumb_up 1
access_time 3 months ago

Pandas is commonly used by Python users to perform data operations. In many scenarios, the results need to be saved to a storage like Teradata. This article shows you how to do that easily using JayDeBeApi or  ...

open_in_new Spark + PySpark

local_offer python

visibility 164
thumb_up 0
access_time 3 months ago

CSV is a common data format used in many applications. It's also a common task for data workers to read and parse CSV and then save it into another storage such as RDBMS (Teradata, SQL Server, MySQL). In my previous article  ...

open_in_new Python Programming

local_offer teradata local_offer python local_offer Java

visibility 348
thumb_up 0
access_time 3 months ago

Python JayDeBeApi module allows you to connect from Python to Teradata databases using Java JDBC drivers. In article Connect to Teradata database through Python , I showed ho...

open_in_new Python Programming

Azure SQL Database Automated Backup Strategy

local_offer Azure local_offer SQL Server

visibility 84
thumb_up 0
access_time 3 months ago

When designing the architecture of Kontext platform, Azure SQL Database is chosen as the storage for relational data. TDE and other advanced security features are always enabled to protect the database. Backup plans are also employed to ensure I can always restore the database for as point of tim...

open_in_new Azure

info About author

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward