{"id":6806,"library":"pymonetdb","title":"MonetDB Python API","description":"pymonetdb is the native Python client API for MonetDB. It is cross-platform and does not depend on any MonetDB libraries. It supports Python 3.7+ and PyPy, and is Python DBAPI 2.0 compatible. Besides standard DBAPI 2.0 functionality, it also provides MonetDB-specific features like file transfers. It is currently at version 1.9.0 and has an active development and release cadence.","status":"active","version":"1.9.0","language":"en","source_language":"en","source_url":"https://github.com/MonetDB/pymonetdb","tags":["database","monetdb","sql","dbapi","data-access"],"install":[{"cmd":"pip install pymonetdb","lang":"bash","label":"Install latest version"}],"dependencies":[],"imports":[{"note":"The old 'python-monetdb' library was replaced by 'pymonetdb'.","wrong":"import monetdb\nconn = monetdb.connect(...)","symbol":"connect","correct":"import pymonetdb\nconn = pymonetdb.connect(...)"},{"symbol":"Error","correct":"from pymonetdb import Error"}],"quickstart":{"code":"import pymonetdb\nimport os\n\n# Configure connection details using environment variables for security\nhostname = os.environ.get('MONETDB_HOSTNAME', 'localhost')\nport = int(os.environ.get('MONETDB_PORT', 50000))\nusername = os.environ.get('MONETDB_USERNAME', 'monetdb')\npassword = os.environ.get('MONETDB_PASSWORD', 'monetdb')\ndatabase = os.environ.get('MONETDB_DATABASE', 'demo')\n\nconn = None\ntry:\n    # Establish a connection to the MonetDB database\n    conn = pymonetdb.connect(\n        hostname=hostname, \n        port=port,\n        username=username, \n        password=password,\n        database=database\n    )\n    print(\"Successfully connected to MonetDB!\")\n\n    # Create a cursor object\n    cursor = conn.cursor()\n\n    # Execute a simple query\n    cursor.execute(\"SELECT 'Hello from pymonetdb!' AS message;\")\n\n    # Fetch the result\n    result = cursor.fetchone()\n    print(f\"Query result: {result[0]}\")\n\n    # Example of fetching multiple rows (e.g., system tables)\n    cursor.execute(\"SELECT name FROM sys.tables ORDER BY name LIMIT 3;\")\n    tables = cursor.fetchall()\n    print(\"First 3 system tables:\", [t[0] for t in tables])\n\n    # Always commit changes if any DML operations were performed\n    # conn.commit()\n\nexcept pymonetdb.Error as e:\n    print(f\"MonetDB Database error: {e}\")\nexcept Exception as e:\n    print(f\"An unexpected error occurred: {e}\")\nfinally:\n    # Ensure the connection is closed\n    if conn:\n        conn.close()\n        print(\"Connection closed.\")","lang":"python","description":"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."},"warnings":[{"fix":"Replace `import monetdb` with `import pymonetdb`.","message":"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`.","severity":"breaking","affected_versions":"<= 1.0 (old `python-monetdb` library)"},{"fix":"Explicitly call `conn.commit()` before `conn.close()` if changes should be saved.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Implement `pymonetdb.set_uploader()` and `pymonetdb.set_downloader()` with a `SafeDirectoryHandler` instance configured to a secure, restricted path.","message":"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.","severity":"gotcha","affected_versions":"All versions using `COPY INTO ... ON CLIENT`"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-15T00:00:00.000Z","next_check":"2026-07-14T00:00:00.000Z","problems":[]}