{"id":9331,"library":"sqlalchemy-jdbcapi","title":"SQLAlchemy JDBCAPI","description":"SQLAlchemy-JDBCAPI is a modern SQLAlchemy dialect that provides native DB-API 2.0 implementation for JDBC connections using JPype, and ODBC connections via pyodbc. It supports a wide range of databases including PostgreSQL, MySQL, SQL Server, Oracle, and many others, with features like automatic JDBC driver management. The current version is 2.2.1, and it maintains an active release cadence with frequent updates and new features such as full asyncio support.","status":"active","version":"2.2.1","language":"en","source_language":"en","source_url":"https://github.com/daneshpatel/sqlalchemy-jdbcapi","tags":["SQLAlchemy","JDBC","ODBC","database","data-access","ORM","async","JPype"],"install":[{"cmd":"pip install sqlalchemy-jdbcapi","lang":"bash","label":"Basic Installation"},{"cmd":"pip install sqlalchemy-jdbcapi[pyodbc]","lang":"bash","label":"Installation with ODBC support"}],"dependencies":[{"reason":"Required for all JDBC connections and core functionality.","package":"JPype1","optional":false},{"reason":"Peer dependency; this library is an SQLAlchemy dialect.","package":"SQLAlchemy","optional":false},{"reason":"Optional: Required for using ODBC-based connections.","package":"pyodbc","optional":true}],"imports":[{"note":"Required for async operations (introduced in v2.2.1).","symbol":"AsyncConnection","correct":"from sqlalchemy_jdbcapi.async_dbapi import AsyncConnection"},{"note":"Required for async operations (introduced in v2.2.1).","symbol":"AsyncCursor","correct":"from sqlalchemy_jdbcapi.async_dbapi import AsyncCursor"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text\n\n# Ensure JAVA_HOME is set for JPype to find a JVM\n# Example: os.environ['JAVA_HOME'] = '/path/to/your/jdk'\n\n# Example for PostgreSQL via JDBC\n# Replace with your actual database details or environment variables\nDB_USER = os.environ.get('JDBC_DB_USER', 'your_user')\nDB_PASS = os.environ.get('JDBC_DB_PASS', 'your_password')\nDB_HOST = os.environ.get('JDBC_DB_HOST', 'localhost')\nDB_PORT = os.environ.get('JDBC_DB_PORT', '5432')\nDB_NAME = os.environ.get('JDBC_DB_NAME', 'your_database')\n\n# The dialect string uses 'jdbcapi+' followed by the driver name\n# For PostgreSQL, it's 'postgresql'. For MySQL, 'mysql', etc.\njdbc_url = f\"jdbcapi+postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}\"\n\ntry:\n    engine = create_engine(jdbc_url)\n\n    with engine.connect() as connection:\n        result = connection.execute(text(\"SELECT 1\"))\n        print(f\"Connection successful, result: {result.scalar()}\")\n\n    # Example with asyncio (requires sqlalchemy-jdbcapi>=2.2.1 and asyncpg, aiomysql, etc.)\n    # from sqlalchemy.ext.asyncio import create_async_engine\n    # async_jdbc_url = f\"jdbcapi+postgresql+asyncpg://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}\"\n    # async_engine = create_async_engine(async_jdbc_url)\n    # async def run_async_query():\n    #     async with async_engine.connect() as conn:\n    #         result = await conn.execute(text(\"SELECT 2\"))\n    #         print(f\"Async connection successful, result: {result.scalar()}\")\n    # import asyncio\n    # asyncio.run(run_async_query())\n\nexcept Exception as e:\n    print(f\"Error connecting to database: {e}\")\n\nfinally:\n    # JPype requires explicit JVM shutdown in some contexts or when done\n    try:\n        from jpype import isJVMStarted, shutdownJVM\n        if isJVMStarted():\n            shutdownJVM()\n            print(\"JVM shut down.\")\n    except ImportError:\n        pass # JPype might not be installed or available\n","lang":"python","description":"This quickstart demonstrates how to establish a connection to a PostgreSQL database using `sqlalchemy-jdbcapi` via a JDBC driver. It uses `create_engine` with the `jdbcapi+postgresql` dialect. Ensure you have a Java Runtime Environment (JRE) or Java Development Kit (JDK) installed and accessible to JPype, typically by setting the `JAVA_HOME` environment variable. The example includes a basic synchronous query and comments on how to extend for asyncio support."},"warnings":[{"fix":"Upgrade to `sqlalchemy-jdbcapi` 2.x and review all connection URLs and any direct DB-API interactions. Connection strings now typically start with `jdbcapi+<driver_name>://`.","message":"Version 2.0.0 of `sqlalchemy-jdbcapi` introduced a complete rewrite of its core infrastructure, transitioning to a native DB-API 2.0 implementation built on JPype. This fundamentally changed how the dialect interacts with databases compared to 1.x versions.","severity":"breaking","affected_versions":"1.x migrating to 2.x"},{"fix":"Ensure a compatible JRE/JDK (Java 8 or higher is generally recommended) is installed and that the `JAVA_HOME` environment variable is correctly set to the JDK/JRE installation directory. Alternatively, configure JPype to find the JVM shared library manually if `JAVA_HOME` is not an option.","message":"`sqlalchemy-jdbcapi` relies on `JPype` for JDBC connectivity, which requires a Java Virtual Machine (JVM) to be present and discoverable on the system. Common issues include `RuntimeError: No JVM shared library found` or `JClassException` for missing Java classes.","severity":"gotcha","affected_versions":"All 2.x versions"},{"fix":"For network issues, ensure proper proxy settings or allowlist Maven Central URLs. For specific drivers, you can manually provide JDBC JAR files by placing them in a directory specified by the `SQL_ALCHEMY_JDBCAPI_JVM_CLASSPATH` environment variable or using the `jdbcapi.driver_path` argument in `create_engine`.","message":"The library automatically downloads JDBC drivers from Maven Central by default. This process can fail due to network restrictions (e.g., corporate proxies, firewalls), or if specific driver versions are required but not available or correctly resolved.","severity":"gotcha","affected_versions":"All 2.x versions"},{"fix":"Upgrade `sqlalchemy-jdbcapi` to version `2.2.1` or newer to leverage its asyncio capabilities. Ensure that you are also using an appropriate SQLAlchemy async driver (e.g., `asyncpg` for PostgreSQL, `aiomysql` for MySQL) in your connection string (e.g., `jdbcapi+postgresql+asyncpg://...`).","message":"Full asyncio support, including `AsyncConnection` and `AsyncCursor` classes, was introduced in version 2.2.1. Code attempting to use `await` with `sqlalchemy-jdbcapi` on older versions will fail or operate synchronously.","severity":"gotcha","affected_versions":"2.0.0 - 2.2.0"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"fix":"Install a Java Development Kit (JDK) or Java Runtime Environment (JRE) (Java 8+ is recommended) and set the `JAVA_HOME` environment variable to point to its installation directory.","cause":"JPype, used by sqlalchemy-jdbcapi, cannot locate a Java Virtual Machine (JVM) on your system.","error":"RuntimeError: No JVM shared library found. Please install a JDK or JRE and set JAVA_HOME."},{"fix":"Verify that the necessary JDBC driver JAR file is available. If using automatic driver management, check network connectivity to Maven Central. If providing manually, ensure the JAR file is placed in a directory listed in `SQL_ALCHEMY_JDBCAPI_JVM_CLASSPATH` or specified via `jdbcapi.driver_path`.","cause":"The JDBC driver class specified in your connection string (or the one automatically chosen) could not be loaded by the JVM, usually because the driver JAR file is missing or corrupted, or its path is incorrect.","error":"jpype.JClass.JClassException: Class <driver_class_name> not found."},{"fix":"Ensure `sqlalchemy-jdbcapi` is correctly installed: `pip install sqlalchemy-jdbcapi`. If using ODBC, make sure `pip install sqlalchemy-jdbcapi[pyodbc]` was used.","cause":"SQLAlchemy cannot find the registered dialect for `jdbcapi+<driver_name>`. This typically happens if `sqlalchemy-jdbcapi` is not installed or incorrectly installed.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:jdbcapi+<driver_name>"},{"fix":"Ensure `pyodbc` is installed (`pip install sqlalchemy-jdbcapi[pyodbc]`) and that your ODBC DSN or driver configuration on the operating system is valid for the specified database.","cause":"This error occurs when using the `jdbcapi+pyodbc` dialect and the underlying ODBC driver or DSN is not correctly configured on the system.","error":"sqlalchemy.exc.InterfaceError: (pyodbc.Error) ('01000', \"[01000] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)\")"}]}