The database_saver module

Autogenerated API documentation for database_saver

Classes for saving coninuous data and data sets to a database

PyExpLabSys.common.database_saver.HOSTNAME = u''

Hostname of the database server

PyExpLabSys.common.database_saver.DATABASE = u'cinfdata'

Database name

class PyExpLabSys.common.database_saver.CustomColumn(value, format_string)

Bases: tuple


Alias for field number 1


Alias for field number 0

class PyExpLabSys.common.database_saver.DataSetSaver(measurements_table, xy_values_table, username, password, measurement_specs=None)[source]

Bases: object

A class to save a measurement


Mapping of codenames to measurements ids


The measurements tables


The x, y values tables


The SqlSaver used to save points


The query used to insert a measurement


The query used to insert a point


The query used to insert a batch of points


The database connection used to register new measurements

Type:MySQLdb connection

The database cursor used to register new measurements

Type:MySQLdb cursor
__init__(measurements_table, xy_values_table, username, password, measurement_specs=None)[source]

Initialize local parameters

  • measurements_table (str) – The name of the measurements table
  • xy_values_table (str) – The name of the xy values table
  • username (str) – The database username
  • passwork (str) – The database password
  • measurement_specs (sequence) – A sequence of measurement_codename, metadata pairs, see below

measurement_specs is used if you want to initialize all the measurements at __init__ time. You can also do it later with add_measurement(). The expected value is a sequence of measurement_codename, metadata e.g:

measurement_specs = [
    ['M2', {'type': 5, 'timestep': 0.1, 'mass_label': 'M2M'}],
    ['M28', {'type': 5, 'timestep': 0.1, 'mass_label': 'M2M'}],
    ['M32', {'type': 5, 'timestep': 0.1, 'mass_label': 'M2M'}],

As above, the expected metadata is simply a mapping of column names to column values in the measurements_table.

Per default, the value will be put into the table as is. If it is necessary to do SQL processing on the value, to make it fit the column type, then the value must be replaced with a CustomColumn instance, whose arguments are the value and the format/processing string. The format/processing string must contain a ‘%s’ as a placeholder for the value. It could look like this:

measurement_specs = [
    ['M2', {'type': 5, 'time': CustomColumn(M2_timestamp, 'FROM_UNIXTIME(%s)')}],
    ['M28', {'type': 5, 'time': CustomColumn(M28_timestamp, 'FROM_UNIXTIME(%s)')}],

The most common use for this is the one shown above, where the time column is of type timestamp and the time value (e.g. in M2_timestamp) is a unix timestamp. The unix timestamp is converted to a SQL timestamp with the FROM_UNIXTIME SQL function.


The SQL timestamp column understand the datetime.datetime type directly, so if the input timestamp is already on that form, then there is no need to convert it

add_measurement(codename, metadata)[source]

Add a measurement

This is equivalent to forming the entry in the measurements table with the metadata values and saving the id of this entry locally for use with add_point().

  • codename (str) – The codename that this measurement should have
  • metadata (dict) – The dictionary that holds the information for the measurements table. See __init__() for details.
save_point(codename, point)[source]

Save a point for a specific codename

  • codename (str) – The codename for the measurement to add the point to
  • point (sequence) – A sequence of x, y
save_points_batch(codename, x_values, y_values, batchsize=1000)[source]

Save a number points for the same codename in batches

  • codename (str) – The codename for the measurement to save the points for
  • x_values (sequence) – A sequence of x values
  • y_values (sequence) – A sequence of y values
  • batchsize (int) – The number of points to send in the same batch. Defaults to 1000, see the warning below before changing it


The batchsize is ultimately limited by the max package size that the MySQL server will receive. The default is 1MB. Each point amounts to around 60 bytes in the final query. Rounding this up to 100, means that the limit is ~10000 points. This means that the default of 1000 should be safe and that if it is changed by the user, expect problems if exceeding the lower 10000ths.


Return a set of unique column values from the measurements database

This is commonly used in fileparsers to identify the files already uploaded

Parameters:column (str) – The column specification to extract values from. This can be just a column name e.g. “time”, but it is also allowed to contain SQL processing e.g. UNIX_TIMESTAMP(time). The value of column will be formatted directly into the query.

Start the DataSetSaver

And the underlying SqlSaver.


Stop the MeasurementSaver

And shut down the underlying SqlSaver instance nicely.


Wait for the query queue in the SqlSaver to empty

This purpose of this method is to avoid usgin too much memory when uploading large amount of data.

class PyExpLabSys.common.database_saver.ContinuousDataSaver(continuous_data_table, username, password, measurement_codenames=None)[source]

Bases: object

This class saves data to the database for continuous measurements

Continuous measurements are measurements of a single parameters as a function of datetime. The class can ONLY be used with the new layout of tables for continous data, where there is only one table per setup, as apposed to the old layout where there was one table per measurement type per setup. The class sends data to the hostname and database named in HOSTNAME and DATABASE respectively.

__init__(continuous_data_table, username, password, measurement_codenames=None)[source]

Initialize the continous logger

  • continuous_data_table (str) – The contunuous data table to log data to
  • username (str) – The MySQL username
  • password (str) – The password for username in the database
  • measurement_codenames (sequence) – A sequence of measurement codenames that this logger will send data to. These codenames can be given here, to initialize them at the time of initialization or later by the use of the add_continuous_measurement() method.


The codenames are the ‘official’ codenames defined in the database for contionuous measurements NOT codenames that can be userdefined


Add a continuous measurement codename to this saver

Parameters:codename (str) – Codename for the measurement to add


The codenames are the ‘official’ codenames defined in the database for contionuous measurements NOT codenames that can be userdefined

save_point_now(codename, value)[source]

Save a value and use now (a call to time.time()) as the timestamp

  • codename (str) – The measurement codename that this point will be saved under
  • value (float) – The value to be logged

The Unixtime used

Return type:


save_point(codename, point)[source]

Save a point

  • codename (str) – The measurement codename that this point will be saved under
  • point (sequence) – The point to be saved, as a sequence of 2 floats: (x, y)

Starts the underlying SqlSaver


Stop the ContiniousDataSaver

And shut down the underlying SqlSaver instance nicely.

class PyExpLabSys.common.database_saver.SqlSaver(username, password, queue=None)[source]

Bases: threading.Thread

The SqlSaver class administers a queue from which it executes SQL queries


In general queries are added to the queue via the enqueue_query() method. If it is desired to add elements manually, remember that they must be on the form of a (query, query_args) tuple. (These are the arguments to the execute method on the cursor object)


The queue the queries and qeury arguments are stored in. See note below.


The number of commits the saver has performed


The timespan the last commit took


The MySQLdb database connection

Type:MySQLdb connection

The MySQLdb database cursor

Type:MySQLdb cursor
__init__(username, password, queue=None)[source]

Initialize local variables

  • username (str) – The username for the MySQL database
  • password (str) – The password for the MySQL database
  • queue (Queue.Queue) – A custom queue to use. If it is left out, a new Queue.Queue object will be used.

Add stop word to queue to exit the loop when the queue is empty

enqueue_query(query, query_args=None)[source]

Enqueue a qeury and arguments

  • query (str) – The SQL query to be executed
  • query_args (sequence or mapping) – Optional sequence or mapping of arguments to be formatted into the query. query and query_args in combination are the arguments to cursor.execute.

Execute SQL inserts from the queue until stopped


Wait for the queue to empty

This purpose of this method is to avoid using too much memory when uploading large amount of data.


Run the module to perform elementary functional test