使用 Python 通过 ODBC 或 JDBC 访问 IRIS 数据库

import pandas as pdimport pyodbcimport jaydebeapiimport jpypedef my_function(jdbc_used)    # some other code to create the connection goes here    cursor.execute(query_string)    if jdbc_used:        # fetch the results, convert java.lang.string in the data to python str        # (java.lang.string is returned "(p,a,i,n,i,n,t,h,e,r,e,a,r)" convert to str type "painintherear"        results = []        for row in cursor.fetchall():            converted_row = [str(item) if isinstance(item, jpype.java.lang.string) else item for item in row]            results.append(converted_row)        # get the column names and ensure they are python strings         column_names = [str(col[0]) for col in cursor.description]        # create the dataframe        df = pd.dataframe.from_records(results, columns=column_names)        # check the results        print(df.head().to_string())    else:          # i was also testing odbc        # for very large result sets get results in chunks using cursor.fetchmany(). or fetchall()        results = cursor.fetchall()        # get the column names        column_names = [column[0] for column in cursor.description]        # create the dataframe        df = pd.dataframe.from_records(results, columns=column_names)    # do stuff with your dataframe

def create_connection(connection_string, password):    connection = none    try:        # print(f"connecting to {connection_string}")        connection = pyodbc.connect(connection_string + ";pwd=" + password)        # ensure strings are read correctly        connection.setdecoding(pyodbc.sql_char, encoding="utf8")        connection.setdecoding(pyodbc.sql_wchar, encoding="utf8")        connection.setencoding(encoding="utf8")    except pyodbc.error as e:        print(f"the error '{e}' occurred")    return connection

$ /usr/libexec/java_home -vmatching java virtual machines (2):    23 (arm64) "oracle corporation" - "java se 23" /library/java/javavirtualmachines/jdk-23.jdk/contents/home    1.8.421.09 (arm64) "oracle corporation" - "java" /library/internet plug-ins/javaappletplugin.plugin/contents/home/library/java/javavirtualmachines/jdk-23.jdk/contents/home$ echo $shell/opt/homebrew/bin/bash$ vi ~/.bash_profile

export java_home=$(/usr/libexec/java_home -v 23)export path=$java_home/bin:$path

$ ls $home/*.jar/users/myname/intersystems-jdbc-3.8.4.jar

import osimport datetimefrom datetime import date, time, datetime, timedeltaimport pandas as pdimport pyodbcimport jaydebeapiimport jpypedef jdbc_create_connection(jdbc_url, jdbc_username, jdbc_password):    # Path to JDBC driver    jdbc_driver_path = '/Users/yourname/intersystems-jdbc-3.8.4.jar'    # Ensure JAVA_HOME is set    os.environ['JAVA_HOME']='/Library/Java/JavaVirtualMachines/jdk-23.jdk/Contents/Home'    os.environ['CLASSPATH'] = jdbc_driver_path    # Start the JVM (if not already running)    if not jpype.isJVMStarted():        jpype.startJVM(jpype.getDefaultJVMPath(), classpath=[jdbc_driver_path])    # Connect to the database    connection = None    try:        connection = jaydebeapi.connect("com.intersystems.jdbc.IRISDriver",                                  jdbc_url,                                  [jdbc_username, jdbc_password],                                  jdbc_driver_path)        print("Connection successful")    except Exception as e:        print(f"An error occurred: {e}")    return connectiondef odbc_create_connection(connection_string):    connection = None    try:        # print(f"Connecting to {connection_string}")        connection = pyodbc.connect(connection_string)        # Ensure strings are read correctly        connection.setdecoding(pyodbc.SQL_CHAR, encoding="utf8")        connection.setdecoding(pyodbc.SQL_WCHAR, encoding="utf8")        connection.setencoding(encoding="utf8")    except pyodbc.Error as e:        print(f"The error '{e}' occurred")    return connection# Parametersodbc_driver = "InterSystems ODBC"odbc_host = "your_host"odbc_port = "51773"odbc_namespace = "your_namespace"odbc_username = "username"odbc_password = "password"jdbc_host = "your_host"jdbc_port = "51773"jdbc_namespace = "your_namespace"jdbc_username = "username"jdbc_password = "password"# Create connection and create chartsjdbc_used = Trueif jdbc_used:    print("Using JDBC")    jdbc_url = f"jdbc:IRIS://{jdbc_host}:{jdbc_port}/{jdbc_namespace}?useUnicode=true&characterEncoding=UTF-8"    connection = jdbc_create_connection(jdbc_url, jdbc_username, jdbc_password)else:    print("Using ODBC")    connection_string = f"Driver={odbc_driver};Host={odbc_host};Port={odbc_port};Database={odbc_namespace};UID={odbc_username};PWD={odbc_password}"    connection = odbc_create_connection(connection_string)if connection is None:    print("Unable to connect to IRIS")    exit()cursor = connection.cursor()site = "SAMPLE"table_name = "your.TableNAME"desired_columns = [    "RunDate",    "ActiveUsersCount",    "EpisodeCountEmergency",    "EpisodeCountInpatient",    "EpisodeCountOutpatient",    "EpisodeCountTotal",    "AppointmentCount",    "PrintCountTotal",    "site",]# Construct the column selection part of the querycolumn_selection = ", ".join(desired_columns)query_string = f"SELECT {column_selection} FROM {table_name} WHERE Site = '{site}'"print(query_string)cursor.execute(query_string)if jdbc_used:    # Fetch the results    results = []    for row in cursor.fetchall():        converted_row = [str(item) if isinstance(item, jpype.java.lang.String) else item for item in row]        results.append(converted_row)    # Get the column names and ensure they are Python strings (java.lang.String is returned "(p,a,i,n,i,n,t,h,e,a,r,s,e)"    column_names = [str(col[0]) for col in cursor.description]    # Create the dataframe    df = pd.DataFrame.from_records(results, columns=column_names)    print(df.head().to_string())else:    # For very large result sets get results in chunks using cursor.fetchmany(). or fetchall()    results = cursor.fetchall()    # Get the column names    column_names = [column[0] for column in cursor.description]    # Create the dataframe    df = pd.DataFrame.from_records(results, columns=column_names)    print(df.head().to_string())# # Build charts for a site# cf.build_7_day_rolling_average_chart(site, cursor, jdbc_used)cursor.close()connection.close()# Shutdown the JVM (if you started it)# jpype.shutdownJVM()