4. The MimerPy module

4.1. The module

The MimerPy module enables the creation of connections to Mimer SQL databases and the opening of cursors to execute Mimer SQL statements.

4.1.1. Constructor

connect(dsn = None, user = None, password = None, 'autocommit' = False, 'errorhandler' = None)

Constructor for creating a connection to the specified database using the Connection class. Returns a Connection object. To establish a default connection with a Mimer SQL database the following parameters are required:

  • dsn – Data source name as a string

  • user – Username as a string

  • password – Password as a string

This will create the default connection according the PEP 249 specification.

The connection’s auto-commit feature is initially turned off and the default errorhandler is used. The following parameters can be set when creating a new connection:

  • autocommit – If ‘autocommit’ = False or unspecified, auto-commit mode is turned off (as by default). If ‘autocommit’ = True queries will be automatically committed.

  • errorhandler – If ‘errorhandler’ = None or unspecified, the default errorhandler is used. The user can choose to use their own errorhandler of choice by setting this parameter.

See also

Information on Connection parameters.

4.1.2. Globals

Globlas are a set of read only variables.

__version__

String that describes the version number of the mimerpy module.

apilevel

String that refers to the supported DB API level, which for mimerpy is 2.0.

threadsafety

Integer that states the thread safety of the interface.

For mimerpy the thread safety is 1. This means that threads may share the module, but not connections, according with the PEP 249 threadsafety specification.

paramstyle

String that states what parameter marker format that is used. This has the value qmark.

See also

Information on Query structure.

4.2. Connection

class Connection

The class Connection is used to establish a connection with a Mimer SQL database.

4.2.1. Connection Methods

Connection.close()

Method is used for closing a connection. The close()-method also closes all cursors opened with the connection.

If the auto-commit feature is turned off and a connection is closed before committing any changes, an implicit roll back is executed. Thus, before evoking close(), commit() should be used to prevent any changes being lost. However, if auto-commit is turned on, changes are automatically committed.

When a connection has been closed using close(), it is unusable and a ProgrammingError is raised if any operations are attempted on the connection.

Connection.commit()

Commits the pending transaction to the database.

Note

If commit() is not performed on a connection, all pending transactions are implicitly rolled back and all data manipulation performed during the transaction is lost.

For information on the auto-commit feature on the connection, see autocommit().

Connection.rollback()

Causes the database to roll back to the start of the transaction. If a connection is closed without committing changes made during the transaction, a rollback() is implicitly performed.

Connection.cursor('scrollable'  = False)

Returns a new Cursor object using the connection.

If scrollable is unspecified, the default cursor class will be returned. If scrollable = True a ScrollCursor will be returned.

Connection.execute(query[, parameters])

This method is not included in the PEP 249. It returns a Cursor object and executes the query.

Connection.executemany(query, seq_of_parameters)

This method is not included in the PEP 249. It returns a Cursor object and executes the query against all the parameter sequences.

4.2.2. Connection Attributes

Connection.autocommitmode

Attribute determines if the connection will auto-commmit any changes or if commit() has to be performed explicitly. This is set to False by default unless otherwise stated when opening the connection or by using the autocommit() method to change this attribute.

4.2.3. Connection Extensions

Connection.autocommit(bool)

This method is used to turn on or off the auto-commit feature on the connection. When autocommit(True) is called, all statements from this point onward are automatically committed.

Turns on auto-commit feature if boolean value True and turns it off if False.

Warning

If autocommit(True) is called, all changes that have not yet been committed during the current transaction are rolled back and the auto-commit feature is later turned on. To prevent this, either set ‘autocommit’ = True when opening a connection or use method commit() before setting autocommit to True.

Connection.messages

List where an exception class and value is appended to as a tuple. The tuple interfaces and receives from the underlying database. If the connection has at least one cursor, then the error is appended to the cursor’s messages attribute, otherwise the error is appended to the connection’s messages attribute. The aim of this attribute is to eliminate the need for a Warning exception which often causes problems.

The list is cleared prior to executing all standard cursor methods except fetch*().

Connection.errorhandler

The attribute states what errorhandler is used. This is set to the default unless otherwise stated when opening the connection. For further information, see Exceptions.

Connection.__enter__()

Returns self which enables the connections’s compatibility with the Python with statement.

See also

Using with for an example how this is used.

