Connect to SQL Server via JayDeBeApi in Python

visibility 8,710 access_time 2 years ago languageEnglish

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 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 10.75
More from Kontext
Spark - Save DataFrame to Hive Table
visibility 63,723
thumb_up 12
access_time 2 years ago
Load XML File into BigQuery
visibility 1,817
thumb_up 0
access_time 2 years ago
Load XML File into BigQuery
Spark Scala: Load Data from SQL Server
visibility 1,758
thumb_up 0
access_time 2 years ago