MonetDB Python API

Warnings

• breaking The `pymonetdb` library is the official and recommended Python client for MonetDB, replacing the older `python-monetdb` module. When migrating, ensure all `import monetdb` statements are updated to `import pymonetdb`.
  Fix: Replace `import monetdb` with `import pymonetdb`.
• gotcha Closing a database connection without an explicit `conn.commit()` will automatically trigger an implicit `rollback()` for any pending transactions. Ensure `commit()` is called for desired persistence.
  Fix: Explicitly call `conn.commit()` before `conn.close()` if changes should be saved.
• gotcha Using the `COPY INTO ... ON CLIENT` SQL statement for file transfers without proper security measures can lead to critical vulnerabilities. It allows the MonetDB server to request the client to open and transfer local files. Always register a `pymonetdb.SafeDirectoryHandler` to restrict file access to an allowed directory.
  Fix: Implement `pymonetdb.set_uploader()` and `pymonetdb.set_downloader()` with a `SafeDirectoryHandler` instance configured to a secure, restricted path.
• gotcha For optimal performance when fetching large result sets, consider adjusting `cursor.arraysize`. The default batch fetching behavior might not be efficient for all workloads, and increasing `arraysize` can reduce network round trips.
  Fix: Set `cursor.arraysize = N` (where N is the desired number of rows to fetch per batch) after creating the cursor and before fetching results.

Install

• pip install pymonetdb Install latest version

Imports

• connect
  wrong:

  import monetdb
  conn = monetdb.connect(...)

  correct:

  import pymonetdb
  conn = pymonetdb.connect(...)

  The old 'python-monetdb' library was replaced by 'pymonetdb'.
• Error

  from pymonetdb import Error

Quickstart

This quickstart demonstrates how to establish a connection to a MonetDB database, execute a simple SQL query, and fetch results using `pymonetdb.connect()` and cursor operations. It emphasizes using environment variables for sensitive connection parameters and proper error handling with connection closure.

import pymonetdb
import os

# Configure connection details using environment variables for security
hostname = os.environ.get('MONETDB_HOSTNAME', 'localhost')
port = int(os.environ.get('MONETDB_PORT', 50000))
username = os.environ.get('MONETDB_USERNAME', 'monetdb')
password = os.environ.get('MONETDB_PASSWORD', 'monetdb')
database = os.environ.get('MONETDB_DATABASE', 'demo')

conn = None
try:
    # Establish a connection to the MonetDB database
    conn = pymonetdb.connect(
        hostname=hostname, 
        port=port,
        username=username, 
        password=password,
        database=database
    )
    print("Successfully connected to MonetDB!")

    # Create a cursor object
    cursor = conn.cursor()

    # Execute a simple query
    cursor.execute("SELECT 'Hello from pymonetdb!' AS message;")

    # Fetch the result
    result = cursor.fetchone()
    print(f"Query result: {result[0]}")

    # Example of fetching multiple rows (e.g., system tables)
    cursor.execute("SELECT name FROM sys.tables ORDER BY name LIMIT 3;")
    tables = cursor.fetchall()
    print("First 3 system tables:", [t[0] for t in tables])

    # Always commit changes if any DML operations were performed
    # conn.commit()

except pymonetdb.Error as e:
    print(f"MonetDB Database error: {e}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")
finally:
    # Ensure the connection is closed
    if conn:
        conn.close()
        print("Connection closed.")