Database Output

Similar to the TeX Output, you can use a real relational database as a backend to hold your data. Databases and tables within those databases can be used output, as well as input parameters. There are different helpers that make it easy to insert data into those databases. When data is inserted into a database the data strictly connected with the run experiment. This makes it at all points clear, where data comes from and where it should go.

Please be aware, that this database interface is very very simple. If you need some more sophisticated stuff, please use a real database abstraction layer (e.g. with an object relation manager) like SQLAlchemy.

The usage of the database package is straightforward. The usage of a database table as an output is shown as an example:

class SimpleExperiment(Experiment):
  outputs = {'table1': TableDict(),
             "table2": Table([("foo", "integer")], db = Database(path="foobar.db"))}

  def run(self):
      self.table1["key1"] = "value1"
      self.table1["bar"]  = "foo"

      self.table2.insert( foo = 23 )
      self.table2.insert( {"foo": 23} )

In this example two tables and two sqlite databases are created in the output directory. The first is where table1 is located, it has the implicit default name “sqlite3.db”. The contents of the database is:

metadata table

experiment (text)	metadata (text)
SimpleExperiment-db3ec040e20dfc657da…	{‘experiment-version’: 1,

SimpleExperiment__table1

experiment (text)	key (text)	value (text)
SimpleExperiment-db3ec040e20dfc657da…	key1	value1
SimpleExperiment-db3ec040e20dfc657da…	bar	foo

The foobar.db is constructed similar. But the SimpleExperiment__table2 there has only one column of type integer.

If you have many sqlite files from different experiments. They can easily be merged by using the merge tool:

$ python -m versuchung.database target.db source1.db source2.db

Additionally to SQLite3 databases, also a MySQL database can be used. But this feature is not very well tested yet.

versuchung.database.Database(database_type='sqlite', *args, **kwargs)

This is a just a wrapper around the supported database abstraction classes. Every other argument and paramater than database_type is forwared directly to those classes.

Supported database_type abstractions are at the moment:

• sqlite – Database_SQLite
• mysql – Database_MySQL

class versuchung.database.Database_MySQL(database=None, host='localhost', user=None, password=None)

Can be used as input parameter and output parameter

A database backend class for a MySQL database.

create_table(name, fields=[('key', 'text'), ('value', 'text')], keys=None, conflict_strategy=None)

Creates a new table in the database. name is the name of newly created table. The fields are a list of columns. A column is a tuple, its first entry is the name, its second entry the column type. If primary is the name of a column this column is marked as the primary key for the table.

conflict_strategy is ignored!

execute(command, *args)

Execute command including the arguments on the sql handle. Question marks in the command are replaces by the *args:

>>> database.execute("SELECT * FROM metadata WHERE experiment = ?", identifer)

handle

Returns:handle – MySQLdb database handle

install_my_cnf()

Creates a my.cnf file and sets the environment variable MYSQL_HOME

class versuchung.database.Database_SQLite(path='sqlite3.db')

Can be used as input parameter and output parameter

A database backend class for sqlite3 database.

create_table(name, fields=[('key', 'text'), ('value', 'text')], keys=None, conflict_strategy='REPLACE')

Creates a new table in the database. name is the name of newly created table. The fields are a list of columns. A column is a tuple, its first entry is the name, its second entry the column type. If primary is the name of a column this column is marked as the primary key for the table.

execute(command, *args)

Execute command including the arguments on the sql handle. Question marks in the command are replaces by the *args:

>>> database.execute("SELECT * FROM metadata WHERE experiment = ?", identifer)

handle

Returns:handle – sqlite3 database handle

path

Returns:string – path to the sqlite database file

class versuchung.database.Table(fields, keys=None, db=None, conflict_strategy='FAIL')

Can be used as input parameter and output parameter

A versuchung table is a table that is aware of experiments. It stores for each dataset the experiment it originates from. The field list consists either of plain strings, then the column type is text. If it’s a tuple the first entry is the name and the second its type:

>>> [("foo", "integer"), "barfoo"]

This will result in two columns, one with type integer and one with type text. If a db is given this one is used instead of a default sqlite database named sqlite3.db

To make a set of field the index keys (UNIQUE), give it as a list of string as keys argument. The conflict_strategy gives the SQL strategy what to do on conflict. If you want to merge databases from multiple experiments without triggering a conflict if the given key set is equal (i.e., if you want the same values in the columns given as keys to be treated different when coming from different experiments), add experiment to the key set.

clear()

Remove all entries associated with the current running experiment

database

Returns:Database – the database the table is located in

insert(data=None, **kwargs)

Insert a dict of data into the database table

table_name

Returns:string – return the name of the table in the database

value

The value of the table. It returns a tuple. The first entry is a tuple of column headings. The second entry is a list of rows, in the same order as the column headings. The column that associates the entry with the experiment is stripped apart and only data for the static enclosing experiment is shown.

class versuchung.database.TableDict(db=None)

Can be used as input parameter and output parameter

This uses a Table as a backend for a python dict. This object can be used in the same way PgfKeyDict is used. Please be aware, that the storage and retrieval of keys from the associated table is done lazy. Therefore the data is only then visible if the experiment was successful.

flush()

Save the current dict content to the database.

insert(*args, **kwargs)

Insert a dict of data into the database table