4.3. Connection pool

The MimerPy connection pool is an extension to PEP 249.

Since opening database connections are quite expensive, it is good practice to not open and close them for each call. A common technique for this is to use a pool of connections. By using a pool, instead of being actually closed, a connection is returned to the pool, and remains open and ready for someone else to use. For standalone applications, you can simply create a common pool, and each method that uses the database simply gets a connection from the pool. This is particularly important for web applications, since if each request would have to create a new connection every time, it would be very expensive. You can simply store it in the web application’s environment, and each request can get a connection from the pool to work with.

Besides improved performance, connection pooling also makes it possible to handle a lot of requests with a limited amount of connections.

See also

Read more about connection pooling at https://en.wikipedia.org/wiki/Connection_pool

class MimerPool

The MimerPool implements the MimerPy connection pool. This is done by using the wrapper class PooledConnection that overide the Connection.close() method.

4.3.1. MimerPool Constructor

MimerPool(dsn=None, user=None, password=None, initialconnections=0, maxunused=0, maxconnections=0, block=False, deep_health_check=False, autocommit=False, errorhandler=None)
Constructor for creating and initializing a connection pool for the specified database. Returns a MimerPool

object. To setting up a default connection pool with a Mimer SQL database the following parameters are required:

  • dsn – Data source name as a string

  • user – Username as a string

  • password – Password as a string

The pools underlying connection’s auto-commit feature is initially turned off and the default errorhandler is used. The following parameters for the underlying connections can be set when setting up the pool:

  • autocommit – If ‘autocommit’ = False or unspecified, auto-commit mode is turned off (as by default). If ‘autocommit’ = True queries will be automatically committed.

  • errorhandler – If ‘errorhandler’ = None or unspecified, the default errorhandler is used. The user can choose to use their own errorhandler of choice by setting this parameter.

The following parameters can be set to configure how the pool behaves:

  • initialconnections – Number of connections to open directly. If ‘initialconnections’ = 0 or unspecified, no initial connections are made.

  • maxunused – Maximum number of unused connections in the pool. If ‘maxunused’ = 0 or unspecified, the pool will not shrink automatically.

  • maxconnections – Maximum number of connections in the pool. If ‘maxconnections’ = 0 or unspecified, the pool will be unlimited in size.

  • block – Behavior when there are no available connections. If ‘block’ = False or unspecified, a MimerPoolExhausted will be trown if there are no available connections, otherwise the MimerPool.get_connection() will block until a connection is available.

  • deep_health_check – More extensive test of the connection state when getting a connection from the pool. If ‘deep_health_check’ = True, a simple query is made to verify the connection before returning it.

4.3.2. MimerPool Methods

MimerPool.get_connection()

Get a new PooledConnection from the connection pool.

MimerPool.close()

Close all connections in the pool.

4.3.3. PooledConnection

class PooledConnection

The PooledConnection is a wrapper for the Connection that override Connection.close() so that instead of closing the connection, it’s returned to the pool.

4.3.3.1. PooledConnection Methods

All methods of Connection are available, except Connection.close().

PooledConnection.close()

Return the connection to the pool.

Note

If commit() is not performed on a connection, all pending transactions are implicitly rolled back and all data manipulation performed during the transaction is lost.

4.4. Cursor

class Cursor

The class cursor is used to execute Mimer SQL statements and manage data result sets.

Cursors that have been created from the same connection are not isolated. This means if data is manipulated by a cursor, this is visible to all other cursors created with that connection. Changes made by a cursor are not visible to other cursors created from different connections until the changes are committed or unless the connection’s attribute autocommitmode is set to True. If auto-commit is turned on, changes made to the database are visible to all cursors independent on their connection.

A cursor can be opened either by calling Connection.cursor(), Connection.execute() or Connection.executemany().

4.4.1. Cursor Methods

Cursor.close()

Closes a cursor. From this point onwards the cursor is unusable and a ProgrammingError is raised if any operations are attempted on the connection.

Cursor.execute(query[, parameters])

Prepares and executes a SQL statement.

The input parameter parameters is optional, as queries can either contain data or parameter markers can be used, see User guide for more information.

Cursor.executemany(query, seq_of_parameters)

Prepares and executes a SQL statement against all parameters in seq_of_parameters.

See also

User guide, for the correct syntax of these methods.

