# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# # Connecting to SQL Server with JayDeBeApi

# **GOAL:** Query SQL Server using Single Sign-On (SSO) and return results as a pandas dataframe

# To get started, need 2 things:

# 1. Open-source jtds drivers from [here](https://sourceforge.net/projects/jtds/files/jtds/).
# 2. Java runtime environment (JRE).  **NOTE:** If you also need to query mainframe DB2, it is recommended that you use the same JRE used to connect to mainframe DB2

# Then review this StackOverflow [example](https://stackoverflow.com/questions/55376100/create-a-jtds-connection-string-in-python) for SQL Server connection string details.

# Review this StackOverflow [example](https://stackoverflow.com/questions/13267764/i-o-error-sso-failed-native-sspi-library-not-loaded) to determine where to save the "ntlmauth.dll" file used for SSO ("Single Sign On" authentication).  Looks like you need to save it in the `java/bin` or `jre/bin` folder.

from getpass import getpass
import jaydebeapi as jdpa
import pandas as pd

# !echo %JAVA_HOME%

# #### Database server details

host = 'some_host'
port = '1433'
database = 'some_database'

# #### Please use `WITH (NOLOCK)` after table name or table alias to prevent blocking others or jobs from accessing table concurrently.

sql = """
SELECT DISTINCT
    some_columns_FROM some_table
FROM
    dbo.some_table WITH (NOLOCK)
WHERE
    PDTLS.IsPartActive = 1
    AND PDTLS.PartEffectiveBeginDate <= getdate()
    AND PDTLS.PartEffectiveEndDate > getdate()
"""

# #### Usually best practice to use `with` statement when making a database connection so that we can ensure database connections are closed

# Connect, execute query, return results back as a pandas dataframe

with jdpa.connect(
    'net.sourceforge.jtds.jdbc.Driver',
    f'jdbc:jtds:sqlserver://{host}:{port}/{database};Trusted_Connection=yes;',
    jars=['C:/Users/some_user/jtds/jtds-1.3.1.jar']
) as conn:
    df_mbpn = pd.read_sql(sql, conn, index_col=None)

# Number of (rows, columns):

df_mbpn.shape

# First 5 rows of data:

df_mbpn.head()

StarcoderdataJupyterScriptsDedupFiltered