API

Basic SQL usage

pymonetdb.connect(*args, **kwargs) → pymonetdb.sql.connections.Connection

Set up a connection to a MonetDB SQL database.

database (str)

name of the database, or MAPI URI (see below)

hostname (str)

Hostname where MonetDB is running

port (int)

port to connect to (default: 50000)

username (str)

username for connection (default: “monetdb”)

password (str)

password for connection (default: “monetdb”)

unix_socket (str)

socket to connect to. used when hostname not set (default: “/tmp/.s.monetdb.50000”)

autocommit (bool)

enable/disable auto commit (default: false)

connect_timeout (int)

the socket timeout while connecting

binary (int)

enable binary result sets when possible if > 0 (default: 1)

replysize(int)

number of rows to retrieve immediately after query execution (default: 100, -1 means everything)

maxprefetch(int)

max. number of rows to prefetch during Cursor.fetchone() or Cursor.fetchmany()

MAPI URI Syntax:

tcp socket

mapi:monetdb://[<username>[:<password>]@]<host>[:<port>]/<database>

unix domain socket

mapi:monetdb:///[<username>[:<password>]@]path/to/socket?database=<database>

class pymonetdb.sql.connections.Connection(database, hostname=None, port=50000, username='monetdb', password='monetdb', unix_socket=None, autocommit=False, host=None, user=None, connect_timeout=-1, binary=1, replysize=None, maxprefetch=None)

Bases: object

A MonetDB SQL database connection

exception DataError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc. It must be a subclass of DatabaseError.

exception DatabaseError

Bases: pymonetdb.exceptions.Error

Exception raised for errors that are related to the database. It must be a subclass of Error.

exception Error

Bases: Exception

Exception that is the base class of all other error exceptions. You can use this to catch all errors with one single ‘except’ statement. Warnings are not considered errors and thus should not use this class as base. It must be a subclass of the Python StandardError (defined in the module exceptions).

exception IntegrityError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It must be a subclass of DatabaseError.

exception InterfaceError

Bases: pymonetdb.exceptions.Error

Exception raised for errors that are related to the database interface rather than the database itself. It must be a subclass of Error.

exception InternalError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc. It must be a subclass of DatabaseError.

exception NotSupportedError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off. It must be a subclass of DatabaseError.

exception OperationalError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of DatabaseError.

exception ProgrammingError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. It must be a subclass of DatabaseError.

exception Warning

Bases: Exception

Exception raised for important warnings like data truncations while inserting, etc. It must be a subclass of the Python StandardError (defined in the module exceptions).

binary

binary_command(command)

use this function to send low level mapi commands that return raw bytes

close()

Close the connection.

The connection will be unusable from this point forward; an Error exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection. Note that closing a connection without committing the changes first will cause an implicit rollback to be performed.

command(command)

use this function to send low level mapi commands

commit()

Commit any pending transaction to the database. Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.

Database modules that do not support transactions should implement this method with void functionality.

cursor()

Return a new Cursor Object using the connection. If the database does not provide a direct cursor concept, the module will have to emulate cursors using other means to the extent needed by this specification.

default_cursor

alias of pymonetdb.sql.cursors.Cursor

execute(query)

use this for executing SQL queries

get_binary() → int

get_maxprefetch() → int

get_replysize() → int

gettimeout()

get the amount of time before a connection times out

maxprefetch

replysize

rollback()

This method is optional since not all databases provide transaction support.

In case a database does provide transactions this method causes the database to roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.

set_autocommit(autocommit)

Set auto commit on or off. ‘autocommit’ must be a boolean

set_binary(binary: int)

set_downloader(downloader)

Register a Downloader object which will handle file download requests.

Must be an instance of class pymonetdb.Downloader or None

set_maxprefetch(maxprefetch: int)

set_replysize(replysize: int)

set_sizeheader(sizeheader)

Set sizeheader on or off. When enabled monetdb will return the size a type. ‘sizeheader’ must be a boolean.

set_timezone(seconds_east_of_utc)

set_uploader(uploader)

Register an Uploader object which will handle file upload requests.

Must be an instance of class pymonetdb.Uploader or None.

settimeout(timeout)

