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)

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 Connection.DatabaseError

Bases: pymonetdb.exceptions.Error

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

exception Connection.Error

Bases: exceptions.StandardError

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 Connection.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 Connection.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 Connection.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 Connection.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 Connection.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 Connection.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 Connection.Warning

Bases: exceptions.StandardError

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

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

Connection.command(command)

use this function to send low level mapi commands

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

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

Connection.default_cursor

alias of Cursor

Connection.execute(query)

use this for executing SQL queries

Connection.gettimeout()

get the amount of time before a connection times out

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

Connection.set_autocommit(autocommit)

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

Connection.set_replysize(replysize)

Connection.set_sizeheader(sizeheader)

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

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

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

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_escape(data)

returns an escaped string

pymonetdb.sql.monetize.monet_none(data)

returns a NULL string

pymonetdb.sql.monetize.monet_unicode(data)

functions for converting monetdb SQL fields to Python objects

pymonetdb.sql.pythonize.Binary(data)

returns binary encoding of data

pymonetdb.sql.pythonize.DateFromTicks(ticks)

Convert ticks to python Date

pymonetdb.sql.pythonize.TimeFromTicks(ticks)

Convert ticks to python Time

pymonetdb.sql.pythonize.TimestampFromTicks(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.py_bool(data)

return python boolean

pymonetdb.sql.pythonize.py_date(data)

Returns a python Date

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)

setup connection to MAPI server

unix_socket is used if hostname is not defined.

disconnect()

disconnect from the monetdb server

pymonetdb.mapi.decode(b)

only decode byte for python3

pymonetdb.mapi.encode(s)

only encode string for python3

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)

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: exceptions.StandardError

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: exceptions.StandardError

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