JayDeBeApi (baztian/jaydebeapi)

JayDeBeApi

https://github.com/baztian/jaydebeapi
Admin
JayDeBeApi is a Python module that bridges JDBC database drivers to Python DB-API, allowing...

Tokens:8,199
Snippets:59
Trust Score:6.8
Update:1 week ago
Show doc for...
Context Summary (auto-generated)
Raw
# JayDeBeApi

JayDeBeApi is a Python module that bridges Java JDBC database drivers to Python's DB-API 2.0 interface. It enables Python applications to connect to any database that has a Java JDBC driver, including Oracle, PostgreSQL, MySQL, IBM DB2, Microsoft SQL Server, SQLite, and many others. The library works on both standard Python (CPython) using JPype for Java integration, as well as on Jython where it directly accesses the Java runtime.

The module provides a uniform interface for accessing diverse databases through their JDBC drivers, making it particularly valuable when working with enterprise databases that may only have robust JDBC support. JayDeBeApi handles the complexity of type conversions between Java and Python data types, exception handling, and connection lifecycle management while presenting a familiar DB-API 2.0 compliant interface to Python developers.

## jaydebeapi.connect

The `connect` function establishes a connection to a database using a specified JDBC driver and returns a DB-API 2.0 compliant Connection object. It accepts the fully qualified Java class name of the JDBC driver, the JDBC connection URL, optional authentication credentials (as a list or dictionary), optional paths to JDBC driver JAR files, and optional paths to native shared libraries required by the driver.

```python
import jaydebeapi

# Basic connection with username/password as a list
conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",           # JDBC driver class name
    "jdbc:hsqldb:mem:testdb",          # JDBC connection URL
    ["SA", ""],                         # [username, password]
    "/path/to/hsqldb.jar"              # Path to JDBC driver JAR
)

# Connection with properties dictionary (for additional connection options)
conn = jaydebeapi.connect(
    "org.postgresql.Driver",
    "jdbc:postgresql://localhost:5432/mydb",
    {
        "user": "dbuser",
        "password": "secret",
        "ssl": "true",
        "sslmode": "require"
    },
    "/path/to/postgresql-42.2.18.jar"
)

# Connection with multiple JAR files
conn = jaydebeapi.connect(
    "oracle.jdbc.OracleDriver",
    "jdbc:oracle:thin:@localhost:1521:xe",
    ["system", "oracle"],
    ["/path/to/ojdbc8.jar", "/path/to/orai18n.jar"]
)

# Using context manager for automatic resource cleanup
with jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""],
    "/path/to/hsqldb.jar"
) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        results = cursor.fetchall()
```

## Connection.cursor

The `cursor` method creates a new Cursor object for executing SQL statements and fetching results. The cursor provides methods for executing queries, fetching data, and accessing result metadata. Cursors should be closed when no longer needed or used with context managers for automatic cleanup.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)

# Create a cursor
cursor = conn.cursor()

# Execute DDL statement
cursor.execute('''
    CREATE TABLE customers (
        id INTEGER PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255),
        created_at TIMESTAMP
    )
''')

# Always close cursor when done
cursor.close()

# Or use context manager
with conn.cursor() as cursor:
    cursor.execute("SELECT COUNT(*) FROM customers")
    count = cursor.fetchone()[0]
    print(f"Total customers: {count}")

conn.close()
```

## Cursor.execute

The `execute` method executes a single SQL statement with optional parameter substitution using the qmark parameter style (question marks as placeholders). Parameters are passed as a tuple or list and are safely bound to prevent SQL injection. The method prepares the statement and executes it, making the results available through fetch methods.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name VARCHAR(100),
        price DECIMAL(10,2),
        quantity INTEGER
    )
''')

# Insert with parameters (qmark style - use ? placeholders)
cursor.execute(
    "INSERT INTO products (id, name, price, quantity) VALUES (?, ?, ?, ?)",
    (1, "Widget", 19.99, 100)
)

# Insert another row
cursor.execute(
    "INSERT INTO products (id, name, price, quantity) VALUES (?, ?, ?, ?)",
    (2, "Gadget", 29.99, 50)
)

# Select with parameters
cursor.execute(
    "SELECT * FROM products WHERE price > ? AND quantity >= ?",
    (15.00, 50)
)

# Access results
for row in cursor.fetchall():
    print(f"ID: {row[0]}, Name: {row[1]}, Price: ${row[2]}, Qty: {row[3]}")

# Check rowcount for DML operations
cursor.execute("UPDATE products SET quantity = ? WHERE id = ?", (75, 1))
print(f"Rows updated: {cursor.rowcount}")  # Output: Rows updated: 1

cursor.close()
conn.close()
```