set the amount of time before a connection times out

class pymonetdb.sql.cursors.Cursor(connection: pymonetdb.sql.connections.Connection)

Bases: object

This object represents a database cursor, which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors

arraysize = None

Default value for the size parameter of fetchmany().

binary

close()

Close the cursor now (rather than whenever __del__ is called). The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor.

debug(query, fname, sample=-1)

Locally debug a given Python UDF function in a SQL query using the PDB debugger. Optionally can run on only a sample of the input data, for faster data export.

execute(operation: str, parameters: Optional[Dict[KT, VT]] = None)

Prepare and execute a database operation (query or command). Parameters may be provided as mapping and will be bound to variables in the operation.

executemany(operation, seq_of_parameters)

Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.

It will return the number or rows affected

export(query, fname, sample=-1, filespath='./')

fetchall()

Fetch all remaining rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

A ProgrammingError is raised if the previous call to .execute*() did not produce any result set or no call was issued yet.

fetchmany(size=None)

Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.

The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor’s arraysize determines the number of rows to be fetched.

A ProgrammingError is raised if the previous call to .execute*() did not produce any result set or no call was issued yet.

fetchone()

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available.

get_binary() → int

get_maxprefetch() → int

get_replysize() → int

maxprefetch

next()

replysize

scroll(value, mode='relative')

Scroll the cursor in the result set to a new position according to mode.

If mode is ‘relative’ (default), value is taken as offset to the current position in the result set, if set to ‘absolute’, value states an absolute target position.

An IndexError is raised in case a scroll operation would leave the result set.

set_binary(level: int)

set_maxprefetch(maxprefetch: int)

set_replysize(replysize: int)

setinputsizes(sizes)

This method would be used before the .execute*() method is invoked to reserve memory. This implementation doesn’t use this.

setoutputsize(size, column=None)

Set a column buffer size for fetches of large columns This implementation doesn’t use this

used_binary_protocol() → bool

Pymonetdb-specific. Return True if the last fetch{one,many,all} for the current statement made use of the binary protocol.

Primarily used for testing.

Note that the binary protocol is never used for the first few rows of a result set. Exactly when it kicks in depends on the replysize setting.

Type conversion

functions for converting python objects to monetdb SQL format. If you want to add support for a specific type you should add a function as a value to the mapping dict and the datatype as key.

pymonetdb.sql.monetize.convert(data)

Return the appropriate convertion function based upon the python type.

pymonetdb.sql.monetize.monet_bool(data)

returns “true” or “false”

pymonetdb.sql.monetize.monet_bytes(data)

converts bytes to string

pymonetdb.sql.monetize.monet_date(data)

returns a casted date

pymonetdb.sql.monetize.monet_datetime(data)

returns a casted timestamp

pymonetdb.sql.monetize.monet_escape(data)

returns an escaped string

pymonetdb.sql.monetize.monet_none(_)

returns a NULL string

pymonetdb.sql.monetize.monet_time(data)

returns a casted time

pymonetdb.sql.monetize.monet_timedelta(data)

returns timedelta casted to interval seconds

pymonetdb.sql.monetize.monet_unicode(data)

functions for converting monetdb SQL fields to Python objects

pymonetdb.sql.pythonize.Binary(data)

Convert to wraps binary data

pymonetdb.sql.pythonize.DateFromTicks(ticks)

Convert ticks to python Date

pymonetdb.sql.pythonize.TimeFromTicks(ticks)

Convert ticks to python Time

pymonetdb.sql.pythonize.TimeTzFromTicks(ticks)

Convert ticks to python Time

pymonetdb.sql.pythonize.TimestampFromTicks(ticks)

Convert ticks to python Timestamp

pymonetdb.sql.pythonize.TimestampTzFromTicks(ticks)

Convert ticks to python Timestamp

pymonetdb.sql.pythonize.convert(data, type_code)

Calls the appropriate convertion function based upon the python type

pymonetdb.sql.pythonize.oid(data)

represents an object identifier

For now we will just return the string representation just like mclient does.

pymonetdb.sql.pythonize.py_bool(data)

return python boolean

pymonetdb.sql.pythonize.py_bytes(data: str)

