API

Basic SQL usage

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)

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).

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

gettimeout()

get the amount of time before a connection times out

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_replysize(replysize)

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)

settimeout(timeout)

set the amount of time before a connection times out

class pymonetdb.sql.cursors.Cursor(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

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, parameters=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). Note that the cursor’s arraysize attribute can affect the performance of this operation.

An Error (or subclass) exception 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. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.

An Error (or subclass) exception is raised if the previous call to .execute*() did not produce any result set or no call was issued yet.

Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one .fetchmany() call to the next.

fetchone()

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

next()

nextset()

This method will make the cursor skip to the next available set, discarding any remaining rows from the current set.

If there are no more sets, the method returns None. Otherwise, it returns a true value and subsequent calls to the fetch methods will return rows from the next result set.

An Error (or subclass) exception is raised if the previous call to .execute*() did not produce any result set or no call was issued yet.

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.

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

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

cmd(operation)

put a mapi command on the line

connect(database, username, password, language, hostname=None, port=None, unix_socket=None, connect_timeout=-1, handshake_options=None)

setup connection to MAPI server

unix_socket is used if hostname is not defined.

disconnect()

disconnect from the monetdb server

set_reply_size(size)

Set the amount of rows returned by the server.

args:

size: The number of rows

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

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).