data subpackage

data.sqldb module

MySQL and TDMS Integration.

Functions

  • add_tdms_file – Insert tdms files into the MySQL database.
  • add_tube_info – Add test-independant Tube information.
  • connect – Get a connection and cursor to a MySQL database.
  • create_tables – Create tables in the database.
  • get_high_low_testids – Get Low and High RH TestIds for a p and t setting.
  • get_rht_results Get DataFrame of evap reate and RH results for a TestId.
  • get_test_dict – Create DataFrame representations of the tests.
  • add_analysis – Pull, analyze, and insert analysis results into database.

Todo

Decouple database and tdms volatility via modulde encapsulation.

chamber.data.sqldb.add_analysis(cnx, test_id, steps=1)[source]

Pull, analyze, and insert analysis results into MySQL database.

Uses experiments module to analyze a DataFrame of data stored in MySQL database. Uses add_rh_targets and add_results to populate MySQL databse RHTargest and Results tables.

Parameters:
  • cnx (mysql.connector.connection.MySQLConnection) – Connection to MySQL database.
  • test_id (int) – TestID for the MySQL database, which is the primary key for the Test table.
  • steps (int) – The step size for Chi2 analysis. Defaults to 1.
Returns:

True if sucessful, else None.
Return type:

True or None

Examples

Add analysis results for an existing TestId.

>>> cnx = connect('my-schema')
>>> test_id = 1
>>> add_analysis(cnx, test_id, steps=2, nu_min=150)
True
chamber.data.sqldb.add_tdms_file(cnx, tdms_obj)[source]

Insert tdms files into the MySQL database.

Uses loops to structure calls to add_setting, add_test, add_obs, and add_temp to build and execute queries that populate the MySQL database for a single tdms_obj.

Parameters:
  • cur (mysql.connector.crsor.MySqlCursor) – Cursor for MySQL database.
  • tdms_obj (nptdms.TdmsFile) – Object containg the data from the tdms test file. Original tdms files were created from UCSD Chamber experiments in the Coimbra Lab in SERF 159.
Returns:

True if sucessful, else None.
Return type:

True or None

Examples

Add a tdms file to a database using a MySQL cursor:

>>> import nptdms
>>> tdms_file = nptdms.TdmsFile('my-file.tdms')
>>> cnx = connect('my-schema')
>>> assert add_tdms_file(cnx, tdms_obj)
chamber.data.sqldb.add_tube_info(cur)[source]

Use a MySQL cursor to add test-independant Tube info.

Uses cursor .execute function on the ADD_TUBE and TUBE_DATA. Adds the new Tube if the Tube doesn’t exist. If the Tube already exists, then the function does nothing.

Parameters:cur (mysql.connector.cursor.MySqlCursor) – Cursor for MySQL database.
Returns:True if tube added, False if tube already exists in database.
Return type:bool

Examples

Add the tube for the first time:

>>> cnx = connect('my-schema')
>>> cur = cnx.cursor()
>>> assert add_tube_info(cur)
Tube added.

Now add it again:

>>> assert not add_tube_info(cur)
Tube already exists.
chamber.data.sqldb.connect(database)[source]

Use config file to return connection and cursor to a MySQL database.

The host, username, and password are all stored in config.ini in the root of the repository. Make sure to edit this file so that it contains your information.

Parameters:database (str) – Name of the database for which to return a cursor.
Returns:cnx – Connection to MySQL database.
Return type:mysql.connector.connection.MySQLConnection

Examples

Obtain a connection and cursor to a schema named ‘test’ using built in config.ini:

>>> cnx = connect('test')
>>> type(cnx)
<class 'mysql.connector.connection.MySQLConnection'>
chamber.data.sqldb.create_tables(cur, tables)[source]

Create tables in the database.

Uses a list of tuples where the 0 index is the name of the table and the 1 index is a string of MySQL DDL used to create the table. A list is required so that the DDL can be executed in order to avoid foreign key constraint errors.

Parameters:
  • cur (mysql.connector.crsor.MySqlCursor) – Cursor for MySQL database.
  • tables (list of tuple of (str, str)) – List of tuples of table names and DDL query language. For example: [(‘UnitTest’, “CREATE TABLE UnitTest (” ” UnitTestID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,” ” Number DECIMAL(5,2) NULL,” ” String VARCHAR(30) NULL,” ” PRIMARY KEY (UnitTestID)” “);”))]
Returns:

True if successful.
Return type:

bool

Examples

Create tables in the MySQL database:

>>> cnx = connect('my-schema')
>>> cur = cnx.cursor()
>>> assert create_tables(cur, TABLES)
Setting up tables...
Setting  OK
Tube  OK
Test  OK
Observation  OK
TempObservation  OK
Unit  OK
chamber.data.sqldb.get_rht_results(cnx, test_id)[source]

Get a DataFrame of evaporation rate and RH results for a TestId.

Use Pandas read_sql functionality and a MySQLConnection object to pull the RH, SigRh, B, SigB results stored in the RHTargets and Resutls tables in the MySql database.

Parameters:
  • cnx (mysql.connector.connection.MySQLConnection) – Connection to MySQL database.
  • test_id (int) – TestID for the MySQL database, which is the primary key for the Test table.
Returns:

DataFrame with columns [‘RH’, ‘SigRH’, ‘B’, ‘SigB’]
Return type:

DataFrame

Examples

>>> cnx = connect('my-schema')
>>> test_id = 1
>>> get_rht_resuts(cnx, test_id)
    RH     SigRH             B          SigB
0   0.10  0.001920 -6.108320e-09  4.862530e-11
1   0.15  0.002802 -5.184070e-09  1.725580e-11
2   0.20  0.003628 -4.707150e-09  3.329170e-12
3   0.25  0.004452 -4.396130e-09  2.163040e-12