Returns a bytes (py3) or string (py2) object representing the input blob.

pymonetdb.sql.pythonize.py_date(data)

Returns a python Date

pymonetdb.sql.pythonize.py_day_interval(data: str) → int

Returns a python number of days where data represents a value of MonetDB’s INTERVAL DAY type which resembles a stringified decimal.

pymonetdb.sql.pythonize.py_sec_interval(data: str) → datetime.timedelta

Returns a python TimeDelta where data represents a value of MonetDB’s INTERVAL SECOND type which resembles a stringified decimal.

pymonetdb.sql.pythonize.py_time(data)

returns a python Time

pymonetdb.sql.pythonize.py_timestamp(data)

Returns a python Timestamp

pymonetdb.sql.pythonize.py_timestamptz(data)

Returns a python Timestamp where data contains a tz code

pymonetdb.sql.pythonize.py_timetz(data)

returns a python Time where data contains a tz code

pymonetdb.sql.pythonize.strip(data)

returns a python string, with chopped off quotes, and replaced escape characters

MAPI

This is the python implementation of the mapi protocol.

class pymonetdb.mapi.Connection

Bases: object

MAPI (low level MonetDB API) connection

binary_cmd(operation: str) → memoryview

put a mapi command on the line, with a binary response.

returns a memoryview that can only be used until the next operation on this Connection object.

cmd(operation: str)

put a mapi command on the line

connect(database: str, username: str, password: str, language: str, hostname: Optional[str] = None, port: Optional[int] = None, unix_socket=None, connect_timeout=-1, handshake_options_callback: Callable[[bool], List[HandshakeOption]] = <function Connection.<lambda>>)

setup connection to MAPI server

unix_socket is used if hostname is not defined.

disconnect()

disconnect from the monetdb server

set_downloader(downloader: Downloader)

Register the given Downloader, or None to deregister

set_reply_size(size)

Set the amount of rows returned by the server.

args:

size: The number of rows

set_uploader(uploader: Uploader)

Register the given Uploader, or None to deregister

class pymonetdb.mapi.HandshakeOption(level, name, fallback, value)

Bases: object

Option that can be set during the MAPI handshake

Should be sent as <name>=<val>, where <val> is value converted to int. The level is used to determine if the server supports this option. The fallback is a function-like object that can be called with the value (not converted to an integer) as a parameter. Field sent can be used to keep track of whether the option has been sent.

pymonetdb.mapi.handle_error(error)

Return exception matching error code.

args:

error (str): error string, potentially containing mapi error code

returns:

tuple (Exception, formatted error): returns OperationalError if unknown

error or no error code in string

pymonetdb.mapi.mapi_url_options(possible_mapi_url: str) → Dict[str, str]

Try to parse the argument as a MAPI URL and return a Dict of url options

Return empty dict if it’s not a MAPI URL.

File Uploads and Downloads

Classes related to file transfer requests as used by COPY INTO ON CLIENT.

class pymonetdb.filetransfer.Upload(mapi: MapiConnection)

Represents a request from the server to upload data to the server. It is passed to the Uploader registered by the application, which for example might retrieve the data from a file on the client system. See pymonetdb.sql.connections.Connection.set_uploader().

Use the method send_error() to refuse the upload, binary_writer() to get a binary file object to write to, or text_writer() to get a text-mode file object to write to.

Implementations should be VERY CAREFUL to validate the file name before opening any files on the client system!

is_cancelled() → bool

Returns true if the server has cancelled the upload.

has_been_used() → bool

Returns true if .send_error(), .text_writer() or .binary_writer() have been called.

set_chunk_size(size: int)

After every CHUNK_SIZE bytes, the server gets the opportunity to cancel the rest of the upload. Defaults to 1 MiB.

send_error(message: str) → None

Tell the server the requested upload has been refused

binary_writer() → io.BufferedIOBase

Returns a binary file-like object. All data written to it is uploaded to the server.

text_writer() → io.TextIOBase

Returns a text-mode file-like object. All text written to it is uploaded to the server. DOS/Windows style line endings (CR LF, \r \n) are automatically rewritten to single \n’s.

close()

End the upload succesfully

