cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
%3CLINGO-SUB%20id%3D%22lingo-sub-632953%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3EJMP%C2%AE%2018%20%E4%BB%A5%E9%99%8D%E3%81%A7%20ODBC%20%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%9F%20JMP%20Python%20%E7%92%B0%E5%A2%83%E3%81%AB%E3%82%88%E3%82%8B%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632953%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3E%3CP%3EJMP%20%E3%81%AB%E7%B5%84%E3%81%BF%E8%BE%BC%E3%81%BE%E3%82%8C%E3%81%A6%E3%81%84%E3%82%8B%20Python%20%E7%92%B0%E5%A2%83%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95%E3%82%92%E7%9F%A5%E3%82%8A%E3%81%9F%E3%81%84%E5%A0%B4%E5%90%88%E3%81%AF%E3%80%81%E9%96%8B%E5%A7%8B%E3%81%99%E3%82%8B%E3%81%AE%E3%81%AB%E5%BD%B9%E7%AB%8B%E3%81%A4%E4%BE%8B%E3%82%92%E3%81%84%E3%81%8F%E3%81%A4%E3%81%8B%E7%A4%BA%E3%81%97%E3%81%BE%E3%81%99%E3%80%82%E6%9C%80%E5%88%9D%E3%81%AE%E4%BE%8B%E3%81%A7%E3%81%AF%E3%80%81pyodbc%20%E3%83%A2%E3%82%B8%E3%83%A5%E3%83%BC%E3%83%AB%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%20SQL%20Server%20%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%8B%E3%82%89%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E3%81%97%E3%81%A6%E3%81%84%E3%81%BE%E3%81%99%E3%80%822%20%E7%95%AA%E7%9B%AE%E3%81%AE%E4%BE%8B%E3%81%A7%E3%81%AF%E3%80%81OracleDB%20%E3%83%A2%E3%82%B8%E3%83%A5%E3%83%BC%E3%83%AB%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%81%84%E3%81%BE%E3%81%99%E3%80%82SQL%20%E3%81%A8%20Python%20%E3%81%AB%E7%B2%BE%E9%80%9A%E3%81%97%E3%81%A6%E3%81%84%E3%81%A6%E3%80%81JMP%20%E3%82%92%E9%9B%A2%E3%82%8C%E3%81%9A%E3%81%AB%20SQL%20%E3%82%92%E3%81%99%E3%81%B0%E3%82%84%E3%81%8F%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B%E5%BF%85%E8%A6%81%E3%81%8C%E3%81%82%E3%82%8B%E5%A0%B4%E5%90%88%E3%81%AF%E3%80%81%E3%81%93%E3%82%8C%E3%82%89%E3%81%AE%E7%B0%A1%E5%8D%98%E3%81%AA%E3%83%92%E3%83%B3%E3%83%88%E3%81%8C%E5%BD%B9%E7%AB%8B%E3%81%A4%E3%81%A8%E6%80%9D%E3%81%84%E3%81%BE%E3%81%99%E3%80%82%3C%2FP%3E%0A%3CP%3E%E6%B3%A8%E6%84%8F%3A%20pyodbc%20%E3%81%AF%E3%80%81%E3%81%95%E3%81%BE%E3%81%96%E3%81%BE%E3%81%AA%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E7%AE%A1%E7%90%86%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%20(MySQL%E3%80%81SQL%20Server%E3%80%81PostgreSQL%20%E3%81%AA%E3%81%A9)%20%E3%81%A7%E4%BD%BF%E7%94%A8%E3%81%A7%E3%81%8D%E3%81%BE%E3%81%99%E3%80%82Oracledb%20%E3%81%AF%E3%80%81Oracle%20%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%8E%A5%E7%B6%9A%E3%82%92%E7%A2%BA%E7%AB%8B%E3%81%99%E3%82%8B%E3%81%9F%E3%82%81%E3%81%AB%E3%81%AE%E3%81%BF%E4%BD%BF%E7%94%A8%E3%81%A7%E3%81%8D%E3%81%BE%E3%81%99%E3%80%82%3C%2FP%3E%0A%3CP%3E%E5%89%8D%E6%8F%90%E6%9D%A1%E4%BB%B6%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E7%AE%A1%E7%90%86%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%20(DBMS)%20%E3%81%A8%E4%BA%92%E6%8F%9B%E6%80%A7%E3%81%AE%E3%81%82%E3%82%8B%20ODBC%203.5%20Unicode%20%E3%83%89%E3%83%A9%E3%82%A4%E3%83%90%E3%83%BC%3C%2FLI%3E%0A%3CLI%3EJMP%2018%E4%BB%A5%E4%B8%8A%3C%2FLI%3E%0A%3CLI%3EPython%20%E3%83%A2%E3%82%B8%E3%83%A5%E3%83%BC%E3%83%AB%3A%20%3CA%20href%3D%22https%3A%2F%2Fpypi.org%2Fproject%2Fpandas%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EPandas%3C%2FA%3E%20%E3%80%81%20%3CA%20href%3D%22https%3A%2F%2Fpypi.org%2Fproject%2Fpyodbc%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Epyodbc%3C%2FA%3E%20%E3%80%81%E3%81%BE%E3%81%9F%E3%81%AF%3CA%20href%3D%22https%3A%2F%2Fpypi.org%2Fproject%2Foracledb%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EOracleDB%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20data-unlink%3D%22true%22%3E%E4%BE%8B1%3A%20Python%E3%83%A2%E3%82%B8%E3%83%A5%E3%83%BC%E3%83%ABpyodbc%E3%81%AE%E4%BD%BF%E7%94%A8%3C%2FP%3E%0A%3CPRE%3E%23Imports%20the%20jmp%20modules%20jmp.%20Pandas%20and%20jmputils.%0Aimport%20jmp%0Aimport%20jmputils%0Aimport%20pandas%20as%20pd%0A%0A%23Installs%20pyodbc%20andpandas%20if%20they%20have%20not%20been%20installed%20before.%0A%23jmputils.jpip('install'%2C%20'pyodbc%20pandas')%0A%20%0A%23import%20the%20module%20pyodbc.%0Aimport%20pyodbc%0A%0A%23Defines%20variables%20for%20the%20driver's%20ODBC%20parameters.%0Adrv%20%3D%20'%3CDRIVER%20name%3D%22%22%3E'%0Adb%20%3D%20'%3CDATABASE%3E'%0Aserver%20%3D%20'%3CSERVER%3E'%0Auid%20%3D%20'%3CUSERNAME%3E'%0Apw%20%3D%20'%3CPASSWORD%3E'%0A%0A%23Passes%20the%20defined%20variables%20to%20the%20connection%20string%20parameters%20and%20establishes%20a%20connection.%0Acnxn%20%3D%20pyodbc.connect('DRIVER%3D%7B%25s%7D%3BServer%3D%25s%3BDatabase%3D%25s%3Buid%3D%25s%3Bpwd%3D%25s'%20%25%20(drv%2C%20server%2C%20db%2C%20uid%2C%20pw))%0A%0A%23Defines%20a%20variable%20for%20the%20SQL%20query.%0Aquery%20%3D%20'SELECT%20*%20FROM%20dbo.tablename'%0A%0A%23Executes%20the%20SQL%20query%20and%20prints%20the%20DataFrame%20df.%0Acursor%20%3D%20cnxn.cursor()%0Adf%20%3D%20pd.read_sql(query%2C%20con%3Dcnxn)%0Aprint%20(df)%0Acnxn.close()%0A%20%20%0A%23Run%20jmp.run_JSL%20to%20get%20the%20DataFrame%20'df'.%0Ajmp.run_jsl('python%20get%20(df)%26lt%3B%26lt%3B%20newdataview%3B'%0A)%3B%3C%2FPASSWORD%3E%3C%2FUSERNAME%3E%3C%2FSERVER%3E%3C%2FDATABASE%3E%3C%2FDRIVER%3E%3C%2FPRE%3E%0A%3CP%20data-unlink%3D%22true%22%3E%E4%BE%8B2%3A%20Python%E3%83%A2%E3%82%B8%E3%83%A5%E3%83%BC%E3%83%ABOracledb%E3%81%AE%E4%BD%BF%E7%94%A8%3C%2FP%3E%0A%3CPRE%3E%23Imports%20the%20jmp%20modules%20jmp%20and%20jmputils.%0Aimport%20jmp%0Aimport%20jmputils%0Aimport%20pandas%20as%20pd%0A%0A%23Installs%20oracledb%20and%20pandas%20if%20they%20have%20not%20been%20installed%20before.%0A%23jmputils.jpip('install'%2C%20'oracledb%20pandas')%0A%0A%23Imports%20the%20module%20oracledb.%0Aimport%20oracledb%0A%0A%23Passes%20the%20connection%20string%20parameters%20to%20the%20database%20to%20establish%20a%20connection.%0Acon%20%3D%20oracledb.connect(user%3D'%3CUSERNAME%3E'%2C%20password%3D'%3CPASSWORD%3E'%2C%20service_name%3D'%3CSERVICE%20name%3D%22%22%3E'%2C%20host%3D'%3CSERVERNAME%3E'%2C%20port%3D'%3CPORT%20number%3D%22%22%3E')%0A%0A%23Executes%20the%20SQL%20query%20and%20prints%20it%20as%20the%20list%20'res'.%0Acur%20%3D%20con.cursor()%0Acur.execute('SELECT%20*%20FROM%20dbo.tablename')%0Ares%20%3D%20cur.fetchall()%0Afor%20row%20in%20res%3A%0A%20%20%20%20print(row)%0A%20%20%0A%23Converts%20the%20list%20'res'%20to%20a%20pandas%20DataFrame.%0Adf_ora%20%3D%20pd.DataFrame(res)%0Aprint(df_ora)%0A%20%20%0A%23Runs%20jmp.run_JSL%20to%20get%20the%20DataFrame%20'df_ora'.%0Ajmp.run_jsl('python%20get%20(%22df_ora%22)%20%26lt%3B%26lt%3B%20newdataview%3B')%20%20%20%3C%2FPORT%3E%3C%2FSERVERNAME%3E%3C%2FSERVICE%3E%3C%2FPASSWORD%3E%3C%2FUSERNAME%3E%3C%2FPRE%3E%0A%3CP%3E%20%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-632953%22%20slang%3D%22en-US%22%20mode%3D%22CREATE%22%3E%3CLINGO-LABEL%3E%E3%83%87%E3%83%BC%E3%82%BF%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E%E4%BB%96%E3%81%AE%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Choose Language Hide Translation Bar
Importing data using the JMP Python environment with ODBC in JMP® 18 and later

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: Pandaspyodbc, 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;')   

 

Last Modified: Sep 16, 2024 9:00 AM