The database_saver module

Autogenerated API documentation for database_saver

Classes for saving coninuous data and data sets to a database

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

Bases: tuple

format_string

Alias for field number 1

value

Alias for field number 0

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

Bases: object

A class to save a measurement

measurement_ids

Mapping of codenames to measurements ids

Type:dict

measurements_table

The measurements tables

Type:str

xy_values_table

The x, y values tables

Type:str

sql_saver

The SqlSaver used to save points

Type:SqlSaver

insert_measurement_query

The query used to insert a measurement

Type:str

insert_point_query

The query used to insert a point

Type:str

insert_batch_query

The query used to insert a batch of points

Type:str

connection

The database connection used to register new measurements

Type:MySQLdb connection

cursor

The db cursor used to register new measurements

Type:MySQLdb cursor

__init__(measurements_table, xy_values_table, username, password, measurement_specs=None)

Initialize local parameters

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.

Note

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)

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

Parameters:

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

Save a point for a specific codename

Parameters:

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

Save a number points for the same codename in batches

Parameters:

• 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

Warning

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.

get_unique_values_from_measurements(column)

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

Start the DataSetSaver

And the underlying SqlSaver.

stop()

Stop the MeasurementSaver

And shut down the underlying SqlSaver instance nicely.

wait_for_queue_to_empty()

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)

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 SETTINGS.sql_server_host and SETTINGS.sql_database respectively.

__init__(continuous_data_table, username, password, measurement_codenames=None)

Initialize the continous logger

Parameters:

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

Note

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

add_continuous_measurement(codename)

Add a continuous measurement codename to this saver

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

Note

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

save_point_now(codename, value)

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

Parameters:

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

Returns:

The Unixtime used

Return type:

float

save_point(codename, point)

Save a point

Parameters:

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

start()

Starts the underlying SqlSaver

stop()

Stop the ContiniousDataSaver

And shut down the underlying SqlSaver instance nicely.

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

Bases: threading.Thread

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

Note

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)

queue

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

Type:Queue.Queue

commits

The number of commits the saver has performed

Type:int

commit_time

The timespan the last commit took

Type:float

connection

The MySQLdb database connection

Type:MySQLdb connection

cursor

The MySQLdb database cursor

Type:MySQLdb cursor

__init__(username, password, queue=None)

Initialize local variables

Parameters:

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

stop()

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

enqueue_query(query, query_args=None)

Enqueue a qeury and arguments

Parameters:

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

run()

Execute SQL inserts from the queue until stopped

wait_for_queue_to_empty()

Wait for the queue to empty

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

PyExpLabSys.common.database_saver.run_module()

Run the module to perform elementary functional test