class pymonetdb.filetransfer.Uploader

Base class for upload hooks. Instances of subclasses of this class can be registered using pymonetdb.Connection.set_uploader(). Every time an upload request is received, an Upload object is created and passed to this objects .handle_upload() method.

If the server cancels the upload halfway, the .cancel() methods is called and all further data written is ignored.

handle_upload(upload: pymonetdb.filetransfer.uploads.Upload, filename: str, text_mode: bool, skip_amount: int)

Called when an upload request is received. Implementations should either send an error using upload.send_error(), or request a writer using upload.text_writer() or upload.binary_writer(). All data written to the writer will be sent to the server.

Parameter ‘filename’ is the file name used in the COPY INTO statement. Parameter ‘text_mode’ indicates whether the server requested a text file or a binary file. In case of a text file, ‘skip_amount’ indicates the number of lines to skip. In binary mode, ‘skip_amount’ is always 0.

SECURITY NOTE! Make sure to carefully validate the file name before opening files on the file system. Otherwise, if an adversary has taken control of the network connection or of the server, they can use file upload requests to read arbitrary files from your computer (../../)

cancel()

Optional method called when the server cancels the upload.

class pymonetdb.filetransfer.Download(mapi: pymonetdb.mapi.Connection)

Represents a request from the server to download data from the server. It is passed to the Downloader registered by the application, which for example might write the data to a file on the client system. See pymonetdb.Connection.set_downloader().

Use the method send_error() to refuse the download, binary_reader() to get a binary file object to read bytes from, or text_reader() to get a text-mode file object to read text from.

Implementations should be EXTREMELY CAREFUL to validate the file name before opening and writing to any files on the client system!

send_error(message: str) → None

Tell the server the requested download is refused

binary_reader()

Returns a binary file-like object to read the downloaded data from.

text_reader()

Returns a text mode file-like object to read the downloaded data from.

close()

End the download succesfully. Any unconsumed data will be discarded.

class pymonetdb.filetransfer.Downloader

Base class for download hooks. Instances of subclasses of this class can be registered using pymonetdb.Connection.set_downloader(). Every time a download request arrives, a Download object is created and passed to this objects .handle_download() method.

SECURITY NOTE! Make sure to carefully validate the file name before opening files on the file system. Otherwise, if an adversary has taken control of the network connection or of the server, they can use download requests to OVERWRITE ARBITRARY FILES on your computer

handle_download(download: pymonetdb.filetransfer.downloads.Download, filename: str, text_mode: bool)

Called when a download request is received. Implementations should either send an error using download.send_error(), or request a reader using download.text_reader() or download.binary_reader().

Parameter ‘filename’ is the file name used in the COPY INTO statement. Parameter ‘text_mode’ indicates whether the server requested text or binary mode.

SECURITY NOTE! Make sure to carefully validate the file name before opening files on the file system. Otherwise, if an adversary has taken control of the network connection or of the server, they can use file download requests to overwrite arbitrary files on your computer. (../../)

class pymonetdb.filetransfer.SafeDirectoryHandler(dir, encoding: Optional[str] = None, newline: Optional[str] = None, compression=True)

File transfer handler which uploads and downloads files from a given directory, taking care not to allow access to files outside that directory. Instances of this class can be registered using the pymonetb.Connection’s set_uploader() and set_downloader() methods.

When downloading text files, the downloaded text is converted according to the encoding and newline parameters, if present. Valid values for encoding are any encoding known to Python, or None. Valid values for newline are “\n”, “\r\n” or None. None means to use the system default.

For binary up- and downloads, no conversions are applied.

When uploading text files, the encoding parameter indicates how the text is read and newline is mostly ignored: both \n and \r\n are valid line endings. The exception is that because the server expects its input to be \n-terminated UTF-8 text, if you set encoding to “utf-8” and newline to “\n”, text mode transfers are performed as binary, which improves performance. For uploads, only do this if you are absolutely, positively sure that all files in the directory are actually valid UTF-8 encoded and have Unix line endings.

If compression is set to True, which is the default, the SafeDirectoryHandler will automatically compress and decompress files with extensions .gz, .bz2, .xz and .lz4. Note that the first three algorithms are built into Python, but LZ4 only works if the lz4.frame module is available.