## Cursor.executemany

The `executemany` method executes the same SQL statement multiple times with different parameter sets, which is efficient for bulk insert or update operations. It accepts a SQL statement and a sequence of parameter sequences. The method uses JDBC batch processing for better performance.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE orders (
        order_id INTEGER PRIMARY KEY,
        customer_name VARCHAR(100),
        amount DECIMAL(10,2),
        status VARCHAR(20)
    )
''')

# Bulk insert using executemany
orders_data = [
    (1, "Alice Smith", 150.00, "pending"),
    (2, "Bob Johnson", 275.50, "shipped"),
    (3, "Carol White", 89.99, "delivered"),
    (4, "David Brown", 432.00, "pending"),
    (5, "Eve Davis", 199.99, "processing")
]

cursor.executemany(
    "INSERT INTO orders (order_id, customer_name, amount, status) VALUES (?, ?, ?, ?)",
    orders_data
)

print(f"Rows inserted: {cursor.rowcount}")  # Output: Rows inserted: 5

# Bulk update
status_updates = [
    ("shipped", 1),
    ("shipped", 4)
]

cursor.executemany(
    "UPDATE orders SET status = ? WHERE order_id = ?",
    status_updates
)

print(f"Rows updated: {cursor.rowcount}")  # Output: Rows updated: 2

cursor.close()
conn.close()
```

## Cursor.fetchone

The `fetchone` method retrieves the next row from the result set as a tuple, or returns None when no more rows are available. Each call advances the cursor position by one row. This method is ideal for processing large result sets one row at a time to minimize memory usage.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE employees (
        emp_id INTEGER PRIMARY KEY,
        name VARCHAR(100),
        department VARCHAR(50),
        salary DECIMAL(10,2)
    )
''')

# Insert sample data
employees = [
    (1, "John Doe", "Engineering", 75000.00),
    (2, "Jane Smith", "Marketing", 65000.00),
    (3, "Bob Wilson", "Engineering", 80000.00)
]
cursor.executemany(
    "INSERT INTO employees VALUES (?, ?, ?, ?)",
    employees
)

# Fetch rows one at a time
cursor.execute("SELECT * FROM employees ORDER BY salary DESC")

print("Processing employees:")
while True:
    row = cursor.fetchone()
    if row is None:
        break
    emp_id, name, dept, salary = row
    print(f"  {name} ({dept}): ${salary:,.2f}")

# Output:
# Processing employees:
#   Bob Wilson (Engineering): $80,000.00
#   John Doe (Engineering): $75,000.00
#   Jane Smith (Marketing): $65,000.00

cursor.close()
conn.close()
```

## Cursor.fetchmany

The `fetchmany` method retrieves the next batch of rows from the result set, with the number of rows specified by the size parameter (defaults to `cursor.arraysize`). It returns a list of tuples representing the rows, or an empty list when no more rows are available. This is useful for paginated processing of large result sets.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE log_entries (
        id INTEGER PRIMARY KEY,
        timestamp TIMESTAMP,
        level VARCHAR(10),
        message VARCHAR(500)
    )
''')

# Insert sample log data
logs = [(i, f"2024-01-{i:02d} 10:00:00", "INFO", f"Log message {i}") for i in range(1, 21)]
cursor.executemany("INSERT INTO log_entries VALUES (?, ?, ?, ?)", logs)

# Process in batches of 5
cursor.execute("SELECT * FROM log_entries ORDER BY id")

batch_num = 0
while True:
    batch = cursor.fetchmany(5)
    if not batch:
        break
    batch_num += 1
    print(f"Batch {batch_num}:")
    for row in batch:
        print(f"  [{row[0]}] {row[2]}: {row[3]}")

# Set default batch size via arraysize
cursor.arraysize = 10
cursor.execute("SELECT COUNT(*) as cnt FROM log_entries")
results = cursor.fetchmany()  # Uses arraysize=10

cursor.close()
conn.close()
```

