spMariaDbConnectorPy 1.0.2

A Python package for simplifying coding and handling data with the MariaDB module.

spMariaDbConnectorPy

This Python package and its MariaDbConnector module use and thus builds on the functionality of the MariaDB module, which pip will automatically install with it.

While the MariaDB module provides great flexibility, it conceptually requires to first open a database connection, which is then used to open a cursor, which is then used to execute SQL statements.

Contrary to this, a MariaDbConnector object is created with the required information to manipulate data (host, port, user, password, database name). You can then directly use its execute() or executemany() methods for SQL statements. This automatically takes care of the opening and closing of connection and cursor objects.

Additionally MariaDbConnector provides a query() method specifically for SELECT statements, which allows access to the result set via fetch methods - same as with the MariaDB cursor object.

The MariaDbConnector therefore greatly simplifies coding for most applications. However, if needed you can also use its cursor() method to get a MariaDB cursor object and then write code with the same versatility of the MariaDB module.

To use the example script, setup a database named 'data_mdb_test' with a user 'test_user' and password 'test_user'. Alternatively use your own names and password and enter these in the config.ini file.

MySQL database compatibility: As stated in the documentation for the underlying MariaDB module, it supports MySQL databases. However, it may develop away from the common MySQL database functionality in future. Therefore, this code may work with MySQL databases today, but is not guaranteed to work in the future. Therefore if you must use a MySQL database, we recommend to carefully monitor the development of this and the MariaDB module and regularly check for compatibility of your application code.

Enjoy

 krokoreit
   

Installation

pip install spMariaDbConnectorPy

Usage & API

MariaDbConnector Class

Import the module and create a connector object:

  from spMariaDbConnectorPy import MariaDbConnector

  connector = MariaDbConnector('path/to/config_file', 'MariaDB')

To ensure a separation of settings and your code, the MariaDbConnector is initialized with a configuration file path and the section name, where database settings and credentials are stored. These data can therefore be located inside a general configuration file for your application, as long as they are provided within the given section name, e.g.:

[MariaDB]
# ip or localhost
host = localhost
port = 3306
db_name = data_mdb_test
username = test_user
password = test_user

API

Methods

• execute()
• executemany()
• query()
• fetchone()
• fetchmany()
• fetchall()
• begin()
• commit()
• rollback()
• connect()
• disconnect()
• cursor()
• get_rowcount()
• last_error()

execute() Method

  execute(sql, data: Sequence = (), prevent_auto_commit = False, buffered=None)

Calls cursor.execute(sql, data) and returns True for succesful execution. In case of failure, check last_error() for details about the error.
The execute() method automatically commits() a transaction unless using 'prevent_auto_commit = True' for the transaction to remain uncommitted for subsequent execute() methods like in the following example:

connector.begin()
try:
    sql = "DROP TABLE IF EXISTS `table_A`;"
    if not connector.execute(sql, prevent_auto_commit=True):
        raise Exception("Failed to drop table table_A")
    sql = "RENAME TABLE `table_B` TO `table_A`;"
    if not connector.execute(sql):
        raise Exception("Could not rename table table_B to table_A")
except Exception as e:
    connector.rollback()
    raise Exception('Error when handling tables: ' + + str(e))

The underlying cursor.execute() method always generates a buffered result unless the optional parameter 'buffered=False' is set.

⇧ back up to list of methods

executemany() Method

  executemany(sql, data, prevent_auto_commit = False)

Calls cursor.executemany(sql, data) and returns True for succesful execution. In case of failure, check last_error() for details about the error. Use 'prevent_auto_commit = True' for transactions to remain uncommitted.

⇧ back up to list of methods

query() Method

  query(sql, data: Sequence = (), buffered=None)

Creates a connection and cursor object before executing the sql query for results to be fetched by the fetch functions of this class - fetchone(), fetchmany() and fetchall().
Returns True for succesful execution. In case of failure, check last_error() for details about the error.
Must call disconnect() to close connection and cursor when no longer used.

⇧ back up to list of methods

fetchone() Method

  fetchone()

Calls cursor.fetchone() to fetch the next row of a query result set, returning a single sequence or None if no more data is available. None is also returned, when an error occurs, e.g. when the previous call to query() / execute() didn't produce a result set or query() / execute() wasn't called before. Therefore, when None is returned, check for 'last_error() is None' to confirm that there are really no more data available.

⇧ back up to list of methods

fetchmany() Method

  fetchmany(size: int = 0)

Calls cursor.fetchmany() to 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 size 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.

When an error occurs, e.g. the previous call to query() / execute() didn't produce a result set or query() / execute() wasn't called before, None is returned.

⇧ back up to list of methods

fetchall() Method

  fetchall()

Calls cursor.fetchall() to fetch all remaining rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

When an error occurs, e.g. the previous call to query() / execute() didn't produce a result set or query() / execute() wasn't called before, None is returned.

⇧ back up to list of methods

begin() Method

  begin()

Starts a new transaction which can be committed by commit() or cancelled by rollback().
Note that a transaction will be automatically committed by the execute() and executemany() methods, unless 'prevent_auto_commit = True' is set.

⇧ back up to list of methods

commit() Method

  commit()

Commits a pending transaction to the database.

⇧ back up to list of methods

rollback() Method

  rollback()

Rolls back a transaction started with begin().

⇧ back up to list of methods

connect() Method

  connect()

Optional method to keep a connection 'open' when executing SQL statements. The execute() and executemany() methods will automatically open and close a connection. However, when running many consecutive execute statements, a performance benefit can be achieved by calling connect() before and disconnect() afterwards.

⇧ back up to list of methods

disconnect() Method

  disconnect()

For closing a connection after query() and fetching the data or when having called connect() before execute() / executemany().

⇧ back up to list of methods

cursor() Method

  cursor(cursorclass=mariadb.cursors.Cursor, **kwargs)

Creates a connection and returns a cursor to be used directly with the MariaDB cursor functions (i.e. not using functions of this class). The application must call disconnect() to close connection and the cursor when no longer used.

⇧ back up to list of methods

get_rowcount() Method

  get_rowcount()

Returns the number of rows held by the cursor after running execute statements.

⇧ back up to list of methods

last_error() Method

  last_error()

Returns the last error from a previous operation.

⇧ back up to list of methods

License

MIT license
Copyright © 2025 by krokoreit