handle_upload(upload: pymonetdb.filetransfer.uploads.Upload, filename: str, text_mode: bool, skip_amount: int)

Meta private:

handle_download(download: pymonetdb.filetransfer.downloads.Download, filename: str, text_mode: bool)

Called when a download request is received. Implementations should either send an error using download.send_error(), or request a reader using download.text_reader() or download.binary_reader().

Parameter ‘filename’ is the file name used in the COPY INTO statement. Parameter ‘text_mode’ indicates whether the server requested text or binary mode.

SECURITY NOTE! Make sure to carefully validate the file name before opening files on the file system. Otherwise, if an adversary has taken control of the network connection or of the server, they can use file download requests to overwrite arbitrary files on your computer. (../../)

MonetDB remote control

class pymonetdb.control.Control(hostname=None, port=50000, passphrase=None, unix_socket=None, connect_timeout=-1)

Bases: object

Use this module to manage your MonetDB databases. You can create, start, stop, lock, unlock, destroy your databases and request status information.

create(database_name)

Initialises a new database or multiplexfunnel in the MonetDB Server. A database created with this command makes it available for use, however in maintenance mode (see pymonetdb lock).

defaults()

destroy(database_name)

Removes the given database, including all its data and logfiles. Once destroy has completed, all data is lost. Be careful when using this command.

get(database_name)

gets value for property for the given database, or retrieves all properties for the given database

inherit(database_name, property_)

unsets property, reverting to its inherited value from the default configuration for the given database

kill(database_name)

Kills the given database, if the MonetDB Database Server is running. Note: killing a database should only be done as last resort to stop a database. A database being killed may end up with data loss.

lock(database_name)

Puts the given database in maintenance mode. A database under maintenance can only be connected to by the DBA. A database which is under maintenance is not started automatically. Use the “release” command to bring the database back for normal usage.

neighbours()

release(database_name)

Brings back a database from maintenance mode. A released database is available again for normal use. Use the “lock” command to take a database under maintenance.

rename(old, new)

set(database_name, property_, value)

sets property to value for the given database for a list of properties, use pymonetdb get all

start(database_name)

Starts the given database, if the MonetDB Database Server is running.

status(database_name=False)

Shows the state of a given glob-style database match, or all known if none given. Instead of the normal mode, a long and crash mode control what information is displayed.

stop(database_name)

Stops the given database, if the MonetDB Database Server is running.

pymonetdb.control.isempty(result)

raises an exception if the result is not empty

pymonetdb.control.parse_statusline(line)

parses a sabdb format status line. Support v1 and v2.

pymonetdb Exceptions

MonetDB Python API specific exceptions

exception pymonetdb.exceptions.DataError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc. It must be a subclass of DatabaseError.

exception pymonetdb.exceptions.DatabaseError

Bases: pymonetdb.exceptions.Error

Exception raised for errors that are related to the database. It must be a subclass of Error.

exception pymonetdb.exceptions.Error

Bases: Exception

Exception that is the base class of all other error exceptions. You can use this to catch all errors with one single ‘except’ statement. Warnings are not considered errors and thus should not use this class as base. It must be a subclass of the Python StandardError (defined in the module exceptions).

exception pymonetdb.exceptions.IntegrityError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It must be a subclass of DatabaseError.

exception pymonetdb.exceptions.InterfaceError

Bases: pymonetdb.exceptions.Error

Exception raised for errors that are related to the database interface rather than the database itself. It must be a subclass of Error.

exception pymonetdb.exceptions.InternalError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc. It must be a subclass of DatabaseError.

exception pymonetdb.exceptions.NotSupportedError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off. It must be a subclass of DatabaseError.

exception pymonetdb.exceptions.OperationalError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of DatabaseError.

exception pymonetdb.exceptions.ProgrammingError

Bases: pymonetdb.exceptions.DatabaseError

Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. It must be a subclass of DatabaseError.

exception pymonetdb.exceptions.Warning

Bases: Exception

Exception raised for important warnings like data truncations while inserting, etc. It must be a subclass of the Python StandardError (defined in the module exceptions).