Importing data using the JMP Python environment with ODBC in JMP® 18 and later

[#1]

If you've wondered how to import your database using JMP's embedded Python environment, here are some examples that may help you get started. In the first example, I am importing data from a SQL Server database using the pyodbc modules; I use the OracleDB module in the second example. If you are familiar with SQL and Python and need to quickly execute SQL without leaving JMP, I think you'll enjoy these quick tips.

Note: You can use pyodbc with numerous database management systems (MySQL, SQL Server, PostgreSQL, etc.). Oracledb can only be used to establish Oracle database connections. 

Pre-requisites:

• ODBC 3.5 Unicode driver compatible with your database management system (DBMS)
• JMP 18 or higher
• Python modules: Pandas, pyodbc, or OracleDB

Example 1: Using the Python module pyodbc

#Imports the jmp modules jmp. Pandas and jmputils.
import jmp
import jmputils
import pandas as pd

#Installs pyodbc andpandas if they have not been installed before.
#jmputils.jpip('install', 'pyodbc pandas')
 
#import the module pyodbc.
import pyodbc

#Defines variables for the driver's ODBC parameters.
drv = '<driver name>'
db = '<database>'
server = '<server>'
uid = '<username>'
pw = '<password>'

#Passes the defined variables to the connection string parameters and establishes a connection.
cnxn = pyodbc.connect('DRIVER={%s};Server=%s;Database=%s;uid=%s;pwd=%s' % (drv, server, db, uid, pw))

#Defines a variable for the SQL query.
query = 'SELECT * FROM dbo.tablename'

#Executes the SQL query and prints the DataFrame df.
cursor = cnxn.cursor()
df = pd.read_sql(query, con=cnxn)
print (df)
cnxn.close()
  
#Run jmp.run_JSL to get the DataFrame 'df'.
jmp.run_jsl('python get (df)<< newdataview;'
);

Example 2: Using the Python module Oracledb

#Imports the jmp modules jmp and jmputils.
import jmp
import jmputils
import pandas as pd

#Installs oracledb and pandas if they have not been installed before.
#jmputils.jpip('install', 'oracledb pandas')

#Imports the module oracledb.
import oracledb

#Passes the connection string parameters to the database to establish a connection.
con = oracledb.connect(user='<username>', password='<password>', service_name='<service name>', host='<servername>', port='<port number>')

#Executes the SQL query and prints it as the list 'res'.
cur = con.cursor()
cur.execute('SELECT * FROM dbo.tablename')
res = cur.fetchall()
for row in res:
    print(row)
  
#Converts the list 'res' to a pandas DataFrame.
df_ora = pd.DataFrame(res)
print(df_ora)
  
#Runs jmp.run_JSL to get the DataFrame 'df_ora'.
jmp.run_jsl('python get ("df_ora") << newdataview;')