pwtools.sql.sql_matrix

pwtools.sql.sql_matrix(lists, header=None, colnames=None, sqlval_funcs=None, fileval_funcs=None)[source]

Convert each entry in a list of lists (“matrix” = sql table) to an SQLEntry based on header. This can be used to quickly convert the result of comb.nested_loops() (nested lists) to input params_lst for ParameterStudy.

The entries in the lists can have arbitrary values, but each “column” should have the same type. Each sublist (= row) can be viewed as a record in an sql database, each column as input for sql_column().

If you provide header, then tyes for each column are taken from that. If colnames are used, then types are fetched (by find_sqltype()) from the first row. May not work for “incomplete” datasets, where some entries in the first row are None (NULL in sqlite).

Parameters:

  • lists (list of lists)

  • header (sequence, optional) – [(‘foo’, ‘integer’), (‘bar’, ‘float’), …], see sql.SQLiteDB

  • colnames (sequence os strings, optional) – Use either colnames or header.

  • sqlval_funcs ({None, dict}) – For certain (or all) columns, you can specify a sqlval_func / fileval_func. They have the same meaning as in sql_column(). E.g. sql_matrix(…, fileval_funcs={‘foo’: lambda x: str(x)+’-value’}) would set fileval_func for the whole column ‘foo’.

  • fileval_funcs ({None, dict}) – For certain (or all) columns, you can specify a sqlval_func / fileval_func. They have the same meaning as in sql_column(). E.g. sql_matrix(…, fileval_funcs={‘foo’: lambda x: str(x)+’-value’}) would set fileval_func for the whole column ‘foo’.

Return type:

list of lists

Examples

>>> lists=comb.nested_loops([[1.0,2.0], zip(['a']*2, [777,888])],flatten=True)
>>> lists
[[1.0, 'a', 777],
 [1.0, 'a', 888],
 [2.0, 'a', 777],
 [2.0, 'a', 888]]
>>> # use explicit header
>>> header=[('col0', 'float'), ('col1', 'text'), ('col2', 'integer')]
>>> m=sql.sql_matrix(lists, header=header)
>>> for row in m: print([(xx.key, xx.fileval, xx.sqltype)) for xx in row]
[('col0', 1.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 777, 'INTEGER')]
[('col0', 1.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 888, 'INTEGER')]
[('col0', 2.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 777, 'INTEGER')]
[('col0', 2.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 888, 'INTEGER')]
>>> # use colnames -> automatic sqltype detected, also use fileval_funcs
>>> m=sql.sql_matrix(lists, colnames=['col0','col1','col2'], fileval_funcs={'col0': lambda x: x*100})
>>> for row in m: print([(xx.key, xx.fileval, xx.sqltype)) for xx in row]
[('col0', 100.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 777, 'INTEGER')]
[('col0', 100.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 888, 'INTEGER')]
[('col0', 200.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 777, 'INTEGER')]
[('col0', 200.0, 'REAL'), ('col1', 'a', 'TEXT'), ('col2', 888, 'INTEGER')]