SQLAlchemy JDBCAPI

2.2.1 · active · verified Thu Apr 16

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.

Common errors

Warnings

Install

Imports

Quickstart

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.

import os
from sqlalchemy import create_engine, text

# Ensure JAVA_HOME is set for JPype to find a JVM
# Example: os.environ['JAVA_HOME'] = '/path/to/your/jdk'

# Example for PostgreSQL via JDBC
# Replace with your actual database details or environment variables
DB_USER = os.environ.get('JDBC_DB_USER', 'your_user')
DB_PASS = os.environ.get('JDBC_DB_PASS', 'your_password')
DB_HOST = os.environ.get('JDBC_DB_HOST', 'localhost')
DB_PORT = os.environ.get('JDBC_DB_PORT', '5432')
DB_NAME = os.environ.get('JDBC_DB_NAME', 'your_database')

# The dialect string uses 'jdbcapi+' followed by the driver name
# For PostgreSQL, it's 'postgresql'. For MySQL, 'mysql', etc.
jdbc_url = f"jdbcapi+postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

try:
    engine = create_engine(jdbc_url)

    with engine.connect() as connection:
        result = connection.execute(text("SELECT 1"))
        print(f"Connection successful, result: {result.scalar()}")

    # Example with asyncio (requires sqlalchemy-jdbcapi>=2.2.1 and asyncpg, aiomysql, etc.)
    # from sqlalchemy.ext.asyncio import create_async_engine
    # async_jdbc_url = f"jdbcapi+postgresql+asyncpg://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
    # async_engine = create_async_engine(async_jdbc_url)
    # async def run_async_query():
    #     async with async_engine.connect() as conn:
    #         result = await conn.execute(text("SELECT 2"))
    #         print(f"Async connection successful, result: {result.scalar()}")
    # import asyncio
    # asyncio.run(run_async_query())

except Exception as e:
    print(f"Error connecting to database: {e}")

finally:
    # JPype requires explicit JVM shutdown in some contexts or when done
    try:
        from jpype import isJVMStarted, shutdownJVM
        if isJVMStarted():
            shutdownJVM()
            print("JVM shut down.")
    except ImportError:
        pass # JPype might not be installed or available

view raw JSON →