## Cursor.fetchall

The `fetchall` method retrieves all remaining rows from the result set as a list of tuples. This is convenient for smaller result sets where you need all data at once. For large result sets, consider using `fetchone` or `fetchmany` to avoid memory issues.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE inventory (
        sku VARCHAR(20) PRIMARY KEY,
        product_name VARCHAR(100),
        category VARCHAR(50),
        in_stock INTEGER,
        reorder_level INTEGER
    )
''')

items = [
    ("SKU001", "Laptop", "Electronics", 25, 10),
    ("SKU002", "Mouse", "Electronics", 150, 50),
    ("SKU003", "Notebook", "Office", 500, 100),
    ("SKU004", "Pen Set", "Office", 75, 200),
    ("SKU005", "Monitor", "Electronics", 8, 15)
]
cursor.executemany("INSERT INTO inventory VALUES (?, ?, ?, ?, ?)", items)

# Fetch all rows at once
cursor.execute("SELECT * FROM inventory WHERE in_stock < reorder_level")
low_stock_items = cursor.fetchall()

print("Low Stock Alert:")
for item in low_stock_items:
    sku, name, category, stock, reorder = item
    print(f"  {sku} - {name}: {stock} units (reorder at {reorder})")

# Output:
# Low Stock Alert:
#   SKU004 - Pen Set: 75 units (reorder at 200)
#   SKU005 - Monitor: 8 units (reorder at 15)

# Using fetchall with aggregation
cursor.execute('''
    SELECT category, SUM(in_stock) as total
    FROM inventory
    GROUP BY category
''')
for category, total in cursor.fetchall():
    print(f"{category}: {total} items")

cursor.close()
conn.close()
```

## Cursor.description

The `description` attribute provides metadata about the columns in the result set after executing a query. It returns a sequence of 7-item tuples, each containing: name, type_code, display_size, internal_size, precision, scale, and null_ok. This is useful for dynamic result processing and schema inspection.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE transactions (
        txn_id INTEGER PRIMARY KEY,
        account_number VARCHAR(20),
        amount DECIMAL(12,2),
        txn_date DATE,
        processed BOOLEAN
    )
''')

cursor.execute("INSERT INTO transactions VALUES (1, 'ACC-001', 1500.50, '2024-01-15', true)")

# Execute query to get description
cursor.execute("SELECT * FROM transactions")

# Access column metadata
print("Column Information:")
print("-" * 60)
for col in cursor.description:
    name = col[0]
    type_code = col[1]
    display_size = col[2]
    precision = col[4]
    scale = col[5]
    nullable = col[6]
    print(f"Column: {name}")
    print(f"  Type: {type_code}, Size: {display_size}")
    print(f"  Precision: {precision}, Scale: {scale}, Nullable: {nullable}")
    print()

# Build dynamic column name list
column_names = [col[0] for col in cursor.description]
print(f"Columns: {column_names}")
# Output: Columns: ['TXN_ID', 'ACCOUNT_NUMBER', 'AMOUNT', 'TXN_DATE', 'PROCESSED']

# Fetch data as dictionaries
cursor.execute("SELECT * FROM transactions")
rows = cursor.fetchall()
columns = [col[0] for col in cursor.description]

for row in rows:
    row_dict = dict(zip(columns, row))
    print(row_dict)

