Connect to SQL Server via JayDeBeApi in Python

access_time 11 months ago visibility3497 comment 0

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 Administrator 6 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext

visibility 810
thumb_up 1
access_time 5 months ago

Apache Kafka is written with Scala. Thus, the most natural way is to use Scala (or Java) to call Kafka APIs, for example, Consumer APIs and Producer APIs. For Python developers, there are open source packages available that function similar as official Java clients.  This article shows you ...

PySpark Read Multiple Lines Records from CSV
visibility 2843
thumb_up 0
access_time 10 months ago

CSV is a common format used when extracting and exchanging data between systems and platforms. Once CSV file is ingested into HDFS, you can easily read them as DataFrame in Spark. However there are a few options you need to pay attention to especially if you source file: Has records across ...

visibility 15130
thumb_up 1
access_time 4 years ago

Teradata published an official Python module which can be used in DevOps projects. More details can be found at the following GitHub site: https://github.com/Teradata/PyTd If pip is installed, you can directly install this module through the following command: pip install Teradata If not ...