Cursor.fetchone()

Fetches the next row of a result set. The row is returned as a tuple. If no more data is available, None is returned.

If fetchone() is called and the previous call to execute() did not produce a result set, a ProgrammingError is raised.

Cursor.fetchmany([size=cursor.arraysize])

Fetches the next rows of a result set. The rows are returned as a list of tuples. If no more data is available, an empty list is returned.

The method fetches the number of rows specified by the parameter. If unspecified, the cursor’s arraysize is used. If the size of the fetch is larger than the number of rows available in the result set, the remaining rows are returned.

If the size parameter is specified, the cursor’s arraysize is changed and if fetchmany() is called upon again without a specified size, the new arraysize is used.

If fetchmany() is called and the previous call to execute() did not produce a result set, a ProgrammingError is raised.

Cursor.fetchall()

Fetches the remaining rows of a result set. The rows are returned as a list of tuples. If no more data is available, an empty list is returned.

If fetchall() is called and the previous call to execute() did not produce a result set, a ProgrammingError is raised.

Cursor.setinputsizes()

The method does not do anything but is a requirement from the DB-API PEP 249.

Cursor.setoutputsize()

The method does not do anything but is a requirement from the DB-API PEP 249.

4.4.2. Cursor Attributes

Cursor.description

A read-only attribute that is a sequence of 7-item sequences. Each sequence stores information regarding the latest result column:

  • name

  • type_code

  • display_size

  • internal_size

  • precision

  • scale

  • null_ok

Only name and type_code are specified, the rest of the items are set to None.

name provides the name of the result column and type_code specifies the native Mimer API type code for the column.

Cursor.rowcount

Read-only attribute that specifies the number of updated rows that the last execute() performed. For example performing an INSERT, UPDATE or DELETE statement, the attribute is changed.

Cursor.arraysize

Read-write attribute which specifies the number of rows to be fetched with fetchmany(). By default this is set to 1 when a cursor is opened, thus it will fetch one row at a time from the result set until it is changed by calling fetchmany() with a different size.

4.4.3. Cursor Extensions

Cursor.connection

Read-only attribute which returns a reference to the connection at which the cursor was created.

Cursor.messages

List where an exception class and value is appended to as a tuple. The tuple interfaces and receives from the underlying database. The aim of this attribute is to eliminate the need for a Warning exception which often causes problems.

The list is cleared prior to executing all standard cursor methods except fetch*().

Cursor.next()

Returns the next row in a result set, with the same semantics as fetchone(). If there is no more data available in the result set, a StopIteration exception is raised.

Cursor.__iter__()

Returns self which enables the cursor’s compatibility with iteration.

See also

Iterating a result set, for an example how this can be used.

Cursor.errorhandler

The attribute states what errorhandler is used. This is set to the default unless otherwise stated when opening the connection. For further information, see Exceptions.

Cursor.__enter__()

Returns self which enables the cursor’s compatibility with the Python with statement.

See also

Using with, for an example how this can be used.

4.4.4. ScrollCursor

class ScrollCursor

ScrollCursor is a subclass to the Cursor-class where the cursor can be scrolled to new positions in the result set. All methods in the baseclass Cursor can also be used by a ScrollCursor.

When opening a cursor by using the method Connection.cursor(), if the parameter scrollable is set to True, the cursor will be scrollable and an instance of ScrollCursor. If not specified, the cursor is by default not scrollable.

Note

A ScrollCursor fetches the whole result set to the client.

4.4.4.1. ScrollCursor Methods

ScrollCursor.scroll(value[, mode='relative'])

Method scrolls the cursor to a new position according to the mode of the scroll.

The mode of the cursor is set to relative by default. This changes the cursor’s position by value number of rows in relation to the current position of the cursor. If mode is set to absolute the cursor is moved value number of rows down from the absolute position.

If the method is called upon and desired position in the result set does not exist, an IndexError is raised.

4.4.4.2. ScrollCursor Attributes

ScrollCursor.rownumber

A read-only attribute that specifies the zero-based index of the cursor in the result set.

This is set to None until a statement resulting in a result set i performed.

If a fetch operation is performed on the result set, the next row to fetch is the row with the rownumber as index.

ScrollCursor.rowcount

Same as for Cursor, but is also updated whenever a SELECT statement is executed.