cursor.close()
conn.close()
```

## Connection.commit and Connection.rollback

The `commit` method commits the current transaction, making all changes permanent, while `rollback` undoes all changes made since the last commit. These methods provide transaction control for data integrity. Note that some databases may have auto-commit enabled by default.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE accounts (
        account_id INTEGER PRIMARY KEY,
        owner VARCHAR(100),
        balance DECIMAL(12,2)
    )
''')
cursor.execute("INSERT INTO accounts VALUES (1, 'Alice', 1000.00)")
cursor.execute("INSERT INTO accounts VALUES (2, 'Bob', 500.00)")
conn.commit()  # Save initial data

# Transaction: Transfer money from Alice to Bob
transfer_amount = 200.00

try:
    # Debit from Alice
    cursor.execute(
        "UPDATE accounts SET balance = balance - ? WHERE account_id = ?",
        (transfer_amount, 1)
    )

    # Verify Alice has sufficient funds
    cursor.execute("SELECT balance FROM accounts WHERE account_id = 1")
    alice_balance = cursor.fetchone()[0]

    if alice_balance < 0:
        raise ValueError("Insufficient funds")

    # Credit to Bob
    cursor.execute(
        "UPDATE accounts SET balance = balance + ? WHERE account_id = ?",
        (transfer_amount, 2)
    )

    # Commit transaction
    conn.commit()
    print(f"Transfer of ${transfer_amount} completed successfully")

except Exception as e:
    # Rollback on any error
    conn.rollback()
    print(f"Transfer failed, rolled back: {e}")

# Verify final balances
cursor.execute("SELECT owner, balance FROM accounts ORDER BY account_id")
for owner, balance in cursor.fetchall():
    print(f"{owner}: ${balance}")

# Output:
# Transfer of $200.0 completed successfully
# Alice: $800.00
# Bob: $700.00

cursor.close()
conn.close()
```

## Exception Classes

JayDeBeApi provides DB-API 2.0 compliant exception classes for handling database errors. The exception hierarchy includes Error (base class), DatabaseError (for database-related errors), InterfaceError (for interface problems), and specific subclasses like IntegrityError, OperationalError, and ProgrammingError.

```python
import jaydebeapi

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE users (
        user_id INTEGER PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100)
    )
''')
cursor.execute("INSERT INTO users VALUES (1, 'johndoe', 'john@example.com')")
conn.commit()

# Handle duplicate key violation
try:
    cursor.execute("INSERT INTO users VALUES (1, 'janedoe', 'jane@example.com')")
except jaydebeapi.IntegrityError as e:
    print(f"Integrity error (duplicate key): {e}")
except jaydebeapi.DatabaseError as e:
    print(f"Database error: {e}")

# Handle syntax errors
try:
    cursor.execute("SELEC * FORM users")  # Typo in SQL
except jaydebeapi.ProgrammingError as e:
    print(f"Programming error (bad SQL): {e}")
except jaydebeapi.DatabaseError as e:
    print(f"Database error: {e}")

# Handle connection errors
try:
    bad_conn = jaydebeapi.connect(
        "com.nonexistent.Driver",
        "jdbc:fake://localhost/db",
        ["user", "pass"]
    )
except jaydebeapi.InterfaceError as e:
    print(f"Interface error (driver not found): {e}")
except jaydebeapi.Error as e:
    print(f"General error: {e}")

# Use exception classes from connection object
try:
    cursor.execute("INSERT INTO users VALUES (1, 'duplicate', 'dup@test.com')")
except conn.IntegrityError as e:
    print(f"Caught via connection attribute: {e}")

cursor.close()
conn.close()
```

## Type Objects and Constructors

JayDeBeApi provides DB-API 2.0 type objects (STRING, BINARY, NUMBER, DATETIME, ROWID) for type comparison and constructor functions (Date, Time, Timestamp, Binary) for creating database-compatible values. These are useful for type checking in result metadata and preparing parameter values.

```python
import jaydebeapi
from datetime import date, time, datetime

conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""]
)
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE events (
        event_id INTEGER PRIMARY KEY,
        event_name VARCHAR(100),
        event_date DATE,
        start_time TIME,
        created_at TIMESTAMP,
        event_data BLOB
    )
''')

# Use type constructors for parameters
event_date = jaydebeapi.Date(2024, 6, 15)
start_time = jaydebeapi.Time(14, 30, 0)
created_at = jaydebeapi.Timestamp(2024, 1, 10, 9, 0, 0)
binary_data = jaydebeapi.Binary(b"Event metadata blob content")

cursor.execute('''
    INSERT INTO events (event_id, event_name, event_date, start_time, created_at, event_data)
    VALUES (?, ?, ?, ?, ?, ?)
''', (1, "Conference", event_date, start_time, created_at, binary_data))

conn.commit()

# Query and check types using type objects
cursor.execute("SELECT * FROM events")
row = cursor.fetchone()

# Compare column types with DB-API type objects
for i, col_desc in enumerate(cursor.description):
    col_name = col_desc[0]
    col_type = col_desc[1]

    if col_type == jaydebeapi.STRING:
        print(f"{col_name}: STRING type")
    elif col_type == jaydebeapi.NUMBER:
        print(f"{col_name}: NUMBER type")
    elif col_type == jaydebeapi.DATETIME:
        print(f"{col_name}: DATETIME type")
    elif col_type == jaydebeapi.DATE:
        print(f"{col_name}: DATE type")
    elif col_type == jaydebeapi.TIME:
        print(f"{col_name}: TIME type")
    elif col_type == jaydebeapi.BINARY:
        print(f"{col_name}: BINARY type")

# Create timestamps from ticks (seconds since epoch)
import time
now_ticks = time.time()
ts_from_ticks = jaydebeapi.TimestampFromTicks(now_ticks)
date_from_ticks = jaydebeapi.DateFromTicks(now_ticks)
time_from_ticks = jaydebeapi.TimeFromTicks(now_ticks)

cursor.close()
conn.close()
```

