pyiron_base.database.generic module
DatabaseAccess class deals with accessing the database
- class pyiron_base.database.generic.AutorestoredConnection(engine, timeout=60)
Bases:
object
- close()
- commit()
- execute(*args, **kwargs)
- execute_once(*args, **kwargs)
- class pyiron_base.database.generic.ConnectionWatchDog(conn, lock, timeout=60)
Bases:
Thread
Helper class that closes idle connections after a given timeout.
Initialize it with the connection to watch and a lock that protects it. The lock prevents the watchdog from killing a connection that is currently used. The timeout is in seconds.
>>> conn = SqlConnection(...) >>> lock = threading.Lock() >>> dog = ConnectionWatchDog(conn, lock, timeout=60)
After it is created, :method:`.kick()` the watchdog periodically before the timeout runs out. It is important to acquire the lock when using the connection object.
>>> dog.kick() >>> with lock: ... conn.execute(...) >>> dog.kick()
Once you want to finish the connection or want to make sure the watchdog quit, call :method:`.kill()` to shut it down. This also causes the watch dog to try and close the connection.
>>> dog.kill()
- kick()
Restarts the timeout.
- kill()
Stop the watchdog and close the connection.
- run()
Starts the watchdog.
- class pyiron_base.database.generic.DatabaseAccess(connection_string, table_name, timeout=60)
Bases:
IsDatabase
A core element of PyIron, which generally deals with accessing the database: getting, sending, changing some data to the db.
- Parameters:
connection_string (str) – SQLalchemy connection string which specifies the database to connect to typical form: dialect+driver://username:password@host:port/database example: ‘postgresql://scott:tiger@cmcent56.mpie.de/mdb’
table_name (str) – database table name, a simple string like: ‘simulation’
Murat Han Celik
- add_column(col_name, col_type)
Add an additional column - required for modification on the database
- Parameters:
col_name (str, list) – name of the new column, normal string like: ‘myColumn’
(str (col_type) – SQL type of the new column, SQL type like: ‘varchar(50)’
list – SQL type of the new column, SQL type like: ‘varchar(50)’
Returns:
- add_item_dict(par_dict)
Create a new database item
- Parameters:
par_dict (dict) –
- Dictionary with the item values and column names as keys, like:
{‘chemicalformula’: ‘BO’,
’computer’: ‘localhost’, ‘hamilton’: ‘VAMPS’, ‘hamversion’: ‘1.1’, ‘job’: ‘testing’, ‘subjob’ : ‘SubJob’, ‘parentid’: 0L, ‘myCol’: ‘Blubbablub’, ‘project’: ‘database.testing’, ‘projectpath’: ‘/root/directory/tmp’, ‘status’: ‘KAAAA’, ‘timestart’: datetime(2016, 5, 2, 11, 31, 4, 253377), ‘timestop’: datetime(2016, 5, 2, 11, 31, 4, 371165), ‘totalcputime’: 0.117788, ‘username’: ‘Test’}
- Returns:
Database ID of the item created as an int, like: 3
- Return type:
int
- change_column_type(col_name, col_type)
Modify data type of an existing column - required for modification on the database
- Parameters:
col_name (str, list) – name of the new column, normal string like: ‘myColumn’
(str (col_type) – SQL type of the new column, SQL type like: ‘varchar(50)’
list – SQL type of the new column, SQL type like: ‘varchar(50)’
Returns:
- delete_item(item_id: int)
Delete Item from database
- Parameters:
item_id (int) – Databse Item ID (Integer), like: 38
Returns:
- get_item_by_id(item_id)
Get item from database by searching for a specific item Id.
- Parameters:
item_id (int) – Databse Item ID (Integer), like: 38
- Returns:
- Dictionary where the key is the column name, like:
- {‘chemicalformula’: u’BO’,
’computer’: u’localhost’, ‘hamilton’: u’VAMPS’, ‘hamversion’: u’1.1’, ‘id’: 1, ‘job’: u’testing’, ‘masterid’: None, ‘parentid’: 0, ‘project’: u’database.testing’, ‘projectpath’: u’/root/directory/tmp’, ‘status’: u’KAAAA’, ‘subjob’: u’SubJob’, ‘timestart’: datetime.datetime(2016, 5, 2, 11, 31, 4, 253377), ‘timestop’: datetime.datetime(2016, 5, 2, 11, 31, 4, 371165), ‘totalcputime’: 0.117788, ‘username’: u’Test’}
- Return type:
dict
- get_items_dict(item_dict, return_all_columns=True)
- Parameters:
item_dict (dict) –
a dict type, which has a certain syntax for this function: a normal dict like {‘hamilton’: ‘VAMPE’, ‘hamversion’: ‘1.1’} has similarities with a simple query like
select * from table_name where hamilton = ‘VAMPE AND hamversion = ‘1.1’
as seen it puts an AND for every key, value combination in the dict and searches for it.
another syntax is for an OR statement, simply: {‘hamilton’: [‘VAMPE’, ‘LAMMPS’]}, the query would be:
select * from table_name where hamilton = ‘VAMPE’ OR hamilton = ‘LAMMPS’
- and lastly for a LIKE statement, simply: {‘project’: ‘database.%’}, the query would be
select * from table_name where project LIKE ‘database.%’
that means you can simply add the syntax for a like statement like ‘%’ and it will automatically operate a like-search
- of course you can also use a more complex select method, with everything in use:
- {‘hamilton’: [‘VAMPE’, ‘LAMMPS’],
’project’: ‘databse%’, ‘hamversion’: ‘1.1’}
- select * from table_name where (hamilton = ‘VAMPE’ Or hamilton = ‘LAMMPS’) AND
(project LIKE ‘database%’) AND hamversion = ‘1.1’
return_all_columns (bool) – return all columns or only the ‘id’ - still the format stays the same.
- Returns:
- the function returns a list of dicts like get_items_sql, but it does not format datetime:
- [{‘chemicalformula’: u’Ni108’,
’computer’: u’mapc157’, ‘hamilton’: u’LAMMPS’, ‘hamversion’: u’1.1’, ‘id’: 24, ‘job’: u’DOF_1_0’, ‘parentid’: 21L, ‘project’: u’lammps.phonons.Ni_fcc’, ‘projectpath’: u’D:/PyIron/PyIron_data/projects’, ‘status’: u’finished’, ‘timestart’: datetime.datetime(2016, 6, 24, 10, 17, 3, 140000), ‘timestop’: datetime.datetime(2016, 6, 24, 10, 17, 3, 173000), ‘totalcputime’: 0.033, ‘username’: u’test’},
- {‘chemicalformula’: u’Ni108’,
’computer’: u’mapc157’, ‘hamilton’: u’LAMMPS’, ‘hamversion’: u’1.1’, ‘id’: 21, ‘job’: u’ref’, ‘parentid’: 20L, ‘project’: u’lammps.phonons.Ni_fcc’, ‘projectpath’: u’D:/PyIron/PyIron_data/projects’, ‘status’: u’finished’, ‘timestart’: datetime.datetime(2016, 6, 24, 10, 17, 2, 429000), ‘timestop’: datetime.datetime(2016, 6, 24, 10, 17, 2, 463000), ‘totalcputime’: 0.034, ‘username’: u’test’},…….]
- Return type:
list
- get_items_sql(where_condition=None, sql_statement=None)
Submit an SQL query to the database
- Parameters:
where_condition (str) – SQL where query, query like: “project LIKE ‘lammps.phonons.Ni_fcc%’”
sql_statement (str) – general SQL query, normal SQL statement
- Returns:
- get a list of dictionaries, where each dictionary represents one item of the table like:
- [{u’chemicalformula’: u’BO’,
u’computer’: u’localhost’, u’hamilton’: u’VAMPS’, u’hamversion’: u’1.1’, u’id’: 1, u’job’: u’testing’, u’masterid’: None, u’parentid’: 0, u’project’: u’database.testing’, u’projectpath’: u’/TESTING’, u’status’: u’KAAAA’, u’subjob’: u’testJob’, u’timestart’: u’2016-05-02 11:31:04.253377’, u’timestop’: u’2016-05-02 11:31:04.371165’, u’totalcputime’: 0.117788, u’username’: u’User’},
- {u’chemicalformula’: u’BO’,
u’computer’: u’localhost’, u’hamilton’: u’VAMPS’, u’hamversion’: u’1.1’, u’id’: 2, u’job’: u’testing’, u’masterid’: 0, u’parentid’: 0, u’project’: u’database.testing’, u’projectpath’: u’/TESTING’, u’status’: u’KAAAA’, u’subjob’: u’testJob’, u’timestart’: u’2016-05-02 11:31:04.253377’, u’timestop’: u’2016-05-02 11:31:04.371165’, u’totalcputime’: 0.117788, u’username’: u’User’}…..]
- Return type:
list
- get_job_status(job_id)
- get_job_working_directory(job_id)
- query_for_element(element)
- static regexp(expr, item)
Regex function for SQLite :param expr: str, regex expression :param item: str, item which needs to be checked
Returns:
- property view_mode
Get view_mode - if view_moded is enable pyiron has read only access to the database.
Some implementations do not allow to set this value.
- Returns:
True when view_mode is enabled
- Return type:
bool
- property viewer_mode
Get view_mode - if view_moded is enable pyiron has read only access to the database.
Some implementations do not allow to set this value.
- Returns:
True when view_mode is enabled
- Return type:
bool