## Common Database Connection Examples

This section demonstrates connecting to various popular databases using their JDBC drivers. Each example shows the driver class name, connection URL format, and typical connection patterns.

```python
import jaydebeapi

# PostgreSQL
pg_conn = jaydebeapi.connect(
    "org.postgresql.Driver",
    "jdbc:postgresql://localhost:5432/mydatabase",
    ["postgres", "password"],
    "/path/to/postgresql-42.6.0.jar"
)

# MySQL
mysql_conn = jaydebeapi.connect(
    "com.mysql.cj.jdbc.Driver",
    "jdbc:mysql://localhost:3306/mydatabase?useSSL=false&serverTimezone=UTC",
    ["root", "password"],
    "/path/to/mysql-connector-java-8.0.33.jar"
)

# Oracle
oracle_conn = jaydebeapi.connect(
    "oracle.jdbc.OracleDriver",
    "jdbc:oracle:thin:@localhost:1521:ORCL",
    ["system", "oracle"],
    "/path/to/ojdbc11.jar"
)

# Microsoft SQL Server
mssql_conn = jaydebeapi.connect(
    "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "jdbc:sqlserver://localhost:1433;databaseName=mydb;encrypt=false",
    ["sa", "YourPassword123"],
    "/path/to/mssql-jdbc-12.4.0.jre11.jar"
)

# SQLite (using Xerial JDBC driver)
sqlite_conn = jaydebeapi.connect(
    "org.sqlite.JDBC",
    "jdbc:sqlite:/path/to/database.db",
    [],  # SQLite typically doesn't need credentials
    "/path/to/sqlite-jdbc-3.43.0.0.jar"
)

# IBM DB2
db2_conn = jaydebeapi.connect(
    "com.ibm.db2.jcc.DB2Driver",
    "jdbc:db2://localhost:50000/SAMPLE",
    ["db2admin", "password"],
    "/path/to/db2jcc4.jar"
)

# H2 Database (in-memory)
h2_conn = jaydebeapi.connect(
    "org.h2.Driver",
    "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1",
    ["sa", ""],
    "/path/to/h2-2.2.224.jar"
)

# HSQLDB (in-memory) - commonly used for testing
hsql_conn = jaydebeapi.connect(
    "org.hsqldb.jdbcDriver",
    "jdbc:hsqldb:mem:testdb",
    ["SA", ""],
    "/path/to/hsqldb.jar"
)

# Using CLASSPATH environment variable instead of specifying JAR
# Set CLASSPATH=/path/to/driver.jar before running Python
conn_via_classpath = jaydebeapi.connect(
    "org.postgresql.Driver",
    "jdbc:postgresql://localhost/mydb",
    ["user", "pass"]
    # No jar parameter - will use CLASSPATH
)
```

---

JayDeBeApi serves as an essential tool for Python developers who need to access databases through JDBC drivers, particularly enterprise databases like Oracle, IBM DB2, and others that may have limited native Python driver support. The library's DB-API 2.0 compliance ensures compatibility with existing Python database code and popular tools like pandas, SQLAlchemy (with appropriate dialects), and other data processing frameworks.

The library is particularly valuable in enterprise environments where Java JDBC drivers are the primary or only supported database connectivity option, allowing Python applications to integrate seamlessly with existing database infrastructure. By supporting both CPython (via JPype) and Jython, JayDeBeApi provides flexibility for various deployment scenarios, from standalone scripts to applications running within Java application servers.